Capstone: an analytics query system end to end

The shape of the system

By the end of this lesson you will have built the full pipeline — schema, query, pagination, concurrency, and the EXPLAIN-driven index fix — and you will know exactly which choice from which unit prevents each class of failure.

The endpoint is GET /tenants/:id/metrics?from=…&to=…&cursor=…. It returns a per-tenant time series — daily active users (DAU), revenue, and the top event types — paginated, fast, under thousands of concurrent tenants. Behind it sits a raw event log and a daily rollup that a background worker maintains. The architecture is a pipeline: raw events flow into a pre-aggregated rollup; the API reads the rollup for the time series and the raw table only for the freshest day; a worker keeps the rollup current without blocking readers.

Schema and types — the foundation (unit 06)

Get the columns right and half the performance problems never exist. The choices are not cosmetic:

CREATE TABLE events (
  id         bigint GENERATED ALWAYS AS IDENTITY,
  tenant_id  bigint      NOT NULL,
  user_id    bigint      NOT NULL,
  type       text        NOT NULL,
  ts         timestamptz NOT NULL,            -- always tz-aware; never naive timestamp
  props      jsonb       NOT NULL DEFAULT '{}',
  PRIMARY KEY (tenant_id, id)                 -- tenant-leading so every read is tenant-local
);

-- The pre-aggregated rollup the API reads for historical days:
CREATE TABLE metrics_daily (
  tenant_id   bigint      NOT NULL,
  day         date        NOT NULL,
  dau         integer     NOT NULL,
  revenue     numeric(14,2) NOT NULL,
  PRIMARY KEY (tenant_id, day)
);

Three senior calls hide in that DDL. ts is timestamptz, not timestamp — a naive timestamp silently drops the offset, so a user in Tokyo and one in São Paulo land in the same “day” bucket and your DAU is wrong by design. props is jsonb, not json — jsonb is parsed once into a binary form, so props->>'amount' and GIN indexes work; json re-parses on every read. And id is a GENERATED ALWAYS AS IDENTITY key, the SQL-standard replacement for serial, with the composite primary key (tenant_id, id) so the table is physically clustered by tenant — every per-tenant scan stays in a contiguous slice of the index.

The metrics query — conditional aggregation (unit 03)

DAU, revenue, and a per-type breakdown are one pass over the data, not three queries. The FILTER clause turns one GROUP BY into many conditional metrics — this is the unit-03 technique doing real work:

SELECT
  date_trunc('day', ts)                                   AS day,
  count(DISTINCT user_id)                                 AS dau,
  count(*) FILTER (WHERE type = 'purchase')               AS purchases,
  coalesce(sum((props->>'amount')::numeric)
           FILTER (WHERE type = 'purchase'), 0)           AS revenue,
  count(*) FILTER (WHERE type = 'signup')                 AS signups
FROM events
WHERE tenant_id = $1
  AND ts >= $2 AND ts < $3
GROUP BY 1
ORDER BY 1;

One scan of one tenant’s slice produces every metric. The alternative — a separate query per metric — multiplies round trips and re-reads the same heap pages each time. FILTER beats CASE WHEN ... END here because it reads as intent and the planner treats it identically. Note count(DISTINCT user_id) is the expensive part: it forces a sort or hash of every user_id in range, which is exactly why historical days go to the pre-computed rollup instead.

Top-N per tenant and running totals — window functions (units 04)

“Top 5 event types this period” and “cumulative revenue” are window-function jobs. Ranking partitions per tenant; the running total accumulates within the ordered frame:

-- Top 5 event types for one tenant, with each type's share:
SELECT type, n,
       round(100.0 * n / sum(n) OVER (), 1) AS pct
FROM (
  SELECT type, count(*) AS n
  FROM events
  WHERE tenant_id = $1 AND ts >= $2 AND ts < $3
  GROUP BY type
) t
ORDER BY n DESC
LIMIT 5;

