How do we get around PostgreSQL autovacuum taking a table-level lock ACCESS EXCLUSIVE on replicas?

We are running:

user@primary/client-n:~$ psql -d database -c "SELECT version();"                                                                   
version
---------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.7 (Ubuntu 10.7-1.pgdg16.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
(1 row)

on:

user@primary/client-n:~$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 16.04.6 LTS
Release:        16.04
Codename:       xenial

and have a setup with one primary and two streaming replication clients configured with:

user@client-n:~$ psql -d postgres -c "SELECT name, setting FROM pg_settings WHERE name IN ( 'hot_standby', 'hot_standby_feedback', 'max_standby_streaming_delay' );"
           name             | setting 
----------------------------+---------
hot_standby                 | on
hot_standby_feedback        | on
max_standby_streaming_delay | 150000
(3 rows)

We have one database only (other than the standard ones) and one table in the database. Approximately 3 to 4 times per day we run into a special case autovacuum situation which is described in the documentation as:


[...] it (autovacuum) will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained

We are monitoring pg_locks and have been able to observe the autovacuum daemon taking the table-level lock ACCESS EXCLUSIVE which in turn leads to a whole host of blocked processes on the clients as illustrated with the log entries below:

Primary:

...
2019-06-04 05:59:29.154 BST [8998-1] LOG:  automatic vacuum of table "database.schema.table": index scans: 1
...

Client 1:

```
...
2019-06-04 05:59:03.660 BST [21167-858] [PostgreSQL JDBC Driver@i

One undocumented way of avoiding VACUUM truncation and the exclusive lock it requires is to set old_snapshot_threshold to a value other than -1. This in turn will get rid of the locks and the resulting recovery conflicts. However, note that the implementation of old_snapshot_threshold had problems, and the parameter has been removed in PostgreSQL v17.

From PostgreSQL v12 on you can use the better technique of disabling VACUUM truncation for individual tables:

ALTER TABLE mytab
   SET (vacuum_truncate = on,
        toast.vacuum_truncate = on);

StackExchange Database Administrators Q#239777, answer score: 3