PostgreSQL is HUGE on my harddrive

[damien28173]

Hey, I read through 4 pages on the forum with nothing like this in the title.

I bought PT3 about a month ago. I have version 3.06.2
I bought 1 million hands a little while ago and imported them. (took forever) and then another million last night. clustering took nearly 7 hrs and ended up not getting finished. I tried to finish it tonight and again its taking forever, after clicking on the harddrive I noticed I only had 1.4 GB lefts on 75GB harddrive partition. after looking around I found the PostgreSQL folder and a properties search unveiled a whopping 25GB used space. Now I only successfully got 1 million hands plus maybe 300k more freebies but they folder is getting huge and bigger by the second (of clustering)

Is there a way to shrink this file, move it without losing all my info or am I just supposed to accept the fact that this file is now 15% of my total disk space and growing?

Is there any quick fixes or do I have to uninstall my PT3 files and PostgreSQL files and create a new partition?
I guess my main question is should this file really be 25 GB large with only 2 million 2.5 million hands tops in it?

[kraada]

Yes, PT3 databases take up a lot of space because they store a lot of data. Your database sounds about the right size for what you've imported, the rule of thumb that I use is 1M FR hands = 10G. It's not perfect but it's close.

The good news though is that large hard drives are fairly cheap - this 2TB HD is going for $100 and should work well. And a 2TB HD could hold a 200M hand database. (I'm sure you could find other drives that work equally well also, this is just an example.)

[damien28173]

Can you move your database without losing any information? do you need to repath anything?

[kraada]

The easiest way to move your database is probably to back it up (see our new Tutorial: Backup & Restore), then uninstall PostgreSQL, PT3, delete the PostgreSQL directory and PT3 configurations directory and reinstall PostgreSQL on your spare drive, then restore the database and everything else from the backup.

[mickle]

I've also got a large footprint on my hard drive. I have 196000 hands of cash and 175000 hands of tournament yet my postgreSQL folder is 4.25 GB in size. I'll do another maintenance run later today, but in the meantime I have noticed that there are lots of large files in C:\Program Files\PostgreSQL\8.3\data\base\1643. The names are 20861, 21197 etc. 8 of the files range from 220mb to 550mb. Is this what I should expect to be seeing?

[kraada]

Yes, that is normal; those files comprise your actual database, if you move or delete those files you will break your database.

The only files safe to remove are in the pg_log directory (not pg_clog or pg_xlog just pg_log).

[puyanwei]

Hi Kraada,

My database is also huge. 15GB but only 500k hands. I play PokerStars SNGs, not cash, and there's a few hands on Full Tilt where I don't play on anymore. I'm using a boot camped Macbook laptop (30BG HD), and I have an external hard drive but I don't want to put my database on there because it will slow down the data retrieval process of my HUD.

[kraada]

That does seem a bit larger than expected. Do you have any other programs that use PostgreSQL? For a database with that many hands I'd expect about half (or less) of the hard drive usage you're reporting.

[puyanwei]

Nope I have nothing else using postgres

[kraada]

Did you look to see how much space was being used by the pg_log directory?