ClickHouse as a Personalization Warehouse: Architecture, Tradeoffs, and When It Wins

TL;DR.

• ClickHouse's columnar storage and vectorized execution can aggregate 10 billion user events in under a second — making it viable as both the analytical warehouse and the near-real-time feature source for personalization.
• AggregatingMergeTree tables backed by materialized views let you compute user features at write time and serve them at read time without a separate batch pipeline.
• Dedicated feature stores (Feast, Tecton, Hopsworks) add operational complexity that a well-modeled ClickHouse cluster often replaces for teams at fewer than 100M MAU.
• In 2026, ClickHouse's SharedMergeTree engine and native approximate nearest-neighbor support make it competitive for hybrid structured-plus-semantic feature workloads.
• If you only remember one thing: ClickHouse is not a KV store — but with projections and a thin Redis cache at the serving boundary, it can power sub-20ms feature serving at scale.

Personalization systems run on features: counters, ratios, recency scores, preference vectors derived from user behavior. The standard architecture is to compute those features in batch, store them in a KV store (Redis, DynamoDB), and serve them at inference time. That works. It also means two systems to operate, two schemas to keep in sync, and a batch pipeline sitting between an event and a decision. By the time a user's last action is reflected in their features, the ranking model is already scoring on stale signals.

ClickHouse offers a different model: keep everything in one columnar store, push feature computation into materialized views that execute at ingest time, and serve features via projections optimized for point lookups. The personalization warehouse and the feature store collapse into one layer. Whether that's the right tradeoff for your team depends on your QPS profile, your team size, and how much operational surface area you're willing to own. Here is what the architecture actually looks like in production.

What Is a Personalization Warehouse?

A personalization warehouse is the data store that sits at the center of a personalization pipeline — it holds user signals (events, interactions, implicit feedback), computes derived features, and serves those features to downstream models and ranking functions. It's distinct from a general-purpose data warehouse (which optimizes for ad-hoc analysis and historical reporting) and from a feature store (which optimizes for low-latency ML feature serving at high QPS).

The tension is that personalization needs both modes. You need to run analytical aggregations over historical signals to compute features, and you need to serve those features with millisecond latency during request scoring. Most teams solve this by operating two separate systems — a warehouse (BigQuery, Snowflake, Redshift) for computation and a KV store (Redis, DynamoDB) for serving. ClickHouse's value proposition is that its OLAP engine is fast enough that a single system can handle both roles at meaningful scale. Whether to take that trade is an engineering decision, not a foregone conclusion.

Why ClickHouse Fits the Personalization Access Pattern

The core operations of a personalization feature pipeline map almost perfectly to ClickHouse's design.

User behavior data is naturally columnar. An event table — (user_id, item_id, event_type, timestamp, context) — is read almost exclusively in column slices: "sum play_duration for user_id = X in the last 30 days" or "give me all event_type values for this user_id range." Columnar databases compress these workloads by 5-20x over row-oriented layouts and execute aggregations via SIMD vectorization, which is why ClickHouse benchmarks show sub-second aggregation over tables with tens of billions of rows (ClickHouse benchmark).

Personalization features are almost always aggregations: how many times has this user interacted with genre:jazz in the last 7 days, what is the p90 session length for this user, which items has this user consumed in the last 30 days with a skip ratio below 0.2. These are GROUP BY plus window queries, and ClickHouse vectorizes these aggressively. Its merge-based table design means aggregation state can be stored partially and merged lazily at read time — which is the mechanism behind AggregatingMergeTree, the engine the whole personalization warehouse pattern is built on.

In 2026, ClickHouse's SharedMergeTree engine (the cloud-native replacement for ReplicatedMergeTree) adds shared object storage and separation of compute from storage. For personalization workloads, which have spiky ingest during product launches followed by steady analytical reads, this means you pay for compute only when you need it rather than provisioning for peak write throughput.

The Architecture: From Raw Events to Served Features

The pattern we use at ×marble follows three layers: a raw event table, materialized views that compute features at ingest time, and projections that serve features by user_id with point-lookup efficiency.

Raw Event Table

CREATE TABLE user_events
(
    event_date   Date,
    user_id      UInt64,
    item_id      UInt64,
    event_type   LowCardinality(String),
    context      String,
    occurred_at  DateTime64(3)
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id, occurred_at);

LowCardinality(String) for event_type cuts dictionary encoding overhead for the small cardinality set you'll have (click, play, skip, purchase). Partitioning by month lets you drop cold partitions without a full table scan and prunes scans efficiently on date-bounded queries.

AggregatingMergeTree for Feature Rollups

This is the most important engine choice. Instead of recomputing "plays in the last 7 days per user" by scanning the full event table at query time, you define a materialized view that maintains partial aggregation state at write time:

