How We Query 1.2 Billion Rows in Under 50ms — Partitioning, Columnstore, and Read Models at Scale

Every dashboard load ran a SUM and a COUNT over a 1.2-billion-row table, and each one took just over two seconds. At 110,000 monthly active users hammering those dashboards, our Azure SQL sat at 78% CPU and every campaign view felt like wading through mud. The fix wasn't a bigger database tier. It was realizing that you never make a billion-row aggregate fast — you make sure you never run it.

This is the data-architecture story behind one of the numbers we're proudest of on Mattrx, our multi-tenant marketing-analytics SaaS: KPI query p95 from 2,100ms to 48ms, on a CampaignEvents table that holds 1.2 billion rows across ~90 days of daily partitions, under a dashboard read load that peaks near 3,200 requests a second. Same data, same questions, same Azure SQL — a completely different read path. Here's every layer of it, with the real Azure SQL and C#, and the React dashboard that finally renders instantly.

TL;DR

Dimension	Before	After
Dashboard query	aggregate raw 1.2B rows	read a pre-aggregated rollup
Table design	rowstore, unpartitioned	day-partitioned + columnstore
Rows touched per query	millions	thousands (rollup) / 1–7 partitions
KPI p95 latency	2,100ms	48ms
DB CPU at peak	78%	22%
Working set	2.1 GB	380 MB
Hot path	always hits SQL	Redis cache

• You don't speed up a billion-row aggregate — you avoid running it. Partition, columnstore, pre-aggregate, cache.
• Partition CampaignEvents by day → partition elimination touches 1–7 partitions, not 1.2B rows (and gives instant retention via sliding-window).
• Clustered columnstore → ~10× compression and batch-mode execution — aggregates over millions of rows in milliseconds (segment elimination prunes on the append-ordered date column, not on GUID keys).
• Pre-aggregated read models (CQRS rollups) maintained in the ingestion path → the dashboard reads thousands of pre-aggregated rows, not raw events. The real sub-50ms enabler — kept correct with an offset-watermark guard so at-least-once redelivery can't double-count.
• Plan stability on the raw fallback (PSP on Azure SQL compat 160, then RECOMPILE/Query Store) → stable p99 across tenants of wildly different sizes; the rollup seek needs no hint.
• Redis cache (short TTL + event-driven invalidation) absorbs hot dashboards; the React frontend hits cache for the common case.
• Read/write separation: writes hit the raw partitioned columnstore; reads hit rollups + cache → they stop contending.
• KPI p95 2,100ms → 48ms; DB CPU 78% → 22%; working set 2.1 GB → 380 MB; ~$280/mo of SQL saved.
• CampaignEvents is the write model; the dashboard reads a smaller, purpose-built read model.

The one mental shift: a billion-row table is a write model, not a read model. You never make a dashboard aggregate a billion rows fast — you make sure it reads something smaller: a partition-eliminated, columnstore-compressed slice at worst, a pre-aggregated rollup normally, and a cache hit usually. Design the read path backward from the 50ms the user expects.

The running example: Mattrx analytics

Mattrx runs a React dashboard on the front (React Query for data fetching) and .NET 9 / ASP.NET Core on the back, over Azure SQL. Events stream in through Confluent Kafka into CampaignEvents — ~1.2B rows spread across ~90 days of daily partitions (Events ~180M, Campaigns ~4M). Every campaign dashboard tile — impressions, clicks, CTR, conversions, spend pacing — is an aggregate over CampaignEvents, filtered by tenant, campaign, and a date range. Multiply by 110k MAU refreshing dashboards — read traffic peaking near 3,200 requests a second — and you have thousands of billion-row aggregations a minute. That's the load. Here's the before that buckled and the after that holds, layer by layer.

