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@xxxxxxxxxxx> OpenPGP: 1024D/ED7DAEA6 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