CREATE TABLE user_play_features
(
    user_id         UInt64,
    feature_window  String,
    total_plays     AggregateFunction(count),
    total_duration  AggregateFunction(sum, Float64),
    distinct_items  AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree()
ORDER BY (user_id, feature_window);

CREATE MATERIALIZED VIEW user_play_features_mv
TO user_play_features
AS SELECT
    user_id,
    '7d'                      AS feature_window,
    countState()              AS total_plays,
    sumState(play_duration)   AS total_duration,
    uniqState(item_id)        AS distinct_items
FROM user_events
WHERE event_type = 'play'
  AND occurred_at >= now() - INTERVAL 7 DAY;

At serving time, you use -Merge combinators to finalize the pre-computed state:

SELECT
    user_id,
    countMerge(total_plays)   AS plays_7d,
    sumMerge(total_duration)  AS duration_7d,
    uniqMerge(distinct_items) AS unique_items_7d
FROM user_play_features
WHERE user_id = 42 AND feature_window = '7d'
GROUP BY user_id;

This query runs in under 5ms for a single user on a properly ordered table. The partial aggregate states were computed at write time; you're merging pre-computed fragments, not scanning raw events (ClickHouse AggregatingMergeTree docs).

Projections for User-ID Point Lookups

If your primary key is (event_date, user_id), a query WHERE user_id = X without a date range will scan the full table. Projections define an alternative physical sort order stored alongside the primary data:

ALTER TABLE user_events
    ADD PROJECTION user_lookup (
        SELECT * ORDER BY (user_id, event_date, occurred_at)
    );

ALTER TABLE user_events MATERIALIZE PROJECTION user_lookup;

After materialization, WHERE user_id = X queries automatically use the projection, giving point-lookup performance without changing the primary key (ClickHouse projections docs). This is the single change that makes ClickHouse viable as a serving layer — without it, point lookups degrade to full-table scans.

Table Engine Selection Guide

The MergeTree family has several variants. Choosing the wrong one for personalization is a common source of slow queries and high storage costs.

MergeTree — raw event storage. Append-only, no deduplication, best write throughput. Use for your primary event table.

ReplacingMergeTree — deduplicates rows with the same primary key on merge, keeping the row with the highest version value. Use for user profile state: current subscription tier, last-known location, onboarding completion flags. Note that deduplication is eventually consistent — use the FINAL modifier or argMax patterns in queries if you need strict latest-value guarantees.

AggregatingMergeTree — stores partial aggregation states as binary blobs. Use for precomputed feature tables behind materialized views. This is the engine the entire feature freshness architecture depends on.

SummingMergeTree — merges rows with the same primary key by summing numeric columns. Use for simple counters (total_plays, total_purchases_lifetime) where you don't need the full aggregation flexibility of AggregatingMergeTree. Lower overhead, narrower capability.

Buffer — in-memory buffer that flushes to a backing table on a time or size threshold. Use in front of high-ingest event tables during write bursts. At ×marble, we run a Buffer engine in front of the raw event table during peak ingest periods, flushing every 10 seconds, which reduces merge pressure without introducing Kafka-level operational overhead for moderate throughput.

SharedMergeTree (ClickHouse Cloud, 2024+) is the cloud-native variant of all the above. It separates storage to object storage and compute to a detachable server pool. For personalization workloads that have irregular compute demand, this is worth evaluating before you commit to self-hosted sizing.

Latency Boundaries, Feature Freshness, and the Cache Layer

Here are realistic latency numbers, not benchmark marketing:

Materialized view feature lookup, single user, AggregatingMergeTree, projection on user_id: 3-8ms. This is achievable for a well-modeled table with normal cardinality.

Ad-hoc aggregation, single user, event table, with user_id projection: 15-50ms depending on per-user event volume.

Ad-hoc aggregation, single user, event table, no projection: 200-900ms. Do not put this on a hot serving path.

Cohort-level aggregation, 1M users: 500ms-3s. Batch and cache, don't serve inline.

Concurrency ceiling without caching: ClickHouse handles 10-50 concurrent heavy analytical queries gracefully on a well-provisioned cluster. At more than 200 concurrent point-lookup queries on a single serving endpoint, latency climbs and merge-thread contention surfaces. This is the empirical boundary where you add Redis in front of feature reads — not as a default assumption, as a measured response.

The serving pattern that works in production: ClickHouse computes features, Redis caches them with a TTL matching your feature staleness tolerance (30 seconds for recency-sensitive signals, 5 minutes for slower-moving cohort features), and the serving layer reads from Redis with a ClickHouse fallback on cache miss. You get sub-10ms p99 at high QPS with features that reflect events within one TTL window.

The reason this architecture beats the traditional batch-pipeline approach isn't just latency. It's feature freshness. Most warehouse-plus-KV-store pipelines run feature computation on 15-minute or hourly cycles. A user who skips three tracks and then plays a new genre has revealed something about their current context — but if their features only update hourly, the ranking model scores that user's next request on stale signals. ClickHouse's materialized views run at ingest time. The delta between an event and its reflection in a served feature is seconds, not minutes. For cold-start users with thin history, where every signal carries disproportionate weight, that freshness difference directly affects recommendation quality (Uber's Michelangelo on feature freshness as an ML quality driver).

We treat feature freshness as a first-class product metric at ×marble — we log the max(occurred_at) of the events that contributed to each served feature vector and track staleness at p50/p95 in production. Every hour of pipeline latency is a missed course-correction on a user who is still actively signaling preferences.

ClickHouse vs. Alternatives

vs. BigQuery or Snowflake

BigQuery and Snowflake are excellent for training data preparation, historical reporting, and ad-hoc exploration at analyst scale. They are not designed for low-latency feature serving — their query startup overhead alone (slot acquisition in BigQuery, warehouse resume in Snowflake) is measured in seconds. Their per-query pricing models also make high-QPS serving economically irrational at scale.

The practical split in 2026: use ClickHouse as the operational personalization warehouse (events, features, serving), and Snowflake or BigQuery as the long-term analytical layer (model training datasets, compliance archives, reporting). Mirror data from ClickHouse to BigQuery via scheduled exports for workloads where BigQuery's SQL ecosystem or BQML capabilities matter. Don't try to serve personalization features out of Snowflake. The architecture will fail the first time you get a traffic spike.

vs. Redis as Primary Feature Store

Redis is the incumbent for feature serving because it's a KV store designed for high-QPS point lookups at sub-millisecond latency. The problems with Redis as the primary feature store: everything must fit in memory (expensive at scale), features are stale until a batch pipeline writes them (adds batch latency), and the compute system and serving system are operationally coupled.

ClickHouse-first with Redis as a caching layer decouples these concerns. Features are computed where the data lives (ClickHouse), and Redis becomes a thin, TTL-bound serving accelerator rather than a stateful store with its own ingestion pipeline. You spend less on Redis memory because you're caching a hot subset, not mirroring the full feature set.

vs. Feast, Tecton, or Hopsworks

Dedicated feature stores like Feast, Tecton, and Hopsworks provide feature registries, point-in-time correct training joins, feature versioning, and offline/online consistency guarantees. These are real problems for large ML platforms. When you have 50+ features across 20+ models and multiple teams need guaranteed training-serving consistency, a feature registry that enforces it is genuinely valuable.

For teams at fewer than 100M MAU or fewer than 10 production ML models, the operational overhead is rarely justified. You add a service, a schema registry, a deployment dependency, and a team-wide abstraction to learn and maintain. A well-modeled ClickHouse cluster with clear materialized view conventions and a Redis serving layer handles 80% of the use cases at 20% of the ops cost. Build the complexity only when the scale demands it.

vs. Pinecone, Qdrant, or Weaviate

Vector databases optimize for nearest-neighbor search over dense embeddings. They don't store the structured behavioral features — click counts, recency decay scores, category affinities — that most personalization systems rely on alongside vector similarity. You end up with a vector store for semantic signals and a separate analytical store for behavioral signals, with joins happening in application code — which is fragile and adds latency to every request.

ClickHouse in 2026 supports approximate nearest-neighbor search via usearch and annoy indexes, allowing hybrid queries that combine structured filters with vector similarity in a single SQL statement. This is not a replacement for Pinecone at billion-vector scale, but it removes the need for a separate vector tier for teams whose embedding workloads are moderate. At ×marble, we store both structured user features and item embedding vectors in ClickHouse, joining them in a single ranking query rather than orchestrating two network round-trips.

Where to Start

If you're evaluating ClickHouse for a personalization warehouse, here is the minimum viable sequence to validate the approach before committing infrastructure:

Step 1: Model your event table with intentional primary and partition keys. Run your first 30 days of events through it. Verify that scan-based aggregations over a single user's history are under 50ms — if they're not, investigate ORDER BY and partition pruning before going further.

Step 2: Define one materialized view per feature family. Start with the features your serving model actually uses. Don't build a feature catalog speculatively. Measure write amplification — materialized views add 1.5-2x in typical personalization schemas, which is acceptable but worth knowing.

Step 3: Add a projection on user_id. If your primary key is time-first (as it should be for event tables), add a projection to support user_id point lookups. Materialize it once, then verify via EXPLAIN that single-user queries use the projection.

Step 4: Benchmark serving latency before adding Redis. Many teams add Redis preemptively because the architecture diagram has a box for it. Run a realistic load test first. At what QPS does ClickHouse latency exceed your serving budget? The answer is usually higher than teams expect. Add Redis when you have empirical evidence you need it.

Step 5: Instrument feature freshness. Add a max(occurred_at) column to your feature tables and log the delta at serving time. Feature staleness is invisible until you measure it. Once you have the metric, you can tune materialized view flush intervals and Redis TTLs against a concrete target rather than guessing.

ClickHouse won't replace every piece of your personalization infrastructure — it doesn't replace a vector index for billion-scale ANN search, it doesn't replace a feature registry for multi-team ML consistency enforcement, and it doesn't replace your OLTP store for transactional user profile writes. What it does is collapse the warehouse-feature-store split for the majority of personalization workloads. That simplification means one fewer system to operate, features that are seconds stale instead of minutes stale, and a SQL-native interface to the full event history rather than a narrow KV lookup interface into a pre-baked feature set. For most teams, that's the right place to start.