Re: pg_toast table growth out of control

On Mon, 2002-03-11 at 14:07, Jan Wieck wrote:
> Jeffrey W. Baker wrote:
> > On Mon, 2002-03-11 at 13:30, Jan Wieck wrote:
> >
> > > 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.
> >
> > I think everybody is missing my point. This entire database is vacuumed
> > every HOUR. All the tables are reasonably sized, and they stay that
> > way. Except, the magic pg_toast table where long objects from resp_body
> > are store is growing and growing and growing and growing and does not
> > seem to respond to VACUUM whatsoever.
>
> You actually did a VACUUM FULL and it didn't shrink? In 7.2
> no table does shrink on a normal VACUUM. So if you don't run
> VACUUM FULL, it cannot!

You still don't understand my problem. I insert into this database at
the rate of 1000 rows per hour. Every hour, I delete the rows that are
more than 1 day old and vacuum. Thus, the maximum size of the table
should be 24 * 1000 = 24000 rows and the file size should be stable.

HOWEVER

The actual observed behavior is that the file simply grows constantly.
Forever. No stability. Period. Despite the fact that select count(*)
from table == a constant.

-jwb