Re: Question about DB VACUUM - Mailing list pgsql-admin

As a starting point, check your free space map settings in the
postgresql.conf. They are low by default in 7.2.x.
free_space_relations* can safely be bumped to 1000. free_space_pages*
should probably be bumped to something like 50000, though you might be
able to determine a better amount be seeing how many pages are used up
after each add/delete/vacuum cycle. One other thing to check on is if
you have an indexes involved in the process, you may get some index
growth issues that will require periodic indexing.  HTH,

Robert Treat

* I'm pretty sure those aren't the exact names, but their similar so you
should be able to find them.


On Thu, 2003-10-02 at 14:39, Chris White (cjwhite) wrote:
> 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

--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL