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.

Quickstart

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.

Table of Contents

• 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:

• Zero network I/O on query path - Events are queued in shared memory, not sent synchronously
• Raw events, not aggregates - All aggregation (p50/p95/p99, top queries, errors) happens in ClickHouse
• Bounded memory - Fixed-size ring buffer with overflow counters; dropped events don't block queries
• Minimal overhead - ~5μs p99 overhead per captured statement

Architecture

Data Flow

1. PostgreSQL Hooks capture query start/end with full instrumentation
2. Shared Memory Ring Buffer stores events (MPSC: multi-producer, single-consumer)
3. Background Worker dequeues batches and exports to ClickHouse
4. 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.

Building from Source

Prerequisites:

• CMake 3.16+, C++17 compiler (GCC 9+, Clang 10+)
• PostgreSQL 16+ development headers
• mise (recommended) or manual PostgreSQL installation

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.

Manual / existing ClickHouse paths:

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

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

4. Create the Extension

CREATE EXTENSION pg_stat_ch;

5. Verify

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

Configuration

GUC Variables

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

SQL API

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.

Testing

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.

Troubleshooting

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.

License

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