PostgreSQL Active Session History (pg_ash) – Complete DBA Guide

As Oracle DBAs, we are very familiar with Active Session History (ASH) for performance diagnostics. PostgreSQL does not provide built-in ASH like Oracle — but thanks to the open-source project pg_ash, we can implement similar functionality in PostgreSQL environments.

---

What is pg_ash?

pg_ash is a PostgreSQL extension that collects and stores Active Session History samples, similar to Oracle ASH. It helps DBAs analyze wait events, long-running queries, blocking sessions, and overall workload behavior.

• Tracks active sessions over time
• Captures wait events
• Stores query execution samples
• Helps in performance troubleshooting
• Provides historical workload visibility

---

Installation Steps

# Clone repository
git clone https://github.com/NikolayS/pg_ash.git

cd pg_ash

# Build and install
make
make install

Then connect to PostgreSQL and enable extension:

CREATE EXTENSION pg_ash;

---

Sample Queries for Monitoring

1️⃣ View ASH Samples:

SELECT * FROM pg_ash;

2️⃣ Top Queries by Activity:

SELECT queryid, count(*) AS samples
FROM pg_ash
GROUP BY queryid
ORDER BY samples DESC;

3️⃣ Analyze Wait Events:

SELECT wait_event_type, wait_event, count(*)
FROM pg_ash
GROUP BY wait_event_type, wait_event
ORDER BY count(*) DESC;

---

Why pg_ash is Important for DBAs

• Provides Oracle-like ASH visibility in PostgreSQL
• Helps identify performance bottlenecks
• Useful for production troubleshooting
• Great learning tool for PostgreSQL internals
• Bridges monitoring gap for hybrid Oracle + PostgreSQL DBAs

---

DBA Perspective

For DBAs transitioning from Oracle to PostgreSQL (especially in cloud environments like AWS RDS), pg_ash provides a familiar monitoring model. It helps us analyze workload patterns in a structured way, similar to Oracle ASH/AWR concepts.

This makes it a powerful tool for performance diagnostics in modern PostgreSQL deployments.

---

Conclusion

Open-source tools like pg_ash demonstrate the strength of the PostgreSQL ecosystem. Understanding ASH-style monitoring is essential for DBAs working in hybrid Oracle and PostgreSQL environments.

Stay tuned for more PostgreSQL performance deep dives as part of my learning journey.

---

References & Credits

This open-source extension is developed by Nikolay Samokhvalov.

Official GitHub Repository:
https://github.com/NikolayS/pg_ash

All credits for the original project belong to the author. This article is written for learning and DBA knowledge-sharing purposes.