# How ClickHouse Stores and Queries Event and Session Data in PostHog

> Discover how PostHog leverages ClickHouse's sharded MergeTree for efficient event and session data storage and millisecond-level analytical queries via Kafka, materialized views, and partition pruning.

- Repository: [PostHog/posthog](https://github.com/PostHog/posthog)
- Tags: internals
- Published: 2026-04-25

---

**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`](https://github.com/PostHog/posthog/blob/main/posthog/models/event/sql.py) and [`posthog/models/raw_sessions/sessions_v3.py`](https://github.com/PostHog/posthog/blob/main/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`](https://github.com/PostHog/posthog/blob/main/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]](https://github.com/PostHog/posthog/blob/master/posthog/models/event/sql.py#L226-L306).

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`](https://github.com/PostHog/posthog/blob/main/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`](https://github.com/PostHog/posthog/blob/main/0087_events_recent_single_shard.py), the table definition includes:

```sql
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`](https://github.com/PostHog/posthog/blob/main/0114_add_ephemeral_props_column.py) adds these columns, while [`0184_sharded_events_add_distinct_id_bloom_filter_index.py`](https://github.com/PostHog/posthog/blob/main/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`](https://github.com/PostHog/posthog/blob/main/posthog/models/raw_sessions/sessions_v3.py) [[source]](https://github.com/PostHog/posthog/blob/master/posthog/models/raw_sessions/sessions_v3.py#L89-L115).

```python

# 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`](https://github.com/PostHog/posthog/blob/main/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`](https://github.com/PostHog/posthog/blob/main/posthog/hogql/printer/clickhouse.py), it automatically injects a `team_id` guard and optimizes time-based filters. A typical events query [[source]](https://github.com/PostHog/posthog/blob/master/posthog/hogql/printer/clickhouse.py#L1020-L1030):

```sql
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`](https://github.com/PostHog/posthog/blob/main/posthog/hogql/database/schema/util/where_clause_extractor.py) [[source]](https://github.com/PostHog/posthog/blob/master/posthog/hogql/database/schema/util/where_clause_extractor.py#L579-L607). HogQL rewrites session filters into sub-queries that push the predicate down to the event scan:

```sql
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:

```sql
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`](https://github.com/PostHog/posthog/blob/main/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:

```sql
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:

```sql
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`](https://github.com/PostHog/posthog/blob/main/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.