pg_stat_ch

Overview

Release tag 0.3.4 still ships extension SQL version 0.1 by upstream design; source tarball bundles third-party dependencies.

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:

• Install
• pig
• dnf
• apt

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

Syntax:

CREATE EXTENSION pg_stat_ch;
SELECT pg_stat_ch_version();
SELECT * FROM pg_stat_ch_stats();

Sources: README, Blog post

pg_stat_ch captures per-query execution telemetry in PostgreSQL and exports raw events to ClickHouse in real time. The upstream project contrasts this with pg_stat_statements: instead of aggregating inside PostgreSQL, it sends raw events to ClickHouse for downstream analysis.

Architecture

The README describes a single pipeline:

PostgreSQL hooks -> shared memory queue -> background worker -> ClickHouse

Design goals called out upstream include:

• no network I/O on the query path
• bounded memory via a fixed-size ring buffer
• raw event export instead of local aggregation
• graceful degradation when the queue overflows or ClickHouse is unavailable

Setup

The extension must be preloaded and configured with ClickHouse connection settings:

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

After PostgreSQL restart and ClickHouse schema setup:

CREATE EXTENSION pg_stat_ch;

SQL API

The README documents these SQL functions:

• pg_stat_ch_version()
• pg_stat_ch_stats()
• pg_stat_ch_reset()

pg_stat_ch_stats() exposes queue and exporter counters so you can verify that events are being captured and flushed.

What Gets Captured

The current README lists support for:

• query timing and row counts
• buffer usage and WAL usage
• CPU time
• DML, DDL, and utility statements
• SQLSTATE and error levels
• JIT instrumentation on PostgreSQL 15+
• parallel worker statistics on PostgreSQL 18+
• client context such as application name and client IP

The project currently states full support for PostgreSQL 16, 17, and 18.