Moving the read path to ClickHouse

For the past couple of weeks the biggest work in Tracely has been invisible: moving the dashboard's read path from SQL rollup tables to ClickHouse. It's mid-flight — some components are migrated, some aren't, and both paths run side by side behind a config switch. This is a writeup of the architecture and, maybe more usefully, the query-writing rules I've had to learn the hard way.

Why move at all

The original design was honest and simple: raw pageviews and events land in the relational database, and ingest jobs maintain hourly rollup tables — per-path, per-dimension, per-event-name — via upserts as batches persist. Dashboards read the rollups, so a "last 30 days" query touches hundreds of rows per path instead of millions of raw ones.

That holds up until two things happen at once. First, unique counts don't roll up: you can sum pageviews across hourly rows, but you cannot sum unique visitors, so anything visitor-shaped either goes back to raw rows or accepts over-counting. Second, the same database serves ingest writes and dashboard reads, and I've watched them fight — in load testing, big batch INSERTs visibly slow dashboard SELECTs because they share the store.

ClickHouse is built for exactly this shape of problem: append-heavy writes, aggregation-heavy reads, and — the part that actually sold me — aggregate function states. An AggregatingMergeTree table can store the intermediate state of uniq(visitor_id) per hour, and a query can merge those states across any window to get a correct unique count without touching raw rows. That's the thing the SQL rollups fundamentally couldn't do.

The new shape

ClickHouse becomes the analytics warehouse: raw tables (pageviews, events, performance_metrics, heatmap_buckets) plus rollups populated by materialized views. Here's the per-path rollup, abbreviated from database/clickhouse/0011_create_pageview_path_hourly_stats.sql:

CREATE TABLE IF NOT EXISTS pageview_path_hourly_stats
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(bucket_hour)
ORDER BY (site_id, bucket_hour, path)
AS SELECT
    site_id,
    toStartOfHour(created_at) AS bucket_hour,
    path,
    countState() AS pageviews,
    uniqIfState(visitor_id, visitor_id != '') AS visitors_state,
    uniqIfState(session_id, session_id != '') AS sessions_state,
    ...

A materialized view (pageview_path_hourly_stats_mv) feeds it on every insert into pageviews, which retires a whole class of application code — the ingest job's rollup upserts become the database's job. Postgres stays the default connection for relational app data (sites, users, teams); ClickHouse is opt-in per call.

Migration tooling

ClickHouse DDL can't live in Laravel migrations — the schema builder has no vocabulary for MergeTree engines or AggregateFunction columns, and CI's RefreshDatabase must never run this DDL. So the schema lives as plain SQL files in database/clickhouse/, applied in filename order by:

php artisan tracely:clickhouse:migrate

Applied files are recorded in a schema_migrations_ch bookkeeping table, so re-runs are idempotent and only new files execute. --pretend prints what would run; --fresh drops every object first. The command even creates the database on a brand-new server by connecting to the server-level default database for the CREATE DATABASE, since the normal connection fails if its target database doesn't exist yet.

The cutover backfill is its own command:

php artisan tracely:clickhouse:backfill --all --before="2026-07-01 00:00:00"

It copies raw analytics rows out of Postgres in chunks (--read-chunk=20000, --write-chunk=20000), per site and per table. The elegant part is what it doesn't do: it never touches the rollups. Because the materialized views fire on insert, rollups populate themselves as backfilled rows land. The sharp edge: re-running without a --fresh reset double-inserts rows, so this is a run-once-per-cutover tool, with --before marking the cutover instant and --after available for incremental catch-up.

There's also a third command, tracely:backfill-mysql-analytics, which copies facts out of ClickHouse (or legacy Postgres) into MySQL fact tables. That exists for benchmarking and as a cutover hedge — I wanted to compare an indexed-SQL read path against ClickHouse on identical data before committing, and I want a credible path backward if the warehouse turns out to be a mistake.

The read-driver switch

All of this only works mid-migration because reads are routed, not hardcoded. In config/tracely.php:

