Part 1 · 3 chapters · ~18 min

Query and Schema Scaling

The slow-query pipeline from capture to verification, index strategy at scale and the write cost of every index, schema design for scale with denormalisation, summary and counter tables, archival and hot-warm-cold tiers, big deletes without killing production, and online schema change across engines.

The slow-query pipeline and index strategy

index decision	rule of thumb
which columns	equality columns first, then the range or sort column (the ESR idea, for any B-tree)
covering	INCLUDE the selected columns for the hottest read path only
partial	index only the rows the hot query touches (`WHERE state = 'pending'`)
cost	every index adds a write per insert and per non-HOT update, plus WAL, plus cache memory
hygiene	drop indexes with zero scans over a month (check replicas too, they have their own stats)

THE SLOW-QUERY PIPELINE

capture, digest, rank, fix, verify, and do it every week

swipe the figure sideways, or tap expand for full screen

1/5

capture

Capture every query with its timing: pg_stat_statements (aggregated in memory), the MySQL slow log with long_query_time near 0 for short periods, or APM traces.

capture everything, briefly if neededpg_stat_statements or a low slow-log threshold

Schema for scale: summaries, counters and tiers

code

-- a hot counter row serialises every writer; spread it over N slots
CREATE TABLE loan_counters (product_id int, slot int, issued bigint, PRIMARY KEY (product_id, slot));
UPDATE loan_counters SET issued = issued + 1 WHERE product_id = $1 AND slot = (random() * 15)::int;
SELECT sum(issued) FROM loan_counters WHERE product_id = $1;

-- a daily summary table, maintained incrementally, instead of aggregating 400M rows per dashboard load
INSERT INTO daily_volume (day, merchant_id, count, amount_kobo)
SELECT date_trunc('day', created_at), merchant_id, count(*), sum(amount_kobo)
FROM transfers WHERE created_at >= $1 AND created_at < $2 GROUP BY 1, 2
ON CONFLICT (day, merchant_id) DO UPDATE SET count = excluded.count, amount_kobo = excluded.amount_kobo;

Data lifecycle: hot data (this month) on the primary, warm data (this year) in partitions or a cheaper replica, cold data (older) archived to object storage in Parquet with a query path (Athena, DuckDB). Regulated data keeps its retention period and then is deleted on purpose.

Big deletes and online schema change

code

-- delete in small batches, sleeping between them, so replicas and vacuum keep up
DO $$ DECLARE n int; BEGIN LOOP
  DELETE FROM events WHERE id IN (SELECT id FROM events WHERE created_at < now() - interval '400 days' LIMIT 5000);
  GET DIAGNOSTICS n = ROW_COUNT; EXIT WHEN n = 0; COMMIT; PERFORM pg_sleep(0.2);
END LOOP; END $$;
-- better still: partition by time and DROP the old partition

engine	online schema change
MySQL	INSTANT and INPLACE DDL where possible; gh-ost (binlog-based, triggerless) or pt-online-schema-change (triggers) otherwise; Vitess runs them as managed migrations
Postgres	metadata-only changes with lock_timeout; CONCURRENTLY for indexes; NOT VALID then VALIDATE for constraints; pg_repack for rewrites
both	expand and contract: add the new shape, dual write, backfill in batches, switch reads, remove the old shape