How ClickHouse Stores and Queries Event and Session Data in PostHog

PostHog uses a sharded MergeTree architecture in ClickHouse where events stream from Kafka into sharded_events via materialized views, while sessions aggregate into raw_sessions.sessions_v3 using time-sortable UUIDv7 integers, enabling millisecond-level analytical queries through partition pruning and materialized property columns.

PostHog processes billions of events daily using ClickHouse as its analytical database. The platform balances high-throughput ingestion with sub-second query performance through a carefully designed schema that leverages materialized views, sharded tables, and specialized UUID representations in posthog/models/event/sql.py and posthog/models/raw_sessions/sessions_v3.py.

Event Storage Architecture

Ingestion Pipeline

Events flow through a multi-stage pipeline before reaching persistent storage. In posthog/models/event/sql.py, the system defines a Kafka engine table named kafka_events_json that consumes from Kafka topics, followed by a materialized view events_json_mv that automatically inserts rows into the main storage table [source].

The pipeline works as follows:

1. Capture: Client events land in Kafka topics.
2. Consumption: The kafka_events_json table (defined in migration 0004_kafka.sql) reads from these topics.
3. Materialization: The events_json_mv materialized view transforms and writes data into sharded_events.
4. Distribution: Queries against the logical events table route to distributed_events, which federates reads across all shards.

Table Schema and Primary Keys

The sharded_events table uses the MergeTree engine with careful partitioning and ordering to optimize analytical workloads. According to migration 0087_events_recent_single_shard.py, the table definition includes:

