Question about DB VACUUM
От | Chris White (cjwhite) |
---|---|
Тема | Question about DB VACUUM |
Дата | |
Msg-id | 000b01c38914$7e11c1b0$5c976b80@amer.cisco.com обсуждение исходный текст |
Ответы |
Re: Question about DB VACUUM
|
Список | 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
В списке pgsql-admin по дате отправления: