TOAST table size in bytes growing despite working autovacuum

On Mon, 2020-06-15 at 11:51 +0300, Kristjan Mustkivi wrote:

Dear all,

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?

select n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count from
pg_stat_all_tables where relname = 'player_data_states';
─[ RECORD 1 ]────┬─────────────────────────────
n_live_tup │ 84730
n_dead_tup │ 8336
last_autovacuum │ 2020-06-15 08:23:58.88791+00
autovacuum_count │ 11306

select n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count from
pg_stat_all_tables where relname = 'pg_toast_293406';
─[ RECORD 1 ]────┬──────────────────────────────
n_live_tup │ 168486
n_dead_tup │ 9835
last_autovacuum │ 2020-06-15 08:33:22.566087+00
autovacuum_count │ 41021

The PG server is 11.7 (Debian 11.7-2.pgdg90+1)

And the table is

Column │ Type │ Nullable │ Storage
────────────────┼───────────────────┼──────────┼────────
id │ bigint │ not null │ plain
cage_player_id │ bigint │ not null │ plain
cage_code │ integer │ not null │ plain
player_data │ json │ │ extended
update_time │ timestamp with tz │ not null │ plain
Indexes:
"player_data_states_pk" PRIMARY KEY, btree (id)
"player_data_states_uk1" UNIQUE CONSTRAINT, btree (cage_player_id,
cage_code)
Referenced by:
TABLE "awards.player_data_state_changes" CONSTRAINT
"player_data_state_changes_fk1" FOREIGN KEY (player_data_state_id)
REFERENCES awards.player_data_states(id)
Publications:
"awards"
Options: fillfactor=90, toast.autovacuum_vacuum_scale_factor=0.05,
toast.autovacuum_vacuum_cost_limit=1000

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.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

On Mon, Jun 15, 2020 at 12:17 PM Laurenz Albe <laurenz.albe@cybertec.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

On Mon, 2020-06-15 at 13:47 +0300, Kristjan Mustkivi wrote:

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

Indeed, the table is almost entirely air.

You should schedule down time and run a VACUUM (FULL) on that table.
That will rewrite the table and get rid of the bloat.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

On Mon, Jun 15, 2020 at 4:37 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Mon, 2020-06-15 at 13:47 +0300, Kristjan Mustkivi wrote:

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

Indeed, the table is almost entirely air.

You should schedule down time and run a VACUUM (FULL) on that table.
That will rewrite the table and get rid of the bloat.

Hello!

But in order to avoid the situation happening again (as it will with
the current settings), I should likely make the autovacuuming on the
TOAST table even more aggressive via
toast.autovacuum_vacuum_scale_factor tinkering, right?

Sorry to pester with this and thank you for the feedback - it is much
appreciated!
--
Kristjan Mustkivi

On Mon, 2020-06-15 at 16:42 +0300, Kristjan Mustkivi wrote:

You should schedule down time and run a VACUUM (FULL) on that table.
That will rewrite the table and get rid of the bloat.

But in order to avoid the situation happening again (as it will with
the current settings), I should likely make the autovacuuming on the
TOAST table even more aggressive via
toast.autovacuum_vacuum_scale_factor tinkering, right?

No, the correct way is to reduce "autovacuum_vacuum_cost_delay".

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com