CREATE TABLE IF NOT EXISTS sharded_events ON CLUSTER '{cluster}'
(
    team_id UInt64,
    event_timestamp DateTime64(6, 'UTC'),
    event_id UUID,
    event String,
    properties String CODEC(ZSTD(3)),
    -- materialised property columns
    mat_$browser LowCardinality(String) DEFAULT JSONExtractString(properties, '$browser')
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (team_id, event_timestamp, event_id);

This schema provides three critical optimizations:

• Partitioning by month (toYYYYMM(event_date)) keeps partitions manageable and enables aggressive partition pruning.
• Primary key ordering by team_id, then event_timestamp, then event_id ensures that queries filtering by team and time range scan contiguous disk blocks.
• Compression via ZSTD(3) on the large properties JSON column reduces storage overhead.

Materialized Columns and Skip Indexes

To avoid expensive JSON parsing at query time, PostHog extracts frequently accessed properties into materialized columns at ingestion. Migration 0114_add_ephemeral_props_column.py adds these columns, while 0184_sharded_events_add_distinct_id_bloom_filter_index.py creates Bloom filter indexes on fields like distinct_id for fast lookups.

When you query properties.$browser, HogQL actually targets the materialized column mat_$browser, eliminating JSON extraction overhead.

Session Storage with UUIDv7

The UUIDv7 Integer Representation

ClickHouse cannot natively sort standard UUID values chronologically. PostHog solves this by storing session IDs as UInt128 integers using the UUIDv7 format, where the first 48 bits encode a Unix timestamp in milliseconds. This technique is implemented in posthog/models/raw_sessions/sessions_v3.py [source].

# Convert $session_id to UInt128 for storage

session_id_v7 = toUInt128(toUUID('$session_id'))

# Extract timestamp from the first 48 bits

session_timestamp = fromUnixTimestamp64Milli(
    toUInt64(bitShiftRight(session_id_v7, 80))
)

Storing sessions as UInt128 allows ClickHouse to use them as primary keys, keeping rows naturally ordered by creation time without secondary indexes.

Session Materialization

A materialized view named sessionize_events_mv aggregates raw events into session rows, populating raw_sessions.sessions_v3. Migration 0109_materialize_session_ids_uuid.py establishes this table with the following key columns:

• session_id_v7 (UInt128) – Primary key for time-ordered access.
• team_id – For multi-tenant isolation.
• session_start / session_end – Temporal boundaries.
• distinct_ids – Array of distinct IDs seen within the session.

The view groups events by $session_id (with heuristic fallbacks) and inserts one row per session, pre-aggregating metrics like duration and pageviews.

Query Optimization Techniques

Partition Pruning and Primary Key Scans

When HogQL compiles queries to ClickHouse SQL in posthog/hogql/printer/clickhouse.py, it automatically injects a team_id guard and optimizes time-based filters. A typical events query [source]:

SELECT event, timestamp, distinct_id, properties
FROM sharded_events
WHERE team_id = 123
  AND timestamp >= toDateTime('2024-01-01')
  AND timestamp < toDateTime('2024-01-02')
  AND event = 'pageview';

This query benefits from partition pruning (eliminating months outside the range) and primary-key range scans (seeking directly to team_id=123 and the relevant timestamp).

Session ID Push-Down Predicates

Filtering events by $session_id triggers a sophisticated optimization in posthog/hogql/database/schema/util/where_clause_extractor.py [source]. HogQL rewrites session filters into sub-queries that push the predicate down to the event scan:

SELECT * FROM sharded_events AS e
WHERE e.team_id = 42
  AND e.session_id_v7 IN (
      SELECT session_id_v7 
      FROM raw_sessions.sessions_v3 
      WHERE team_id = 42 
        AND session_id_v7 = reinterpretAsUInt128(toUUID('123e4567-e89b-12d3-a456-426614174000'))
  );

This push-down predicate dramatically reduces I/O by scanning only events belonging to the specific session rather than filtering after a full table scan.

Practical Query Examples

Querying Events with Materialized Properties

To leverage materialized columns and avoid JSON parsing:

SELECT
    event,
    timestamp,
    distinct_id,
    mat_$browser AS browser
FROM sharded_events
WHERE team_id = 42
  AND timestamp BETWEEN toDateTime('2024-06-01') AND toDateTime('2024-06-30')
  AND event = 'pageview'
  AND mat_$browser = 'Chrome';

The mat_$browser column references the pre-extracted value, while the Bloom filter index on distinct_id (added by migration 0184_sharded_events_add_distinct_id_bloom_filter_index.py) accelerates lookups on that field.

Filtering Events by Session

When you need events from a specific session, use the session_id_v7 column directly:

SELECT *
FROM sharded_events
WHERE team_id = 42
  AND session_id_v7 = reinterpretAsUInt128(toUUID('123e4567-e89b-12d3-a456-426614174000'))
  AND timestamp >= toDateTime('2024-06-01');

This query combines the primary key on session_id_v7 with the timestamp filter for optimal performance.

Direct Session Analysis

To analyze session aggregates without touching raw events:

SELECT
    session_id_v7,
    session_start,
    session_end,
    arrayJoin(distinct_ids) AS distinct_id,
    pageviews,
    duration
FROM raw_sessions.sessions_v3
WHERE team_id = 42
  AND session_timestamp >= toDateTime('2024-06-01')
  AND session_timestamp < toDateTime('2024-07-01')
ORDER BY session_id_v7 DESC
LIMIT 100;

Ordering by session_id_v7 DESC leverages the chronological encoding of UUIDv7 to return the most recent sessions first.

Summary

• Events stream from Kafka through materialized views into the sharded MergeTree table sharded_events, partitioned by month and ordered by (team_id, event_timestamp, event_id).
• Materialized columns (e.g., mat_$browser) and Bloom filter indexes eliminate JSON parsing and accelerate property filtering without full table scans.
• Sessions use UUIDv7 encoded as UInt128 (session_id_v7) in raw_sessions.sessions_v3, enabling time-ordered storage and fast range queries.
• HogQL automatically injects team_id guards and pushes session filters down to event queries via sub-selects, minimizing data movement and I/O according to the implementation in posthog/hogql/printer/clickhouse.py.

Frequently Asked Questions

Why does PostHog use UUIDv7 instead of standard UUID for session IDs?

Standard UUIDs contain random segments that prevent chronological sorting. PostHog uses UUIDv7 because the first 48 bits store a Unix timestamp, allowing ClickHouse to sort sessions by time when stored as UInt128. This avoids expensive secondary indexes and enables efficient time-range queries on the session_id_v7 primary key.

How does PostHog handle high-throughput event ingestion without dropping data?

Events land in Kafka first, providing a durable buffer. The ClickHouse Kafka engine table kafka_events_json consumes from these topics continuously, while a materialized view (events_json_mv) inserts data into sharded_events. This decouples ingestion spikes from database write capacity and ensures exactly-once delivery semantics.

What is the difference between sharded_events and distributed_events?

sharded_events exists on each ClickHouse node and contains the actual data for that shard, partitioned and ordered for local query efficiency. distributed_events is a Distributed engine table that acts as a coordinator, routing queries to all shards and aggregating results. Application queries target the logical events name, which resolves to the distributed table.

When should I query raw_sessions.sessions_v3 directly versus filtering events by session?

Query sessions_v3 directly when you need session-level aggregates (duration, pageview counts, distinct ID arrays) or want to list sessions chronologically. Filter events by session_id_v7 when you need event-level details (specific timestamps, properties) within a session. HogQL automatically optimizes the latter by pushing session filters down into the events table scan.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}