From Postgres to ClickHouse without changing a query

Every analytics product eventually meets the same table. Pageviews, clicks, scroll depth, vitals and errors arrive at a rate of dozens per session, they are never updated, and every chart scans a month of them. Sessions, people, dashboards, reports and the AI’s conversations are small by comparison. The architecture question is almost entirely about where that one table lives.

InsightClickIQ’s answer is: in Postgres for as long as Postgres is comfortable, and in ClickHouse after that — with the move done by setting environment variables rather than by rewriting the product. This post covers both halves: what makes Postgres last, how the ClickHouse store is wired, and what happened when we ran the same queries against both.

How big is the table, really?

Measured on the demo data: an event costs about 0.9 KB in Postgres with its indexes (271k events took 246 MB), and sessions and people add roughly another 10%. Replay bodies, the largest bytes in the system, never touch the database — they are gzipped rrweb JSON in an object store. That makes sizing a matter of arithmetic.

Most deployments sit in the first two rows and should stay on Postgres. The rest of this post explains why that is a comfortable place to be, and what the exit looks like when it is not.

Making Postgres last

Monthly partitions

The event table is partitioned by month on its timestamp: event_2026_09 covers September in UTC, event_2026_10 October, and a default partition catches a timestamp outside every month. A daily background round creates partitions two months ahead, so ingest never waits on DDL. A 30-day chart touches one or two partitions; vacuum stays cheap because each partition carries its own autovacuum setting (scale factor 0.02).

The partitioning came with an index diet. The ten indexes the table used to carry cost about 45% of its bytes; it now keeps the ones every query uses — project and time, project and session, project and event name, project and user, project and anonymous id, a BRIN on time — and makes the page-type index and the error-fingerprint index partial, so they only index the rows they serve.

Retention that drops, rather than deletes

Each project has a retention_days. A daily round, at a quiet local hour (3 a.m. by default), works out each project’s cutoff. A month partition no project still needs a row from is dropped — instant, and no bloat. Anything else is deleted in 50,000-row batches, one short transaction each. The same round removes the replay chunks and their objects, sessions that ended before the cutoff, people not seen since, insight-cache rows older than 30 days and dismissed signals older than 90.

Erasure uses the same machinery: DELETE /api/people/{id} removes a person, every id they were seen under, their sessions, events and replay objects.

A read replica for the console

When ingest and dashboards start competing, set CLICKIQ_READ_DATABASE_URL. The read-heavy routes — queries, the overview, journeys, heatmaps, forms, errors, performance, events — move to the replica, pinned to the caller’s organization exactly as on the primary. Unset, the replica is simply the primary. The role defaults matter too: a 30-second statement timeout and 64 MB of work_mem for the API role, and PgBouncer in transaction mode is safe because the tenant is set per transaction.

The switch: one query module, two dialects

Two decisions made early are what make the move cheap. First, nothing in the product writes SQL against the event table except one module. Every chart, widget, alert, AI answer and MCP tool compiles a QuerySpec through app/services/query. Second, that module writes each statement once and asks a dialect for the parts that differ between engines.

The dialect is a small class with one method per difference: the table name; how to read a JSON property (props #>> … against JSONExtractString); a distinct count (uniqExact); a percentile (percentile_cont against quantileExactInclusive — both interpolate, so a p75 agrees); a filtered aggregate; a time bucket in the project’s time zone; case-insensitive match; regex; an IN list; the first event of a session. Placeholders follow the engine — :p0 for Postgres, server-side {p0:String} for ClickHouse — so no value is ever spliced into a statement on either.

A handful of hand-written statements (quality, forms, signals, the digest, zones) carry one text per engine. A statement with no ClickHouse text raises NotPorted rather than quietly running the wrong SQL. That is the important design choice: a gap in the port is an error you see in testing, not a wrong number on a dashboard.

Tenancy without row-level security

On Postgres, tenancy has two lines: every route scopes its queries, and row-level security on the organization is the second line that catches a mistake. ClickHouse has no row-level security, so the second line is rebuilt in code. The query builder always binds organization = {org} and the project; every hand-written ClickHouse text does the same; and the ClickHouse dialect’s execute refuses a statement without an organization predicate. A test asserts the predicate on every generated statement. On top of that, give the console a read-only ClickHouse user (GRANT SELECT) and give ingest a separate writer through CLICKIQ_CLICKHOUSE_WRITE_URL.

Dual-write and backfill

Ingest inserts each batch into Postgres, folds sessions and people, commits, and then hands the same rows — same ids — to ClickHouse. A ClickHouse failure never fails the request: it is logged and counted, and the rows wait in a bounded in-memory queue of 10,000 (oldest drop first) that the next batch drains ahead of itself. The readiness endpoint shows the counters — inserted, batches, failed batches, queued, dropped, last error — so a lagging store is visible.

The ClickHouse table is a ReplacingMergeTree ordered by (project_id, ts, id), partitioned by month, with a TTL of 400 days by default, so a retried batch or a repeated backfill converges. The backfill script reads Postgres in id order, asks ClickHouse which ids of each batch already exist, and inserts the rest. It is re-runnable and interruptible: locally it copied 271k rows in 10 seconds, and a second run copied none.

The same queries on both engines

The architecture document records that the same specs answer identically on a Postgres-backed and a ClickHouse-backed process, with ClickHouse 1.3–17× faster per statement on 270k rows, and the user.manager breakdown going from 957 ms to 54 ms. For this post we ran the comparison again ourselves: two API processes on one laptop, one on the Postgres store and one on a local ClickHouse 24.8 holding the same 270,893 rows, AI switched off on both, a fixed 30-day window. Figures are the engine’s own sql_ms, median of five runs after a warm-up; the last rows are whole endpoints timed from the client.

Three things are worth reading out of that table, and none of them is “ClickHouse is faster”.

• Parity held everywhere. Every result was byte-for-byte the same once timings were removed. The only difference was the order of single-view pages tied at the bottom of a 100-row list, where neither engine promises an order.
• At this size Postgres is not the problem. Almost everything answers in tens of milliseconds on both. The funnel and retention are slower on ClickHouse here, because the port deliberately keeps the same self-join shape on both engines so the answers agree; the ClickHouse-native forms (windowFunnel and friends) are a later optimisation.
• The person-trait breakdown is the outlier. Joining every event to the person’s traits under row-level security is the one query that hurts on Postgres — 1.8 s here, 2.3 s in the console screenshot below — and the one the trait-map approach on ClickHouse turns into 45 ms. It also grows with rows, which is why it is the first thing a large tenant feels.

Analyze · users by manager

Analyze · checkout funnel

When to move

Do not move because ClickHouse is fashionable. Move when one of these is true:

1. You are past about 100 million events a month, or will be within the retention window, and 30-day console queries are scanning more than 100 million rows.
2. Trait breakdowns are central to how you report — by manager, team, branch, plan — and they are getting slow.
3. You run many tenants on one deployment and want the event scans off the database that holds everyone’s state.

Before that, the cheaper wins are in Postgres: the partitions and retention already shipped, a read replica, and the daily rollups that are the next storage item on the list. Those are engine-independent and are needed before the ClickHouse step anyway.

Keep the events where they are cheapest to keep, and keep every question the same wherever they live.

Running it on AWS →

← All posts   Request a demo