Saturday, January 16, 2016

Blocked by rdsadmin

One of our clients on RDS had a VACUUM FREEZE that was hanging for a long time. "I bet it's waiting on a lock," I thought. Yup, but the curious part is what it was waiting on: a mysterious process run by the rdsadmin role (see RECORD 2, below):
SELECT pg_stat_activity, pg_locks.mode
  FROM pg_stat_activity
  JOIN pg_locks USING (pid)
  JOIN pg_class ON pg_locks.relation = pg_class.oid
 WHERE pg_class.relname = 'users'
   AND pg_locks.mode IN ('ShareUpdateExclusiveLock', 'ShareLock', 'ShareRowExclusiveLock', 'ExclusiveLock', 'AccessExclusiveLock');

-[ RECORD 1 ]----+------------------------------
datid            | 1234
datname          | my_database
pid              | 14641
usesysid         | 16396
usename          | postgres
application_name | psql
client_addr      |
client_hostname  |
client_port      | 5430
backend_start    | 2016-01-15 22:05:06.161987+00
xact_start       | 2016-01-15 22:14:39.301425+00
query_start      | 2016-01-15 22:14:39.301425+00
state_change     | 2016-01-15 22:14:39.301429+00
waiting          | t
state            | active
query            | VACUUM FREEZE verbose users;
mode             | ShareUpdateExclusiveLock
-[ RECORD 2 ]----+------------------------------
datid            | 1234
datname          | my_database
pid              | 22328
usesysid         | 10
usename          | rdsadmin
application_name |
client_addr      |
client_hostname  |
client_port      |
backend_start    |
xact_start       |
query_start      |
state_change     |
waiting          |
state            |
query            | 
mode             | ShareUpdateExclusiveLock
Further examination showed that this process was locking only the users table (and its indexes):
SELECT locktype, relation::regclass AS tablename
  FROM pg_locks
  JOIN pg_stat_activity USING (pid)
 WHERE pid = 22328;

locktype    |                  tablename
 relation   | user_index_a
 relation   | user_index_b
 relation   | user_index_c
 relation   | users_pkey
 virtualxid |
 relation   | users
(13 rows)

Has anyone else seen such a process? I'm curious as to what it is. My current best guess is a vacuum We opened a ticket with Amazon to ask them, so I'll update this post when Amazon replies.

EDIT 2016-01-17: the above is a braino for "an autovacuum." An Amazon rep wrote back the next day to say that it was, indeed, an autovacuum process, and included some vacuum-tuning tips. So good on them, although it's unfortunate that RDS's privilege system doesn't allow you to see the pg_stat_activity.query field when the rdsadmin role is working on a table you own.


  1. This comment has been removed by the author.

  2. quirks of rds vacuum were discussed by Grant McAlister at recent PGSV conference:

  3. Heh, I was in that session, but I didn't remember that! He covered a lot of material.

  4. I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.