-- Cumulative revenue across the daily series (run after the per-day rollup):
SELECT day, revenue,
       sum(revenue) OVER (ORDER BY day
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS revenue_to_date
FROM metrics_daily
WHERE tenant_id = $1 AND day >= $2 AND day < $3
ORDER BY day;

The sum(n) OVER () with an empty window computes the grand total without collapsing rows, so each type keeps its share — a GROUP BY could not do that in the same pass. For true per-tenant top-N over many tenants at once, you would wrap row_number() OVER (PARTITION BY tenant_id ORDER BY n DESC) and filter <= 5 — the exact pattern from unit 04’s ranking lesson.

A CTE pipeline for a derived dimension (unit 05)

Event types roll up into categories (purchase, refund → commerce; signup, login → lifecycle). A category tree is naturally recursive; a recursive CTE walks it, then a second CTE joins events to the resolved category. This is the unit-05 pattern — a readable, staged pipeline instead of a nested-subquery thicket:

WITH RECURSIVE category_tree AS (
  SELECT id, parent_id, name, name AS root
  FROM event_categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.root
  FROM event_categories c JOIN category_tree t ON c.parent_id = t.id
),
typed AS (
  SELECT et.type, ct.root AS category
  FROM event_types et JOIN category_tree ct ON et.category_id = ct.id
)
SELECT typed.category, count(*) AS n
FROM events e
JOIN typed ON typed.type = e.type
WHERE e.tenant_id = $1 AND e.ts >= $2 AND e.ts < $3
GROUP BY typed.category
ORDER BY n DESC;

Keyset pagination — not OFFSET (units 01 and 05)

When you add pagination to an analytics endpoint, the choice of mechanism determines whether page 500 is as fast as page 1 — or fifty times slower.

OFFSET 100000 LIMIT 50 makes Postgres read and discard 100,000 rows to return 50 — page latency grows linearly with page number, so the deepest scroll is the slowest. Keyset (a.k.a. cursor) pagination carries the last row’s sort key and seeks straight to it:

-- First page: no cursor.
SELECT day, dau, revenue
FROM metrics_daily
WHERE tenant_id = $1
ORDER BY day DESC
LIMIT 50;

-- Next page: cursor is the last day seen; seek past it. O(log n), not O(offset).
SELECT day, dau, revenue
FROM metrics_daily
WHERE tenant_id = $1
  AND day < $2                       -- $2 = last day from the previous page
ORDER BY day DESC
LIMIT 50;

With the primary key (tenant_id, day) the cursor predicate is an index range scan: page 1 and page 10,000 cost the same. When the sort key is not unique, the cursor must be a tuple — (day, id) < ($cursor_day, $cursor_id) — using row comparison so you never skip or duplicate rows across a tie.

Joins without fan-out (unit 02)

Joining events to a per-event dimension (one category per type) is safe. The trap is joining to a one-to-many table — say a tenant_features table with many rows per tenant — which silently multiplies your event rows before the aggregate, so DAU and revenue come back inflated. The fix is to aggregate the dimension to one row per key before the join, or to keep the dimension lookup one-to-one:

-- WRONG: tenant_features has N rows per tenant → every event counted N times.
-- SELECT count(*) FROM events e JOIN tenant_features f USING (tenant_id) ...

-- RIGHT: collapse the dimension first, then join one-to-one.
SELECT e.type, count(*) AS n
FROM events e
JOIN (SELECT tenant_id, max(plan) AS plan
      FROM tenant_features GROUP BY tenant_id) f USING (tenant_id)
WHERE e.tenant_id = $1
GROUP BY e.type;

Inflated aggregates from a fan-out join are the analytics bug that ships: the query runs, returns numbers, and the numbers are quietly 3× too high.

Concurrency: the rollup worker (unit 07)

Many worker instances must drain the backlog without two of them processing the same batch. SELECT ... FOR UPDATE SKIP LOCKED is the queue primitive: each worker claims rows no one else holds and skips locked ones instead of blocking. Isolation choice matters too — the worker runs each batch in READ COMMITTED (the default) because it does not need a frozen snapshot across statements; a reporting export that must be internally consistent would use REPEATABLE READ instead.

BEGIN;  -- READ COMMITTED is enough for an idempotent upsert worker
WITH batch AS (
  SELECT id, tenant_id, ts, type, props
  FROM events
  WHERE rolled_up = false
  ORDER BY id
  LIMIT 1000
  FOR UPDATE SKIP LOCKED          -- claim 1000 rows no other worker holds
)
INSERT INTO metrics_daily (tenant_id, day, dau, revenue)
SELECT tenant_id, date_trunc('day', ts)::date,
       count(DISTINCT user_id),
       coalesce(sum((props->>'amount')::numeric) FILTER (WHERE type='purchase'),0)
FROM batch GROUP BY 1, 2
ON CONFLICT (tenant_id, day)
  DO UPDATE SET dau = excluded.dau, revenue = excluded.revenue;
-- (mark the batch rolled_up = true here)
COMMIT;

Without SKIP LOCKED, ten workers serialize on the same head-of-queue rows and you get one worker’s throughput with ten workers’ cost. With it, throughput scales close to linearly until I/O saturates.

Tuning: make EXPLAIN tell you the truth (unit 08 + databases tracks)

The metrics query is slow on day one because there is no useful index — Postgres seq-scans the whole event log per request. The senior workflow is measure, diagnose, fix one thing, re-measure:

EXPLAIN (ANALYZE, BUFFERS)
SELECT date_trunc('day', ts) AS day, count(DISTINCT user_id) AS dau
FROM events
WHERE tenant_id = 7 AND ts >= '2026-05-01' AND ts < '2026-06-01'
GROUP BY 1;
-- Seq Scan on events  (rows=480000)  Buffers: shared read=61000
-- Planner estimated 12 rows; actual 480000 → a 40,000× misestimate.

Two fixes, in order. When you see a misestimate this large, resist the reflex to jump straight to an index — fix the planner’s statistics first, or the index may not even be chosen. First, the misestimate: tenant_id and ts are correlated (a tenant’s events cluster in time), so the planner multiplies their selectivities as if independent and guesses far too few rows. CREATE STATISTICS with dependencies teaches it the correlation. Second, the access path: a composite index with the equality column first and the range column second turns the seq scan into a tight index range scan.

-- Teach the planner that tenant_id and ts are correlated (extended statistics):
CREATE STATISTICS events_tenant_ts (dependencies)
  ON tenant_id, ts FROM events;
ANALYZE events;

-- The index that matters: equality (tenant_id) first, range (ts) second.
CREATE INDEX events_tenant_ts_idx ON events (tenant_id, ts);

Column order is the whole game (the databases track’s leading-column rule): (tenant_id, ts) lets Postgres seek to one tenant and then range-scan the time window inside it; (ts, tenant_id) would force a scan of every tenant in the window. After the index, the same query is an Index Scan reading shared read=420 instead of 61000 — that is the 9-seconds-to-40-milliseconds jump. Last, watch bloat: the rollup table is UPDATE-heavy via the upsert, so dead tuples accumulate; let autovacuum keep it lean (the databases MVCC chapter covers why dead rows slow scans even after the live set shrinks).