. -->

pg_stat_ch: a PostgreSQL extension that exports every metric to ClickHouse | ClickHouse<br>Skip to content

Open searchOpen region selectorEnglish<br>Japanese<br>Korean<br>Chinese<br>French<br>Spanish<br>Portuguese<br>Arabic

49.2kSign inGet Started

->Scroll to top<br>BackBlog<br>Engineering<br>Copy pageCopied!More actionsView as Markdown Open this page in Markdown<br>Open in ChatGPT Ask questions about this page<br>Open in Claude Ask questions about this page<br>Open in v0 Ask questions about this page

pg_stat_ch: a PostgreSQL extension that exports every metric to ClickHouse

Kaushik Iska<br>Feb 13, 2026 · 21 minutes read

We’re open sourcing pg_stat_ch : a PostgreSQL extension that turns every query execution into a fixed-size ~4.6KB event and streams them into ClickHouse.

Once events are in ClickHouse, you can slice and drill into query behavior like an APM: p50 to p99 latency over time, top queries by runtime, errors by app, and “what changed between 2pm and 3pm” across days or months of history.

It’s open-source, Apache 2.0, and supports PostgreSQL 16 to 18. If you want to try it, give the quickstart a go.

In this post, I’ll walk through how it works under the hood, the tradeoffs we made, and how it compares with existing extensions.

Why build pg_stat_ch? #

In January, we launched Postgres managed by ClickHouse. We need to understand how the clusters we manage are running, and we want to provide the same level of insight to our customers.

ClickHouse, the analytical database we’re best known for, comes with its own internal system tables that collect everything happening within the server. It’s also built for analytics, so you can just analyse your ClickHouse usage within ClickHouse itself. We rely on this for the managed ClickHouse service in ClickHouse Cloud, as do our customers.

Postgres doesn’t have that level of introspection capability out of the box, and isn’t built for analytics. So, we wanted a way to match that level of detail about what's happening inside Postgres, and the same level of analytical capability to work with it. See below for examples of the kind of insights you can get with pg_stat_ch.

We frequently use extensions like pg_stat_statements, pg_stat_monitor and pg_tracing, and while they cover some parts of the problem, we had 3 primary goals that they didn’t cover:

Capture everything a PostgreSQL cluster does: every SELECT, INSERT, DDL, and even the queries that fail.

Ship events off to an external system to handle analyses

Incur minimal overhead to PostgreSQL

We haven’t shipped pg_stat_ch to production yet, but we’re actively driving it there as part of ClickHouse’s managed Postgres effort. Today it streams per-query events into ClickHouse with a 2KB query text cap and no plan capture yet.

If you operate PostgreSQL at scale, we’d love feedback, and we’ll keep sharing what we learn as it hardens.

Try the unified data stack<br>ClickHouse + Postgres has become the unified data stack for applications that scale. With Managed Postgres now available in ClickHouse Cloud, this stack is a day-1 decision.<br>Get access<br>The architecture in 30 seconds #

Under the hood, pg_stat_ch does the minimum work possible: on the hot path it’s just a memcpy into a shared-memory ring buffer. A background worker wakes up once a second, batches what’s in the buffer, and flushes it using ClickHouse’s native binary protocol with LZ4 compression.

Every time PostgreSQL executes a statement, whether it’s a SELECT, an INSERT, DDL, or even a query that fails with a syntax error, pg_stat_ch records it as a fixed size event. Each event includes 45 fields, covering timing, buffer I/O, WAL, CPU and JIT stats, error details, and basic client context.

From there, the backend does one quick copy into a shared-memory ring buffer and moves on. Once a second, a background worker drains up to 10,000 events, packs them into a columnar block, compresses it with LZ4, and sends it to ClickHouse over the native binary protocol.

On the ClickHouse side, raw events land in the events_raw table, and four materialized views pre-aggregate them into dashboards you can query immediately:

ViewWhat it doesevents_recent_1hRolling 1-hour copy for “what’s happening right now”query_stats_5m5-minute buckets with p95/p99 via quantilesTDigestdb_app_user_1mPer-database/app/user load attribution, 1-minute granularityerrors_recent7-day rolling window of every error with full context

The important bit here is that all aggregation happens in ClickHouse, not in PostgreSQL. Postgres captures events and pushes them out. ClickHouse compresses, stores, and answers analytical queries.

Engineering decisions #

Decision 1: fixed-size events #

We had two options for event data: variable-length (allocate per event based on query text size, like pg_stat_monitor does with PostgreSQL’s DSA allocator) or fixed-size.

We chose fixed-size. The memcpy is fast, but the real cost is LWLock acquisition, name resolution (get_database_name, GetUserNameFromId, GetClientAddress), and...