How to optimize a table for a very high-frequency updates?

I have a table that contains a list of tasks that need to be run periodically:

```
applaudience=> \d+ maintenance_task
Table "public.maintenance_task"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------------------------------+--------------------------+-----------+----------+----------------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('maintenance_task_id_seq'::regclass) | plain | |
nid | citext | | not null | | extended | |
execution_interval | interval | | not null | | plain | |
last_attempted_at | timestamp with time zone | | | now() | plain | |
last_maintenance_task_execution_id | integer | | | | plain | |
disabled_at | timestamp with time zone | | | | plain | |
maximum_execution_duration | interval | | not null | '00:05:00'::interval | plain | |
maximum_concurrent_execution_count | integer | | not null | 0 | plain | |
last_exhausted_at | timestamp with time zone | | not null | now() | plain

The setting old_snapshot_threshold was added in Postgres 9.6 for cases like this. A long-open query will not be allowed to hold back the vacuum indefinitely. If the query never needs data that might have been vacuumed away, it will complete as normal. If finds it does need such data, it will throw an error. And if it is part of a forgotten-about connection, it will hang around indefinitely but not cause table bloat while doing so.

Note, however, that this setting prevents autovacuum from returning freed space at the end of relations to the operating system, since that is needed to detect the error condition. Only a manual VACUUM FULL will still force it. So while it can help to fight one type of table bloat (exactly your problem), it can lead to another kind (often less critical).

Alternatively consider idle_in_transaction_session_timeout, which terminates sessions being idle for too long. Chose your settings wisely.

StackExchange Database Administrators Q#224495, answer score: 4