Denormalization and SQL tuning

Relational storage engines & indexing

Optimizing database throughput requires understanding how disk pages, buffer pools, and index trees interact under concurrent load.

---

1. Pragmatic denormalization

In third normal form (3NF), data redundancy is eliminated by spreading entities across normalized relational tables connected by foreign keys.

Joins are expensive, and once data is spread across federated databases or shards they become harder still. Denormalization improves read performance at the expense of some write performance, by writing redundant copies of data so reads can skip the join:

• Pre-computed aggregates: Store order_count on the users table rather than running COUNT(*) on every page render.
• Embedded snapshot data: Store customer_name and shipping_address in the orders record to avoid joins against users.
• Materialized views: Some databases (PostgreSQL, Oracle) can store a query's results and refresh them, keeping redundant data for you.

The costs: data is duplicated, constraints that keep the copies in sync complicate the design, and under heavy write load a denormalized database can perform worse than a normalized one.

Most relational databases store tables and indexes in B-trees; chapter 5 compares B-trees with the log-structured merge trees (LSM-trees) behind many write-heavy stores.

---

2. Composite indexing rules

A composite index on (tenant_id, status, created_at) is a B-tree sorted by tenant_id, then status, then created_at.

The leftmost prefix rule

Conditions on the leading columns let the database jump straight to the matching part of the index:

• Narrows the scan: WHERE tenant_id = 42
• Narrows the scan: WHERE tenant_id = 42 AND status = 'ACTIVE'
• Narrows the scan: WHERE tenant_id = 42 AND status = 'ACTIVE' AND created_at >= '2026-01-01'
• Cannot narrow the scan: WHERE status = 'ACTIVE' skips the leading column tenant_id. The database either ignores the index or reads all of it (some, such as PostgreSQL, can sometimes "skip scan" when the leading column has few distinct values).

Equality before range

Order composite index columns so that equality conditions come before range conditions:

-- CORRECT Index: (tenant_id, status, created_at)
SELECT * FROM ledger_entries 
WHERE tenant_id = 42 AND status = 'POSTED' AND created_at > '2026-11-01';

The rule (as PostgreSQL states it): equality conditions on the leading columns, plus a range condition on the first column without an equality condition, limit the part of the index that is scanned. Conditions on columns after that are still checked inside the index, which saves visits to the table, but they do not shrink the part of the index that has to be read. If the index were (tenant_id, created_at, status), the range on created_at would stop status from narrowing the scan: every entry in the date range would be read and filtered.

---

3. Covering indexes (index-only scans)

If an index contains every column a query needs, the database can answer from the index alone:

CREATE INDEX idx_user_summary ON users (id) INCLUDE (email, status);

-- Can run as an index-only scan
SELECT email, status FROM users WHERE id = 1042;

In PostgreSQL there is a catch: an index-only scan must still visit the table for any page not yet marked all-visible in the visibility map, so it only pays off on tables that are not changing quickly.

---

4. Connection pooling and connection storms

Connections are not free: PostgreSQL starts a separate backend process for every client connection. If thousands of application threads each open their own connection, the database spends its memory and CPU on connections instead of queries.

• Solution: Put a connection pooler between the application and the database, so many client connections share a small set of database connections. PgBouncer in transaction pooling mode, for example, assigns a server connection to a client only for the duration of a transaction; connection pools inside each application server likewise reuse a fixed set of connections.

---

5. SQL tuning checklist

Benchmark and profile first, then: choose data types carefully, index the columns you query, sort, group or join on (indexes take space and slow writes), avoid expensive joins (denormalize where performance demands it), and partition hot data into separate tables so it is more likely to stay in memory.