The Data Layer

Most frontend developers treat the database as a box they throw queries at. That works until it doesn't, and the day it stops working is usually a bad one. This chapter gives you enough of how the box works that slow queries and weird bugs stop being mysteries.

Relational modeling, from first principles

A relational database stores data in tables of rows and columns. Each row has a primary key that identifies it. A row in one table can point at a row in another through a foreign key, which just holds the other row's primary key. Every advanced idea is built on those two things.

Normalization is the discipline of keeping each fact in exactly one place. In practice you aim for third normal form, which roughly means: one value per column, every column depends on the whole key, and no column depends on another non-key column.

Here's why it matters. A bad orders table:

orders(id, customer_id, customer_email, customer_address, total)

The customer's email and address don't belong to the order, they belong to the customer. If the customer moves house, you'd have to update every past order. The fixed version splits them apart:

customers(id, email, address)
orders(id, customer_id REFERENCES customers(id), total)

Normalization is the starting point, not the finish line. Sometimes you deliberately store a fact twice, because a join is too slow at scale, or because you need a snapshot in time. An invoice should keep the address it was sent to, even after the customer moves. That's denormalization, and it's fine as long as you do it on purpose and accept the cost of keeping the copies in sync.

Postgres internals, enough to be dangerous

Postgres uses a model called MVCC, multi-version concurrency control. When you update a row, Postgres doesn't change it in place. It writes a new version and marks the old one dead. Readers who started earlier keep seeing the old version, so reads stay consistent while a write is happening.

Three consequences you have to hold in your head:

1. Updates are physically inserts

   A heavily updated table grows faster than you'd expect, because old row versions linger.

2. Dead rows need cleanup

   A process called VACUUM reclaims the space. It usually runs on its own (autovacuum), but on busy tables you may have to tune it.

3. Long transactions block cleanup

   A six-hour analytics query holds back VACUUM for every row changed since it started. Tables bloat, queries slow down. This is a real production bug, not a theory one.

Every change is first written to the Write-Ahead Log (WAL) before it touches the data files. That's how Postgres survives a crash mid-write: on restart it replays the WAL. Replicas also read the WAL to stay in sync, so when a replica lags, it's almost always WAL replay speed.

Two physical layouts to know. A heap is the unordered pile of rows on disk. An index is a separate structure that maps values to row locations. Without an index, finding a row by value means scanning the whole heap.

Indexes that earn their keep

You can feel the difference an index makes. Without one, a lookup scans every row. With one, it's close to instant. Run this and compare the timings.

In Postgres, the default index is a B-tree: a sorted tree that's great for equality (=), ranges (<, >, BETWEEN), and prefix matches like LIKE 'foo%'. It's no help for suffix matches like LIKE '%foo'. Ninety percent of your indexes will be B-trees.

A few index types worth knowing:

• Composite (multi-column). An index on (a, b, c) helps queries that filter on a, on a and b, or on all three. It does not help a query that filters on b alone. Order matters: put the most-filtered column first.
• Partial. An index that only covers rows matching a condition, like WHERE status = 'active'. Smaller and faster for "active rows only" queries.
• Covering. An index that includes every column a query needs, so Postgres answers from the index alone without touching the table.
• GIN. For "many values per row": JSON fields, arrays, full-text search.

Query planning, the highest-leverage skill

When you send Postgres a query, it doesn't just run it. It plans it: weighs different ways to get the data, estimates the cost of each, and picks the cheapest. Your job is to read what it chose and judge whether it was right.

EXPLAIN shows the plan. EXPLAIN ANALYZE shows the plan and runs the query so you see real timings.

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

Scan types, best to worst: an index-only scan answers from the index alone, an index scan uses the index then fetches from the heap, a bitmap scan batches those fetches, and a sequential scan reads the whole table. A seq scan is fine on a small table and a red flag on a big one when you only want a few rows.

Transactions and isolation levels

A transaction is a group of statements that all succeed or all fail. The classic case is moving money: you never want a world where account A is debited but B is never credited.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
  UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT; -- or ROLLBACK if anything went wrong

The four properties go by the name ACID: atomicity (all or nothing), consistency (constraints always hold), isolation (concurrent transactions don't see each other's half-done work), and durability (once committed, it survives a crash).

Isolation has levels, because perfect isolation would mean transactions can never run at the same time. Postgres defaults to Read Committed, where each statement sees data committed when it started. Repeatable Read gives every statement in the transaction the same snapshot. Serializable makes transactions behave as if they ran one at a time, aborting one when two would have conflicted.

When two transactions each hold a lock the other wants, you get a deadlock. Postgres detects it and kills one. The fix is to always acquire locks in the same order, for example by row ID ascending, across all your code.

Connection pooling

A Postgres connection is expensive. It's a separate server process with its own memory, and an instance is usually tuned for a couple hundred connections, not ten thousand. Your app should keep a small pool and reuse it.

const pool = new Pool({
  max: 20,                    // connections this app holds
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

When many short-lived processes each want their own pool (think serverless functions), put a pooler like PgBouncer in front. It multiplexes thousands of client connections onto a few hundred real ones. Serverless Postgres providers like Neon and Supabase solve the same problem with HTTP-based pooling.

ORMs vs query builders vs raw SQL

Raw SQL gives maximum control and performance, at the cost of type safety. If you write it, always use parameter placeholders, never string concatenation, or you've opened the door to SQL injection.

Migrations and zero-downtime changes

A migration is a versioned, ordered change to your schema. You run migrations forward in production and almost never backward. The tricky part is timing: a migration that drops a column breaks the old version of your app the instant it runs, and during a deploy both versions are briefly live.

The safe approach is expand, migrate, contract:

1. Expand

   Add the new column. Old code ignores it, new code can use it. Both versions work.

2. Migrate

   Backfill the data, ship code that uses the new column, stop using the old one.

3. Contract

   In a later deploy, drop the old column.

Renaming a column? Don't. Add the new one, migrate, drop the old one across three deploys. And watch for locks: adding a NOT NULL constraint or creating an index without CONCURRENTLY can take an exclusive lock and stall a busy table for minutes. Add columns nullable, backfill in batches, then tighten the constraint as a separate step.

Redis, used sparingly

Redis keeps everything in memory, so reads and writes are sub-millisecond. It's not a replacement for your database. It's a sharp tool for specific jobs.

The data structures that earn their place: strings for basic caching, hashes for structured values, sorted sets for leaderboards and time-ordered feeds, lists for simple queues, and streams for durable queues with consumer groups.

The most common pattern is cache-aside: the app checks the cache, and on a miss reads the database and writes the result back. Rate limiting is another natural fit, since an atomic increment with an expiry is exactly a per-user request counter.