Re: more about pg_toast growth

On Wed, 2002-03-13 at 07:22, Jan Wieck wrote:
> Jeffrey W. Baker wrote:
> > I have more data about unchecked growth of pg_toast tables.  After
> > another day's worth of inserting and deleting, the table in question has
> > 39504 tuples.  The pg_toast table has 234773 tuples, and 126697 are
> > marked as unused.  The sum(length(chunk_data)) from pg_toast is
> > 433165242.  The sum(length(resp_body)) -- the actual data in the table
> > -- is much bigger: 921615964.  How is that possible?
> >
> > In any case it is clear that the table is just growing again.  The file
> > increased from 420MB to 730MB overnight, without a corresponding
> > increase in tuples.
> >
> > The free space map settings in postgresql.conf are commented out.
> >
> > I'd be very interested to find out how the sum of the length of the
> > tuples can be much larger than both the sum of lengths from the toast
> > table and the actual size of the file.
>
>     Remember,  TOAST  doesn't  only  come  in  slices,  don't you
>     usually brown it?  Meaning, the data gets compressed (with  a
>     lousy  but  really  fast  algorithm).   What  kind of data is
>     resp_body? 50% compression  ratio  ...  I  guess  it's  html,
>     right?

It is gzipped and base64-encoded text.  It's somewhat strange that a
fast LZ would deflate it very much, but I guess it must be an artifact
of the base64.  The initial gzip tends to deflate the data by about 90%.

>     Anyway,  I  would  suggest  you  increase  the  max_fsm_pages
>     parameter.  Commented out parameters in  the  postgresql.conf
>     file  means  "default".   You  said  you're doing about 1,000
>     inserts an hour and a daily bulk delete  of  approx.  24,000.
>     Assuming  most of the toast tuples are contigous, that'd mean
>     you are freeing something like 35,000 toast  pages.  I  would
>     suggest  a freespace map size of 50,000 pages, to start with.
>     That should at least lower the growth rate. If you still  see
>     growth, go ahead and increase it further.

I will happily do so.  What is the argument against increasing the free
space map?  Does it consume more memory?  The machine has 4GB main
memory, and I wouln't notice increased consumption of a few megabytes.

What triggers storage in the toast tables?  Is it because of a tuple
longer than some threshold?

-jwb