When facing such ordeal and if you can accept to impact availability just VACUUM FREEZE the most impacted tables (or, if in doubt, the whole database).
If transaction activity is somewhat predictable adjust your autovacuum parameters per-table, using "ALTER TABLE ((tablename)) SET", to have it kick not too early (constantly fiddling) and not too late (at worse leading to an automatic emergency DB locking preventing a wrapping).
See https://www.postgresql.org/docs/current/sql-altertable.html
Pertinent parameters:
autovacuum_analyze_scale_factor
autovacuum_analyze_threshold
autovacuum_enabled = true
autovacuum_vacuum_insert_scale_factor
autovacuum_vacuum_insert_threshold
autovacuum_vacuum_scale_factor
autovacuum_vacuum_threshold
autovacuum_vacuum_cost_delay
autovacuum_vacuum_cost_limit
When facing such ordeal and if you can accept to impact availability just VACUUM FREEZE the most impacted tables (or, if in doubt, the whole database).
If transaction activity is somewhat predictable adjust your autovacuum parameters per-table, using "ALTER TABLE ((tablename)) SET", to have it kick not too early (constantly fiddling) and not too late (at worse leading to an automatic emergency DB locking preventing a wrapping). See https://www.postgresql.org/docs/current/sql-altertable.html Pertinent parameters: autovacuum_analyze_scale_factor autovacuum_analyze_threshold autovacuum_enabled = true autovacuum_vacuum_insert_scale_factor autovacuum_vacuum_insert_threshold autovacuum_vacuum_scale_factor autovacuum_vacuum_threshold autovacuum_vacuum_cost_delay autovacuum_vacuum_cost_limit
If multiple clients simultaneously hammer INSERT or UPDATE a table you can modify their sourcecode in order to have them cooperate by using PG's advisory locking, see https://www.postgresql.org/docs/13/explicit-locking.html#ADV...