Why move
Dashboards aggregate: events per page, per topic, per segment, per day, across whatever range the user picks. On a row store those scans grew with traffic. Tuning the Postgres queries bought 20–30%; the aggregate-heavy screens needed a columnar engine.
The goal wasn't to replace Postgres. Segments, experiences, topics and billing stayed there. ClickHouse took the data that is written once, read in aggregate, and never edited by hand.
Schema decisions
Split
Postgres: configuration and business objects. ClickHouse: events, sessions, profiles, consent, topic and page scores, and segment membership.
Ingest
The browser tracker posts to SQS and a Lambda writer batches events and sessions into ClickHouse. The backend writes profile-derived tables, and Postgres changes to topics and page scores reach ClickHouse through LISTEN/NOTIFY and a queue.
Engines
Tables that get rewritten — profiles, scores, membership — use ReplacingMergeTree with a version column, so a retry or a re-sync is idempotent.
Sort keys
Keys follow the questions the dashboards ask: (scope, page, topic, cluster) for page scores, (scope, profile, topic, cluster) for visitor affinity, (profile, segment) for membership lookups. Partitioned by year.
Pre-aggregate
Materialized views for topic analytics carry segment IDs, so "this topic, for this segment" never rescans raw events.
Operate
Versioned SQL migrations, Terraform for the server, backups to S3, and error and slow-query logs shipped to CloudWatch.
Segment engine
A segment is a rule tree: conditions joined by AND, where any condition can be an OR group. Evaluating one takes four steps.
Flatten
Walk the tree into a list of leaf conditions and a plan: single conditions are AND-ed, OR groups stay together.
Compile
Each leaf becomes one ClickHouse query against the right table — profile properties, sessions, events, goals, consent, distance from a point, pages not visited, topic and cluster affinity, journeys, Meta, Google and LinkedIn ad campaigns — and returns a set of profile IDs. Values travel as typed query parameters; column names come from an allowlist.
Combine
Fold the plan: union inside OR groups, intersection across them. Profiles merged into another identity are dropped.
Materialize
Clear the rule-computed members for that segment — members pinned by hand survive via a fixed flag — then INSERT … SELECT the new ones in batches of 1,000, reading only live profiles.
Trade-offs
- Set logic lives in Node. Every leaf is a small, testable query and the combination is easy to debug, at the cost of holding ID sets in memory. At a much larger scale I'd push the whole tree into a single ClickHouse query with bitmap functions.
- ReplacingMergeTree deduplicates eventually, not immediately. FINAL makes a read exact but costs, so it's used where a duplicate would be wrong: membership, profiles and scores.
- The same pipeline predicts a segment's size over the last N days before it's saved — which is what the agent shows while it builds one.