Re: Autovacuum not vacuuming pg_largeobject

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Denis Gasparin <denis(at)edistar(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Autovacuum not vacuuming pg_largeobject
Date: 2007-08-30 15:37:53
Message-ID: 1606.1188488273@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Denis Gasparin <denis(at)edistar(dot)com> writes:
> I'm a bit concerned about the autovacuum daemon.
> Today I runned a vacuum full during a normal maintainance task and I
> noticed that the size of pg_largeobject
> decreased from 14GB to 4GB...

> Every night we have a procedure that deletes large object no more
> referenced using the vacuumlo program.

> This program issues delete commands to the pg_largeobject table in order
> to erase the rows of the los no more referenced.

> Autovacuum is up and running... but now i'm thinking it doesn't examine
> system tables such as pg_largeobject...

> Am I wrong?

Yeah, you're wrong. The difference is that plain vacuum does not try
very hard to reduce the length of a table file --- it just frees up
space within the file for reuse. vacuum full will actually move things
from the end of the file to free space nearer the head of the file,
so that it can shorten the file.

What I suspect the above observations really prove is you don't have
max_fsm_pages set high enough, and so pg_largeobject was bloating because
the free space was being forgotten instead of reused.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2007-08-30 15:38:09 Re: PostgreSQL.Org
Previous Message Joshua D. Drake 2007-08-30 15:34:30 Re: PostgreSQL.Org