A process is trying to ALTER a table (to set default column value) in a workflow. It is getting blocked due to postgres auto-vacuum process not releasing a lock (It might be holding a ShareUpdateExclusiveLock).
The following logs are seen in postgres -
LOG: automatic vacuum to prevent wraparound of table "my_db.public.event_spl_1740300900": index scans: 0
pages: 0 removed, 16591962 remain, 0 skipped due to pins, 16300723 skipped frozen
tuples: 0 removed, 1244397113 remain, 0 are dead but not yet removable, oldest xmin: 1410720248
buffer usage: 2361602 hits, 6947399 misses, 291262 dirtied
avg read rate: 3.879 MB/s, avg write rate: 0.163 MB/s
system usage: CPU: user: 83.69 s, system: 135.74 s, elapsed: 13990.91 s
LOG: process 113323 acquired AccessExclusiveLock on relation 181282787 of database 180669275 after 5823314.224 ms
Is there enough evidence here to suggest auto-vacuum might be the culprit? If not, what could be the other blocking process?
Yes, a VACUUM will place a SHARE UPDATE EXCLUSIVE table lock on the table, which conflicts with the ACCESS EXCLUSIVE lock the ALTER TABLE statement needs.
Normal autovacuum processes will silently die after deadlock_timeout if they are blocking a user statement, but an autovacuum “to prevent wraparound” has business to attent to and won't back down.