Skip to main content

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.

​
Quick start with Docker

From the repository root: 
./scripts/quickstart.sh up
This starts PostgreSQL (with pg_stat_ch pre-loaded) and ClickHouse with the full schema applied. See docker/quickstart/ for stack details. To stop the stack: 
./scripts/quickstart.sh down

​
Production setup

For production, apply the schema directly to your ClickHouse instance: 
clickhouse-client < docker/init/00-schema.sql
The schema file (docker/init/00-schema.sql) is the single source of truth. It creates:
  • The pg_stat_ch database
  • The events_raw table with all columns
  • Four materialized views for common analytics

​
Configure PostgreSQL to connect

Set the ClickHouse connection parameters in postgresql.conf: 
pg_stat_ch.clickhouse_host = 'clickhouse.internal'
pg_stat_ch.clickhouse_port = 9000
pg_stat_ch.clickhouse_user = 'default'
pg_stat_ch.clickhouse_password = 'your-password'
pg_stat_ch.clickhouse_database = 'pg_stat_ch'
These parameters require a PostgreSQL restart. See the configuration reference for all connection options including TLS.

​
Verify data flow

After connecting, check that events are being exported: 
-- In PostgreSQL
SELECT * FROM pg_stat_ch_stats();
exported_events should increase as queries run. If send_failures is non-zero, check last_error_text for the error message. 
-- In ClickHouse
SELECT count() FROM pg_stat_ch.events_raw;

​
Schema overview

The events_raw table stores one row per query execution with 50+ columns covering timing, buffer usage, WAL, CPU, JIT, errors, and client context. For the complete column reference, see events schema. Four materialized views provide pre-aggregated analytics:
ViewPurposeRetention
events_recent_1hReal-time debugging1-hour TTL
query_stats_5mQuery performance dashboards (p95/p99)Unbounded
db_app_user_1mLoad by application/userUnbounded
errors_recentError investigation7-day TTL
For view schemas, query patterns, and the -State/-Merge aggregation pattern, see materialized views.

​
Data retention

The events_raw table has no TTL by default. To limit storage, add a TTL: 
ALTER TABLE pg_stat_ch.events_raw
MODIFY TTL toDateTime(ts_start) + INTERVAL 30 DAY DELETE;
The materialized views with TTL (events_recent_1h, errors_recent) clean up automatically. For the unbounded views (query_stats_5m, db_app_user_1m), add TTLs based on your retention needs: 
ALTER TABLE pg_stat_ch.query_stats_5m
MODIFY TTL toDateTime(bucket) + INTERVAL 90 DAY DELETE;

​
ClickHouse sizing

pg_stat_ch events compress well in ClickHouse. Rough estimates:
QPSEvents/dayRaw size/dayCompressed/day
1008.6M~39 GB~2-4 GB
1,00086M~390 GB~20-40 GB
10,000864M~3.9 TB~200-400 GB
Actual compression depends on query diversity. Workloads with many similar queries compress better due to ClickHouse’s column-oriented storage and LZ4 compression.