Re: pg_toast table growth out of control

Jeffrey W. Baker wrote:
> On Mon, 2002-03-11 at 14:07, Jan Wieck wrote:
> > 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.

Get the name of the toast table with

SELECT T.relname FROM pg_class T, pg_class R
WHERE R.relname = '<your main tables name>'
AND R.reltoastrelid = T.oid;

Now query that toast table with:

SELECT sum(length(chunk_data)) FROM pg_toast_????????;

What is the length sum and how big is the real file?

Another question, do you frequently restart the postmaster? I
don't know for sure, but maybe a restart of the postmaster
will cause you to loose the freespace map for the relation
and therefore all new tuples go allways at the end, not into
some free'd space.

How big is the freespace map anyway, could this be an example
for that this table needs to be vacuumed even more often than
once per hour, Tom?

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