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
