What is AWR?
AWR stands for Automatic Workload Repository — a feature introduced in Oracle Database that periodically captures a snapshot of key performance statistics and stores them in a repository. By comparing two snapshots, DBAs can generate detailed workload reports that reveal exactly how the database behaved during a specific period.
PMP4PG implements an equivalent mechanism for PostgreSQL, bringing the same proactive analysis methodology to the open-source world.
The Core Concept
While ASH gives you a real-time, sample-based view of session activity, AWR operates at a different timescale. Instead of looking at individual sessions second by second, AWR answers questions like:
- How did my database perform between 9 AM and 11 AM compared to yesterday?
- Which queries consumed the most total CPU time during last night's batch?
- Did my cache hit ratio degrade after the schema change I deployed on Friday?
- How much WAL did we generate during peak hours this week?
How PMP4PG Implements AWR
Every 30 minutes, the PMP4PG backend automatically creates a numbered snapshot — a point-in-time aggregation of all key PostgreSQL statistics accumulated since the previous snapshot.
Snapshot N (09:00) Snapshot N+1 (09:30)
│ │
│←────── 30 minutes ────────→│
│ │
└──────── AWR Report ────────┘
(delta between N and N+1)
Each snapshot records:
| Data Source | What Is Captured |
|---|---|
pg_stat_statements | Query-level execution statistics (cumulative counters) |
pg_stat_database | Database-level throughput statistics |
pg_stat_bgwriter | Background writer and checkpoint statistics |
pg_stat_wal | WAL generation statistics |
pg_stat_user_tables | Table-level access and vacuum statistics |
pg_stat_user_indexes | Index-level usage statistics |
pg_locks | Lock contention data |
pg_database_size() | Database sizes |
| OS metrics | CPU, memory, disk at snapshot time |
Snapshots vs. Live Data
| Live Data (ASH) | AWR Snapshots | |
|---|---|---|
| Granularity | 2-second samples | 30-minute aggregates |
| Retention | Hours (purged regularly) | Long-term (configurable) |
| Use case | Real-time incident diagnosis | Proactive analysis, trends |
| Report type | ASH Report | AWR Report |
Relationship to Oracle AWR
| Oracle Concept | PMP4PG Equivalent |
|---|---|
| AWR Snapshot | PMP4PG AWR Snapshot (every 30 min) |
DBA_HIST_* tables | *_history tables in PMP repository |
DBMS_WORKLOAD_REPOSITORY | PMP4PG AWR Snapshot Engine (backend) |
AWR Report (awrrpt.sql) | PMP4PG AWR Viewer + HTML Export |
| Snapshot ID | PMP4PG Snapshot number |
| Baseline | Planned for a future release |