Re: TOAST table size in bytes growing despite working autovacuum

On Mon, Jun 15, 2020 at 12:17 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:
>
> On Mon, 2020-06-15 at 11:51 +0300, Kristjan Mustkivi wrote:
> > I have a table which contains a "json" column and it gets heavily
> > updated. Before introducing toast.autovacuum_vacuum_scale_factor=0.05
> > and toast.autovacuum_vacuum_cost_limit=1000 this table bloated to
> > nearly 1TB in a short while. Now the n_dead_tup value is nicely under
> > control but still, the table is slowly growing in size but not in
> > rows. The odd thing is that the value of n_live_tup in the TOAST is
> > twice of that in the main table. I know it is a statistical value, but
> > this does not feel right.
> >
> > Why is that? What to do to make it stop growing?
>
> It is not surprising if there are more entries in the TOAST table than
> in the base table: a big value will be split in several chunks,
> each of which is an entry in the TOAST table.
>
> To see if the TOAST table is bloated, use pgstattuples:
>
> SELECT * FROM pgstattuple('pg_toast.pg_toast_293406');
>
> Vacuum does not remove existing bloat, it just prevents increased bloat.

Thank you Laurenz,

So the TOAST table entries exceeding the base table entries are due to
that the toasted value is split and each chunk is considered as a
separate entry - good to know!

Still, pgstattuple reveals that the table size is 715MB while live
tuple len is just 39MB and 94% of the table is vacant. I do not have
much experience in interpreting this but it would seem that it is
still getting bloated. Should the autovacuum be made even more
aggressive? E.g toast.autovacuum_vacuum_scale_factor=0.01 instead of
0.05 and tweaked further when necessary until the size stabilizes
(more precisely pgstattuple will reflect the bloat to be under
control):

SELECT * FROM pgstattuple('pg_toast.pg_toast_293406');
─[ RECORD 1 ]──────┬──────────
table_len │ 715776000
tuple_count │ 25545
tuple_len │ 39241366
tuple_percent │ 5.48
dead_tuple_count │ 1116
dead_tuple_len │ 1930508
dead_tuple_percent │ 0.27
free_space │ 669701052
free_percent │ 93.56

With my the best,
--
Kristjan Mustkivi