> ## Documentation Index
> Fetch the complete documentation index at: https://pg-stat-ch.clickhouse.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Introduction

> Per-query telemetry for PostgreSQL, stored and analyzed in ClickHouse

pg\_stat\_ch is a PostgreSQL extension that captures per-execution query telemetry and exports it to ClickHouse. Every query execution becomes a row in ClickHouse with timing, buffer usage, WAL activity, CPU time, JIT stats, error details, and client context.

## Why pg\_stat\_ch

PostgreSQL ships with `pg_stat_statements`, which aggregates query statistics in shared memory. It answers "how does this query perform on average?" but cannot answer:

* **When did it get slow?** Cumulative counters hide time-series trends. You can't see a latency spike that started 10 minutes ago.
* **What happened during that one slow execution?** Averages smooth over outliers. A single 30-second query disappears into a mean of 5ms.
* **Which application or user caused the load?** `pg_stat_statements` groups by query text, not by client.
* **What errors are happening and when?** Error tracking is not part of `pg_stat_statements`.

pg\_stat\_ch solves this by exporting raw, per-execution events to ClickHouse, where you can slice and aggregate the data however you need.

|                        | pg\_stat\_statements     | pg\_stat\_ch                      |
| ---------------------- | ------------------------ | --------------------------------- |
| **Granularity**        | Cumulative per query     | Per execution                     |
| **Time-series**        | No (counters only)       | Yes (timestamped events)          |
| **Percentiles**        | No                       | p50/p95/p99 via ClickHouse        |
| **Error tracking**     | No                       | SQLSTATE, error level, message    |
| **Client attribution** | No                       | Application name, client IP       |
| **Storage**            | PostgreSQL shared memory | ClickHouse (compressed, scalable) |
| **Retention**          | Resets on restart        | Days, weeks, or longer            |
| **Query overhead**     | \~1-2 us                 | \~5 us p99                        |

## Architecture

```mermaid theme={null}
graph LR
    A["PostgreSQL hooks<br/>(foreground)"] --> B["Shared-memory<br/>ring buffer"]
    B --> C["Background worker<br/>(exporter)"]
    C --> D["ClickHouse<br/>events_raw"]
    D --> E["Materialized views<br/>(aggregation)"]
```

1. **Hooks** capture query telemetry in the foreground path. The extension hooks into `ExecutorStart`, `ExecutorRun`, `ExecutorFinish`, `ExecutorEnd`, `ProcessUtility`, and `emit_log` to collect timing, buffer stats, WAL usage, CPU time, JIT metrics, errors, and client context.

2. **Shared-memory ring buffer** receives events with no network I/O on the query path. The buffer uses a multi-producer, single-consumer (MPSC) design with batched writes.

3. **Background worker** drains the ring buffer and inserts events to ClickHouse in batches. It runs on a configurable interval (default 200ms) with automatic retry and exponential backoff.

4. **ClickHouse materialized views** handle all aggregation. Pre-built views provide 5-minute query stats with percentiles, per-application load breakdowns, and error feeds. You can add your own views for custom analytics.

## What it captures

Every query execution produces an event with these fields:

| Category           | Fields                                          | Notes                                |
| ------------------ | ----------------------------------------------- | ------------------------------------ |
| **Timing**         | `ts_start`, `duration_us`                       | Microsecond precision                |
| **Identity**       | `db`, `username`, `pid`, `query_id`, `cmd_type` | `query_id` groups normalized queries |
| **Results**        | `rows`, `query`                                 | Query text truncated to 2 KB         |
| **Shared buffers** | `shared_blks_hit/read/dirtied/written`          | Cache hit ratio                      |
| **Local buffers**  | `local_blks_hit/read/dirtied/written`           | Temp table I/O                       |
| **Temp files**     | `temp_blks_read/written`                        | `work_mem` pressure                  |
| **I/O timing**     | `shared/local/temp_blk_read/write_time_us`      | Requires `track_io_timing=on`        |
| **WAL**            | `wal_records`, `wal_fpi`, `wal_bytes`           | Write-ahead log activity             |
| **CPU**            | `cpu_user_time_us`, `cpu_sys_time_us`           | User vs kernel time                  |
| **JIT**            | `jit_functions`, `jit_*_time_us`                | JIT compilation overhead (PG 15+)    |
| **Parallel**       | `parallel_workers_planned/launched`             | Worker efficiency (PG 18+)           |
| **Errors**         | `err_sqlstate`, `err_elevel`, `err_message`     | SQLSTATE code and severity           |
| **Client**         | `app`, `client_addr`                            | Load attribution                     |

See the [events schema reference](/reference/events-schema) for the full field list with types and tuning guidance.

## Supported versions

* PostgreSQL 16, 17, and 18
* ClickHouse (any recent version) or OpenTelemetry-compatible collectors

Newer PostgreSQL versions expose additional metrics. See [version compatibility](/reference/version-compatibility) for the feature matrix.

## Next steps

<CardGroup cols={2}>
  <Card title="Installation" icon="download" href="/get-started/installation">
    Build from source and load the extension
  </Card>

  <Card title="Quick start" icon="bolt" href="/get-started/quick-start">
    End-to-end setup in 5 minutes with Docker
  </Card>
</CardGroup>