7.2.4 — Keys, Migrations, Pools and ORMs

Three decisions get made in the first week of a project, are never revisited, and decide how much pain the next three years hold: what the primary key is, how a row gets deleted, and how the schema changes while the service is running.

None of them is hard. All of them are close to irreversible.

1. Sequential integer or UUID

id bigint GENERATED ALWAYS AS IDENTITY   -- 1, 2, 3, …
id uuid   DEFAULT gen_random_uuid()      -- 8f14e45f-ce…

The difference is not aesthetic. It changes how your table is physically written.

A sequential key appends. New rows go at the end of the index, which means one index page stays hot in memory and every insert touches it. Cheap, dense, cache-friendly.

A random UUID inserts everywhere. Each new row lands at a random position in the index, so each insert dirties a different page, and the buffer pool (Chapter 7.3.1) fills with index pages instead of data. As the table grows past memory, insert throughput falls sharply. In MySQL's InnoDB this is worse than in PostgreSQL, because InnoDB stores the whole table inside the primary key index — a random primary key means the entire table is written in random order, and page splits fragment it badly.

	Sequential bigint	Random UUID v4	UUID v7
Size	8 bytes	16 bytes	16 bytes
Insert locality	Appends	Scattered	Appends
Guessable	Yes	No	Partly (time)
Generated by	Database	Client or database	Client or database
Leaks row count	Yes	No	No

The two real reasons to want a UUID:

• The client can generate the identity before the row exists. That makes an insert idempotent — retry it and the unique constraint catches the duplicate — and it lets you build a whole object graph in memory before a single round trip. This is a genuine architectural benefit, not a preference.
• Sequential ids leak information and invite enumeration. /orders/1042 tells a competitor how many orders you have, and tells an attacker to try /orders/1041. Note the second one is only a symptom — the real defect is an endpoint that returns another customer's order at all. Authorise properly; unguessable ids are defence in depth, not the defence.

UUID v7 removes the main cost. It puts a millisecond timestamp in the high bits and randomness in the low bits, so values sort roughly by creation time and inserts append like a sequential key while remaining unguessable in practice. If you want UUIDs, use v7, not v4. It is standardised in RFC 9562 (2024) and available in PostgreSQL 18's uuidv7(), with well-tested library implementations everywhere else.

A common and reasonable middle path: a bigint primary key for internal joins and foreign keys, plus a separate unguessable public_id exposed in URLs and APIs. You pay one extra unique index and get compact joins with no enumeration surface.

Never store a UUID as varchar(36). It is 36 bytes instead of 16, comparisons are string comparisons, and every index doubles. Use the native uuid type, or BINARY(16) in MySQL.

2. Deleting a row without deleting it

DELETE loses history, breaks foreign keys pointing at the row, and cannot be undone by a support agent at 4pm on a Friday. So most systems mark rows instead.

ALTER TABLE customers ADD COLUMN deleted_at timestamptz;

deleted_at beats is_deleted because it records when for free, and IS NULL is a perfectly good index condition.

Then four consequences arrive, and every one of them has bitten a real system.

Every query must filter. One forgotten WHERE deleted_at IS NULL shows a deleted customer in a list. There are three defences: a database view that every read goes through, an ORM-level default scope, or row-level security. Pick one and apply it everywhere — a convention people remember is not a defence.

Unique constraints break. A deleted ana@x.com still occupies the unique index, so she cannot re-register. The fix is a partial unique index over live rows only:

CREATE UNIQUE INDEX customers_email_live
  ON customers (email) WHERE deleted_at IS NULL;    -- (1)

(1) Only rows with a null deleted_at participate, so any number of deleted rows may share an email while at most one live row holds it. MySQL has no partial indexes; the usual workaround is a generated column that is the email when live and the row id when deleted.

Foreign keys stop protecting you. A deleted customer's id still exists, so the database will happily accept a new order for them. Application code has to check.

Deleted rows never stop costing. They sit in every index and every scan. After a few years they can be most of the table. Archive to a separate table on a schedule.

And soft delete does not satisfy a deletion request. Under GDPR-style rules "delete my data" means the data is gone, not flagged. The usual answer is anonymisation: keep the row for referential integrity and financial records, overwrite the personal fields. Chapter 8.7 covers the obligations.

