When you drop posthog.capture('user_signed_up', { plan: 'enterprise' }) into a web client or backend service, your application gets back an HTTP 200 response in about 4 milliseconds.
To the application, the event is recorded. Done.
Behind that 4-millisecond handshake, an architecture ingests hundreds of thousands of raw JSON payloads per second, flattens arbitrary schemas, tracks anonymous sessions across domain jumps, and stores petabytes of events so that five-step retention funnels over 100 million rows resolve in under 400 milliseconds.
In the early days, PostHog ran entirely on PostgreSQL. It made sense: Postgres was reliable, everyone knew how to operate it, and JSONB columns offered flexibility. But as traffic scaled from hundreds of events per day to tens of thousands per second, Postgres hit an immovable wall.
Understanding how PostHog solved this problem reveals why classical relational databases buckle under event streams, and how modern event-driven architectures bridge fast client ingestion with analytical query engines like ClickHouse.
Why PostgreSQL Fell Over: The Row-Store Trap
In a standard web application, PostgreSQL is exceptional. You fetch a user by ID, update an account balance, or query an orders table. These are Online Transaction Processing (OLTP) access patterns: point lookups and small transactional updates across single rows.
In product analytics, the access pattern is inverted:
OLTP Query (PostgreSQL):
SELECT id, name, email, plan, updated_at FROM users WHERE id = 42;
--> Reads 1 row, touches all columns in that row.
OLAP Query (Product Analytics):
SELECT event, count(*) FROM events
WHERE timestamp >= now() - INTERVAL '30 days'
GROUP BY event;
--> Reads 100,000,000 rows, touches ONLY 2 columns (event, timestamp).
The Disk Page Problem
PostgreSQL stores data in 8KB disk pages organized by row. If an event row contains a giant JSONB payload with user agents, system screen dimensions, UTM parameters, and custom properties, that single row might consume 4KB to 8KB of disk space.
When running a query to count how many users completed an onboarding step, Postgres must read the entire 8KB page off disk into the buffer pool just to inspect two small fields: event and timestamp.
Ninety-eight percent of the data loaded from NVMe drives and cached in RAM is discarded immediately. At 50 million events, the database server runs out of buffer cache, thrashing disk I/O to scan gigabytes of data it does not need.
Write Amplification and Autovacuum Starvation
Every analytics event is an append-only write. But in Postgres:
- Every
INSERTwrites a full heap tuple to the write-ahead log (WAL) and disk page. - Every secondary index (on
distinct_id,team_id,timestamp) requires an immediate B-tree leaf update. - High write volume forces aggressive autovacuuming to clean transaction IDs (MVCC overhead) and update visibility maps.
At 20,000 writes per second, autovacuum could not keep up with table bloat. Disk usage ballooned, B-tree indexes fragmented, and locking during vacuum cycles choked analytical queries.
PostHog needed to separate ingestion from storage, and replace the underlying row-oriented storage engine with a columnar analytics warehouse.
The Ingestion Pipeline: Step-by-Step
The modern PostHog architecture splits event processing into four decoupled stages:
[ Client SDK ]
│ (HTTP POST /batch/ in ~4ms)
▼
[ Rust Capture Service (capture-rs) ]
│ (Token validation + Redis rate-limiting)
▼
[ Apache Kafka / WarpStream ] (Topic: events_plugin_ingestion)
│
▼
[ Node.js / Rust Ingestion Workers (CDP) ]
│ (GeoIP, HogQL plugins, distinct_id mapping)
▼
[ Apache Kafka / WarpStream ] (Topic: clickhouse_events_json)
│
▼
[ ClickHouse Cluster (4-Layer Engine Pipeline) ]
Step 1: The Rust Ingestion Gateway (capture-rs)
When an SDK calls posthog.capture(), the HTTP request lands on capture-rs, a high-throughput proxy written in Rust.
Why Rust instead of Python (Django) or Node.js?
Because the entry gateway has one primary job: get data off the network socket, validate credentials, and buffer it to disk in the fewest possible CPU cycles.
When the gateway receives a payload:
- It validates the project token (
api_key) to ensure the request belongs to an active team. - It queries an in-memory Redis instance using a token-bucket algorithm to protect against abusive or runaway clients.
- It immediately pushes the raw payload as a byte array into an Apache Kafka topic named
events_plugin_ingestion. - It returns
HTTP 200 {"status": 1}to the client.
No parsing of nested JSON properties happens here. No database connections are held open. The client receives an acknowledgment in single-digit milliseconds, and client latency is decoupled from the storage layer.
Step 2: The Shock Absorber (Kafka / WarpStream)
Why not write directly from the ingestion gateway into ClickHouse?
ClickHouse excels at processing batch writes containing 10,000 to 100,000 rows. It struggles when receiving 10,000 individual single-row inserts per second. Frequent tiny inserts create thousands of tiny data parts on disk, overwhelming the storage engine with background merge pressure (the dreaded "Too many parts" error).
Kafka acts as the system's shock absorber. If a customer runs a viral marketing campaign and traffic surges from 5,000 events/sec to 150,000 events/sec, Kafka buffers the burst without dropping packets. The downstream consumers continue batching and writing at a predictable, controlled pace.
Step 3: Enrichment and Identity Resolution (The Ingestion Worker)
Workers pull batches off the events_plugin_ingestion Kafka topic. This layer performs three critical tasks before the data reaches permanent storage:
1. GeoIP and User-Agent Enrichment
The worker reads the incoming client IP address, performs a local lookup in an in-memory MaxMind database, and appends $geoip_country_name, $geoip_city_name, and coordinate fields. The raw user agent is parsed into $browser, $os, and $device_type.
2. Event Transformations and HogVM
Users can configure transformations (such as masking PII, hashing emails, or routing specific properties). PostHog executes these transformations using HogVM: a custom, sandboxed bytecode runner designed to execute lightweight HogQL scripts safely at ingestion speed without spinning up full Node virtual machines.
3. Identity Stitching (distinct_id to person_id)
In analytics, a user often starts as an anonymous visitor with an assigned UUID cookie (distinct_id: "anon_8f3a"). Twenty minutes later, they log in, and their user ID becomes user_4912.
If queries had to join millions of events across changing IDs on the fly, database performance would collapse. The ingestion worker checks a PostgreSQL and Redis mapping table to resolve distinct_id to an internal immutable person_id.
Once enriched, the worker publishes the normalized JSON payload to a second Kafka topic: clickhouse_events_json.
Step 4: The 4-Layer ClickHouse Engine Ingestion Pattern
The way PostHog connects Kafka to ClickHouse is an elegant pattern in distributed database design. Rather than writing manual batch-insert scripts, PostHog uses ClickHouse's native table engines and Materialized Views.
[ Kafka: clickhouse_events_json ]
│
▼
[ kafka_events Table ]
(Engine: Kafka)
│ (Continuous Pull Stream)
▼
[ events_mv View ]
(Materialized View Trigger)
│ (Pushes to)
▼
[ writable_events Table ]
(Engine: Distributed)
│ (Shards by sipHash64(distinct_id))
▼
[ sharded_events Table ]
(Engine: ReplicatedReplacingMergeTree)
Layer 1: The Kafka Engine Table (kafka_events)
ClickHouse features an internal Kafka engine that acts as a native Kafka consumer:
CREATE TABLE posthog.kafka_events (
uuid UUID,
event String,
properties String,
timestamp DateTime64(6, 'UTC'),
team_id Int64,
distinct_id String,
elements_chain String,
created_at DateTime64(6, 'UTC'),
_timestamp DateTime,
_offset UInt64
) ENGINE = Kafka
SETTINGS
kafka_broker_list = 'kafka:9092',
kafka_topic_list = 'clickhouse_events_json',
kafka_group_name = 'clickhouse_events_json',
kafka_format = 'JSONEachRow',
kafka_skip_broken_messages = 100;
This table does not store data on disk. Reading from this table is like draining a queue: it reads messages, advances Kafka offsets, and yields data in memory.
Layer 2: The Materialized View (events_mv)
In ClickHouse, a Materialized View is not a cached query result like in PostgreSQL. It is an insert trigger. Whenever the kafka_events table consumes a batch of messages, events_mv fires automatically, extracts the rows, and pipes them directly into the destination table:
CREATE MATERIALIZED VIEW posthog.events_mv TO posthog.writable_events AS
SELECT
uuid,
event,
properties,
timestamp,
team_id,
distinct_id,
elements_chain,
created_at,
_timestamp,
_offset
FROM posthog.kafka_events;
Layer 3: The Distributed Sharding Router (writable_events)
PostHog runs across a distributed ClickHouse cluster with multiple nodes. Writing directly to specific nodes would create load imbalances.
The writable_events table uses the Distributed engine:
CREATE TABLE posthog.writable_events (
uuid UUID,
event String,
properties String,
timestamp DateTime64(6, 'UTC'),
team_id Int64,
distinct_id String,
elements_chain String,
created_at DateTime64(6, 'UTC'),
_timestamp DateTime,
_offset UInt64
) ENGINE = Distributed('posthog_cluster', 'posthog', 'sharded_events', sipHash64(distinct_id));
Look closely at the last parameter: sipHash64(distinct_id).
This is the sharding key. ClickHouse hashes the user's distinct_id and uses the hash to pick the destination shard.
Why shard by distinct_id instead of uuid or timestamp?
Because in analytics, nearly every heavy calculation (retention curves, user journeys, funnel conversions) calculates what an individual person did over time. If a user's events were scattered across five different physical nodes, calculating a funnel would require massive cross-node network shuffles.
By sharding on sipHash64(distinct_id), every single event generated by the same user lands on the exact same physical ClickHouse server. Queries for a specific user or cohort run locally on individual nodes with zero network coordination.
Layer 4: The Storage Engine (sharded_events)
Finally, the data arrives at the real storage table on the shard:
CREATE TABLE posthog.sharded_events (
uuid UUID,
event String,
properties String CODEC(ZSTD(3)),
timestamp DateTime64(6, 'UTC'),
team_id Int64,
distinct_id String,
elements_chain String,
created_at DateTime64(6, 'UTC'),
_timestamp DateTime,
_offset UInt64
) ENGINE = ReplicatedReplacingMergeTree(
'/clickhouse/tables/{shard}/posthog.events',
'{replica}',
_timestamp
)
PARTITION BY toYYYYMM(timestamp)
ORDER BY (team_id, toDate(timestamp), event, cityHash64(distinct_id), cityHash64(uuid));
Three design decisions here explain ClickHouse's speed:
-
PARTITION BY toYYYYMM(timestamp): Data is partitioned into physical monthly folders on disk. If you run a query filtering on events from the last 7 days, ClickHouse skips entire partitions from previous months without reading a single byte off the drive. -
ORDER BY (team_id, toDate(timestamp), event, ...): ClickHouse stores data sorted on disk by this primary key tuple. A sparse primary index stores an entry every 8,192 rows (an index mark). When you queryWHERE team_id = 42 AND event = '$pageview', ClickHouse uses the sparse index to jump directly to the matching byte range. -
properties String CODEC(ZSTD(3)): Raw JSON payloads are compressed with Zstandard level 3. Because similar events have identical JSON keys ($browser,$os,utm_source), columnar compression ratios regularly hit 5:1 to 8:1, turning a 1TB dataset into 150GB of disk footprint.
Why ClickHouse Crushes PostgreSQL for Analytical Queries
Once data is stored in sharded_events, the speed difference between Postgres and ClickHouse comes down to low-level computer science:
| Characteristic | PostgreSQL (Row-Store) | ClickHouse (Column-Store) |
|---|---|---|
| Storage Layout | All columns for a row packed together on 8KB pages | Each column stored in its own independent compressed file (.bin) |
Disk Read for count(*) |
Reads entire row (including unneeded JSON blobs) | Reads ONLY the single targeted column file |
| Compression Ratio | Low (mixed data types per page, ~1.5x) | Massive (identical data types contiguous, 5x-10x) |
| CPU Execution | Scalar (interprets one tuple at a time) | Vectorized (processes 65,536 elements per vector loop via SIMD) |
| Primary Index | Dense B-Tree (stores pointer for every single row) | Sparse Index (stores index entry every 8,192 rows) |
| Memory Footprint | B-tree indexes often consume more RAM than data | Primary index fits in 50MB to 200MB of RAM |
When ClickHouse executes an aggregation:
SELECT event, count(*)
FROM events
WHERE team_id = 12
AND timestamp >= '2026-11-01'
GROUP BY event;
It opens only the team_id.bin, timestamp.bin, and event.bin files. It checks the sparse mark files (.mrk3), streams the compressed data blocks directly into L3 CPU cache, decompresses them in memory, and uses AVX2 / AVX-512 SIMD instructions to scan tens of millions of rows per core per second.
PostgreSQL would need 45 seconds to scan that data. ClickHouse completes it in 80 milliseconds.
Architectural Pitfalls to Avoid When Scaling Event Streams
If you are designing an event-driven telemetry or analytics stack, PostHog's engineering handbook highlights three subtle traps:
1. The ReplacingMergeTree Deduplication Myth
Developers often see ReplacingMergeTree and assume it acts like a Postgres UNIQUE constraint. It does not.
ReplacingMergeTree only removes duplicate rows during asynchronous background part merges. If you insert duplicate rows with the same sorting key, both rows exist simultaneously until ClickHouse decides to merge their parts: which could happen five seconds later, two days later, or never if the partition is cold.
If your query requires absolute deduplication, you must write queries that handle it explicitly:
-- Naive query (may count unmerged duplicates):
SELECT count(*) FROM events;
-- Deduplicated query:
SELECT count(DISTINCT uuid) FROM events;
-- OR:
SELECT count(*) FROM events FINAL; -- Note: FINAL incurs heavy disk I/O
2. The JSON Parsing Performance Wall
Storing event properties as a JSON string gives you unlimited schema flexibility. But parsing JSON strings inside ClickHouse during every query with JSONExtractString(properties, 'plan') forces the engine to run string regexes on every row, nullifying vectorized SIMD speed.
PostHog's solution is Materialized Columns:
ALTER TABLE sharded_events ADD COLUMN `$browser` VARCHAR
MATERIALIZED replaceRegexpAll(JSONExtractRaw(properties, '$browser'), '^"|"$', '');
When a property becomes popular across a team's queries, PostHog turns it into a physical, materialized column. ClickHouse extracts the value once during ingestion and stores it as its own column file. Subsequent queries read the native column directly, speeding up dashboards by 30x.
3. Session Recording is Not Video
Many developers wonder how PostHog provides full user session replays without spending thousands on video encoding and S3 storage.
PostHog does not record video. It uses rrweb to serialize the browser's Document Object Model (DOM).
- On initial page load, it takes a single JSON snapshot of the DOM tree structure.
- As the user moves their mouse, types, or navigates, the SDK streams small DOM mutation deltas (e.g., "node 14 changed text to 'Submit'", "cursor moved to (410, 290)").
- These delta streams are compressed using gzip or zstd and sent to object storage (S3 / MinIO).
A 10-minute video session might weigh 50MB. An rrweb mutation stream weighs 300KB. When you press play in the dashboard, PostHog spins up a sandboxed iframe, injects the initial DOM, and applies the recorded mutation events in real time.
Key Takeaways
- OLTP and OLAP require different databases. Storing append-only event streams in row-oriented databases like PostgreSQL creates buffer cache thrashing, WAL exhaustion, and autovacuum death spirals.
-
Buffer at the gate. High-throughput ingestion gateways should never write directly to analytical storage. A fast proxy (like Rust
capture-rs) writing to a distributed buffer (Kafka or WarpStream) protects the database from traffic spikes. -
Shard by entity, not by time. Sharding analytics data by user ID (
sipHash64(distinct_id)) guarantees that all events for a given user land on the same physical node, eliminating expensive distributed joins during funnel and retention calculations. - Columnar storage wins on selectivity. ClickHouse achieves 100x query speedups because it only reads the specific column files involved in the query, decompressing contiguous arrays directly into CPU registers.
Top comments (0)