What is ASH?
ASH stands for Active Session History. It is a real-time diagnostic technique pioneered by Oracle that provides a continuous, sampled record of session activity in a database engine.
PMP4PG implements an ASH mechanism natively for PostgreSQL, giving DBAs the same level of real-time visibility they expect from Oracle environments.
The Core Idea
Instead of looking at cumulative counters (which tell you how much happened but not when or why), ASH captures snapshots of active sessions at regular intervals — recording exactly what each session was doing, waiting for, and executing at that precise moment in time.
This sampling approach makes it possible to answer questions like:
- What was the database doing at 14:32:15?
- Which query caused the spike I see in my application at 3 PM?
- How long did sessions wait on lock events this afternoon?
- Which user or application was generating the most load in the last hour?
How PMP4PG Implements ASH
The PMP4PG agent queries pg_stat_activity every 2 seconds on the monitored PostgreSQL instance. Each sample captures the state of all active sessions at that moment:
pg_stat_activity snapshot (every 2s)
↓
PMP Agent (Go)
↓ (batched, gzip-compressed)
PMP4PG Backend
↓
ASH live table (repository)
↓
ASH Viewer (frontend — polled every 5s to 60s)
Each sample record captures:
| Field | Source |
|---|---|
| Session PID | pg_stat_activity.pid |
| Database name | pg_stat_activity.datname |
| Username | pg_stat_activity.usename |
| Application name | pg_stat_activity.application_name |
| Client address | pg_stat_activity.client_addr |
| Session state | pg_stat_activity.state |
| Wait event type | pg_stat_activity.wait_event_type |
| Wait event | pg_stat_activity.wait_event |
| Query text | pg_stat_activity.query |
| Query start time | pg_stat_activity.query_start |
| Transaction start | pg_stat_activity.xact_start |
ASH vs. Traditional Monitoring
| Traditional Monitoring | ASH |
|---|---|
| Cumulative counters | Point-in-time snapshots |
| Tells you totals | Tells you what was happening |
| Hard to diagnose past incidents | Can reconstruct any moment in the past 8h |
| Aggregated per minute | Sampled every 2 seconds |
Relationship to Oracle ASH
| Oracle Concept | PMP4PG Equivalent |
|---|---|
V$SESSION | pg_stat_activity |
V$ACTIVE_SESSION_HISTORY (in-memory) | ASH live table (last few hours) |
DBA_HIST_ACTIVE_SESS_HISTORY (on disk) | ASH history table (AWR snapshots) |
| ASH Report | PMP4PG ASH Report (HTML export) |