TLDR Postgres feature highlight — Wait events in 9.6 and 10 by Michael Paquier

Postgres got a new DBA facing feature — alike wait_class and wait event from Oracle’s v$session.

Since 9.6 there’s a really cool feature — tracking of so-called wait events.

Two new columns in pg_stat_activity — wait_event_type and wait_event

Wait_event_types:

• LWLockNamed — light-weight lock that controls access to shared memory structures etc.
• LWLockTranche — same but mostly waiting for some I/O access.
• Lock — a heavy-weight lock — used mostly for SQL level objects, like relations for example.
• BufferPin —for when backend is waiting to get shared buffer access.

wait event facility of statistics collector is designed to be light-weight and highly flexible, so as new event types could be tracked on the top.

Postgres 10 added to that

Accounting for waiting on latches, sockets, etc. That allows monitoring, for example, of latencies due to synchronous replication — SyncRep wait_event.

New wait_event_type‘s — “IPC” for processes waiting for activity from another process, “Activity” means that the process is basically idle, etc. Check the docs to know more.

There’s a limitation though:

it is not possible to look at the wait points of auxiliary system processes, like the startup process at recovery, the archiver, autovacuum…

Summary:

9.6 allowed some analysis based on a lookup of the locks taken but being able to look at the additional bottlenecks like the client-server communication completes the set and allows far deeper analysis.

SELECT query, wait_event_type, wait_event FROM pg_stat_activity
   WHERE wait_event IS NOT NULL;

This week sponsor okmeter.io — a complete Postgres monitoring.