A PostgreSQL extension that captures per-query execution telemetry and exports it to ClickHouse in real-time. Unlike pg_stat_statements which aggregates statistics in PostgreSQL, pg_stat_ch exports raw events to ClickHouse where aggregation happens via ClickHouse's powerful analytical engine.

Run local PostgreSQL + ClickHouse with schema preloaded:

./scripts/quickstart.sh up ./scripts/quickstart.sh check

Stop:

./scripts/quickstart.sh down

See docker/quickstart/README.md for endpoints and stack details.

Overview

Architecture

Features

Supported Versions

Building from Source

Installation

Recommended PostgreSQL Settings

Configuration

SQL API

Example Queries

Testing

Troubleshooting

License

Overview

pg_stat_ch captures detailed telemetry for every query executed in PostgreSQL and exports it to ClickHouse via a single data pipeline:

PostgreSQL Hooks (foreground) → Shared Memory Queue → Background Worker → ClickHouse

Key design principles:

Data Flow

PostgreSQL Hooks capture query start/end with full instrumentation

Shared Memory Ring Buffer stores events (MPSC: multi-producer, single-consumer)

Background Worker dequeues batches and exports to ClickHouse

ClickHouse stores raw events in events_raw table; views/MVs provide aggregates

Features

Full Query Telemetry: Timing, row counts, buffer usage, WAL usage, CPU time

All Statement Types: DML (SELECT/INSERT/UPDATE/DELETE/MERGE), DDL, utility statements

Error Capture: SQLSTATE codes and error levels via emit_log_hook

JIT Instrumentation (PG15+): Function count, generation/inlining/optimization/emission time

Parallel Worker Stats (PG18+): Planned vs launched workers

Client Context: Application name, client IP address

Query Text: Captured with truncation (2KB max)

Graceful Degradation: Queue overflow drops events with counters; ClickHouse unavailability doesn't block PostgreSQL

Supported Versions

PostgreSQL 16, 17, and 18 are fully supported. See docs/version-compatibility.md for the feature matrix and version-specific fields.

ClickHouse 23.3+ is required for native export.

clickhouse-c is vendored as a submodule and statically linked.

git submodule update --init --recursive

Using mise (recommended)

mise run build # Debug build (use build:16/17/18 for specific versions) mise run build:release # Release build mise run install # Install the extension

Or manually

cmake -B build -G Ninja -DPG_CONFIG=/path/to/pg_config cmake --build build && cmake --install build

Installation

1. Configure PostgreSQL

Add to postgresql.conf:

shared_preload_libraries = 'pg_stat_ch' track_io_timing = on # Enables I/O timing columns for captured events

ClickHouse connection (change for your setup)

pg_stat_ch.clickhouse_host = 'localhost' pg_stat_ch.clickhouse_port = 9000 pg_stat_ch.clickhouse_database = 'pg_stat_ch'

TLS (recommended for production)

pg_stat_ch.clickhouse_use_tls = on pg_stat_ch.clickhouse_skip_tls_verify = off

2. Restart PostgreSQL

Restart PostgreSQL using your service manager (systemd, brew services, Docker, etc.)

3. Set Up Schema on ClickHouse

Quickstart path: run ./scripts/quickstart.sh up and schema setup is handled automatically.

clickhouse-client < docker/init/00-schema.sql

Creates events_raw plus materialized views. See docs/clickhouse.md for full schema details.

CREATE EXTENSION pg_stat_ch;

5. Verify

SELECT pg_stat_ch_version(); SELECT * FROM pg_stat_ch_stats();

Configuration

GUC Variables

Parameter Type Default Reload Description

pg_stat_ch.enabled bool on SIGHUP Enable/disable telemetry collection

pg_stat_ch.clickhouse_host string localhost Restart ClickHouse server hostname

pg_stat_ch.clickhouse_port int 9000 Restart ClickHouse native protocol port

pg_stat_ch.clickhouse_user string default Restart ClickHouse username

pg_stat_ch.clickhouse_password string "" Restart ClickHouse password

pg_stat_ch.clickhouse_database string pg_stat_ch Restart ClickHouse database name

pg_stat_ch.queue_capacity int 131072 Restart Ring buffer size (must be power of 2)

pg_stat_ch.string_area_size int 64MB Restart DSA size for variable-length query and error strings

pg_stat_ch.flush_interval_ms int 500 SIGHUP Export batch interval in milliseconds

pg_stat_ch.batch_max int 131072 SIGHUP Maximum events per ClickHouse insert (clamped to queue_capacity)

pg_stat_ch.clickhouse_use_tls bool off Restart Enable TLS for ClickHouse connections

pg_stat_ch.clickhouse_skip_tls_verify bool off Restart Skip TLS certificate verification (insecure)

pg_stat_ch.log_min_elevel enum warning Superuser Minimum error level to capture (debug5..panic)

See Error Level Values for the complete list of error levels and their numeric values in ClickHouse.

Function Description

pg_stat_ch_version() Returns extension version string

pg_stat_ch_stats() Queue and exporter statistics (column details)

pg_stat_ch_reset() Reset all queue counters to zero

pg_stat_ch_flush() Trigger immediate flush of queued events to ClickHouse

Example Queries

Raw per-execution events enable percentiles, time-series, and per-app drill-downs:

-- Slowest queries for a specific application, with percentiles SELECT query_id, count() AS calls, quantile(0.95)(duration_us) / 1000 AS p95_ms, quantile(0.99)(duration_us) / 1000 AS p99_ms FROM pg_stat_ch.events_raw WHERE app = 'myapp' AND ts_start > now() - INTERVAL 1 HOUR GROUP BY query_id ORDER BY p99_ms DESC LIMIT 10;

See docs/clickhouse.md for materialized view definitions and more query examples.

mise run test:all # Run all tests mise run test:regress # SQL regression tests only ./scripts/run-tests.sh 18 all # Specific PG version ./scripts/run-tests.sh ../postgres/install_tap tap # TAP tests with local PG build

See docs/testing.md for test types, TAP test setup, and a full listing of test files.

Common issues: extension not loading (check shared_preload_libraries), events not appearing (check pg_stat_ch_stats() for errors), high queue usage or dropped events (tune pg_stat_ch.queue_capacity, pg_stat_ch.flush_interval_ms, pg_stat_ch.batch_max).

See docs/troubleshooting.md for detailed solutions.

This project is licensed under the Apache License, Version 2.0. See LICENSE.md for the full license text.