The architecture we ended up with

  Kafka (event ingestion)
        |
        v
  Raw CampaignEvents  (Azure SQL)
    - RANGE partitioned by day        (partition elimination)
    - clustered COLUMNSTORE           (10x compression, batch-mode aggregation)
    - append-only  <-- the WRITE model
        |  (incremental rollup: the ingestion consumer aggregates as it writes)
        v
  CampaignDailyKpis  (rollup READ model)
    - per tenant x campaign x day  -> thousands of rows, not 1.2B
    - clustered rowstore, seek-friendly
        |
        v
  Redis cache  (hot dashboards: short TTL + event-driven invalidation)
        |
        v
  React dashboard (React Query)  -----> KPI p95 = 48ms

1. The naive query — and why it was 2,100ms

Before

Every dashboard tile aggregated the raw events, on every load.

-- BEFORE: aggregate 1.2B raw rows for one dashboard tile, on every request.
SELECT
    SUM(CASE WHEN EventType = 1 THEN 1 ELSE 0 END)     AS Impressions,
    SUM(CASE WHEN EventType = 2 THEN 1 ELSE 0 END)     AS Clicks,
    SUM(CASE WHEN EventType = 3 THEN Value ELSE 0 END) AS Conversions
FROM dbo.CampaignEvents
WHERE TenantId = @tenant AND CampaignId = @campaign
  AND EventDay >= @from AND EventDay < @to;
-- Rowstore, unpartitioned: touches millions of matching rows, row by row,
-- while competing with the continuous write ingestion. ~2,100ms p95.

Diagnostic: two problems compound. First, even with an index the query has to aggregate every matching row — a busy campaign over 30 days is millions of rows, added up one at a time in row-mode. Second, that read work runs on the same table the ingestion is furiously writing to, so reads and writes fight for CPU and locks. Nobody ever needed a billion-row scan; they needed a number. We were computing it the most expensive way possible.

2. Partitioning — touch a fraction of the data

Before

One giant unpartitioned table. A "last 7 days" query still had to consider the whole structure.

After

Range-partition CampaignEvents by day. With ~90 days of history that's ~90 daily partitions, so a 7-day query touches ~7 of them and the optimizer eliminates the other ~83 — it reads under 100M rows instead of all 1.2B.

-- Range-partition by day so a "last 7 days" query scans ~7 partitions, not the whole table.
CREATE PARTITION FUNCTION pf_events_day (date)
    AS RANGE RIGHT FOR VALUES ('2026-06-01', '2026-06-02' /* ... one boundary per day ... */);

CREATE PARTITION SCHEME ps_events_day
    AS PARTITION pf_events_day ALL TO ([PRIMARY]);   -- or dedicated filegroups

CREATE TABLE dbo.CampaignEvents
(
    TenantId   uniqueidentifier NOT NULL,
    CampaignId uniqueidentifier NOT NULL,
    EventDay   date             NOT NULL,   -- the partition key
    EventType  tinyint          NOT NULL,
    Value      decimal(18,4)    NULL,
    OccurredAt datetime2(3)     NOT NULL
) ON ps_events_day(EventDay);

Diagnostic: partition elimination only works if the partition key is in the query predicate — here, the date range every dashboard already uses. Bonus: retention gets cheap. But a real sliding window has two edges, and the two-line SWITCH … DROP you see in most blog posts silently stops sliding after the first run — so it's worth showing both edges:

-- LEADING edge (run before ingesting a new day): create tomorrow's partition.
ALTER PARTITION SCHEME  ps_events_day NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION pf_events_day() SPLIT RANGE ('2026-07-12');

-- TRAILING edge (retention): age out the oldest day.
-- SWITCH is metadata-only (no DELETE); MERGE then removes the emptied boundary
-- so the window actually slides instead of leaving a dead partition behind.
ALTER TABLE dbo.CampaignEvents
    SWITCH PARTITION 2 TO dbo.CampaignEvents_Stage;   -- staging: same schema + filegroup + aligned indexes
DROP TABLE dbo.CampaignEvents_Stage;
ALTER PARTITION FUNCTION pf_events_day() MERGE RANGE ('2026-04-12');

