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