Database Design and Optimization: Normalization, Indexing, and Performance in Practice

There is a myth that slow databases are fixed by better hardware. In reality, most production performance problems are design problems: a table that mixes three concerns, a missing index on a foreign key, a query forced to scan millions of rows because the schema made the fast path impossible. This post covers the two disciplines that prevent those problems — normalization and indexing — and, just as importantly, when to deliberately break the rules.

The audience: developers and data engineers who have built schemas before and now want them to be correct and fast.

Normalization: One Fact, One Place

Normalization is often taught as a wall of academic definitions, but the core idea fits in a sentence: every fact should be stored exactly once. When a fact lives in multiple rows, updates eventually miss one of them, and your database starts disagreeing with itself.

Consider this denormalized orders table, the kind that grows organically in every startup:

Three problems hide in this innocent table:

• Update anomaly. Asha moves to Mumbai. You must update every one of her order rows; miss one and she now lives in two cities.
• Insert anomaly. You can’t record a new customer until they order something — there’s nowhere to put them.
• Delete anomaly. Cancel order 1003 and Ben vanishes from your database entirely.

The normal forms, in plain language

First normal form (1NF): every column holds one atomic value — no comma-separated lists, no repeating column groups like phone1, phone2, phone3. If you’re tempted by tags = 'red,sale,new', you need a child table.

Second normal form (2NF): every non-key column depends on the whole primary key. This only bites with composite keys: in an order_items(order_id, product_id, qty, product_name) table, product_name depends only on product_id, not the full key — so it belongs in the products table.

Third normal form (3NF): no column depends on another non-key column. In the table above, customer_city depends on customer_name, which depends on order_id — a transitive chain. Cities belong with customers, not orders.

Normalizing our example produces customers, products, orders, and order_items — four tables where each fact lives once. For transactional (OLTP) systems, 3NF is the practical target. Higher forms (BCNF, 4NF, 5NF) exist and occasionally matter, but if you reach 3NF thoughtfully you’ve eliminated the anomalies that cause real damage.

One subtle case worth knowing: the price problem

Should order_items store the price? Normalization instinct says no — price lives on the product. But business reality says yes: the product’s price changes over time, and the order must record what the customer actually paid. This isn’t denormalization; unit_price_at_purchase is a different fact from current_price. Distinguishing “duplicated fact” from “point-in-time snapshot” is one of the marks of an experienced designer.

Deliberate Denormalization

Once a schema is normalized, you may denormalize specific, measured hot spots — the key words being specific and measured. Legitimate patterns:

• Derived aggregates: storing order_total on the order instead of summing line items on every read, maintained in the same transaction that modifies the lines.
• Snapshot columns: the price example above; shipping address copied onto the order.
• Read-model tables: a reporting table rebuilt nightly so analysts stop hammering the transactional schema (this instinct, taken further, becomes the star schema — covered later in this series).

The rule that keeps denormalization safe: the normalized data remains the source of truth, and every duplicated value has a documented owner and refresh mechanism. Denormalization without a maintenance plan is just corruption on a delay.

Indexing: Designing the Fast Paths

An index is a sorted structure (almost always a B-tree) that lets the database find rows without scanning the table. Without an index, finding one customer among 10 million reads every row. With one, it reads a handful of pages.

Where indexes belong

Start with these, which cover the large majority of real workloads:

1. Primary keys — indexed automatically.
2. Foreign keys — not indexed automatically in most databases (PostgreSQL included), yet they’re used in nearly every join and every cascading delete. Unindexed FKs are the single most common performance bug in the wild.
3. Columns in frequent WHERE clauses — email for logins, status for queues, created_at for date filtering.
4. Columns driving ORDER BY on large result sets — an index can hand rows back pre-sorted.

Composite indexes and the leftmost rule

An index on (customer_id, order_date) serves queries filtering on customer_id alone, or on both columns — but not on order_date alone. Think of a phone book sorted by last name, then first name: useless for finding everyone named “Maria.” Order composite index columns by how queries actually filter: equality columns first, then the range or sort column.

-- Query: recent orders for one customer
SELECT * FROM orders
WHERE customer_id = 42 AND order_date >= now() - interval '30 days'
ORDER BY order_date DESC;

-- The matching index: equality column first, range column second
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date DESC);

Why not index everything?

Every index is a second copy of part of your data that must be updated on every INSERT, UPDATE, and DELETE. A table with twelve indexes does thirteen writes per insert. Indexes also consume RAM that would otherwise cache data. The discipline: index for the queries you actually run, and periodically drop indexes your database reports as unused (PostgreSQL’s pg_stat_user_indexes makes this a five-minute audit).

Reading a Query Plan: The Five-Minute Skill

You don’t need to be a DBA to diagnose most slow queries. Run EXPLAIN ANALYZE and look for three things:

• Seq Scan on a large table where you expected an index — usually a missing index, or a query written so the index can’t be used (e.g., wrapping the column in a function: WHERE lower(email) = ... needs an index on lower(email)).
• Row estimate vs. actual wildly off — stale statistics; run ANALYZE.
• Nested Loop over huge row counts — often a missing FK index on the inner side.

A concrete war story pattern: a dashboard query took 40 seconds; EXPLAIN showed a sequential scan on a 30-million-row events table filtered by account_id. One index later, it ran in 20 milliseconds. Nothing about the hardware changed. This is the norm, not the exception — design-level fixes routinely beat hardware by three orders of magnitude.

A Practical Design-Review Checklist

• Is every table in 3NF, with any exception documented as a deliberate snapshot or aggregate?
• Does every foreign key column have an index?
• For the five most frequent queries, does a matching index exist — with composite columns in the right order?
• Are there indexes with zero scans in the stats views? Drop them.
• Are large text/JSON blobs separated from hot, frequently-scanned tables?
• Have you load-tested with realistic data volume? A schema that flies with 10,000 rows can crawl at 10 million.

Normalization gives you a database that tells the truth; indexing gives you one that answers quickly. Do them in that order — a fast wrong answer helps nobody. In the next post, we move from transactional design to analytical modeling, where the optimization goals flip almost completely.