TOAST table size in bytes growing despite working autovacuum

From: Kristjan Mustkivi <sonicmonkey(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: TOAST table size in bytes growing despite working autovacuum
Date: 2020-06-15 08:51:08
Message-ID: CAOQPKasgHoNpNZqjAfad10xQ5v9niANVXFZGhp=Q+NLwy1y3kw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Best regards
--
Kristjan Mustkivi

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2020-06-15 09:17:24 Re: TOAST table size in bytes growing despite working autovacuum
Previous Message Tom Lane 2020-06-15 05:00:02 Re: Unable to execute pg_dump