Wait Events in PostgreSQL

 A wait event occurrence might indicate a performance problem. In the last module, we discussed WALWriteLock. Recall that it is a lock that appears when connections are trying to write to the same transaction log. A great place for DBAs to see wait events is in Performance Insights, where there is a stacked graph. This graph breaks down what a query is waiting on at any given point. Wait event spikes often correlate with higher central processing unit (CPU) or I/O usage. They are particularly useful for getting to the root cause of production incidents.

The wait_event_type column indicates the type of event for which the backend is waiting. NULL indicates that there is no specific event.

There are wait event types and wait event names. For example, there might be an LWLock. This type of wait event might have 1 of 65 wait event names.

The wait event name indicates what exactly is being waited on. An example is a ShmemIndexLock. This indicates that the backend is waiting to find or allocate space in shared memory.

Here, there are two types of wait events: one instance of Lock and seven of LWLockNamed. The wait_event named types are WALWriteLock and transactionid.

-To check wait event

---------------------

select pid,wait_event_type,wait_event from pg_stat_activity where wait_event is NOT NULL;

Wait event types

--------------

There are nine different wait event types. Review the following table of wait event types. For the full documentation, see Table 27.4 in the Statistics Collector chapter in the PostgreSQL documentation.

Review the table of common lightweight wait event values.

Type	Description
LWLock	Lock protecting a data structure in shared memory
Lock	Lock protecting SQL-visible objects such as tables
BufferPin	Waiting for access to a data buffer
Activity	Waiting for system processes
Extension	Waiting for activity in an extension module
Client	Waiting for some activity on a socket
IPC	Waiting for another process in the server
Timeout	Waiting for a timeout to expire
IO	Waiting for an IO to complete

Locks

--------

A lock is a heavyweight lock. Heavyweight locks are primarily used to protect structured query language (SQL)-visible objects, such as tables. 

wait_event will indicate the type of lock and name to identify the purpose of the lock.

Type	Description
Advisory	Waiting to acquire an advisory user lock
Page	Waiting to acquire a lock on a page of a relation
Userlock	Waiting to acquire a user lock
Relation	Waiting to acquire a lock on a relation
Tuple	Waiting to acquire a lock on a tuple

Lightweight locks

--------------

A lightweight lock refers to a quick event. LWLock indicates a lightweight lock. 

Review the following table for some examples of lightweight locks.

Type	Description
AutoFile	Waiting to update the PostgreSQL.auto.conf file
TablespaceCreate	Waiting to create or drop a tablespace
WALWrite	Waiting for Write Ahead Log (WAL) buffers to be written to disk