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.

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.
• A billion-row table is a write model, not a read model.

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
      |
      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

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.

Two problems compound: even with an index the query has to aggregate every matching row (millions, one at a time in row-mode), and that read work runs on the same table ingestion is furiously writing to, so reads and writes fight for CPU and locks. Nobody needed a billion-row scan; they needed a number.

2. Partitioning — touch a fraction of the data

Range-partition CampaignEvents by day. A 7-day query touches ~7 of ~90 partitions; the optimizer eliminates the other ~83 — under 100M rows instead of all 1.2B.

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]);

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);

Partition elimination only works if the partition key is in the predicate — here, the date range every dashboard already uses. A real sliding window needs both edges, not the two-line SWITCH … DROP most blogs show:

-- 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;
-- MERGE then removes the emptied boundary so the window actually slides.
ALTER TABLE dbo.CampaignEvents
    SWITCH PARTITION 2 TO dbo.CampaignEvents_Stage;
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 gets no per-day elimination. Skip the trailing MERGE and the emptied partition lingers. Both edges, on a schedule.

3. Columnstore — aggregates in batch mode

A clustered columnstore index on the partitioned table: column compression, batch-mode execution (a thousand rows per CPU instruction instead of one), and per-segment min/max for segment elimination.

CREATE CLUSTERED COLUMNSTORE INDEX cci_CampaignEvents
    ON dbo.CampaignEvents
    ON ps_events_day(EventDay);   -- aligned to the partition scheme

Compression (~10×) is why the working set collapsed from 2.1 GB to 380 MB; batch mode is why a SUM over millions of rows runs in milliseconds. One honest caveat: segment elimination only prunes on a column the data is physically ordered by — here EventDay (append order). It does not prune on TenantId/CampaignId (random GUIDs smeared across every rowgroup). Columnstore loves append-only data and hates heavy random updates — an events table is append-only, so it's a clean fit.

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

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

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 folds each batch into the rollup — but Kafka is at-least-once, so a rebalance WILL redeliver a batch. A blind += delta double-counts and drifts upward forever. The guard: apply the delta AND advance the 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:

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.

This is CQRS applied to one table. Staleness is a timing property — the rollup trails the last committed batch by seconds and self-heals. Drift is a correctness property — a blind incremental += delta over at-least-once redelivery double-counts and never self-heals. The offset watermark keeps the rollup merely stale, not drifting.

5. Covering indexes and plan stability

The rollup seek needs no hint — its clustered PK (TenantId, CampaignId, Day) covers it; it's plan-stable by construction. The raw fallback aggregate is where parameter sensitivity bites: per-tenant cardinality swings the ideal plan by orders of magnitude, and one tenant's cached plan poisons another's.

-- 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 /* ... */
FROM dbo.CampaignEvents
WHERE TenantId = @tenant AND CampaignId = @campaign AND EventDay >= @from AND EventDay < @toOPTION (RECOMPILE);   -- fresh plan per call; or OPTIMIZE FOR UNKNOWN

On Azure SQL compat 160, Parameter Sensitive Plan optimization handles the common case automatically (up to three plan variants bucketed by a skewed equality predicate). For the stubborn cases, RECOMPILE or a Query Store forced plan.

6. Redis cache and the React dashboard

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

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 cache isn't why we hit 48ms — the rollup is. The cache is why database load fell off a cliff: the common request (a tenant staring at their current campaign) is served from Redis, so reads and writes stop fighting. DB CPU at peak dropped from 78% to 22% — ~$280/mo of reclaimed SQL.

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 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. Design the read path backward from the 48 milliseconds the user expects.

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.
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 — covering indexes and plan stability, not just a good average.

---

Originally published at prepstack.co.in.