Re: more about pg_toast growth

Caveat: Because it's otherwise quiet, I'm trying to be helpful. If you
already have someone more knowledgeable than I on the case, please don't
feel you need to respond!

On Tue, 2002-03-12 at 21:30, 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?
>
TOAST compresses data where possible -if your data is English text, then
that seems like a reasonable compression factor.

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

This implies to me that there are some updates going on against the
table. Presumably the sum(length(chunk_data)) hasn't increased during
the same period?

I should point out that although I know how TOAST is implemented, I have
no real knowledge of lazy vacuum and the free space map.

I notice that ordinary 'lazy' vacuum will only attempt a truncation if
it thinks it can reclaim at least 1/16 of the relation size.
Furthermore, it does need an exclusive lock to do this. You don't have
any process that performs lemgthy operations on resp_body which would
prevent vacuum from getting its lock? -if it can't get the lock, it will
just give up rather than blocking.

Perhaps in this case, a message could be printed in the vacuum verbose
output - "Could not get exclusive lock on relation -not truncating" - to
distinguish this case from the case where the relation is not seen as a
good candidate for attempting truncation.

> The free space map settings in postgresql.conf are commented out.
>

This implies they'll have their default values. How many tables in your
database get touched in your update process? What is the ballpark volume
of data that gets updated/inserted per day?

> 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.
>
LZ Compression, as mentioned above.

You may wish to wait for a better answer before doing anything drastic
with your DB!

Regards

John