A Mysterious PostgreSQL Performance Bug

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...