Postgres Aurora 9.6 - Toast Vacuum Causing Table Lock

Question

I have a table in Aurora Postgres 9.6 that is just:

create table myTable
(
    id uuid default extensions.uuid_generate_v4() not null
    blobs jsonb not null
);

The blobs can get rather large at time, but are usually a few MBs. And end up getting stored in the toast tables.

Under increased load, I started to see tables locks in Aurora replica (lock_relation) or AccessExclusiveLock in Postgres equivalent. Looking at pg_locks tables, it seems that the cause for the table lock is a system process.

We are not able to select any rows from that table. What I found is that once we kill the vacuum, the table locks is released and we can fetch rows.

Locks

id,locktype,database,relation,page,tuple,virtualxid,transactionid,classid,objid,objsubid,virtualtransaction,pid,mode,granted,fastpath
1,relation,16394,142767200,,,,,,,,1/0,33244,AccessExclusiveLock,true,false

Relation Translation

142767200 -> pg_toast.pg_toast_142767196
pg_toast.pg_toast_142767196 - mySchema.myTable

Vacuum Run

0 years 0 mons 0 days 1 hours 37 mins 47.688907 secs    rdsadmin    autovacuum: VACUUM pg_toast.pg_toast_142767196  active

Screenshots of locks

Questions:

My understanding is that an auto vacuum shouldn't cause table locks that interfere with other operations, why do I see those?
Is there any other system processes that is connected to auto vacuum that once killed, unlocks the table (pids don't match up directly)

Postgres Aurora 9.6 - Toast Vacuum Causing Table Lock

0 Answers