Hi,
I am using a Postgres 7.2.1 db to store binary data as large objects
which users can add or delete as they need. I have found that without
frequent vacuums of the database the disk space used by the database
grows very quickly, as users on average add and delete binary objects of
about 160K at a time. So I was trying to determine how often I should do
a vacuum in order to keep the database from growing too quickly. So I
ran a test where I did a full vacuum of the database to compact the
tables, then I added and deleted 12 large objects of 80K each and then
did a vacuum, not a full vacuum. I did this add/delete/vacuum process 4
times in a row. I thought that by adding and deleting the same objects
and then vacuuming, the database shouldn't grow, as the new inserts
would reuse the space taken up by the deleted objects after the vacuum.
However, I was seeing the database grow each time. Here are the disk
usage values after each step:
After initial vacuum full:
bash-2.05b# du -b -s /data/sql
56664064 /data/sql
After first add/delete/vacuum:
bash-2.05b# du -b -s /data/sql
56987648 /data/sql
323584 byte increase
After second add/delete/vacuum:
bash-2.05b# du -b -s /data/sql
57012224 /data/sql
24576 byte increase
After third add/delete/vacuum:
bash-2.05b# du -b -s /data/sql
57061376 /data/sql
49152 byte increase
After fourth add/delete/vacuum:
bash-2.05b# du -b -s /data/sql
57085952 /data/sql
24576 byte increase
Is this expected behavior? As at some point in time, if I carry on
repeating this test, I would have to do a vacuum full to retrieve disk
space, even though the actual contents of the database has not increased
from the initial starting point.
Chris White