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