CODEWITHSUNDEEP
databasepostgresql
somenickname
somenickname

Reputation: 619

Postgres toast table grow out of proportion

We are seeing number of tuples deleted in dead tuple grow much faster than number of tuples updated+deleted in the regular table (conveniently named 'regular').

Why is this happening? What would you suggest us do to avoid bloated toast table?

select now(),relname,n_tup_upd,n_tup_del,n_tup_hot_upd,n_live_tup,n_dead_tup,n_mod_since_analyze from pg_stat_all_tables where relname in ('regular','pg_toast_16428');
-[ RECORD 1 ]-------+------------------------------
now                 | 2022-01-27 16:46:11.934005+00
relname             | regular
n_tup_upd           | 100724318
n_tup_del           | 9818
n_tup_hot_upd       | 81957705
n_live_tup          | 3940453
n_dead_tup          | 20268
n_mod_since_analyze | 98221
-[ RECORD 2 ]-------+------------------------------
now                 | 2022-01-27 16:46:11.934005+00
relname             | pg_toast_16428
n_tup_upd           | 0
n_tup_del           | 12774108278
n_tup_hot_upd       | 0
n_live_tup          | 3652091
n_dead_tup          | 3927007666
n_mod_since_analyze | 25550832222

fre=> select now(),relname,n_tup_upd,n_tup_del,n_tup_hot_upd,n_live_tup,n_dead_tup,n_mod_since_analyze from pg_stat_all_tables where relname in ('regular','pg_toast_16428');
-[ RECORD 1 ]-------+------------------------------
now                 | 2022-01-27 16:46:13.198182+00
relname             | regular
n_tup_upd           | 100724383
n_tup_del           | 9818
n_tup_hot_upd       | 81957761
n_live_tup          | 3940453
n_dead_tup          | 20333
n_mod_since_analyze | 98286
-[ RECORD 2 ]-------+------------------------------
now                 | 2022-01-27 16:46:13.198182+00
relname             | pg_toast_16428
n_tup_upd           | 0
n_tup_del           | 12774129076
n_tup_hot_upd       | 0
n_live_tup          | 3652091
n_dead_tup          | 3927028464
n_mod_since_analyze | 25550873818

Upvotes: 0

Views: 1875

Answers (1)

freakish
freakish

Reputation: 56477

Big values are divided into multiple rows in Toast table (depending on size and TOAST_MAX_CHUNK_SIZE setting). And thus a single row update may result in multiple row updates in the associated Toast table.

AFAIK Postgresql will not allow a row to exceed page size, which is 8Kb by default. This applies to both normal and Toast tables. And so for example a 8Mb value in a regular table will result in a thousand or so rows in Toast table. That's why Toast table is a lot bigger than the regular one.

For more information read the docs:

https://www.postgresql.org/docs/current/storage-toast.html

So how to deal with the bloat? The typical method is to vacuum full. That however locks entire table. There are methods to reduce bloat without locking but require a lot more space (typically twice the size of the table) and are harder to maintain. You create a clone table, you setup triggers to insert/update/delete to both tables, then you copy all of the data there, you switch tables and drop the old one. This of course gets messy when foreign keys (and other constraints) are involved.

There are tools that do that semi-automatically. You may want to read about pg_squeeze and/or pg_repack.

Upvotes: 3

Related Questions