Re: 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