Skip the leading SPLIT and every new day piles into one open-ended top partition — so your hottest data (the "last 7 days" everyone queries) gets no per-day elimination. Skip the trailing MERGE and the emptied partition lingers, so the next retention run re-targets an already-empty slot instead of the next-oldest day. Both edges, on a schedule (SQL Agent or an Azure Function).

Mattrx metric: partitioning alone cut the rows a 7-day dashboard query considers from ~1.2B to under 100M — a ~13× reduction, before columnstore and the rollup take it the rest of the way.

3. Columnstore — aggregates in batch mode

Before

A rowstore table means aggregation walks matching rows one at a time and stores them uncompressed — 2.1 GB of hot working set.

After

A clustered columnstore index on the partitioned table. Columnstore stores data by column in compressed segments, runs aggregation in batch mode (a thousand rows per CPU instruction instead of one), and keeps per-segment min/max so it can skip whole segments (segment elimination).

-- Clustered columnstore: column compression + batch-mode aggregation + segment elimination.
CREATE CLUSTERED COLUMNSTORE INDEX cci_CampaignEvents
    ON dbo.CampaignEvents
    ON ps_events_day(EventDay);   -- aligned to the partition scheme

Diagnostic: columnstore is built for exactly this workload — large, append-only, aggregate-heavy. Compression (~10×) is why the working set collapsed; batch-mode execution is why a SUM over millions of rows runs in milliseconds instead of seconds. One honest caveat on segment elimination: it uses each rowgroup's min/max, so it only prunes on a column the data is physically ordered by — here that's EventDay (append order), which partitioning already handles. It does not prune on TenantId/CampaignId: those are random GUIDs smeared across every rowgroup, so the raw fallback scans every rowgroup in the date range and filters by tenant row-by-value. Its cost scales with the total rows in the range, not your tenant's slice. (If you need the raw path to prune by tenant, an ordered clustered columnstore — ... ORDER (TenantId, EventDay) — or a rowstore index is the lever.) The one rule: columnstore loves append-only data and hates heavy random updates — and CampaignEvents is append-only, which is why this is a clean fit (see the honest section for when it isn't).

Mattrx metric: columnstore compression is why the working set dropped from 2.1 GB to 380 MB, and batch-mode aggregation is what makes the fallback raw query (section 5) finish in ~45ms instead of two seconds.

4. Pre-aggregated read models — the real sub-50ms enabler

Before

Partitioning and columnstore made the raw aggregate tolerable, but a busy campaign over a long range still aggregates a lot of rows on demand. To get to 48ms consistently, you stop aggregating on read entirely.

After

Maintain a rollup read model — pre-aggregated per tenant × campaign × day — and update it incrementally as events ingest. The dashboard reads a handful of pre-computed rows.

-- The READ model: pre-aggregated KPIs. Thousands of rows total, not 1.2 billion.
CREATE TABLE dbo.CampaignDailyKpis
(
    TenantId    uniqueidentifier NOT NULL,
    CampaignId  uniqueidentifier NOT NULL,
    Day         date             NOT NULL,
    Impressions bigint           NOT NULL,
    Clicks      bigint           NOT NULL,
    Conversions decimal(18,4)    NOT NULL,
    CONSTRAINT PK_CampaignDailyKpis PRIMARY KEY CLUSTERED (TenantId, CampaignId, Day)
);

The ingestion consumer (the same Kafka consumer that writes raw events) folds each batch into the rollup — so the read model is always warm:

