Today I did a backup, dump, drop, create and restore on a db which
seemed to be taking up too much disk space after a vacuum full.
The db was using about six gigs (as backed up) and now uses about two
thirds of a gig after the restore (plus many inserts and a few deletes).
Right after the restore the db took up less than ten percent as much
space as the backup. (For the backup I stopped pg and used rsync on
the /var/lib/postgresql/data directory.)
Why was the db using that extra five plus gigs?
Some more background on the table:
Some time back I did alter most of the tables to replace various int8
columns with int4. When I did that the disk space used (after a vacuum
full) was reduced almost in half; it didn't appear that anything failed
to be reclaimed....
When I did that int8 to int4 change, the table ended up at about 12 or
13 gigs. That was for about 250k to 300k entries, where each entry
generates one row in a few tables, two rows (including an auto-toasted
bytea column) in one table and usually around 50 rows in another table.
The other day, while doing some testing, I had stopped the incoming
application and allowed all of the volatile data to expire. So the
only data in the db was in the non-volatile tables: about 7k rows
in two tables and about 1k in another, plus less than 6 in a couple
more. A vacuum full at that point got the table down to five and
a half gigs. Which is why I thought to try out a dump & restore,
though I didn't actually do so until it had been back in production
for a day or two.
-JimC
--
James Cloos <cloos@jhcloos.com> OpenPGP: 1024D/ED7DAEA6
