I've got two PostgreSQL databases that have been created using the same sql file. One of them is 2GB larger. Can someone help me figure out why? I'm sure the databases have the same row counts, tables, indexes, etc.. The databases are on different servers, there are small differences in the postgresql.conf and the PostgreSQL version. Would something there change size on disk?
A:
Try using the VACUUM command on PostgreSQL to free formerly used space.
Henning
2008-10-22 15:36:16
"vacuumed" as in VACUUM FULL or as in just VACUUM?
Milen A. Radev
2008-10-22 23:06:03
I tried both. VACUUM FULL actually increased the size, but only by a very small amount. Thats another thing I can't figure out...
MarsCat
2008-10-23 17:10:31
A:
You mentioned they are on two different servers and two different versions. Is it possible you have a 32-bit OS and a 64-bit OS differences, or major version changes (because we don't know all the changes that the Postgresql team has made to the underlying data structures on disk). You may find it has something to do with internal formats of indexes that are optimized for a particular machine's OS/CPU features, etc.
My comments are purely speculation, I have no data to back up my 'claims'.
Redbeard 0x0A
2008-10-22 17:51:11
Yes, one is 32 bit and one is 64 bit. The version differences are minor (8.1.3 and 8.1.9). I suppose either one of these could be possible, but I can't find any info to back it up either. Thanks though...
MarsCat
2008-10-23 17:19:05
A:
In PostgreSQL, any single table, index or log is stored as a separate file. A complex database can have thousands of files. Maybe the difference is due to different block size ore cluster size in the server filesystems.
Andrea Bertani
2008-12-04 18:00:44