A restatement worth remembering: a soft-deleted row is not deleted, it is in a different state. If a record genuinely has states — active, suspended, closed — then say so with a status column, and stop pretending it is about deletion.

3. Migrations that do not take the site down

A schema change runs against a database that is serving traffic, while both the old and the new version of your code are running at once — because a deploy is never instantaneous.

The rule that makes this tractable: every migration must be safe for the code that is already running. That means additive first, destructive later, and never both in one release.

The expand-and-contract pattern, for renaming name to full_name:

1. Expand. Add full_name, nullable. Deploy. Nothing reads it yet.
2. Backfill. Copy name into full_name in batches. Deploy code that writes both and reads full_name with a fallback.
3. Migrate reads. Deploy code that reads only full_name.
4. Contract. Drop name, after enough time that a rollback will not need it.

Four deploys instead of one. That is the actual cost of not having downtime, and it is worth stating plainly rather than discovering mid-incident.

The operations that lock, and their safe forms:

Operation	Danger	Safe form
Add nullable column	Safe	—
Add column with default	Rewrote the table pre-PG11	Modern PG is instant; older engines: add, backfill, set default
Add NOT NULL	Full table scan under lock	Add CHECK … NOT VALID, VALIDATE, then convert
Create index	Blocks writes	CREATE INDEX CONCURRENTLY
Change column type	Rewrites and locks	New column, backfill, swap
Drop column	Fast, but breaks running code	Deploy code first

CREATE INDEX CONCURRENTLY is the one to remember. A plain CREATE INDEX holds a lock that blocks writes for the whole build, which on a large table is minutes. The concurrent form takes two passes and does not block, at the cost of being slower and unable to run inside a transaction. If it fails it leaves an invalid index behind, which you must drop before retrying — the failure mode is worth knowing because the retry otherwise fails confusingly.

Set a lock timeout on migrations. SET lock_timeout = '3s'; means a migration that cannot get its lock fails fast instead of queueing. This matters more than it sounds: in PostgreSQL a migration waiting for a lock blocks every query that arrives behind it, so a five-second wait for one ALTER TABLE can produce a full outage. Failing fast and retrying is strictly better.

Backfill in batches with a pause. A single UPDATE over ten million rows holds one enormous transaction, bloats the table, and blocks vacuum. Loop over 10,000 rows at a time, committing each batch.

Migrations belong in version control, run in order, and are never edited after being applied. Every framework does this the same way; the discipline is that a migration already applied in production is history and gets fixed by a new migration, not by editing the old one.

4. Connection pooling

Every database connection is expensive. In PostgreSQL a connection is an operating-system process with several megabytes of memory. In MySQL it is a thread. Setting one up costs a TCP handshake, TLS, authentication and session setup — tens of milliseconds — and the server has a hard limit, typically 100 to 500.

A connection pool keeps a small set of connections open and hands them out. A request borrows one, runs its queries, and returns it.

// (1) One pool per process — never one per request.
const pool = new Pool({
  max: 10,                        // (2)
  idleTimeoutMillis: 30_000,      // (3)
  connectionTimeoutMillis: 3_000, // (4)
});

async function getOrder(id: string) {
  const { rows } = await pool.query(                 // (5)
    'SELECT id, total_minor FROM orders WHERE id = $1', [id]);
  return rows[0];
}

(1) The pool is a long-lived object created at startup. Creating one per request is the same as having no pool, and is a real bug people ship. (2) Ten connections, not a hundred — see the arithmetic below. (3) Idle connections are closed after 30 seconds so a traffic spike does not permanently hold capacity. (4) Waiting for a free connection has a timeout; without it, a slow query backs up the pool and every request hangs forever instead of failing quickly. (5) pool.query borrows and returns automatically. When you need several statements in one transaction you must pool.connect() and release explicitly in a finally — a forgotten release leaks a connection permanently, and the symptom is an application that works for two hours and then stops responding.

Sizing, which almost everyone gets wrong. More connections is not more throughput. A database with 8 cores runs 8 queries at once; the rest queue, and each queued connection still costs memory and lock contention. The commonly used starting point is connections ≈ (2 × cores) + effective_spindles, which for an 8-core server on SSDs is roughly 16 to 20 in total across every application instance.

