Question about DB VACUUM

From: "Chris White (cjwhite)" <cjwhite(at)cisco(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: Question about DB VACUUM
Date: 2003-10-02 18:39:14
Message-ID: 000b01c38914$7e11c1b0$5c976b80@amer.cisco.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Rob Long 2003-10-02 19:48:28 pg_restore fails postgres 7.3.4
Previous Message Marvin Gonzalez 2003-10-02 17:22:40 Current Computer Name