Understanding where a PostgreSQL server spends its time is essential when diagnosing performance issues. Many database performance problems occur when backend processes are waiting for resources such as locks, I/O operations, or background worker activities. PostgreSQL exposes current wait events through the pg_stat_activity view, but it does not provide a historical sampling of wait events by default.
The pg_wait_sampling extension solves this limitation by periodically sampling wait events from active PostgreSQL processes and storing them in shared memory. This allows database administrators and developers to analyze wait behavior over time and identify bottlenecks more effectively.
What is pg_wait_sampling?
pg_wait_sampling is an extension that collects periodic samples of PostgreSQL wait events. Instead of only seeing the current wait state of processes, it allows users to analyze historical wait patterns.
The extension periodically records:
- Process ID (PID)
- Wait event type
- Specific wait event
- Query ID
- Timestamp of the event
This data can be accessed through SQL functions and views provided by the extension.Key benefits include the following:
- Understanding system performance bottlenecks
- Identifying I/O or lock contention
- Tracking wait patterns over time
- Profiling backend process behavior
Installing pg_wait_sampling
The extension is available on GitHub and can be compiled using PostgreSQL’s extension build infrastructure.
Step 1: Clone the Repository
git clone https://github.com/postgrespro/pg_wait_sampling.git
cd pg_wait_sampling
This downloads the source code of the extension.
Step 2: Compile the Extension
make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config
Explanation:
- USE_PGXS=1 tells PostgreSQL to build the extension using the PostgreSQL extension building system.
- PG_CONFIG specifies the location of the PostgreSQL installation.
Step 3: Install the Extension
sudo make USE_PGXS=1 PG_CONFIG=/usr/lib/postgresql/18/bin/pg_config install
This command copies the compiled files into PostgreSQL directories.
Typical installed locations include:
/usr/lib/postgresql/18/lib/pg_wait_sampling.so
/usr/share/postgresql/18/extension/pg_wait_sampling.control
/usr/share/postgresql/18/extension/pg_wait_sampling--1.1.sql
These files allow PostgreSQL to recognize and load the extension
Configuring shared_preload_libraries
After installation, the extension must be added to shared_preload_libraries.
First, check the configuration file location:
show config_file;
Result:
/etc/postgresql/18/main/postgresql.conf
Before modification:
show shared_preload_libraries;
Result :
shared_preload_libraries
--------------------------
pg_stat_statements
Edit the configuration file:
sudo nano /etc/postgresql/18/main/postgresql.conf
Modify the setting:
shared_preload_libraries = 'pg_stat_statements,pg_wait_sampling'
Restart PostgreSQL:
sudo systemctl restart postgresql
Why shared_preload_libraries is Required
pg_wait_sampling collects wait events using background hooks and shared memory structures. These must be initialized during PostgreSQL server startup.
Extensions listed in shared_preload_libraries are loaded when PostgreSQL starts, allowing them to:
- allocate shared memory
- register background workers
- hook into internal server events
If the extension is not preloaded, shared memory structures cannot be created, which causes the following error:
ERROR: pg_wait_sampling shared memory wasn't initialized yet
This occurs because the sampling collector has not been initialized.
Creating the Extension
After configuring the server, connect to PostgreSQL:
sudo su postgres
psql
Create the extension:
CREATE EXTENSION pg_wait_sampling;
Verify installation:
\dx+ pg_wait_sampling
Result:
Objects in extension "pg_wait_sampling"
function pg_wait_sampling_get_current(integer)
function pg_wait_sampling_get_history()
function pg_wait_sampling_get_profile()
function pg_wait_sampling_reset_profile()
type pg_wait_sampling_current
type pg_wait_sampling_history
type pg_wait_sampling_profile
view pg_wait_sampling_current
view pg_wait_sampling_history
view pg_wait_sampling_profile
The extension provides functions, types, and views for accessing wait sampling data.
Understanding pg_wait_sampling Functions
1. pg_wait_sampling_get_current()
This function returns the current wait event of a specific backend process.
Function Definition
\df+ pg_wait_sampling_get_current
Result:
Schema | public
Name | pg_wait_sampling_get_current
Result data type | SETOF record
Argument data types | pid integer, OUT pid integer, OUT event_type text, OUT event text, OUT queryid bigint
Type | func
Volatility | volatile
Parallel | unsafe
Owner | postgres
Security | invoker
Leakproof? | no
Access privileges | =X/postgres +
| postgres=X/postgres +
| anon=X/postgres +
| authenticated=X/postgres +
| service_role=X/postgres
Language | c
Internal name | pg_wait_sampling_get_current
Description |
Example
SELECT * FROM pg_wait_sampling_get_current(39846);
Result:
pid | 39846
event_type | Activity
event | WalWriterMain
queryid | 0
Explanation:
- pid - backend process ID
- event_type - category of wait event
- event - specific wait activity
- queryid - query identifier
This indicates that process 39846 is currently performing the WalWriterMain activity.
2. pg_wait_sampling_get_history()
This function returns a history of sampled wait events.
Check the functions metadata by this command
\df+ pg_wait_sampling_get_history
Result :
Schema | public
Name | pg_wait_sampling_get_history
Result data type | SETOF record
Argument data types | OUT pid integer, OUT ts timestamp with time zone, OUT event_type text, OUT event text, OUT queryid bigint
Type | func
Volatility | volatile
Parallel | unsafe
Owner | postgres
Security | invoker
Leakproof? | no
Access privileges | =X/postgres +
| postgres=X/postgres +
| anon=X/postgres +
| authenticated=X/postgres +
| service_role=X/postgres
Language | c
Internal name | pg_wait_sampling_get_history
Description |
Example:
SELECT * FROM pg_wait_sampling_get_history() LIMIT 5;
Result:
pid | ts | event_type | event | queryid
--------------------------------------------------------
39847 | 2026-03-17 23:09:20 | Activity | AutovacuumMain | 0
39849 | 2026-03-17 23:09:20 | Activity | LogicalLauncherMain | 0
39840 | 2026-03-17 23:09:20 | Activity | IoWorkerMain | 0
39841 | 2026-03-17 23:09:20 | Activity | IoWorkerMain | 0
39842 | 2026-03-17 23:09:20 | Activity | IoWorkerMain | 0
Explanation:
- pid - backend process ID
- ts - timestamp when the sample was collected
- event_type - category of wait
- event - specific event name
- queryid - identifier of the executed query
This data allows administrators to analyze how backend processes behaved over time.
3. pg_wait_sampling_get_profile()
This function aggregates wait events and returns counts.
Check this functions metadata
\df+ pg_wait_sampling_get_profile
Result :
Schema | public
Name | pg_wait_sampling_get_profile
Result data type | SETOF record
Argument data types | OUT pid integer, OUT event_type text, OUT event text, OUT queryid bigint, OUT count bigint
Type | func
Volatility | volatile
Parallel | unsafe
Owner | postgres
Security | invoker
Leakproof? | no
Access privileges | =X/postgres +
| postgres=X/postgres +
| anon=X/postgres +
| authenticated=X/postgres +
| service_role=X/postgres
Language | c
Internal name | pg_wait_sampling_get_profile
Description |
Example:
SELECT * FROM pg_wait_sampling_get_profile() LIMIT 5;
Result:
pid | event_type | event | queryid | count
------------------------------------------
39846 | IO | WalSync | 0 | 2
39842 | Activity | IoWorkerMain | 0 | 9482
40030 | | | 0 | 4
39844 | | | 0 | 3
39841 | Activity | IoWorkerMain | 0 | 9482
Explanation:
- count represents how many times a specific wait event occurred.
- High counts can indicate frequent waiting on that resource.
For example:
39842 | Activity | IoWorkerMain | 9952
This means process 39842 spent significant time performing I/O worker activities.
4. pg_wait_sampling_reset_profile()
This function resets collected profiling data.
Check this function’s metadata.
\df+ pg_wait_sampling_reset_profile
Result :
Schema | public
Name | pg_wait_sampling_reset_profile
Result data type | void
Argument data types |
Type | func
Volatility | volatile
Parallel | unsafe
Owner | postgres
Security | invoker
Leakproof? | no
Access privileges | postgres=X/postgres +
| anon=X/postgres +
| authenticated=X/postgres +
| service_role=X/postgres
Language | c
Internal name | pg_wait_sampling_reset_profile
Description |
Example:
SELECT * FROM pg_wait_sampling_reset_profile();
Result:
pg_wait_sampling_reset_profile
--------------------------------
(1 row)
Purpose:
- Clears accumulated wait statistics
- Useful before starting a new performance analysis session
Using the Extension Views
The extension also provides convenient views.
pg_wait_sampling_current
Shows the current wait events of active processes.
Example:
SELECT * FROM pg_wait_sampling_current;
Result:
pid | event_type | event | queryid
-------------------------------------------
39847 | Activity | AutovacuumMain | 0
39848 | Extension | Extension | 0
39849 | Activity | LogicalLauncherMain | 0
39840 | Activity | IoWorkerMain | 0
39841 | Activity | IoWorkerMain | 0
39842 | Activity | IoWorkerMain | 0
39843 | Activity | CheckpointerMain | 0
39844 | Activity | BgwriterMain | 0
39846 | Activity | WalWriterMain | 0
These processes correspond to PostgreSQL background workers such as:
- autovacuum
- checkpointer
- WAL writer
- background writer
- I/O workers
pg_wait_sampling_history
Provides historical samples.
Example:
SELECT * FROM pg_wait_sampling_history LIMIT 5;
Result:
pid | ts | event_type | event | queryid
--------------------------------------------------------
39847 | 2026-03-17 23:10:11 | Activity | AutovacuumMain | 0
39849 | 2026-03-17 23:10:11 | Activity | LogicalLauncherMain | 0
39840 | 2026-03-17 23:10:11 | Activity | IoWorkerMain | 0
39841 | 2026-03-17 23:10:11 | Activity | IoWorkerMain | 0
39842 | 2026-03-17 23:10:11 | Activity | IoWorkerMain | 0
This view helps track wait patterns over time.
pg_wait_sampling_profile
Shows aggregated wait statistics.
Example:
SELECT * FROM pg_wait_sampling_profile;
Sample result:
pid | event_type | event | queryid | count
------------------------------------------
39846 | IO | WalSync | 0 | 2
39842 | Activity | IoWorkerMain | 9952
39843 | Activity | CheckpointerMain | 9952
39847 | Activity | AutovacuumMain | 9952
39849 | Activity | LogicalLauncherMain | 9952
This makes it easier to identify which processes spend the most time waiting.
Common Wait Event Types Observed
From the collected results, several important PostgreSQL activities appear:
AutovacuumMain
Responsible for automatic vacuuming to reclaim dead tuples.
LogicalLauncherMain
Manages logical replication workers.
IoWorkerMain
Handles asynchronous I/O operations.A new feature implemented in postgresql version 18
WalWriterMain
Responsible for writing WAL buffers to disk.
CheckpointerMain
Periodically writes dirty buffers to disk.
BgwriterMain
Background writer that flushes buffers to reduce checkpoint spikes.
The pg_wait_sampling extension provides a powerful way to analyze PostgreSQL wait events over time. By sampling wait activity and storing it in shared memory, the extension enables deeper performance analysis compared to standard PostgreSQL monitoring tools.
Through functions such as pg_wait_sampling_get_current, pg_wait_sampling_get_history, and pg_wait_sampling_get_profile, administrators can track both real-time and historical wait behavior. Combined with proper configuration using shared_preload_libraries, this extension becomes an effective tool for diagnosing database performance issues.
Understanding wait events is a key step in identifying system bottlenecks, and pg_wait_sampling makes that analysis significantly easier for PostgreSQL users.