A look at PostgreSQL 9.6 – The wait interface

For a long time there was no real wait interface in PostgreSQL core. Now, with the upcoming 9.6, pg_stat_activity was extended to provide more meaningful information for what a session is currently waiting on. If you are interested in reading through the history: It all started here. Lets have a look at it by doing same simple tests.

When I want to see what a session is waiting for I’ll at least need two sessions: One doing some work and the other for querying the wait event information. Obviously I’ll need to know my backend process identifier which I then can use to query pg_stat_activity. PostgreSQL provides a function to get the backend process id:

(postgres@[local]:5432) [postgres] > select * from pg_backend_pid();
 pg_backend_pid 
----------------
           3048
(1 row)

Using this session lets create a table and populate it with some rows:

(postgres@[local]:5432) [postgres] > create table t1 (a int);
CREATE TABLE
(postgres@[local]:5432) [postgres] > insert into t1 (a) values ( generate_series(1,1000000) );
INSERT 0 1000000

If you are fast enough to switch to the other session and query for the wait event you’ll see this:

(postgres@[local]:5432) [postgres] > select wait_event_type,wait_event from pg_stat_activity where pid = 3048;
 wait_event_type |  wait_event  
-----------------+--------------
 LWLockNamed     | WALWriteLock
(1 row)

So, what does this mean? The documentation gives the answer:

• LWLockNamed: The backend is waiting for a specific named lightweight lock. Each such lock protects a particular data structure in shared memory. wait_event will contain the name of the lightweight lock.
• WALWriteLock: Waiting for WAL buffers to be written to disk.

Makes sense: Changed data needs to be written to the WAL.

Another example: In one session I’ll start a transaction and update one row in the table without closing/committing the same:

(postgres@[local]:5432) [postgres] > begin;
BEGIN
Time: 0.103 ms
(postgres@[local]:5432) [postgres] > update t1 set a = 2 where a = 1;
UPDATE 1
Time: 56.086 ms
(postgres@[local]:5432) [postgres] > 

In a third session lets try to update the same row:

(postgres@[local]:5432) [postgres] > select * from pg_backend_pid();
 pg_backend_pid 
----------------
           3397
(1 row)

Time: 0.220 ms
(postgres@[local]:5432) [postgres] > update t1 set a = 2 where a = 1;

This should cause a wait event, but which one?

(postgres@[local]:5432) [postgres] > select wait_event_type,wait_event from pg_stat_activity where pid = 3397;
 wait_event_type |  wait_event   
-----------------+---------------
 Lock            | transactionid
(1 row)

Another event type and another event:

• Lock: The backend is waiting for a heavyweight lock. Heavyweight locks, also known as lock manager locks or simply locks, primarily protect SQL-visible objects such as tables. However, they are also used to ensure mutual exclusion for certain internal operations such as relation extension. wait_event will identify the type of lock awaited.
• transactionid: Waiting for a transaction to finish

In PostgreSQL DDL is transactional, too, so we should be able to create another wait event when we try to modify the table in one session:

(postgres@[local]:5432) [postgres] > begin;
BEGIN
Time: 0.085 ms
(postgres@[local]:5432) [postgres] > alter table t1 add column b int;
ALTER TABLE
Time: 0.318 ms
(postgres@[local]:5432) [postgres] > 

… and then try to update a row in another session:

(postgres@[local]:5432) [postgres] > update t1 set a = 2 where a = 1;

What is reported?

(postgres@[local]:5432) [postgres] > select wait_event_type,wait_event from pg_stat_activity where pid = 3397;
 wait_event_type | wait_event 
-----------------+------------
 Lock            | relation
(1 row)

• Lock: The backend is waiting for a heavyweight lock. Heavyweight locks, also known as lock manager locks or simply locks, primarily protect SQL-visible objects such as tables. However, they are also used to ensure mutual exclusion for certain internal operations such as relation extension. wait_event will identify the type of lock awaited.
• relation: Waiting to acquire a lock on a relation.

Currently there are 68 documented wait events and I am sure more will show up during the next releases. This is a huge help if you need to troubleshoot sessions because now you do not need extensions any more and do not need to compile PostgreSQL with dynamic tracing.