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?

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?

• postgresql
• postgresql-12

• to prevent wraparound How did you get into this situation? To avoid this issue, it's better to optimize the configuration of your database first before making changes to tables. – Frank Heikens Commented Mar 6 at 14:36
• I am using a database with high volume of inserts and hence had autovacuum turned on with the setting - autovacuum_freeze_min_age = 0, ""autovacuum_freeze_max_age = 100000 – Yashodhan Agnihotri Commented Mar 6 at 16:43
• That's a very unusual setting, also for high-volume systems. That might also be the reason why your disk system is so slow: avg read rate: 3.879 MB/s, avg write rate: 0.163 MB/s – Frank Heikens Commented Mar 6 at 17:38
• What should be the ideal setting here? – Yashodhan Agnihotri Commented Mar 7 at 5:12

1 Answer 1

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.