Then divide by your instance count. Twenty application instances with max: 20 each is 400 connections, which will exhaust the server. Total demand is instances × max, and it must sit under the server limit with room for migrations and administrative access.

An external pooler solves this properly. PgBouncer sits between applications and PostgreSQL, letting a thousand client connections share thirty real ones. Its transaction pooling mode assigns a real connection only for the duration of a transaction, which gives enormous multiplexing — and forbids anything that keeps session state: prepared statements outside a transaction, SET at session level, advisory locks, LISTEN/NOTIFY. Serverless functions make an external pooler close to mandatory, because each concurrent invocation is its own process with its own pool and the count is unbounded.

5. ORMs, honestly

An ORM (object-relational mapper) turns tables into objects. An ODM does the same for document databases. The debate around them is unproductive because both sides are describing real experiences of different jobs.

What an ORM genuinely gives you: parameterised queries by default, so injection (Chapter 8.5) is far harder to write by accident; typed models that fail at compile time when the schema changes; migrations tied to model definitions; and the removal of a large amount of boring mapping code.

What it costs, specifically:

The N+1 problem is the big one.

const orders = await repo.find({ take: 100 });     // 1 query
for (const o of orders) {
  console.log(o.customer.name);                    // 100 more queries  ← (1)
}

(1) Each lazy-loaded relation is a separate round trip. The page takes 101 network latencies instead of one, and every individual query is fast, so nothing appears in a slow-query log. The fix is to say what you need up front — relations: ['customer'], include, joinedload, .includes — the keyword differs per ORM and the idea is identical. Log query counts per request in development; the number jumping from 3 to 104 is visible immediately and invisible otherwise.

Generated SQL for complex reads is often poor. Deep eager loading produces joins that multiply rows (Chapter 7.2.1 section 4) or a cascade of separate queries. Window functions, LATERAL and recursive CTEs are usually unreachable through the object interface.

Types are not what you configured. An ORM that maps a decimal column to a JavaScript number has already lost money precision. One that sends a string where the column is bigint produces the implicit-cast index loss from Chapter 7.2.3.

It hides transaction boundaries. Lazy loading outside a transaction, or a "unit of work" that flushes at a moment you did not choose, produces behaviour that is very hard to reason about under concurrency.

The stance that holds up: use the ORM for the 80% of operations that are single-row reads, inserts and updates, where it removes real drudgery and prevents real bugs. Drop to raw SQL for reports, aggregations and anything you had to EXPLAIN — every serious ORM has a first-class escape hatch, and using it is not a defeat. What you must not do is use an ORM to avoid learning SQL, because the moment something is slow you will be debugging generated SQL you cannot read.

Query builders sit in between — Knex, jOOQ, Drizzle, SQLAlchemy Core — giving composable, typed, parameterised SQL without pretending rows are objects. For services that mostly query, that is often the better fit than a full ORM.

Always log the generated SQL in development. Everything in this section becomes obvious the first time you see what your two lines of code actually sent.

6. Small schema decisions that pay for themselves

Store money as integer minor units. total_minor bigint holding pence. Floating point cannot represent 0.10 exactly (Chapter 1.4), and rounding differences in a ledger are unfixable after the fact. NUMERIC/DECIMAL is the other correct choice — exact, slower, and awkward across language boundaries.

Store timestamps as timestamptz in UTC. PostgreSQL's timestamptz stores a moment in time; timestamp stores a wall-clock reading with no timezone, which is a different and usually wrong thing. Convert to the user's zone at the edge. Chapter 9.7.27 covers the cases where local time genuinely must be stored instead.

Use text over varchar(n) in PostgreSQL — identical performance, and changing a length limit is a migration. Enforce real limits with a CHECK, which states the rule where you can find it.

Prefer an enumerated status text with a CHECK constraint over a database ENUM type. Adding a value to a PostgreSQL enum is easy; removing or reordering one is not. A CHECK (status IN (…)) is a one-line migration in both directions. A lookup table is the third option and is right when the set is data rather than code.

Add created_at and updated_at to every table. They cost 16 bytes and answer the first question of every investigation.