'analytics' => [
    'read_driver' => env('TRACELY_ANALYTICS_READ_DRIVER', 'clickhouse'),

A resolver class, AnalyticsReads, is the single place read code asks three questions: which connection, which SQL dialect, and what the physical rollup table is called. Raw tables share column names across stores, so only the connection differs. Rollups differ more: in ClickHouse the readable, merged shape lives in *_v compat views (pageview_path_hourly_stats_v and friends), while the SQL path uses the logical table name as-is. Any migrated component branches on AnalyticsReads::isClickhouse() and keeps its SQL branch byte-identical to before, so flipping the env var back is always safe.

Rules I learned the hard way

This is the part I wish someone had written for me two weeks ago.

Raw SQL only. The ClickHouse Laravel driver's query builder is not Laravel's query builder — no selectRaw, no whereBetween, no groupByRaw. Every ClickHouse read goes through a single sanctioned facade, App\Analytics\Clickhouse::select($sql, $bindings), with named, ClickHouse-typed bindings like {sid:String}. Positional ? placeholders don't work. Centralizing access in one class also gives me a per-request query profiler, because Debugbar is bound to the Postgres connection and can never see these round-trips.

Merge states on the base table, not the view. The *_v compat views merge states per bucket_hour — fine for hour-granularity reads, wrong for windows. If you re-aggregate a view's already-merged unique column across 720 hours, you've re-invented the over-counting bug the warehouse was supposed to fix. Multi-hour reads go to the base AggregatingMergeTree table with -Merge combinators, and each combinator must match the state column's definition — countState pairs with countMerge, uniqIfState with uniqIfMerge, sumState with sumMerge. From the overview compiler, verbatim:

'countMerge(pageviews) AS pageviews, uniqIfMerge(visitors_state) AS visitors, '
.'uniqIfMerge(sessions_state) AS sessions, countIfMerge(bounces) AS bounces, '

Never alias an aggregate to a column name you re-reference. countMerge(pageviews) AS pageviews is fine on its own — but reference pageviews again in the same SELECT and ClickHouse resolves it to the UInt64 result, not the column, and errors with "Illegal type UInt64 ... must be AggregateFunction." My fix is unglamorous: derived values like average duration are computed in PHP from the merged sums rather than in SQL. There's a comment in the compiler marking exactly this trap so I don't re-walk into it.

Never mix stores in one code path. The subtlest bug class: a component whose rollup reads were migrated but whose raw reads still went through Eloquent relations. Those relations hit Postgres — while shared SQL helpers, now branching on the ClickHouse dialect, emitted ClickHouse-flavored SQL at them. Postgres has opinions about sum(boolean). The rule that came out of it: a component is migrated when every read in it is migrated, and read_driver doesn't flip for an environment until that's true everywhere the environment exercises.

The write path had its own war story worth one paragraph: bulk inserts stream rows in the HTTP body rather than a giant INSERT ... VALUES string, and they use JSONEachRow instead of tab-separated format because real page titles contain literal tabs and newlines — TSV let them through, splitting rows mid-stream, and ClickHouse rejected the mangled batch after the HTTP client had already reported success. The insert also sets wait_end_of_query=1 so ClickHouse buffers its response until the insert fully commits; without it, a mid-body parse failure never surfaced and rows silently vanished while jobs logged success. Silent data loss in an analytics product is the nightmare scenario, and I only caught it because backfill counts didn't reconcile.

Trade-offs, honestly

I now operate two databases, and every migrated component carries two read implementations until the SQL path retires — that's real, ongoing cost. Feature tests that need ClickHouse skip when a local server is absent, which is convenient and slightly dangerous (a skipped test looks a lot like a passing one in a green run). And the migration is genuinely incomplete: components move one at a time, each one an exercise in translating date_trunc to toStartOfHour and COUNT(*) FILTER to countIf without regressing the SQL branch.

But the destination is right. Correct unique counts over arbitrary windows, reads that don't arm-wrestle ingest writes for the same tables, and rollups maintained by the database instead of by application code in the hot path. When the last component flips I'll write up the before-and-after properly. Until then: two paths, one switch, and a growing file of rules taped above my desk. The feed is here if you want the next installment.