Thread: after vacuum, db is still "growing" :(

hi,

i'm using postgresql 7.4.8.

we have a database that only contains one table that contains session
data,

so it changes very often.

until now, we were vacuuming the database weekly.

it's current size is 15GB.

i know that when i once did a full-vacuum on this database, it's size
shrunk to 100MB.

so something is wrong there.
yesterday i did a vacuum, but in  5 hours, the database-size started to
grow again.

i don't understand this.

(well, there's always a possibility that we are "leaking" some db-rows
(means we are not deleting them from the db), but let's  ignore that
possibility for now)

i know that a vacuum does not reclaim disk space...
after the vacuum the "real" db-size should become 100MB, and there
should be around 14.9GB "free" db-space. why isn't postgresql using that
space?

are there any cases, where a normal vacuum is unable to reclaim some
space, but a full-vacuum is able?

or any other ideas?

thanks,
gabor

--
That's life for you, said McDunn.  Someone always waiting for someone
who never comes home.  Always someone loving something more than that
thing loves them.  And after awhile you want to destroy whatever
that thing is, so it can't hurt you no more.
                -- R. Bradbury, "The Fog Horn"

On Fri, 2005-12-09 at 08:45, gabor wrote:
[...........]
>
> are there any cases, where a normal vacuum is unable to reclaim some
> space

Hello

We have det same problem in some databases running 7.4.8. Having
max_fsm_pages and max_fsm_relations properly configurated does not help.

It looks like this happens in tables with data that is updated/deleted
very often and that the files growing are the ones for the indexes, when
using B-trees.

We noticed this problem when a clean restore of a database used much
less space and the big different were in the indexes files.

In a busy 24/7 database a reindex of the indexes in a big table is not
a solution because using an ACCESS EXCLUSIVE lock for a 'long' time will
block access to data.

I have not tested if the only problem is that we use more and more space
or if performance also gets worst.

Is it possible to fix this, do we have any plans, is it better with 8.0
or 8.1?

Thanks in advance for your answers :)
--
Rafael Martinez, <r.m.guerrero@usit.uio.no>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/

On Fri, Dec 09, 2005 at 10:00:45AM +0100, Rafael Martinez Guerrero wrote:
> On Fri, 2005-12-09 at 08:45, gabor wrote:
> [...........]
> >
> > are there any cases, where a normal vacuum is unable to reclaim some
> > space
>
> Hello
>
> We have det same problem in some databases running 7.4.8. Having
> max_fsm_pages and max_fsm_relations properly configurated does not help.
>
> It looks like this happens in tables with data that is updated/deleted
> very often and that the files growing are the ones for the indexes, when
> using B-trees.

More recent versions do reuse index space much better than before. And
on busy tables you need to vacuum very regularly. In recent versions
you have an autovacuum daemon to handle much of this for you...

Have a ncie day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.