Name things consistently. snake_case, plural table names or singular but not both, foreign keys as <table>_id. This is not taste — it is what makes a schema readable by someone who joins in year three.

What the interviewer will push on

"UUID or auto-increment primary key?" The real answer is about write locality: a random UUID scatters index inserts and destroys cache behaviour as the table outgrows memory, which is worst in InnoDB where the table lives inside the primary key. Then give the resolution — UUID v7 for time-ordered unguessable keys, or an internal bigint with a separate public identifier. The weak answer is "UUIDs are better for distributed systems" with no mention of cost.

"How do you rename a column with zero downtime?" Expand and contract: add the new column, backfill in batches, write both, move reads, then drop. Four deploys. The tell is knowing why — old and new code run at the same time during a deploy, so every intermediate state must be valid for both.

"What is wrong with soft deletes?" Nothing, if you handle the four consequences: every query must filter, unique constraints need a partial index over live rows, foreign keys stop protecting you, and the rows never stop costing. Then add that soft delete does not satisfy a legal deletion request — anonymisation does.

"How large should the connection pool be?" Small. Around 2 × cores in total, divided across instances, because a database with 8 cores cannot run 200 queries at once and each extra connection costs memory and lock contention. Then name the multiplication trap: instances × max is the real number, which is why serverless needs an external pooler like PgBouncer.

"Do you use an ORM?" Yes for simple reads and writes, raw SQL for reports and anything you had to EXPLAIN. Name N+1 as the specific cost, describe how you detect it (query count per request in development, not the slow-query log), and mention decimal-to-float mapping as the correctness one people forget.

"How do you add an index to a large busy table?" CREATE INDEX CONCURRENTLY, outside a transaction, with a lock timeout set. Then volunteer the failure mode: if it fails it leaves an invalid index that must be dropped before retrying.

One thing to volunteer: mention setting lock_timeout on migrations. A migration waiting for a lock blocks every query queued behind it, so a slow ALTER TABLE becomes a full outage rather than a slow deploy. Failing fast and retrying is the correct behaviour, and very few people mention it.

Recall

• Sequential keys append; random UUID v4 keys scatter index writes and fall off a cliff once the table exceeds memory — worst in InnoDB, where the table lives inside the primary key. Use UUID v7 if you want unguessable and ordered, or a bigint key plus a separate public id. Never store a UUID as varchar(36).
• Soft delete with deleted_at, then handle all four consequences: filter every read through a view or default scope, add a partial unique index over live rows, accept that foreign keys no longer protect you, and archive. It does not satisfy a legal deletion request — anonymisation does.
• Expand and contract is the zero-downtime schema change: add, backfill in batches, write both, move reads, drop. Old and new code run simultaneously during a deploy, so every intermediate state must be valid for both.
• CREATE INDEX CONCURRENTLY does not block writes, cannot run in a transaction, and leaves an invalid index if it fails. Set lock_timeout on every migration, because a waiting lock blocks everything behind it.
• Pool size is roughly 2 × cores in total, divided across instances — instances × max is the number that matters. Give the pool a connection-acquire timeout, and release explicitly in finally when you take a client for a transaction.
• PgBouncer transaction pooling multiplexes many clients onto few connections and forbids session state — prepared statements outside a transaction, session SET, advisory locks, LISTEN.
• ORMs are good for single-row work and bad for reports. N+1 is the defining cost, it never shows in a slow-query log, and the detection is query count per request. Drop to SQL for anything you had to EXPLAIN.
• Money as integer minor units, time as timestamptz in UTC, text with a CHECK over varchar(n), status text with a CHECK over a database ENUM, and created_at/updated_at everywhere.

Self-test: Why does a v4 UUID primary key slow inserts as the table grows? · What breaks about UNIQUE under soft delete, and what fixes it? · Name the four steps of expand-and-contract and why each exists · Why is a bigger connection pool often slower? · How do you detect N+1 if every query is fast? · Why lock_timeout on a migration?

Next: 7.2.5 opens the box Chapter 7.2.3 kept pointing at — where rows=1000 in a plan actually comes from, how cost is computed from page reads, and why the planner picks the plan it picks.