Skip to main content

​
Quick start with Docker

From the repository root:
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:

​
Production setup

For production, apply the schema directly to your ClickHouse instance:
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:
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:
exported_events should increase as queries run. If send_failures is non-zero, check last_error_text for the error message.

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

​
ClickHouse sizing

pg_stat_ch events compress well in ClickHouse. Rough estimates: Actual compression depends on query diversity. Workloads with many similar queries compress better due to ClickHouse’s column-oriented storage and LZ4 compression.