Unlocking PostgreSQL Performance Insights with pg_wait_sampling

When running PostgreSQL in production, you’ll eventually face the situation where queries feel slower than they should be. You check pg_stat_activity, maybe even dive into logs, but still wonder:

👉 What exactly is my database waiting on?

That’s where the pg_wait_sampling extension shines. It provides lightweight, real-time insights into PostgreSQL wait events — helping you understand whether queries are blocked by locks, disk I/O, or internal background tasks.

In this article, we’ll explore what pg_wait_sampling is, why it’s useful, and how you can use it with practical query examples — plus a real-world troubleshooting story.

What is pg_wait_sampling?

PostgreSQL processes often wait for resources: locks, I/O, buffer pins, lightweight locks (LWLocks), etc. Normally, you’d only see the current wait state in pg_stat_activity.

pg_wait_sampling goes further by:

• Sampling wait events at regular intervals
• Storing them in system views
• Allowing historical analysis

This means you can detect bottlenecks that happen intermittently, not just when you happen to be looking.

Installing pg_wait_sampling

The extension is bundled with PostgreSQL contrib packages. After installing contrib modules, simply run:

CREATE EXTENSION pg_wait_sampling;

This creates several views, the most useful being:

• pg_wait_sampling_current → snapshot of active waits right now
• pg_wait_sampling_history → sampled history of wait events
• pg_wait_sampling_profile → aggregated stats per process

Example: Checking Current Waits

To see what queries are waiting on right now:

SELECT pid, event_type, event, query
FROM pg_wait_sampling_current c
JOIN pg_stat_activity a USING (pid)
WHERE a.state = 'active';

This helps you quickly spot if queries are stuck on a lock or slow I/O.

Example: Analyzing Historical Waits

Since the extension collects samples over time, you can query the history:

SELECT event_type, event, count(*) AS samples
FROM pg_wait_sampling_history
GROUP BY event_type, event
ORDER BY samples DESC
LIMIT 10;

This gives you the top wait events in your system. If Lock:transactionid dominates, you might have contention. If IO:DataFileRead shows up a lot, you may need to check disk performance.

Example: Profiling by Backend

Want to see which backend (session) spent the most time waiting?

SELECT pid, event_type, event, count(*) AS samples
FROM pg_wait_sampling_profile
GROUP BY pid, event_type, event
ORDER BY samples DESC
LIMIT 10;

This helps tie waits back to specific connections or workloads.

Real-World Troubleshooting Story

At one company I worked with, the team noticed their nightly batch jobs were running 2–3x slower than usual. CPU usage looked fine, and pg_stat_activity only showed a bunch of “active” queries — no obvious blockers.

After enabling pg_wait_sampling, we ran:

SELECT event_type, event, count(*) AS samples
FROM pg_wait_sampling_history
GROUP BY event_type, event
ORDER BY samples DESC;

The results were clear:

• Lock:transactionid dominated the wait samples.

Digging deeper, we found a long-running transaction from an analytics tool that had been left open. It wasn’t consuming CPU, but it was blocking autovacuum and piling up row version locks. Once the session was terminated and the tool’s connection handling was fixed, batch jobs returned to normal speed.

Without pg_wait_sampling, this would have been a frustrating case of guesswork. With it, the root cause was pinpointed in minutes.

Why It Matters

Without tools like pg_wait_sampling, database tuning often feels like guesswork. This extension makes performance troubleshooting data-driven by answering:

• Are queries waiting on locks?
• Is the system I/O-bound?
• Do certain queries starve others of resources?

And because it works with low overhead, you can safely use it in production.

Wrapping Up

pg_wait_sampling is a hidden gem for PostgreSQL operators. It turns mysterious “slow queries” into actionable insights by showing you exactly where time is spent.

If you’re managing PostgreSQL at scale, I recommend enabling it alongside pg_stat_statements for a complete performance toolkit.

I’ve been an Architect, a DevOps, a DataOps, and a DBA. I’m focused on databases, real-time data and streaming technologies. I work with Apache Kafka, RabbitMQ, Elastic Stack and PostgreSQL/MySQL. After hours I love to share my knowledge and experience. I’m founder of DataOps Poland group, it’s an exchange place of experience, concepts and ideas from Data Engineering world.

https://www.linkedin.com/in/tomasz-gintowt/