Re: pg_toast table growth out of control

Jeffrey W. Baker wrote:
> On Mon, 2002-03-11 at 12:18, John Gray wrote:
> > On Mon, 2002-03-11 at 19:00, Jeffrey W. Baker wrote:
> > > (resent -- mailing lists seem horked up today)
> > >
> > > I have a pg_toast table that is using up my entire storage device. When
> > > I vacuum the database, it does not get smaller. This is driving me
> > > crazy, since I have to dump and reload the data to shrink the database.
> > >
> >
> > Are you using VACUUM FULL at all? AIUI, the current VACUUM will
> > generally not truncate tables -in this respect, the TOAST table is like
> > any other.
> >
> > I would suggest running VACUUM FULL VERBOSE resp_body; and see whether
> > you get any different message. (this gets an exclusive lock on the
> > table, so it will block operations on resp_body while it operates).
>
> That isn't the problem ... the rest of my tables get truncated normally
> but only this one which contains long objects grows.
>
> In any case VACUUM FULL takes ages. pg_dump + pg_restore is actually
> faster.

The best cure for a problem is avoiding it. I would suggest
running the light weight VACUUM more often, so that it
doesn't grow that big in the first place.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com