// The ingestion consumer aggregates as it goes: each batch merges deltas into the daily rollup.
// CRITICAL: Kafka is at-least-once — a rebalance or redeploy WILL redeliver a batch. A blind
// `+= delta` would double-count and drift upward forever. So we apply the delta AND advance the
// partition's offset watermark in the SAME transaction, and skip any batch already applied.
public async Task ApplyAsync(int partition, long toOffset,
                             IReadOnlyList<CampaignEvent> batch, CancellationToken ct)
{
    await using var tx = await db.BeginTransactionAsync(ct);

    if (await offsets.WatermarkAsync(partition, tx, ct) >= toOffset)
        return;   // already applied — a redelivered batch is a no-op, so the rollup can't drift

    var deltas = batch
        .GroupBy(e => (e.TenantId, e.CampaignId, Day: e.OccurredAt.Date))
        .Select(g => new KpiDelta(
            g.Key.TenantId, g.Key.CampaignId, g.Key.Day,
            Impressions: g.Count(e => e.Type == EventType.Impression),
            Clicks:      g.Count(e => e.Type == EventType.Click),
            Conversions: g.Where(e => e.Type == EventType.Conversion).Sum(e => e.Value)));

    await rollup.MergeAsync(deltas, tx, ct);              // UPDATE ... += delta, else INSERT
    await offsets.AdvanceAsync(partition, toOffset, tx, ct);
    await tx.CommitAsync(ct);                             // delta + watermark commit atomically
}

Now the dashboard query is an index seek over a few daily rows:

-- AFTER: the dashboard reads the rollup — a seek over ~7 daily rows for a week.
SELECT SUM(Impressions) AS Impressions, SUM(Clicks) AS Clicks, SUM(Conversions) AS Conversions
FROM dbo.CampaignDailyKpis
WHERE TenantId = @tenant AND CampaignId = @campaign
  AND Day >= @from AND Day < @to;
-- Sub-millisecond in SQL; ~15ms end to end.

