PostgreSQL for the Oracle DBA, Part 5: Understanding Wait Analysis

Oracle: Wait Classes & Events

As discussed in an earlier blog post, Oracle groups wait events into wait classes like User I/O, System I/O, Concurrency, Network, Idle, Commit, and more.  By grouping wait events into categories, it helps the technologist identify where time is being spent in the database, (DB Time) and as there is often a correlation between waits, identifying culprits.

• This two-tier model (Class → Event) lets you query V$SYSTEM_WAIT_CLASS or V$SYSTEM_EVENT for high-level patterns and drill into specific events, as in the example: “db file sequential read” falls under the wait class User I/O.
• Class-based stats streamlines root‑cause analysis: identify a top class, then dive into its sub-events, which enables quicker resolution.

PostgreSQL: Wait Event Types

PostgreSQL’s wait metrics are moving at the speed of light as the open-source community builds out optimization advancements into the platform.  Although nowhere near as advanced as what is built into Oracle and its enterprise investment, its come a long way in recent years.

• PostgreSQL exposes atomic wait events such as ClientRead, DataFileRead, Lock:tuple, LWLock:buffer_io, and Timeout:PgSleep, among others
• There is no official grouping into wait classes; you work with a flat list of wait events (via pg_stat_activity.wait_event_type/wait_event or pg_wait_events)  I want to thank Bertrand Drovout, who pointed out this is not true for most PostgreSQL databases these days.  pg_stat_activity absolutely has categories of waits, referred to as event types, (where in Oracle it is Wait Class and SQL Server its Wait Categories).  Funniest part is that I was already calling them in my query below, but had read somewhere it wasn’t official in PostgreSQL.  Docs proved otherwise! 
• This does offer you very precise wait information, such as a session waiting on a tuple-level lock is clearly labeled, but as with any visual display of waits in a database platform, we prefer higher level information displayed, which can then be researched at a deeper level if desired.

Practical Implications for Oracle DBAs

Capability	Oracle (Wait Class → Event)	PostgreSQL (Wait Event Types)
High‑level visibility	Via wait class aggregation	Use Event Type in pg_stat_activity
Drill‑down to root cause	Drill into events per class	Direct via wait_event in pg_stat_activity
Session wait stats	V$SESSION_WAIT, V$SYSTEM_WAIT_CLASS	pg_stat_activity, pg_wait_events, pg_locks
Monitoring tools	AWR/ASH, OEM wait-class views, Redgate Monitor	pg_stat_activity, pg_wait_events, pgbadger, Grafana, Redgate PGNow and Redgate Monitor
PExtension support	N/A	pg_wait_sampling, EDB session_waits

Tips for Oracle DBAs in PostgreSQL

1. Think in events types not classes
   You’ll monitor for wait type events vs. wait classes.
2. Leverage system views
   Query pg_stat_activity for active wait events and pg_wait_events for descriptions such as the following query would deliver:

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

3. Use monitoring tools
Tools like pgbader, Prometheus + PostgreSQL exporter, Redgate’s Monitor or EDB’s session_wait history give insights similar to AWR/ASH and will only improve with time and community investment.

4. Apply Oracle mindset
Oracle may offer aggregated drill-down by class, but PostgreSQL gives you finer granularity per event type.  This simplifies the transition between the two database platforms and ensures Oracle DBAs are able to get up and running quickly when performance problems arise.

Wait Type Example in PostgreSQL

Using an example OLTP workload in PostgreSQL, we can use pg_stat_activity, which provides the data we need to identify wait types, just as we would in Oracle.

Using our query from above, we can view the waits and wait types, along with the PID and queries involved:

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

pid	wait_event_type	wait_event	query
1234	Lock	transactionid	UPDATE customers SET balance = balance – 100 WHERE id = 42;
1235	IO	DataFileRead	SELECT * FROM orders WHERE customer_id = 123 LIMIT 10;
1236	LWLock	BufferMapping	INSERT INTO sales (item_id, qty) VALUES (10, 2);
1237	Lock	tuple	DELETE FROM inventory WHERE item_id = 10;
1238	Activity	CheckpointerMain	in transaction
1239	IPC	BgworkerStartup
1240	IO	WALWrite	COMMIT;

What These Mean in Context:

• Lock → transactionid/tuple: Waiting for a lock on a tuple or transaction, often due to contention during concurrent DMLs (ie., two sessions trying to update the same row).
• IO → DataFileRead/WALWrite: Waiting for data to be read from disk or WAL (Write-Ahead Log) flushes, which is typical in high-write or read-on-cache-miss situations.
• LWLock → BufferMapping: Lightweight locks for internal buffers, often shows up in heavy OLTP workloads due to frequent access to shared buffers.
• IPC → BgworkerStartup: Background worker is idle and waiting to start up.
• Activity → CheckpointerMain: Waiting in main loop of checkpointer process.

With this information and after deeper research, you might consider:

• OLTP workloads create high concurrency and frequent access to shared resources, so you’ll commonly see lock waits and buffer or IO-related waits.
• To interpret these in real time, it’s useful to join this with pg_locks or use pg_stat_activity along with EXPLAIN (ANALYZE) output.
• For long-lasting waits, you might want to investigate potential deadlocks or inefficient indexing or query design.

In Summary

• Oracle uses wait classes to summarize performance issues, then allows detailed event drill-down.
• PostgreSQL provides event types for categories, similar to wait classes, but offering more precision at event level.

By translating your Oracle methodology and identifying wait category first, then drilling  into specifics, you’ll quickly adapt to PostgreSQL’s model. Your DBA toolkit stays valid; it’s just a different lens on waiting sessions.