Mid-Course Project: High-Performance Analytics DB

This mid-course project brings the first four modules together into one task: design and tune a PostgreSQL database that ingests a large, ever-growing stream of events and serves fast analytical queries over it. You will model the schema, partition the events table by time, build the right indexes, accelerate dashboards with materialised views, and prove each decision with EXPLAIN ANALYZE — turning a naive slow design into a performant one with evidence at every step.

The scenario is an analytics database for a sports-streaming platform recording viewing events — millions per day — that product teams query for dashboards: daily active viewers, top matches, watch-time distributions. The point is integration: combining data types, schema design, partitioning, indexing, query optimisation, and concurrency-aware maintenance into a coherent, fast system.

Learning Objectives

• Model an analytics schema using appropriate data types, constraints, and relationships.
• Partition a large, append-mostly events table by time so pruning and data ageing are efficient.
• Design indexes (composite, partial, covering) that match the dashboard query patterns.
• Use materialised views to precompute expensive aggregations and refresh them on a schedule.
• Validate every optimisation with EXPLAIN ANALYZE, confirming plans and timings improved.
• Plan for concurrency and maintenance: keep transactions short and vacuum/partition lifecycle healthy.

Technical Requirements

• PostgreSQL 15+ with permission to create tables, partitions, indexes, and materialised views.
• An events table partitioned by RANGE on the event timestamp, with monthly (or daily) partitions.
• A small set of dimension tables (viewers, matches) with primary keys and foreign keys.
• Indexes aligned to the dashboard queries, validated against EXPLAIN ANALYZE.
• At least one materialised view for a costly aggregation, with a documented refresh strategy.
• A maintenance plan: partition creation/retention automation and autovacuum tuning notes.

Architecture & Design

The core is a partitioned events fact table holding one row per viewing event, referencing slim dimension tables for viewers and matches. Partitioning by event time gives partition pruning for the time-bounded dashboard queries and makes retention a fast DETACH/DROP. Indexes are designed per query: a composite index for 'events by match over a window', a partial index for any hot subset, and covering indexes where a dashboard reads a fixed set of columns.

Expensive rollups — daily active viewers, per-match watch time — are precomputed as materialised views refreshed on a schedule, so dashboards read a small summary instead of scanning millions of raw events. Raw ingestion stays simple and append-only; the heavy aggregation work happens once per refresh, not once per dashboard load, mirroring the seed-time-versus-request-time principle of fast analytics.

-- Partitioned fact table + dimension references
CREATE TABLE view_events (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    viewer_id   bigint NOT NULL,
    match_id    bigint NOT NULL,
    watched_ms  integer NOT NULL CHECK (watched_ms >= 0),
    event_time  timestamptz NOT NULL,
    PRIMARY KEY (id, event_time)
) PARTITION BY RANGE (event_time);

CREATE TABLE view_events_2026_06 PARTITION OF view_events
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

-- Index aligned to 'events for a match in a time window'
CREATE INDEX ON view_events (match_id, event_time);

Phase 1 — Model and Partition

Design the schema first: slim viewers and matches dimension tables with primary keys, and a view_events fact table partitioned by RANGE on event_time with monthly partitions. Choose data types deliberately — bigint ids, timestamptz for time, integer milliseconds for watch time — and add CHECK constraints to enforce validity at the source.

Create a few partitions spanning your data range and confirm that a time-bounded query prunes to the relevant partitions with EXPLAIN. This phase establishes the storage foundation: data ages cleanly by partition, and time-filtered queries already avoid scanning the whole dataset before you add a single index.

-- Dimension tables
CREATE TABLE viewers (id bigint PRIMARY KEY, country text NOT NULL);
CREATE TABLE matches (id bigint PRIMARY KEY, title text NOT NULL, played_on date);

-- Confirm pruning on a time-bounded query
EXPLAIN
SELECT count(*) FROM view_events
WHERE event_time >= '2026-06-10' AND event_time < '2026-06-11';
-- Plan should scan only view_events_2026_06, others pruned

Phase 2 — Index for the Dashboard Queries

Enumerate the actual dashboard queries — top matches by watch time over a window, daily active viewers, per-country breakdowns — and design indexes to match each. A composite index on (match_id, event_time) serves per-match windows; a covering index can answer a hot summary from the index alone; a partial index targets any skewed hot slice.

Capture each query's baseline plan, add the targeted index, run ANALYZE, and confirm the plan switched from a sequential scan to an index or bitmap scan with a large drop in time. Let the plans, not intuition, dictate which indexes exist, and remove any that go unused.

-- Baseline then targeted index for 'top matches by watch time last 7 days'
EXPLAIN (ANALYZE, BUFFERS)
SELECT match_id, sum(watched_ms) AS total_ms
FROM view_events
WHERE event_time >= now() - interval '7 days'
GROUP BY match_id ORDER BY total_ms DESC LIMIT 10;

CREATE INDEX idx_ve_match_time ON view_events (match_id, event_time) INCLUDE (watched_ms);
ANALYZE view_events;
-- Re-run EXPLAIN ANALYZE: expect index usage and lower time

Phase 3 — Materialise and Maintain

Precompute the costliest dashboards as materialised views: a daily per-match watch-time rollup and a daily-active-viewers summary. Add a unique index on each so it can be refreshed concurrently, and refresh on a schedule (a scheduled job, foreshadowing the pg_cron lesson) so dashboards read a tiny summary instead of aggregating millions of raw rows on every load.

Finish with the maintenance plan: automate creation of next month's partition and retention/DROP of old ones, and tune autovacuum on the high-churn tables so dead tuples from any updates are reclaimed promptly. Document that ingestion uses short transactions to avoid blocking vacuum, tying the concurrency lessons into the operational design.

CREATE MATERIALIZED VIEW mv_daily_match_watch AS
SELECT date_trunc('day', event_time) AS day, match_id,
       sum(watched_ms) AS total_ms, count(*) AS events
FROM view_events
GROUP BY 1, 2;

-- Unique index enables REFRESH ... CONCURRENTLY (no read blocking)
CREATE UNIQUE INDEX ON mv_daily_match_watch (day, match_id);

-- Refresh on a schedule (see the pg_cron lesson); concurrently to avoid blocking reads
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_match_watch;

-- Tune autovacuum on the busiest partition; automate next-month partition + retention

Evaluation Rubric

• Schema & data modelling (15%): appropriate types, constraints, and dimension/fact separation.
• Partitioning (20%): events partitioned by time with verified pruning and a retention strategy.
• Indexing (25%): indexes match the dashboard queries; each justified by an EXPLAIN ANALYZE before/after.
• Materialised views (20%): costly aggregations precomputed, uniquely indexed, and refreshed concurrently on a schedule.
• Evidence (10%): every optimisation backed by a captured plan and timing, not intuition.
• Maintenance & concurrency (10%): partition lifecycle automation, autovacuum tuning, and short-transaction ingestion documented.