[SR-Users] Postgres: Exclusive Transaction Locks vs. Vacuumer

[Stefan Kohlhauser]

Hey everyone!

We noticed that under high load of registrations and using the pua module we can reach a state where the PUA table bloats our Postgres to several GB. However, a SELECT on pua only shows a few hundred entries. The other half million or so entries are obsoleted but not cleaned up.
The reason for this seems to be that the autovacuumer of the Postgres can't get to work because the PUA table is almost permanently locked.
Our Postgres logs are full with errors about the autovacuumer being canceled because it couldn't get to work.

Looking at modules/db_postgres/km_dbase.c::db_postgres_start_transaction() I noticed that there are two locking modes used:
str lock_write_end_str = str_init(" IN EXCLUSIVE MODE");
str lock_full_end_str = str_init(" IN ACCESS EXCLUSIVE MODE");

According to
https://www.postgresql.org/docs/9.2/static/explicit-locking.html
these modes mean forbidding everything else except reads and forbidding everything else.
However, according to this documentation ROW EXCLUSIVE is the standard lock for entry altering operations (INSERT, UPDATE, DELETE). And this would still allow the autovacuumer to do its job.

Is there a specific reason why these more restricting locking modes are used instead of ROW EXCLUSIVE?

Thanks.

Best regards,
Stefan