nikolays/pg_ash

Purpose and entry points

pg_ash is a pure SQL/PL/pgSQL Active Session History subsystem installed inside PostgreSQL. It samples live activity, retains compact history, and exposes SQL readers for incident analysis. Primary entry point: sql/ash-install.sql; current full implementation: sql/ash-1.5-to-2.0.sql and versioned install scripts such as sql/ash-1.5.sql.

Installation, schema, and lifecycle

Installation creates the ash schema, storage objects, functions, privileges, and catalog documentation. Administrative control is through ash.start(), ash.stop(), ash.status(), manual sampling, rotation, rollups, reader grants, and destructive rebuild/uninstall operations. Upgrade is explicit and ordered through sql/migrations/ash-1.0-to-1.1.sql through sql/migrations/ash-1.5-to-2.0.sql; root-level migration files remain compatibility wrappers.

Sampling and query attribution

ash.take_sample() reads PostgreSQL runtime statistics, principally pg_stat_activity, and stores active-session wait state rather than idle heartbeats. It records query IDs and can enrich reader output with text through optional pg_stat_statements; sampling remains functional without that extension, but attribution/text is degraded. The sampler requires appropriate statistics visibility, notably pg_read_all_stats, to observe other roles’ query IDs.

Raw retention boundary

Raw history uses a partitioned ring: partitions are rotated and old slots truncated, yielding bounded short-term evidence. Rotation also protects data by requiring raw samples to be rolled into minute aggregates before endangered partitions are removed. Partition geometry, retention safety checks, and query-map reconstruction are part of the install/runtime SQL in sql/ash-1.5-to-2.0.sql; the intended partition/rollup design is documented in blueprints/CONFIGURABLE_PARTITIONS_AND_ROLLUP.md.

Compaction pipeline

Two retention tiers preserve longer history: ash.rollup_minute() folds raw samples into rollup_1m, then ash.rollup_hour() compacts minute history into rollup_1h; ash.rollup_cleanup() expires rollups. This is a deliberate fidelity boundary: raw preserves exact query/wait links, while rollups retain aggregates and limited query attribution. Reader plans disclose source/grain and degrade honestly when only coarser history is available.

Reader and reporting API

The ash.* reader surface provides progressive investigation: periods() identifies affected windows; timeline() locates spikes; top() ranks waits, query IDs, and databases; samples() returns raw evidence. aas(), compare(), summary(), chart(), and JSONB report() serve scalar, before/after, terminal, and machine-consumer use cases. Query-specific and combined wait/query filters force raw reads, preventing rollup omissions from being misrepresented as zero.

Scheduling and runtime dependencies

PostgreSQL 14+ is the material runtime. pg_cron is optional: when available, ash.start() schedules sampling, rotation, rollups, and cleanup; otherwise the database stores intended cadence and operators run the same functions externally via psql/cron. pg_stat_statements and compute_query_id are optional but recommended dependencies for useful query attribution. The project targets managed PostgreSQL because it needs neither a C extension nor server restart.

Delivery validation and demonstrations

CI validates fresh installation and upgrade chains using repository-discovered SQL paths; discovery logic is in devel/scripts/ash_sql_chain.py and workflow orchestration in .github/workflows/test.yml. The terminal demonstration drives a real PostgreSQL workload, queries the public API, and renders reproducible artifacts through demos/bin/ash-demo, demos/lib/seed.sql, and demos/render/ansi2svg.py.