Postgres toast table grow out of proportion-CodePudding

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

CodePudding user response：

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.