Database Optimization: Indexes, Hot/Cold Data, Denormalization and Views

A query scans all 10 million rows of orders to find user 12345's orders: 8 seconds, and users abandon the page. With the right index, data layout and query, the same request takes about 10 milliseconds. Once the data model is designed, optimization decides how the database stores and retrieves that data: an unoptimized database full-scans on every query, while an optimized one serves millions of queries per second.

Indexing

Without an index on user_id, SELECT * FROM orders WHERE user_id = 12345 reads every row. With one, the database walks a B-tree straight to that user's rows. See database indexing.

• Single-column index: for queries that filter on one field you query often, such as user_id.
• Composite (multi-column) index: for queries that filter on several fields together. An index on (user_id, status) helps queries on user_id alone or on both, but not on status alone; column order matters.
• Selectivity: an index works when it shrinks the candidate set a lot. user_id across 1 million users is great (few rows each); status with values active/inactive/pending is poor because each matches about a third of rows, so the database still reads hundreds of thousands.
• Write cost: every insert, update and delete must update every relevant index. Index only fields that matter for performance, find slow queries with EXPLAIN first, and note in interviews that over-indexing wastes memory and slows writes.

-- Composite index for "a user's orders by status, newest first"
CREATE INDEX idx_orders_user_status_created ON orders (user_id, status, created_at DESC);

EXPLAIN ANALYZE
SELECT id, total, created_at FROM orders
WHERE user_id = 12345 AND status = 'shipped'
ORDER BY created_at DESC LIMIT 20;
-- Before: Seq Scan on orders (rows=10000000) ... Execution Time: 8012 ms
-- After:  Index Scan using idx_orders_user_status_created (rows=20) ... Execution Time: 0.4 ms

Partitioning and replication

Partitioning splits data across servers to handle more writes and storage; replication copies it for availability and read scaling. They're the foundation of scaling databases; the techniques below make them work well.

Separating hot and cold data

Hot data is accessed often; cold data rarely. Keeping both on fast, expensive storage wastes money and memory. Recent orders are hot: users check status constantly in the first few days, so they stay on SSDs in the primary database. Orders older than 30 days are cold and get archived to cheaper storage (HDDs, or object storage like Amazon S3). The application checks the primary first and falls back to the archive.

A smaller primary database fits more of its working set in memory, so the hot path gets faster. Cold queries are slower, which is fine because they're rare.

-- Nightly archive job (PostgreSQL): move one batch of old orders atomically
WITH moved AS (
  DELETE FROM orders
  WHERE id IN (SELECT id FROM orders
               WHERE created_at < now() - interval '30 days'
               LIMIT 10000)
  RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;
-- Repeat until no rows move. At scale, range-partition orders by month and
-- detach old partitions (then export them to object storage) instead.

Query optimization

Use EXPLAIN to see the execution plan. A query that scans 1 million rows when it should scan 1,000 points to a missing index or a badly shaped query (functions on indexed columns, SELECT * on wide rows, N+1 query loops, OFFSET pagination deep into a table; see pagination).

• Denormalization trades storage for speed: store the customer name directly on each order instead of joining orders to customers on every read. Reads get faster; writes get harder because a name change must reach every order. Use it when reads far outnumber writes. See normalization vs denormalization.
• Materialized views precompute expensive queries: a dashboard of monthly sales by region would otherwise re-aggregate millions of rows on each load. The view stores the result, and you refresh it hourly or when source data changes. See materialized views.

CREATE MATERIALIZED VIEW monthly_sales_by_region AS
SELECT c.region, date_trunc('month', o.created_at) AS month, SUM(o.total) AS revenue
FROM orders o JOIN customers c ON c.id = o.customer_id
GROUP BY 1, 2;

CREATE UNIQUE INDEX ON monthly_sales_by_region (region, month);
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_by_region;   -- e.g. hourly via cron

Using this in an interview

Designing a social network's database? Index users by user_id and posts by (author_id, created_at); point to partitioning and replication for horizontal scale; then add use-case optimizations: denormalize author name and avatar into posts for fast timeline rendering, a materialized view for trending topics, and archive posts older than a year to cold storage. Then state each trade-off: indexes speed reads but slow writes; denormalization speeds reads but complicates updates; materialized views cost storage and refresh work; cold storage saves money but makes old data slower.