Diagnostic: this is CQRS applied to one table. The raw CampaignEvents is the write model — append-only, partitioned, columnstore. The rollup is the read model — small, seek-friendly, purpose-built for the exact question dashboards ask. The dashboard never touches the billion rows; it reads thousands. Two properties are easy to conflate here, and getting them wrong is how "fast" quietly becomes "wrong." Staleness is a timing property — the rollup trails the last committed batch by a few seconds and self-heals on the next one, which is fine for a dashboard. Drift is a correctness property — an incremental += delta over an at-least-once stream, with no idempotency guard, double-counts every redelivered batch and never self-heals: your KPIs creep permanently upward on every rebalance. The offset watermark in the code above is exactly what keeps this rollup merely stale, not drifting (recomputing the day's total from raw, or deduping by event id, are the other two ways). For the rare "exact latest second" need, fall back to the raw columnstore query, which is now fast anyway.

Mattrx metric: the rollup is what pinned KPI p95 at 48ms — the common dashboard query became a seek over a handful of rows instead of an aggregate over millions.

5. Covering indexes and plan stability

Before

The rollup query is safe — but the raw fallback (path 3) isn't. Multi-tenant aggregates share a plan cache, and the first tenant to run one "trains" the plan. A plan built around a tiny tenant's cardinality, then reused for your biggest tenant, is a p99 disaster: wrong memory grant, wrong parallelism, wrong aggregate strategy.

After

Two different tools for two different queries — and the trick is knowing which query is which.

The rollup seek needs nothing. Its clustered PK (TenantId, CampaignId, Day) already covers it — equality on the two leading columns plus a range on the third is always a partial-range seek returning ~7 rows, for any tenant. It's plan-stable by construction; adding a hint there is cargo-culting.

The raw fallback aggregate is where parameter sensitivity actually bites — per-tenant cardinality swings the ideal plan (memory grant, parallelism, batch-vs-row mode) by orders of magnitude. On Azure SQL at compatibility level 160, Parameter Sensitive Plan (PSP) optimization handles the common case with no code: it's on by default and caches up to three plan variants bucketed by the cardinality of a skewed equality predicate like TenantId. When you need more control:

-- For the RAW fallback aggregate (not the rollup seek): stop one tenant's plan poisoning another's.
SELECT SUM(CASE WHEN EventType = 1 THEN 1 ELSE 0 END) AS Impressions /* ...clicks, conversions... */
FROM dbo.CampaignEvents
WHERE TenantId = @tenant AND CampaignId = @campaign AND EventDay >= @from AND EventDay < @to
OPTION (RECOMPILE);   -- fresh plan per call; or OPTIMIZE FOR UNKNOWN for a distribution-agnostic plan

-- Catch and pin regressions: Query Store surfaces plan flips and lets you force the good one.
ALTER DATABASE CURRENT SET QUERY_STORE = ON;
-- ...then EXEC sp_query_store_force_plan @query_id, @plan_id;  for a regressed query.

Diagnostic: the discipline is knowing which queries are plan-sensitive and which aren't. A single-table seek on a covering key (the rollup) never needs a hint. The raw aggregate over uneven tenants does: PSP first (automatic on compat 160 — though it's limited to one equality predicate and three buckets, so it's a first line, not a cure-all), then RECOMPILE (a little CPU for a fresh plan) or a Query Store forced plan for the stubborn cases.

Mattrx metric: stabilizing the fallback's plans is what kept p99 near p95 — before, a big tenant's ad-hoc range would randomly spike to seconds when a small-tenant plan got cached; after, the tail flattened.

6. Redis cache and the React dashboard

Before

Every dashboard load — even the same tenant refreshing "today" ten times — hit SQL.

After

A Redis cache with a short TTL and event-driven invalidation absorbs the hot path; SQL only sees a query on a cache miss.

// Hot dashboards (a tenant refreshing "today") hit Redis; new events invalidate that key.
public async Task<CampaignKpis> GetKpisAsync(TenantId tenant, string campaignId, DateRange range, CancellationToken ct)
{
    var key = $"kpis:{tenant}:{campaignId}:{range.CacheKey()}";
    if (await cache.TryGetAsync<CampaignKpis>(key, ct) is { } hit) return hit;   // ~2ms

    var kpis = await rollup.QueryAsync(tenant, campaignId, range, ct);           // ~15ms
    await cache.SetAsync(key, kpis, ttl: TimeSpan.FromSeconds(30), ct);
    return kpis;
}

The React front end fetches KPIs with React Query — and because the API answers from cache or rollup in ~48ms p95, the tiles render instantly instead of spinning:

// The React dashboard: React Query hits an API that answers in ~48ms p95 (cache -> rollup -> raw).
function CampaignKpiTiles({ campaignId, range }: Props) {
  const { data, isLoading } = useQuery({
    queryKey: ["kpis", campaignId, range],
    queryFn: () => api.getCampaignKpis(campaignId, range),
    staleTime: 30_000,
  });

  if (isLoading) return <KpiSkeleton />;
  return <KpiRow impressions={data.impressions} clicks={data.clicks} conversions={data.conversions} />;
}

Diagnostic: the cache isn't the reason we hit 48ms — the rollup is. The cache is why the database load fell off a cliff: the overwhelmingly common request (a tenant staring at their current campaign) is served from Redis, so SQL only does real work on genuine misses. That's what let read and write stop fighting.

Mattrx metric: with reads served from cache + rollup and writes isolated to the raw partitioned columnstore, DB CPU at peak dropped from 78% to 22%, which is where the ~$280/mo of reclaimed SQL capacity came from.

The query path, and where the 48ms goes

  Dashboard asks: "campaign 4821 KPIs, last 7 days"
        |
        v
  1. Redis cache?  --- HIT (most requests) ---> ~2ms    -> return
        | MISS
        v
  2. Rollup read model (7 daily rows, index seek) ----> ~15ms  -> cache + return
        | (rare: an ad-hoc range not covered by the rollup)
        v
  3. Raw CampaignEvents: partition elimination (by day)
     + batch-mode columnstore aggregate over the range -------> ~45ms -> return

  p95 across all paths: 48ms   (was 2,100ms, aggregating raw rows every time)

The numbers, in one place

Metric	Before	After
KPI query p95	2,100ms	48ms
Rows touched (typical)	millions	~7 (rollup)
DB CPU at peak	78%	22%
Working set	2.1 GB	380 MB
Retention (drop old data)	huge DELETE	metadata SWITCH + MERGE
p99 stability	random plan spikes	flat
SQL cost	baseline	−~$280 / mo

The design checklist

• Treat the big table as the write model; build a read model for what dashboards actually ask.
• Partition by the column your queries filter on (date here) for partition elimination + sliding-window retention.
• Use a clustered columnstore for append-only, aggregate-heavy data — compression + batch mode (segment elimination only prunes the append-ordered column).
• Pre-aggregate into rollups maintained incrementally in the ingestion path — with an idempotency guard (offset watermark / dedup) so at-least-once redelivery can't double-count; read thousands of rows, not billions.
• Keep a seek-friendly index on the rollup (plan-stable by construction); stabilize plans on the raw fallback (PSP on compat 160, then RECOMPILE / Query Store) for multi-tenant tails.
• Cache the hot path (short TTL + event invalidation); the frontend hits cache for the common case.
• Separate reads from writes so ingestion and dashboards stop contending.
• Fall back to the (now fast) raw columnstore query for ad-hoc ranges the rollup doesn't cover.

The honest stuff: when NOT to do all this

1. Small tables (< ~10–50M rows). A plain rowstore table with the right covering index is simpler and plenty fast. Partitioning + columnstore + rollups is overkill until the raw aggregate genuinely hurts.
2. Point lookups / OLTP reads. Columnstore is for aggregates. Keep rowstore (or a nonclustered rowstore index) for single-row lookups; don't columnstore a table you fetch one row from.
3. Staleness is fine; drift is not. A rollup trailing the last committed batch by a few seconds self-heals — that's staleness, and it's fine for a dashboard. If a screen truly needs the live-to-the-second count, query the raw columnstore (now fast). Don't confuse this with the next point.
4. Incremental rollups need an idempotency guard. Maintaining a rollup with += delta over an at-least-once stream (Kafka) is only correct if a redelivered batch is a no-op — via an offset watermark, event-id dedup, or recomputing the day from raw. Skip it and your KPIs drift permanently upward on every rebalance. This is a correctness requirement, not an optimization — see the Kafka post below.
5. Don't pre-aggregate every dimension. Roll up only the dimensions dashboards use. A rollup per possible slice is a combinatorial explosion that costs more to maintain than it saves.
6. Cache invalidation is genuinely hard. Event-driven invalidation on a multi-tenant hot key is subtle; a short TTL is a simpler, forgiving safety net — start there.
7. Partitioning helps only if the key matches the predicate, and the window needs both edges. Partition by date, then query by something else, and you get all the complexity and none of the elimination. And a real sliding window needs SPLIT ahead of the newest day and SWITCH-then-MERGE behind the oldest — the two-line version silently stops sliding.
8. Columnstore + heavy random updates don't mix. The delta store and tombstones from lots of updates/deletes erode columnstore's wins. It shines on append-only data — which is exactly what an events table is. Remember segment elimination only prunes on the append-ordered column (date here), not on random GUID keys.

The model to carry forward

A billion-row table is a write model, not a read model. You never make a dashboard aggregate a billion rows fast — you make sure it reads something smaller: a partition-eliminated, columnstore-compressed slice at worst, a pre-aggregated rollup normally, and a cache hit usually. The 1.2B rows still exist, still ingest continuously, still answer the rare ad-hoc question — but the dashboard, the thing a human waits on, reads a purpose-built read model designed backward from the 48 milliseconds they expect.

Three habits for querying huge tables fast:

1. Separate the write model from the read model. The raw table ingests; a purpose-built rollup serves dashboards. They should never be the same query.
2. Make the common query touch thousands of rows, not billions. Partition, pre-aggregate, and cache so the hot path never scans the big table.
3. Design for p99 across uneven tenants. The biggest tenant is where plans regress and tails blow up — covering indexes and plan stability, not just a good average.

Further reading

• We Replaced REST with Kafka and Cut Failures by 90%
• Kafka Consumers Rebalancing Every 5 Minutes? The Config Nobody Tells You About
• Outbox Pattern — A Complete Guide with Order Processing Example

---

Fighting to make a huge table fast and want a second pair of eyes on the partitioning or read-model design? I'm always happy to compare notes — reach me at randhir.jassal@gmail.com.