# nikolays/pg_ash architecture

> Architecture diagram of the GitHub repository nikolays/pg_ash, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-08-13). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/nikolays/pg_ash
- Repository: https://github.com/nikolays/pg_ash

## Overview

#### 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`.

Main entry points: [`sql/ash-1.5-to-2.0.sql`](https://github.com/nikolays/pg_ash/blob/main/sql/ash-1.5-to-2.0.sql) (Raw partition ring), [`.github/workflows/test.yml`](https://github.com/nikolays/pg_ash/blob/main/.github/workflows/test.yml) (Install & upgrade CI), [`demos/bin/ash-demo`](https://github.com/nikolays/pg_ash/blob/main/demos/bin/ash-demo) (Terminal demo), [`sql/ash-install.sql`](https://github.com/nikolays/pg_ash/blob/main/sql/ash-install.sql) (Install entry point)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_install["Install & lifecycle"]
  node_install_entry["Install entry point<br/>SQL installer<br/>[ash-install.sql]"]
  node_implementation["ASH 2.0 implementation<br/>versioned SQL<br/>[ash-1.5-to-2.0.sql]"]
  node_migrations["Ordered migrations<br/>upgrade SQL<br/>[ash-1.0-to-1.1.sql]"]
  node_admin_api["Lifecycle controls<br/>ash admin API"]
end

subgraph group_capture["Capture &amp; retention"]
  node_runtime_stats[("pg_stat_activity<br/>PostgreSQL statistics")]
  node_sampler["take_sample&#40;&#41;<br/>sampling function"]
  node_raw_ring[("Raw partition ring<br/>bounded raw storage<br/>[ash-1.5-to-2.0.sql]")]
  node_rotation["Rotation safety<br/>retention function<br/>[ash-1.5-to-2.0.sql]"]
  node_minute_rollup[("rollup_1m<br/>minute aggregate")]
  node_hour_rollup[("rollup_1h<br/>hour aggregate")]
  node_rollup_jobs["Rollup &amp; cleanup<br/>retention functions"]
end

subgraph group_readers["Reader API"]
  node_reader_api["Investigation readers<br/>ash reader API"]
  node_raw_filters["Exact raw filters<br/>reader policy"]
end

subgraph group_delivery["Validation &amp; demo"]
  node_ci_discovery["SQL chain discovery<br/>validation script<br/>[ash_sql_chain.py]"]
  node_ci_workflow["Install &amp; upgrade CI<br/>GitHub workflow<br/>[test.yml]"]
  node_demo["Terminal demo<br/>demonstration runner"]
  node_demo_seed["Demo workload<br/>seed SQL<br/>[seed.sql]"]
  node_renderer["ANSI artifact renderer<br/>demo renderer<br/>[ansi2svg.py]"]
end

node_postgres[("PostgreSQL 14+<br/>database runtime")]
node_pgss{{"pg_stat_statements<br/>optional extension"}}
node_scheduler["pg_cron or external cron<br/>optional scheduler"]

node_install_entry -->|"loads"| node_implementation
node_implementation -->|"installs into"| node_postgres
node_implementation -->|"defines"| node_admin_api
node_implementation -->|"defines"| node_raw_ring
node_admin_api -->|"controls"| node_sampler
node_runtime_stats -->|"sampled by"| node_sampler
node_sampler -->|"writes active evidence"| node_raw_ring
node_pgss -.->|"optional query-text enrichment"| node_sampler
node_scheduler -.->|"invokes scheduled work"| node_admin_api
node_rotation -->|"rotates and truncates"| node_raw_ring
node_raw_ring -->|"must roll up before removal"| node_rotation
node_raw_ring -->|"folded by rollup_minute"| node_minute_rollup
node_minute_rollup -->|"compacted by rollup_hour"| node_hour_rollup
node_rollup_jobs -->|"maintains"| node_minute_rollup
node_rollup_jobs -->|"maintains and expires"| node_hour_rollup
node_reader_api -->|"reads exact evidence"| node_raw_ring
node_reader_api -->|"reads minute grain"| node_minute_rollup
node_reader_api -->|"falls back to hour grain"| node_hour_rollup
node_raw_filters -->|"constrains plans"| node_reader_api
node_raw_filters -->|"requires"| node_raw_ring
node_ci_discovery -->|"supplies SQL paths"| node_ci_workflow
node_ci_workflow -->|"validates fresh install"| node_install_entry
node_ci_workflow -->|"validates upgrades"| node_migrations
node_demo_seed -->|"generates workload"| node_demo
node_demo -->|"exercises"| node_reader_api
node_demo -->|"renders artifacts with"| node_renderer

click node_install_entry "https://github.com/nikolays/pg_ash/blob/main/sql/ash-install.sql"
click node_implementation "https://github.com/nikolays/pg_ash/blob/main/sql/ash-1.5-to-2.0.sql"
click node_migrations "https://github.com/nikolays/pg_ash/blob/main/sql/migrations/ash-1.0-to-1.1.sql"
click node_raw_ring "https://github.com/nikolays/pg_ash/blob/main/sql/ash-1.5-to-2.0.sql"
click node_rotation "https://github.com/nikolays/pg_ash/blob/main/sql/ash-1.5-to-2.0.sql"
click node_ci_discovery "https://github.com/nikolays/pg_ash/blob/main/devel/scripts/ash_sql_chain.py"
click node_ci_workflow "https://github.com/nikolays/pg_ash/blob/main/.github/workflows/test.yml"
click node_demo "https://github.com/nikolays/pg_ash/blob/main/demos/bin/ash-demo"
click node_demo_seed "https://github.com/nikolays/pg_ash/blob/main/demos/lib/seed.sql"
click node_renderer "https://github.com/nikolays/pg_ash/blob/main/demos/render/ansi2svg.py"

classDef toneNeutral fill:#f8fafc,stroke:#334155,stroke-width:1.5px,color:#0f172a
classDef toneBlue fill:#dbeafe,stroke:#2563eb,stroke-width:1.5px,color:#172554
classDef toneAmber fill:#fef3c7,stroke:#d97706,stroke-width:1.5px,color:#78350f
classDef toneMint fill:#dcfce7,stroke:#16a34a,stroke-width:1.5px,color:#14532d
classDef toneRose fill:#ffe4e6,stroke:#e11d48,stroke-width:1.5px,color:#881337
classDef toneIndigo fill:#e0e7ff,stroke:#4f46e5,stroke-width:1.5px,color:#312e81
classDef toneTeal fill:#ccfbf1,stroke:#0f766e,stroke-width:1.5px,color:#134e4a
class node_install_entry,node_implementation,node_migrations,node_admin_api toneBlue
class node_runtime_stats,node_sampler,node_raw_ring,node_rotation,node_minute_rollup,node_hour_rollup,node_rollup_jobs toneAmber
class node_reader_api,node_raw_filters toneMint
class node_ci_discovery,node_ci_workflow,node_demo,node_demo_seed,node_renderer toneRose
class node_postgres,node_pgss,node_scheduler toneNeutral
```

## Components

### Install & lifecycle

- [Install entry point](https://github.com/nikolays/pg_ash/blob/main/sql/ash-install.sql): SQL installer · `sql/ash-install.sql`
- [ASH 2.0 implementation](https://github.com/nikolays/pg_ash/blob/main/sql/ash-1.5-to-2.0.sql): versioned SQL · Creates schema, storage, functions, privileges, and documentation. · `sql/ash-1.5-to-2.0.sql`
- [Ordered migrations](https://github.com/nikolays/pg_ash/blob/main/sql/migrations/ash-1.0-to-1.1.sql): upgrade SQL · Explicit upgrades span 1.0 through 2.0. · `sql/migrations/ash-1.0-to-1.1.sql`
- **Lifecycle controls**: ash admin API · start, stop, status, manual sampling, rotation, and rebuild controls.

### Capture & retention

- **pg_stat_activity**: PostgreSQL statistics · Live session, wait-state, and query-ID source.
- **take_sample()**: sampling function · Stores active-session evidence and skips idle heartbeats.
- [Raw partition ring](https://github.com/nikolays/pg_ash/blob/main/sql/ash-1.5-to-2.0.sql): bounded raw storage · Rotated partitions retain short-term exact query/wait links. · `sql/ash-1.5-to-2.0.sql`
- [Rotation safety](https://github.com/nikolays/pg_ash/blob/main/sql/ash-1.5-to-2.0.sql): retention function · Rolls endangered raw data up before truncating old slots. · `sql/ash-1.5-to-2.0.sql`
- **rollup_1m**: minute aggregate
- **rollup_1h**: hour aggregate
- **Rollup & cleanup**: retention functions · Compacts minute history to hours and expires aggregate history.

### Reader API

- **Investigation readers**: ash reader API · periods, timeline, top, samples, aas, compare, summary, chart, and report.
- **Exact raw filters**: reader policy · Query-specific and wait/query filters force raw reads.

### Validation & demo

- [SQL chain discovery](https://github.com/nikolays/pg_ash/blob/main/devel/scripts/ash_sql_chain.py): validation script · `devel/scripts/ash_sql_chain.py`
- [Install & upgrade CI](https://github.com/nikolays/pg_ash/blob/main/.github/workflows/test.yml): GitHub workflow · Validates fresh installs and upgrade chains. · `.github/workflows/test.yml`
- [Terminal demo](https://github.com/nikolays/pg_ash/blob/main/demos/bin/ash-demo): demonstration runner · Drives a live workload and queries the public API. · `demos/bin/ash-demo`
- [Demo workload](https://github.com/nikolays/pg_ash/blob/main/demos/lib/seed.sql): seed SQL · `demos/lib/seed.sql`
- [ANSI artifact renderer](https://github.com/nikolays/pg_ash/blob/main/demos/render/ansi2svg.py): demo renderer · `demos/render/ansi2svg.py`

### Other components

- **PostgreSQL 14+**: database runtime · Hosts the pure SQL/PLpgSQL subsystem.
- **pg_stat_statements**: optional extension · Enriches query IDs with query text when available.
- **pg_cron or external cron**: optional scheduler · Runs sampling, rotation, rollups, and cleanup on the intended cadence.

## Connections

- Install entry point → ASH 2.0 implementation: loads
- ASH 2.0 implementation → PostgreSQL 14+: installs into
- ASH 2.0 implementation → Lifecycle controls: defines
- ASH 2.0 implementation → Raw partition ring: defines
- Lifecycle controls → take_sample(): controls
- pg_stat_activity → take_sample(): sampled by
- take_sample() → Raw partition ring: writes active evidence
- pg_stat_statements → take_sample(): optional query-text enrichment
- pg_cron or external cron → Lifecycle controls: invokes scheduled work
- Rotation safety → Raw partition ring: rotates and truncates
- Raw partition ring → Rotation safety: must roll up before removal
- Raw partition ring → rollup_1m: folded by rollup_minute
- rollup_1m → rollup_1h: compacted by rollup_hour
- Rollup & cleanup → rollup_1m: maintains
- Rollup & cleanup → rollup_1h: maintains and expires
- Investigation readers → Raw partition ring: reads exact evidence
- Investigation readers → rollup_1m: reads minute grain
- Investigation readers → rollup_1h: falls back to hour grain
- Exact raw filters → Investigation readers: constrains plans
- Exact raw filters → Raw partition ring: requires
- SQL chain discovery → Install & upgrade CI: supplies SQL paths
- Install & upgrade CI → Install entry point: validates fresh install
- Install & upgrade CI → Ordered migrations: validates upgrades
- Demo workload → Terminal demo: generates workload
- Terminal demo → Investigation readers: exercises
- Terminal demo → ANSI artifact renderer: renders artifacts with