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@Yahoo.com #



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