Space not clearing up!

[kslghost]

So my PT2 database reached about 7.3 GB over a ton of hands, so I did a purge of observed hands older than 3-4 months. It killed some 2.5 million hands (or was it 250k?), but it should be a huge amount of space. The amount of space PostgreSQL was taking up did not budge. I did a full vacuum (twice) and purged empty players, but no dice. How can I free up some space?

Thanks,

kslghost

[ptrack pat]

Doing a full vacuum/analyze should have freed up the disk space taken by the deleted records, I don't know why you wouldn't have recovered a fair amount of space if you deleted that many hands. Do you have more than one PostgreSQL database?

[kslghost]

I do, but they are miniscule in comparison. Do i have to full vacuum all of them?

[kslghost]

When I look at the database files in windows, I see the folder for my database "16400" which is 6.35GB. There are four files that are larger than one GB... they are 16440 and 16450 as well as their ".1" extensions.

Thanks for your quick response

[kslghost]

After restarting, I was able to clear up around 500MB by running another vacuum full. Is there a difference between Vacuum Full/Analyze and Vacuum Full alone? Also, would repeatedly running vacuums help clear additional space?

Finally, I read (doing a search on this forum) that hand histories databases aren't cleared in this process. Does that mean the 500,000 hands I purged still have their hand histories in the database? How can I get rid of those? Sorry for all the questions!

[ptrack pat]

The analyze part of the vacuum/analyze is, from what I understand about PostgreSQL, is updating the idexes in attempts to make querying the database faster. So that doesn't have anything to do with freeing up disk space.

The only way to clear out the hand_history data would be for you to write a SQL statement using pgAdmin that deletes the records in that table that no longer exist in the game table.

[kslghost]

I don't suppose you have this command written before, do you? Or could give me a clue on how to write it?

[ptrack pat]

This statement will probably take a very long time to run so be prepared for that. ALSO MAkE SURE YOU BACKUP YOUR DATABASE FIRST!!!

Open the pgAdmin program - Start > PostgreSQL 8.x > pgAdmin III

Then right-click on the red X at the top left part of the window and select Connect from the menu. Enter you p/w when prompted.

Then click on your database in the list of databases and then select the Tools > Query Tool menu option.

On the next window, copy/paste this text, then hit F5 to run it.

delete from hand_histories
where hand_histories.game_number not in
(select game.game_id from game
where game.game_number = hand_histories.game_number
and game.site_id = hand_histories.site_id
and game.import_date = hand_histories.import_date) ;

[kslghost]

Thank you for all the help, pat!