pg_stat_ch

Overview

freeze due to new vcpkg dependency stack

Version

Build

You can build the RPM / DEB packages for pg_stat_ch using pig build:

pig build pkg pg_stat_ch         # build RPM / DEB packages

Install

You can install pg_stat_ch directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pg_stat_ch;          # Install for current active PG version

pig ext install -y pg_stat_ch -v 18  # PG 18
pig ext install -y pg_stat_ch -v 17  # PG 17
pig ext install -y pg_stat_ch -v 16  # PG 16

dnf install -y pg_stat_ch_18       # PG 18
dnf install -y pg_stat_ch_17       # PG 17
dnf install -y pg_stat_ch_16       # PG 16

apt install -y postgresql-18-pg-stat-ch   # PG 18
apt install -y postgresql-17-pg-stat-ch   # PG 17
apt install -y postgresql-16-pg-stat-ch   # PG 16

Preload:

shared_preload_libraries = 'pg_stat_ch';

Create Extension:

CREATE EXTENSION pg_stat_ch;

Usage

Sources: README, release 0.3.6

pg_stat_ch captures per-query PostgreSQL execution telemetry and exports raw events to ClickHouse through a shared-memory queue and background worker. The upstream project positions it as a raw-event alternative to pg_stat_statements, with aggregation and dashboards handled in ClickHouse instead of PostgreSQL.

CREATE EXTENSION pg_stat_ch;

Required Setup

pg_stat_ch must be preloaded and pointed at a ClickHouse database:

shared_preload_libraries = 'pg_stat_ch'
track_io_timing = on

pg_stat_ch.clickhouse_host = 'localhost'
pg_stat_ch.clickhouse_port = 9000
pg_stat_ch.clickhouse_database = 'pg_stat_ch'
pg_stat_ch.clickhouse_use_tls = on
pg_stat_ch.clickhouse_skip_tls_verify = off

The README says PostgreSQL 16, 17, and 18 are fully supported; the latest release is 0.3.6 from April 15, 2026.

SQL API

• pg_stat_ch_version() returns the extension version.
• pg_stat_ch_stats() exposes queue and exporter counters.
• pg_stat_ch_reset() clears queue counters.
• pg_stat_ch_flush() triggers an immediate export flush.

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

Important GUCs

• pg_stat_ch.enabled toggles collection.
• pg_stat_ch.queue_capacity and pg_stat_ch.string_area_size size the shared-memory buffers.
• pg_stat_ch.flush_interval_ms and pg_stat_ch.batch_max control exporter cadence and batch size.
• pg_stat_ch.log_min_elevel controls which errors are captured.

What Gets Captured

• Query timing, row counts, buffer usage, WAL usage, and CPU time.
• DML, DDL, and utility statements.
• SQLSTATE and error levels.
• JIT metrics on PostgreSQL 15+.
• Parallel worker stats on PostgreSQL 18+.
• Application name, client IP, and query text up to the upstream truncation limit.

Release 0.3.6 replaces the normalize cache with a queryId-keyed LRU cache and replaces the OpenTelemetry SDK with a direct-protobuf exporter plus sampling support. Existing SQL usage remains centered on the event tables, summary views, and reset/version helper functions above.

Caveats

• The design intentionally drops events on queue overflow instead of blocking the foreground query path.
• ClickHouse schema creation is a required part of setup; upstream quickstart scripts preload it automatically, but manual deployments must load the schema separately.
• Pigsty’s current RPM refresh for pg_stat_ch is PostgreSQL 16-18 on EL9/EL10 only; Debian/Ubuntu packages remain PostgreSQL 16-18 on the active targets.