Zabbix partitioning autovacuum causes locking
A normal autovacuum process will succumb silently if it blocks another transaction, but if that happens all the time and autovacuum can never finish, you will eventually have old table rows that have never been vacuumed. To prevent the danger of losing data as soon as the transaction ID counter wraps around, PostgreSQL then triggers a less benevolent autovacuum run that is not willing to back down.

Now creating a new partition for a table requires an ACCESS EXCLUSIVE lock on the partitioned table. Since this seems to happen every day, and autovacuum seems to run too slow to finish in one day, you end up getting stuck at midnight, when the ghosts of automatic table partitioning start rattling their chains.

Killing autovacuum is not a solution, because it will only make the problem worse. The work will be lost, and PostgreSQL will start autovacuum again, and even if you manage to silence it, eventually the database will refuse data modifications. Then your only option is to take down the database start it in single user mode and run a manual VACUUM. That causes a longer outage, so you don't want that.

The best solution is to tune autovacuum so that it can run as fast as possible, in the hope that it gets done within 24 hours:


set autovacuum_work_mem as high as you can
set autovacuum_vacuum_cost_delay to 0


Additionally, if you have many partitions, you should increase autovacuum_max_workers somewhat.

Now keep your fingers crossed and hope that autovacuum gets done before midnight.

You can monitor the state of affairs with something like

SELECT s.last_autovacuum, s.last_vacuum, age(t.relfrozenxid)
FROM pg_stat_all_tables AS s
   JOIN pg_class AS t ON s.relid = t.oid
WHERE age(t.relfrozenxid) &gt; 200000000
ORDER BY age(t.relfrozenxid) DESC;


Keep working until that list is empty!

If you cannot get autovacuum to complete a single table in 24 hours, your only choice is to skip creating new partitions for a while (or at least not attaching them to the partitioned table).