Re: pg_toast table growth out of control

On Mon, 2002-03-11 at 15:04, Jan Wieck wrote:
> 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?

Unfortunately during the course of this conversation I was forced to
dump and restore the database because of critical shortage of disk
space. The query now returns 362619750. pg_class says 48080008 is
stored in .../data/base/.../48080010, which has size 420536320.

>
> 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.

I don't ever restart it unless except to upgrade the software. The last
time was 23 days ago.

> 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?

I don't know how to tell.

-jwb