Re: disk space usage enlarging despite vacuuming

Tom Lane wrote:
> Tzvetan Tzankov <ceco@noxis.net> writes:
> > I use debian package postgresql 7.3.2r1-2, it is set to
> vacuum every 5
> > hours and once weekly (sunday) vacuum -f, aditionally there
> are some
> > session tables which vacuum at 5 minutes, dispite this the
> disk usage
> > enlarges with 300-400MB for about 2 days and in sundey with
> the full vacuum
> > very few MB-s are recovered.
>
> There isn't enough info here to really tell what's going on;
> you need to
> look at the individual tables and indexes of the problem databases to
> see where the space is going.  (pg_class's relpages column will give
> you the right data, if you vacuum first.)

(Sorry if it seems I'm hijacking the thread-- my problems seem pretty
similar to Tzvetan's, and I thought our problems might be related.)

I've noticed similar behavior, and have tracked it down to the
pg_largeobject table.  pg_largeobject is using about 50 Gigs of disk space
in my currently running server (7.2.1), but going through a dump-n-restore
cycle always ends up freeing up some disk. Doing it today (restored into a
test 7.3.2 database, but results are similar to restoring into 7.2.1) freed
up about 13 gigs. (The dump file was 30 gigs, and was current as of about
7am this morning.)

We've got a 'vacuum analyze' loop going all the time, and we reindex the
database every night.

All of that freed space is from the pg_largeobject table space using less
space than before. (39 gigs in the new database, compared to ~52 from the
old.)

When I look at the number of relpages reported for pg_largeobject in the old
and new servers, they accurately represent the disk space in use. I guess
for me, the question is now: why did the old server think that
pg_largeobject has more tuples than the new server does now, and where did
they go?
Any chance that the client apps have a transaction open on some
large_objects (keeping them from being deleted), but the tuple gets deleted
through a dump and restore cycle?
If that's the case, shouldn't the tuple also get deleted when I stop and
restart the server?

-ron



>
> A first guess is that the problem is index bloat, but that's really
> theorizing in advance of the data...
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>