Search Postgresql Archives

Re: TOAST table size in bytes growing despite working autovacuum

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux