2 Postgres, Deeply

An ERP is a set of invariants — "never allocate more units than you can sell," "one open invoice per order," "every ledger entry balances" — under concurrent attack from many sessions. This chapter builds the Postgres fluency that keeps those invariants true: isolation levels and what they actually promise, the locking toolkit for the allocation engine, constraints as the last line of defense, RLS mechanics, window functions for reporting, plan-reading, and zero-downtime migration discipline. If half of those words are new, that is expected. Each one is defined from the ground up before it is used.

What you need to know first

Chapter 1 gave you tables, rows, SQL (Structured Query Language, the language you use to talk to a database), transactions, and the append-only ledger. This chapter assumes all of that and nothing more. Here is the rest of the vocabulary before we put it to work.

PostgreSQL is a program. People shorten it to "Postgres". You run it as a server: a long-lived process that starts up, sits on a machine, and waits. That process owns files on disk, and those files hold your tables. Your application never opens them. It sends SQL text across a network socket. Postgres does the work and sends rows back.

That split is called client/server. Your app is the client. Postgres is the server. Supabase is a hosted Postgres server plus authentication and an auto-generated API on top. When this chapter says "Postgres does X," it means that server process, not your app and not your ORM (Object-Relational Mapper, the library that turns rows into objects in your programming language).

Version numbers matter in this chapter, because several of the techniques below only exist in newer releases. PostgreSQL 18 is the current major release as of July 2026. It shipped on 25 September 2025. Versions 14 through 18 are all still supported. Where a feature depends on the version, the text says so.

Connections, sessions, and concurrency

A connection is an open phone line to that server. Your app dials in, Postgres dedicates a worker process to you, and the line stays open. A session is the conversation over that line, from "hello" to "goodbye". Any setting you change mid-conversation, any lock you hold, any transaction you have open belongs to your session and vanishes when the line drops.

Real web apps do not dial a fresh line per request. They use a connection pool, a fixed set of already-open lines that requests borrow and return. That matters later, because a pooled line is a used line: the session you get was somebody else's a millisecond ago, and whatever they left on it is now yours. Hence our preference for transaction-scoped locks over session-scoped ones.

"At the same time" is literal. A production ERP (Enterprise Resource Planning, the system of record for inventory, orders, and money) runs with dozens or hundreds of open connections. Two sales reps in two cities click "Confirm Order" within the same 50 milliseconds. Postgres handles each in its own process, genuinely in parallel on separate CPU cores, interleaving their reads and writes in an order nobody chose or can predict.

Concurrency is the word for that: more than one piece of work in flight at once, with no guaranteed ordering between them. Almost every bug in this chapter is code that is correct alone and catastrophic when a second copy runs beside it.

Locks, indexes, and the query planner

A lock is the only key to a room. Whoever holds it works. Everyone else waits at the door. Postgres takes some locks for you automatically (UPDATE a row and that row is locked until your transaction ends) and lets you ask for others explicitly. A lock makes concurrent code safe by removing the concurrency, deliberately, in a small area. The cost is waiting. Hold a key too long and everyone piles up behind you. Have two people each holding the key the other needs and neither can ever proceed (that stalemate is a deadlock, covered later).

An index is the alphabetical index at the back of a book. Without one, finding every mention of "gabardine" means reading all 900 pages. The database's version of that is a sequential scan, and it is as slow as it sounds on a big table. With an index you look the word up, see "page 412," and jump straight there. A database index is a separate, always-sorted structure mapping column values to row locations. Reads get much faster. Writes get slightly slower, since every insert must update the index too. Indexes are neither automatic nor free, and designing them is a whole section of this chapter.

A query planner decides how. SQL is declarative: you describe the rows you want, and something else invents the steps to fetch them. That something is the query planner. It turns your query into an execution plan: use this index or scan the whole table, read this table first, join them this way. It chooses by estimating cost, using statistics: approximate counts of how many rows match a condition, gathered by a maintenance command called ANALYZE.

This is why the same query over the same data can be fast on Monday and slow on Friday. The estimate moved even though your code stood still. Reading the plan it picked is a core skill of this chapter.

Migrations and the ACID promises

A migration is a versioned script that changes the shape of your tables. Adding a column, adding a rule, creating an index: each is a small numbered file, checked into git and run in order everywhere, so your laptop, staging, and production have identical table shapes. Writing them is easy. Running them against a table thousands of users are reading and writing right now, without anybody noticing, is the hard part, and it closes this chapter.

One last acronym: ACID, the four promises a serious database makes about transactions:

• Atomicity: all-or-nothing, never half-applied.
• Consistency: your declared rules still hold at the end.
• Isolation: concurrent transactions do not corrupt each other.
• Durability: once it says "committed," a power cut cannot take it back.

Three of those you get for free. This chapter is about the third, Isolation, because it is the only letter with a dial on it, and the factory setting sits well short of the safe end.

The forcing function: the allocation engine

Everything in this chapter is motivated by one workload. An invariant is a statement about your data that must be true at every moment, no exceptions. "Allocated units never exceed units on hand" is an invariant.

Two industry words you will need throughout. A SKU (Stock Keeping Unit) is one exact sellable thing: this style, in this color, in this size. TEE-CLASSIC / Black / M is one SKU. The same tee in Large is a different SKU with its own stock count. A wholesale order is a retailer buying from you in bulk for resale, so quantities run in dozens and hundreds rather than ones.

Your chapter-1 ledger gives you on-hand quantity as SUM(qty) over an append-only table: you never store the balance, you add up all the movements. Available-to-sell (ATS) is on-hand plus inbound purchase orders minus open allocations. When a sales rep confirms a wholesale order for 30 units of TEE-CLASSIC / Black / M, the system must check ATS and, if sufficient, record an allocation. It must stay correct when two reps confirm orders against the same 40 remaining units in the same 50 milliseconds.

TOCTOU: the gap between check and use

That "check, then write" shape — read some state, decide, write a decision that was only valid given what you read — is the single hardest thing relational databases do, and the default settings do not handle it for you.

It has a name in the security and systems literature: TOCTOU, short for time-of-check to time-of-use. The gap between the moment you checked a fact and the moment you acted on it is a window, and in that window someone else can change the fact.

ORMs actively hide this. The code reads like a simple if-statement and gives no hint that a second process is standing in the gap. So the place to start is what the defaults really promise.

READ COMMITTED: what the default actually guarantees

An isolation level is the dial mentioned above. It is a setting on a transaction that decides how much your transaction is allowed to notice about other transactions running at the same time. Turn it up and you get more safety and more waiting or more retrying. Turn it down and you get more speed and more ways to be wrong. Postgres offers three usable settings, from weakest to strongest: READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. It accepts a fourth name, READ UNCOMMITTED, but treats it exactly like READ COMMITTED, so there are really three.

Postgres's default isolation level is READ COMMITTED. Its contract: each statement sees a snapshot of data committed before that statement began, and you never see uncommitted data. A snapshot is a frozen photograph of the database at one instant. Postgres keeps multiple versions of every row behind the scenes (a technique called MVCC, Multi-Version Concurrency Control), so it can hand your statement a consistent picture of the past while other people are busy changing the present. That is why readers never block writers in Postgres, and writers never block readers: they are looking at different versions of the same rows.

And that is the whole contract. It rules out one bad thing and permits three others. The one it rules out is a dirty read: seeing another transaction's changes before that transaction committed, changes that might still be rolled back and never have existed. Postgres never allows a dirty read at any isolation level.

What READ COMMITTED still permits:

• two statements in the same transaction can see different data, because each got its own fresh snapshot (a non-repeatable read: you run the same SELECT twice and a row's value changed underneath you);
• a repeated query can return rows that were not there before (a phantom read: the set of matching rows grew, not just a value);
• and nothing stops two transactions from making decisions based on the same stale read, which is the one that will hurt you.

The bug in application code

Here is the bug, in the exact form it appears in application code. Assume a denormalized sku_buckets table caching per-SKU totals (rebuilt from the ledger, as in chapter 1). "Denormalized" means we are deliberately storing a pre-computed answer (the running totals) instead of adding up the ledger every time, trading correctness risk for speed:

// THE BUG: read-check-write across statements.
// Two sessions, same SKU, ATS = 40.
const { rows: [bucket] } = await client.query(
  `select qty_on_hand, qty_allocated
     from sku_buckets where sku_id = $1`,
  [skuId]
);
// Session A reads {40, 0}. Session B reads {40, 0}.
// Both snapshots are "correct".

const ats = bucket.qty_on_hand - bucket.qty_allocated;  // 40 in both
if (ats >= requestedQty) {                     // 40 >= 30 passes twice
  await client.query(
    `update sku_buckets set qty_allocated = $2 where sku_id = $1`,
    [skuId, bucket.qty_allocated + requestedQty]  // both write 30
  );
}

Walk through it as a story on a timeline. Two sales reps confirm 30 units each, at almost the same instant, against a SKU with 40 on hand and 0 already allocated:

• Moment 1. Session A runs the SELECT and gets back {qty_on_hand: 40, qty_allocated: 0}.
• Moment 2, roughly a millisecond later and before A has written anything: Session B runs the same SELECT and gets back the same {40, 0}. Neither read is wrong: at the instant each one ran, that genuinely was the state of the row.
• Moment 3. Session A computes ats = 40 - 0 = 40, checks 40 >= 30, and decides yes.
• Moment 4. Session B, holding its own copy of the same numbers, computes the same 40, checks the same condition, and also decides yes.
• Moment 5. Session A sends update ... set qty_allocated = 30. Look closely at what it sends: the literal number 30, computed back in JavaScript from a number A read at Moment 1.
• Moment 6. Session B sends the identical statement, also setting qty_allocated = 30, computed from its own stale 0.

Both sessions commit. You have promised 60 units against 40 on hand, and the counter says 30: a lost update (B overwrote A's write, so A's 30 units vanished from the counter as if they had never been allocated) stacked on an oversell (both checks passed on the same stale snapshot). No error was raised. Nothing was violated as far as Postgres is concerned, because you never told Postgres about the invariant. Two customers have been promised the same shirts, and you will find out at the warehouse.

Why narrowing the window fails

Checking again right before you write only makes the window smaller. There is still a gap between that last check and the write itself, and a gap is all a concurrent session needs. Narrowing a TOCTOU window never closes it. You have to remove it.

READ COMMITTED does give you one important behavior to understand: when an UPDATE finds its target row concurrently modified, it waits for the other transaction, then re-evaluates its WHERE clause against the new committed row version before applying. The Postgres docs put it plainly: "The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition." That is a genuine exception to the snapshot rule, and it exists precisely so that single-statement updates can be trusted. It makes single-statement compare-and-set correct even at the default level:

-- CORRECT under READ COMMITTED: check and write are one
-- statement, and the guard references only the row being updated.
update sku_buckets
   set qty_allocated = qty_allocated + $2
 where sku_id = $1
   and qty_on_hand - qty_allocated >= $2   -- re-checked against
                                           -- the LATEST row version
returning qty_allocated;
-- rowcount 0  =>  insufficient ATS, reject the order line

The guarded single-statement UPDATE

Read this statement carefully, because two small changes carry all the weight. First, the set clause says qty_allocated = qty_allocated + $2 ("add 30 to whatever is currently there") instead of "set it to the 30 I computed in JavaScript." Postgres does the arithmetic, using the value it sees at write time, so nothing stale from your application can overwrite a fresh value.

Second, the condition that decides whether the allocation is allowed now lives in the where clause, inside the same statement as the write. There is no gap between them for anyone to slip into. The returning clause hands back the new value so your app can log it, and the row count tells you what happened: one row updated means the allocation succeeded, zero rows updated means the guard was false and there was not enough stock.

Now replay the two-rep story against this version. Session A and Session B both fire the statement at nearly the same instant. A gets there first, locks the row, checks 40 - 0 >= 30 (true), writes qty_allocated = 30, and commits. B arrives while A still holds the row lock, so B waits at the door.

When A commits and releases, B re-reads the row as A left it and re-runs its own where clause against those fresh numbers: 40 − 30 ≥ 30 is false. Zero rows updated. B's application sees rowcount 0 and rejects that order line as out of stock.

Oversell prevented, no error, no retry logic, no extra round trip. When your invariant can be expressed as a predicate over the single row you're writing, this is the cheapest correct tool in the box. Pair it with CHECK (qty_allocated <= qty_on_hand) (a rule stored in the table itself, covered in detail later) so a future refactor that breaks the guard fails loudly instead of overselling silently.

REPEATABLE READ, and the anomaly it still allows

REPEATABLE READ is the next notch up the dial. It takes one snapshot at the transaction's first real query and uses that same frozen photograph for every statement in the transaction. Your transaction therefore sees a completely stable world from beginning to end: no non-repeatable reads, no phantoms. Postgres's REPEATABLE READ is stricter here than the SQL standard demands: the standard permits phantom reads at this level and Postgres does not. If your transaction then tries to modify a row that another transaction modified after your snapshot was taken, Postgres refuses to guess. It aborts yours with SQLSTATE 40001 and you must retry.

Two terms there. SQLSTATE is the standardized five-character error code every SQL database returns alongside its error message. 40001 is the one named serialization_failure, and its message reads "could not serialize access due to concurrent update." Read that message as "two transactions collided and I cannot safely apply both; run yours again against fresh data."

Your SQL was fine and your data was fine. The Postgres docs prescribe the same response: "When an application receives this error message, it should abort the current transaction and retry the whole transaction from the beginning."

A retry loop is the code that does exactly that: catch the 40001, roll back, wait a few milliseconds, and run the whole transaction again from the top. Because REPEATABLE READ raises this error, the classic lost update is dead: the two sessions above can no longer both win. One of them gets 40001 and, on retry, reads the updated numbers and correctly rejects.

Write skew survives the upgrade

But it does not kill write skew: two transactions read an overlapping set of rows, each writes different rows, and the combination violates an invariant neither could see the other breaking. That last part is the key. Postgres's collision detection at this level asks one narrow question: "did anyone else modify a row that I am modifying?" If the two transactions touch entirely different rows, the answer is no, and both commit happily. In our domain, allocation done properly against the ledger is exactly this shape, because the check aggregates many rows and the write inserts a new row:

-- Both sessions, REPEATABLE READ, same SKU, 40 on hand, 0 allocated:
begin isolation level repeatable read;

select coalesce(sum(l.qty), 0)                  -- 40 (ledger rows)
     - coalesce((select sum(a.qty) from allocations a
                  where a.sku_id = $1
                    and a.status = 'open'), 0)  -- 0
  as ats;                                       -- 40 in BOTH snapshots

-- both pass the app-side check, both insert DIFFERENT rows:
insert into allocations
  (tenant_id, sku_id, order_line_id, qty, status)
values ($tenant, $1, $line, 30, 'open');

commit;  -- BOTH commit: no row was updated by two transactions

One new column to explain before the story: tenant_id. A single ERP installation usually serves several separate companies, and each company is called a tenant. Every row is stamped with the tenant it belongs to so that one company can never see another's data. Chapter 5 covers that properly. For now, read tenant_id as "which company owns this row."

Replaying the story at REPEATABLE READ

Now the story again, with the stronger isolation level switched on:

• Moment 1. Session A opens a transaction at REPEATABLE READ and runs the SELECT. That query adds up every ledger row for the SKU (total: 40 received) and subtracts the sum of every open allocation (total: 0), producing ATS = 40. coalesce is just a null-guard: if there are no rows at all, sum returns null, and coalesce(..., 0) turns that into 0 so the arithmetic works.
• Moment 2. Session B opens its own transaction and runs the same SELECT. Nothing has been written yet, so B also computes 40.
• Moment 3. A's application logic checks 40 >= 30, passes, and inserts a brand-new row into allocations for 30 units.
• Moment 4. B does the same and inserts its own brand-new row, for a different order line, for another 30 units.
• Moment 5. Both commit.

Ask Postgres what rule was broken and it has nothing to point at. A never touched B's row and B never touched A's row. They each only added. No shared row was written, so REPEATABLE READ has nothing to object to, yet 60 units are now allocated against 40. This is a serialization anomaly: there is no serial order of these two transactions that produces this outcome. That phrase is the formal definition of "wrong" here.

If A had run completely, then B, B would have seen ATS = 10 and rejected. If B had run completely, then A, A would have rejected. No one-after-the-other ordering yields "both succeed," so the result the database just produced could not have come from any sequential execution. Only SERIALIZABLE detects that automatically.

Read that table as a ladder. Every row you move down, you buy protection against one more failure mode, and you pay for it in waiting or retrying. Note the bold cells: they are the two anomalies that will actually bite an ERP, and the default level allows both.

SERIALIZABLE with a retry loop vs. explicit row locks

You have two correct tools for multi-row invariants, and they trade off differently. One is optimistic: assume collisions are rare, let everyone run, detect the trouble afterward and redo the loser. The other is pessimistic: assume collisions will happen, take a key up front, make everyone else wait. Neither is universally better. The right choice depends on how often two people really do reach for the same SKU at the same time.

SERIALIZABLE (SSI): optimistic, requires retries

Postgres implements SERIALIZABLE as SSI, Serializable Snapshot Isolation. It runs everybody at full speed without blocking, quietly keeps notes on which transaction read what and wrote what, and if those notes ever prove that the outcome could not have come from running the transactions one after another, it kills one of them.

Concretely, it tracks read/write dependencies with predicate locks, bookkeeping entries recording "this transaction depends on the set of rows matching this condition," visible as SIReadLock in the pg_locks system view. Predicate locks block nobody. They exist purely as evidence for the dependency check.

When the dependency graph proves no serial order could produce the same result, Postgres aborts one transaction with 40001. The write-skew example above: one session commits, the other gets 40001 and, on retry, correctly sees ATS = 10 and rejects the line.

Correctness is automatic if and only if every transaction touching those tables also runs SERIALIZABLE, and your application actually retries. Both halves are load-bearing. A single reader running at READ COMMITTED is invisible to the dependency tracker and can smuggle a stale value out. And a 40001 means the protocol is working as designed, so logging it and moving on throws away the very signal you asked for. Swallowing it means silently dropping a customer's order. Build the retry loop once, at the bottom of your data layer, so no feature developer ever has to remember it:

import { Pool, PoolClient } from 'pg';

// 40001 = serialization_failure, 40P01 = deadlock_detected
const RETRYABLE = new Set(['40001', '40P01']);

export async function serializableTx<T>(
  pool: Pool,
  fn: (client: PoolClient) => Promise<T>,
  maxAttempts = 5,
): Promise<T> {
  for (let attempt = 1; ; attempt++) {
    const client = await pool.connect();
    try {
      await client.query('begin isolation level serializable');
      const result = await fn(client);
      await client.query('commit');
      return result;
    } catch (err: any) {
      await client.query('rollback').catch(() => {});
      if (RETRYABLE.has(err?.code) && attempt < maxAttempts) {
        // backoff with jitter: 10-20ms, 20-40ms, 40-80ms, ...
        const base = 10 * 2 ** (attempt - 1);
        await new Promise(r =>
          setTimeout(r, base + Math.random() * base));
        continue;
      }
      throw err; // non-retryable, or retry budget exhausted
    } finally {
      client.release();
    }
  }
}

Reading the retry wrapper

Line by line. serializableTx takes a connection pool and a function fn containing whatever work you want done, and it promises to run that work inside a serializable transaction, retrying if the database says so. The outer for loop has no exit condition of its own. Every path out is either a return (success) or a throw (give up), which is what makes it a retry loop rather than a fixed loop.

Inside one attempt: borrow a connection from the pool, send begin isolation level serializable to open the transaction at the strongest setting, run your work, then commit. If all of that succeeds, return the result and we are done on the first try, which is the common case.

If anything throws, we land in the catch. First we rollback to abandon the failed transaction, with .catch(() => {}) tacked on because if the connection is already broken, the rollback itself will fail and we do not care.

Then we check whether this error is one of the two the database expects us to retry. 40001 is the serialization failure we met above. 40P01 is deadlock_detected, which we will define in a moment. Anything else — a typo in your SQL, a constraint violation, a network drop — is a real bug or a real business rejection, and the final throw err sends it up to the caller untouched.

Backoff, jitter, and the connection

If it is retryable and we have attempts left, we wait before trying again. The wait grows exponentially and carries a random component, called jitter: base doubles each attempt (10ms, 20ms, 40ms, 80ms), and we sleep a random amount between base and 2 × base. Doubling keeps a busy database from being hammered by immediate retries.

The randomness is the part people forget, and it matters. Without it, ten sessions that collided at the same instant would all sleep exactly 10ms and all wake up at the same instant to collide again. With maxAttempts = 5 you sleep at most four times, so the worst case is roughly 150 to 300 ms of waiting before the error is handed back to the caller.

Finally, client.release() sits in a finally block so the connection goes back to the pool on every path, success or failure. Forget that line and your app runs out of connections under load.

Keeping the abort rate down

Operational notes from the Postgres docs that matter in practice:

• declare read-only transactions READ ONLY (they can release predicate locks early and rarely abort);
• keep serializable transactions short and avoid leaving them idle in transaction. An open transaction sitting around waiting on a slow HTTP call keeps its bookkeeping alive and makes everyone else more likely to abort.
• The docs also state flatly that "a sequential scan will always necessitate a relation-level predicate lock" (meaning "I depend on this entire table" rather than "I depend on these three rows"), which inflates false-positive aborts. Well-indexed access paths therefore reduce your retry rate, so a well-chosen index buys you speed and fewer aborts at once.
• One documented caveat: two serializable transactions racing to insert the same key can surface as a unique-violation (23505) rather than 40001 even though a retry would succeed. The docs' own fix is to make sure every serializable transaction that inserts a potentially conflicting key checks for it first. For insert-if-absent flows, decide deliberately whether 23505 belongs in your retryable set.

SELECT ... FOR UPDATE: pessimistic, no retries

The alternative is to designate a row as the mutex for the invariant and lock it first. "Mutex" is short for mutual exclusion, the single key to the room. A row lock is exactly that key, scoped to one row: while you hold it, no other transaction can modify or lock that row, and any that tries waits until you commit or roll back. SELECT ... FOR UPDATE is how you ask for one without changing anything: it reads the rows and locks them in the same breath, as if you had updated them.

For allocation, the natural choice of mutex is the SKU row: every code path that creates, releases, or transfers allocations for a SKU must lock that SKU row before reading the aggregates. We never change the SKU row itself. It is purely an agreed-upon token that every allocator reaches for first, which converts write skew back into an orderly queue:

begin; -- READ COMMITTED is fine: the row lock serializes us
-- Lock ALL SKUs in the order, in a stable order. Unordered
-- multi-row FOR UPDATE is the classic deadlock generator
-- (A locks 1 then 2; B locks 2 then 1).
select id from skus
 where id = any($1::bigint[])
 order by id
   for update;

-- Aggregates are now stable with respect to any other
-- allocator following the same protocol:
select s.id,
       coalesce(sum(l.qty), 0)
         - coalesce((select sum(a.qty) from allocations a
                      where a.sku_id = s.id
                        and a.status = 'open'), 0) as ats
  from skus s
  left join inventory_ledger l on l.sku_id = s.id
 where s.id = any($1::bigint[])
 group by s.id;

-- app verifies ats >= requested per line, then:
insert into allocations
  (tenant_id, sku_id, order_line_id, qty, status)
select $tenant, x.sku_id, x.order_line_id, x.qty, 'open'
  from jsonb_to_recordset($2)
    as x(sku_id bigint, order_line_id bigint, qty int);
commit;

Three statements, in a strict order, and the order is the whole point. Statement one selects the SKU rows for every line on this order (id = any($1::bigint[]) means "id is any of the values in this array I'm passing in") and appends for update, which locks each one. Statement two computes ATS per SKU exactly as before. Statement three inserts the allocation rows, and jsonb_to_recordset unpacks a JSON array of line items your application sent as a single parameter into rows, so all the lines insert in one statement instead of one round trip each.

The two reps, queued at the door

Now the two-rep story a third time:

• Moment 1. Session A runs the locking SELECT and takes the key to SKU 77.
• Moment 2. Session B runs the same locking SELECT for the same SKU and stops dead at the door. B gets no rejection and no error. It simply waits, holding its connection open.
• Moment 3. A, undisturbed, computes ATS = 40, verifies 40 >= 30, inserts its allocation row, and commits. Committing releases the lock.
• Moment 4. B's SELECT finally returns, and only now does B run its aggregate query, which, because A has already committed, counts A's new allocation row and returns ATS = 10.
• Moment 5. B's application checks 10 >= 30, fails, and rejects the line cleanly.

Same correct outcome as SERIALIZABLE, with no error code and no retry, paid for with B's waiting time.

Lock ordering prevents deadlocks

The order by id inside the locking SELECT does real work. Suppose one order covers SKUs 1 and 2, and a second order covers the same two. If A grabs the key to 1 and then reaches for 2, while B grabs the key to 2 and then reaches for 1, each is waiting for a key the other is holding, and neither can ever let go. That is a deadlock.

Postgres notices the cycle after one second by default (the deadlock_timeout setting) and kills one transaction with SQLSTATE 40P01, the second code in our retry set.

The clean fix is to make the cycle impossible: if every session always takes locks in ascending id order, whoever gets id 1 first is guaranteed to get id 2 as well, and no cycle can form.

FOR NO KEY UPDATE and SKIP LOCKED

Two more lock modes earn their keep in this codebase. FOR NO KEY UPDATE is a slightly weaker exclusive lock. The docs say it "will not block SELECT FOR KEY SHARE commands," and FOR KEY SHARE is the lock that foreign-key checks take, so use it when you're updating non-key columns and don't want to fight FK traffic.

And FOR UPDATE SKIP LOCKED turns a table into a work queue. Normally a locked row makes you wait. SKIP LOCKED tells Postgres "pretend rows somebody else has locked do not exist and give me the next free one." Workers each grab a different unlocked row instead of queuing behind the same one, which is how you drain an allocation_jobs table with N workers:

-- Claim one pending allocation job.
-- Concurrent workers skip each other's rows.
with next_job as (
  select id from allocation_jobs
   where status = 'pending'
   order by priority desc, created_at
   limit 1
   for update skip locked
)
update allocation_jobs j
   set status = 'running', started_at = now()
  from next_job
 where j.id = next_job.id
returning j.*;

The with next_job as (...) part is a CTE, or Common Table Expression: a named temporary result set that exists only for the duration of this one statement. It works like a variable holding a small query result, which the rest of the statement can then refer to by name. The reporting section covers CTEs properly. Here it lets us pick a row and update it in a single atomic statement.

The inner query finds pending jobs, sorts them by priority and then age, takes the single best one, and locks it, skipping any row another worker already holds. The outer UPDATE flips that job to running, stamps the start time, and returning j.* hands the whole row back so the worker knows what to do.

Picture ten workers calling this at the same instant: worker 1 locks the top job; worker 2's query reaches that same row, sees it locked, silently skips it and takes the second-best; worker 3 takes the third; and so on. Ten workers, ten different jobs, zero waiting, zero duplicated work. Without skip locked, all ten would queue behind the top job and you would effectively have one worker.

Of the five rows in that table, the second is your default for order confirmation. Reach for SERIALIZABLE when the invariant is too tangled to pin to a single lockable row, and reach for SKIP LOCKED only for queues, never for anything that needs to see every row.

Advisory locks: one allocation run per tenant

Some critical sections aren't about rows at all. A nightly reallocation run releases expired allocations, re-ranks open order lines by customer tier, and re-allocates scarce inventory. It must never run twice concurrently for the same tenant, even if two crons fire (a cron is a job scheduled to start automatically at a set time) or an operator clicks the button twice.

Locking "all the rows it might touch" is the wrong shape. You would be locking most of the database, and you still would not have said the thing you actually mean, which is "only one of these processes at a time."

An advisory lock is Postgres's answer. It is a lock on a number you make up, not on any row or table. Postgres attaches no meaning to the number. It just guarantees that only one session can hold a given number at a time, and makes everyone else wait or fail. You get a named mutex, enforced by the database, for any concept you can label, which means it keeps working across every application server, every container, and every restart, unlike a lock held in one process's memory.

-- Transaction-scoped: released automatically at commit or
-- rollback. Prefer this form. pg_advisory_lock (session-scoped)
-- survives rollback and leaks through pooled connections unless
-- you unlock in a finally block.
select pg_advisory_xact_lock(
  -- tenant uuid -> bigint key
  hashtextextended('allocation_run:' || $1::text, 0)
);
-- ... entire run executes here, serialized per tenant ...
commit;  -- lock released

-- Non-blocking variant for "skip if already running":
select pg_try_advisory_xact_lock(
  hashtextextended('allocation_run:' || $1::text, 0)
) as acquired;  -- false => another run holds it; exit cleanly

Both calls do the same conversion first. 'allocation_run:' || $1::text glues a fixed prefix onto the tenant's identifier, producing a human-meaningful string like allocation_run:9f3c.... hashtextextended then squashes that string into a 64-bit integer, because the lock functions only accept numbers. Same tenant, same string, same number, every time. That is exactly the property we need. (hashtext and hashtextextended are internal helper functions. They work and are widely used for this, but they are not part of the documented, version-stable API, so pin the exact values you depend on if you ever need them to survive a major upgrade byte-for-byte.)

Blocking vs. try: choosing the variant

The difference is what happens when the key is already taken. pg_advisory_xact_lock blocks: the second run stands at the door until the first finishes, then does its own full pass. pg_try_advisory_xact_lock never waits: it returns true if it got the lock and false immediately if someone else holds it, so your job can log "already running" and exit with a zero status.

For a nightly reallocation triggered by two overlapping crons, the try form is almost always what you want, because running the job twice back to back is wasted work rather than a correctness problem.

The xact in both names means transaction-scoped: the lock is released the instant the transaction ends, whether it commits or crashes. The variants without xact are session-scoped and survive rollback, which is a trap with a connection pool: the crashed job returns its connection to the pool still holding the lock, and the next unrelated request inherits it.

The key space is a single 64-bit integer (or a two-int form, handy as (namespace, id), e.g. pg_advisory_xact_lock(1, hashtext(tenant_id::text)) with namespace 1 reserved for allocation runs). Since real keys are usually text or UUIDs (Universally Unique Identifiers, the long random strings we use as primary keys), you hash, and a hash collision means two unrelated jobs happen to produce the same number and serialize against each other. One waits on the other for no good reason. That is a liveness nuisance rather than a correctness bug, which is the right failure direction: the worst case is "slower than expected" and never "oversold."

Two cautions from the docs

The first caution is a memory budget. The docs note that "both advisory locks and regular locks are stored in a shared memory pool whose size is defined by the configuration variables max_locks_per_transaction and max_connections," so don't take thousands of advisory locks in one transaction.

Second, never call a lock function inside a larger query where the planner might evaluate it on more rows than your LIMIT returns. The docs warn that "the LIMIT is not guaranteed to be applied before the locking function is executed," so you can silently take hundreds of locks you never asked for. Take the lock in a statement of its own: one plain select pg_advisory_xact_lock(...) at the top of the critical section.

Constraints: making invalid states unrepresentable

Everything above assumes your application code is correct. It won't be. A refactor will bypass the guarded UPDATE, and a new hire will add an endpoint that skips the lock. Constraints are rules you attach to the table itself, which Postgres checks on every write no matter which code path produced it. They are the layer that turns those inevitable bugs into loud errors instead of silent corruption of financial data.

Four kinds show up below:

• A CHECK constraint is a condition each row must satisfy. Write a row that fails it and the write is rejected.
• A unique index is an index that also refuses duplicates in the columns it covers.
• A partial index is an index built over only some rows, chosen by a where clause. Combine the two and you get uniqueness enforced over a subset.
• An exclusion constraint is the generalization: instead of "no two rows are equal here," it says "no two rows relate to each other this way here," for any comparison operator you name.

-- CHECK: encode the domain's arithmetic truths.
alter table inventory_ledger
  add constraint ledger_qty_nonzero check (qty <> 0),
  add constraint ledger_movement_sign check (
    (movement_type in ('receipt','return','adjustment_in')
       and qty > 0) or
    (movement_type in ('shipment','adjustment_out')
       and qty < 0)
  );

alter table allocations
  add constraint allocations_qty_positive check (qty > 0);

alter table sku_buckets
  add constraint buckets_no_oversell
  check (qty_allocated between 0 and qty_on_hand);

-- Partial unique index: uniqueness over a SUBSET of rows.
-- "At most one open invoice per wholesale order" --
-- historical and voided ones may repeat.
create unique index one_open_invoice_per_order
  on invoices (order_id) where status = 'open';

-- Soft-delete-safe SKU identity: dead rows don't block
-- reuse of the variant.
create unique index skus_identity_live
  on skus (tenant_id, style_id, color_code, size_code)
  where deleted_at is null;

-- Exclusion constraint: "no two price lists for the same style
-- with overlapping validity windows" -- unenforceable with
-- UNIQUE, straightforward with GiST.
create extension if not exists btree_gist;
alter table price_lists
  add constraint price_windows_no_overlap
  exclude using gist (
    tenant_id  with =,
    style_id   with =,
    valid_during with &&    -- daterange overlap operator
  );

What each constraint says

Take them in order. The first block says a ledger entry may never have quantity zero (<> means "not equal"), and that the sign of the quantity must match the movement type: receipts, returns, and positive adjustments add stock and must be positive; shipments and negative adjustments remove stock and must be negative. That single rule kills an entire family of bugs where a shipment gets recorded with a positive number and mysteriously increases inventory.

The second says allocation quantities are always positive. The third is the safety net promised earlier: qty_allocated between 0 and qty_on_hand means the bucket can never go negative and can never claim more allocated than exists. If a future refactor drops the guard from the UPDATE, the very first oversell attempt raises an error instead of quietly shipping.

Partial unique indexes and soft deletes

The next two statements create partial unique indexes. on invoices (order_id) where status = 'open' builds an index containing only the open invoices, and because it is unique, two open invoices for the same order are impossible, while any number of paid, voided, or historical invoices for that order remain perfectly legal, since they are not in the index at all.

The SKU one applies the same trick to soft deletes. A soft delete marks a row dead by stamping a timestamp into a deleted_at column instead of physically removing it, so history and references survive. The index says the combination of tenant, style, color, and size must be unique among live rows (deleted_at is null), so retiring a variant frees its identity for reuse instead of permanently poisoning it.

Exclusion constraints: beyond equality

The last statement is the exclusion constraint. btree_gist is an extension that teaches the GiST index type (Generalized Search Tree, an index structure built for shapes, ranges, and other non-scalar comparisons) how to handle ordinary scalar columns too, so we can mix both in one index. The constraint then reads almost like English: reject any new row where all three of these hold against an existing row: same tenant (with =), same style (with =), and overlapping date range (with &&, the range-overlap operator). One price window per style per tenant per stretch of calendar, enforced by the index itself, under full concurrency.

Lean hard on the partial unique index. ERPs are full of "exactly one active X" rules — one open invoice, one current cost per SKU, one default warehouse — and modeling them as a boolean column plus application checks is how you end up with two defaults.

The exclusion constraint generalizes uniqueness from "equal" to any operator you can name: = on the identity columns, && (overlaps) on the range column, all of it enforced by the index itself while other sessions are writing to the same table.

Note what that gets you for free: two sessions inserting overlapping price windows at the same instant is precisely the write-skew shape from earlier in the chapter, and the index defeats it without a lock, a retry loop, or a single line of application code.

Foreign keys and the unindexed column

A foreign key (FK) is the fifth kind: a column in one table that must contain a value present in another table's key column, so an order line can never point at a SKU that does not exist.

FK discipline for financial tables: default to ON DELETE RESTRICT (which means "refuse to delete the parent while children reference it"), so nothing referenced by an invoice line or a ledger entry is ever deletable. That's what soft deletes and status columns are for. ON DELETE CASCADE, which deletes the children along with the parent, is reserved for true composition (order → order_lines) and even then think twice once invoices reference lines.

And remember Postgres indexes the referenced side of an FK automatically (it's a unique constraint) but not the referencing column: an unindexed order_lines.sku_id means every check against a SKU sequential-scans your largest table, reading it end to end the way you would read all 900 pages of that book. Index every FK column unless you can prove you never join or cascade through it.

Row-Level Security: the mechanism

RLS stands for Row-Level Security: a feature where Postgres itself attaches an invisible filter to every query against a table, so a given user can only ever see or write the rows they are entitled to. The filter lives in the database, below your API layer and below your ORM, so no application code can forget it.

Chapter 5 covers multi-tenant architecture end to end. Here you need the mechanism, because a Supabase project puts far more weight on RLS than a conventional backend does. The client-facing API executes SQL as a low-privilege role on behalf of the browser, and RLS is the only thing standing between tenants.

The model: once ENABLE ROW LEVEL SECURITY is set on a table, the docs say "a default-deny policy is used, meaning that no rows are visible or can be modified." That default is a gift. Forgetting to write a policy fails closed, returning nothing, rather than failing open and leaking everything.

Policies then grant visibility per command:

• USING filters which existing rows a command can see (SELECT/UPDATE/DELETE);
• WITH CHECK validates rows being written (INSERT/UPDATE), so a tenant cannot insert a row stamped with somebody else's tenant id.
• Multiple permissive policies OR together, and any one of them saying yes is enough;
• restrictive policies AND on top, so every one must say yes.

A table with only restrictive policies passes nothing, since there is no permissive grant left for them to narrow.

alter table wholesale_orders enable row level security;
alter table wholesale_orders force row level security;
-- FORCE matters: table OWNERS bypass RLS by default. If your app
-- connects as a role that owns the tables (common in
-- migrations-run-as-app setups), RLS silently does nothing
-- without FORCE. Superusers and BYPASSRLS roles always bypass.

create policy tenant_isolation on wholesale_orders
  for all
  to authenticated
  using      (tenant_id =
              (select (auth.jwt() ->> 'tenant_id')::uuid))
  with check (tenant_id =
              (select (auth.jwt() ->> 'tenant_id')::uuid));

Why FORCE matters

The first line switches RLS on for the table, immediately hiding every row from everyone. The second is the one people miss: by default the owner of a table is exempt from its own policies, and in many setups the application connects as the same role that ran the migrations and therefore owns the tables. In that configuration RLS is enabled, looks enabled in every dashboard, and does nothing. force row level security removes the owner exemption. Superusers and roles granted BYPASSRLS still bypass it, by design, so your admin tooling and backups keep working.

Reading the tenant-isolation policy

The policy itself is named tenant_isolation and applies for all commands to the authenticated role, the role Supabase uses for a logged-in end user. auth.jwt() returns the claims from the caller's JWT (JSON Web Token, the signed token the browser presents to prove who it is). ->> pulls one claim out as text, and ::uuid casts it to the right type.

So the rule reads: a logged-in user may see a row only if the row's tenant_id equals the tenant_id baked into their signed token (using), and may write a row only if the same is true of the row they are writing (with check).

Because the token is signed by the auth server, a user cannot forge a different tenant id, and because the check lives in the database, there is no forgotten WHERE tenant_id = ... in some new endpoint that leaks another company's orders.

Policy performance, and a covert channel

Two performance-critical details. First, the (select ...) wrapper around the JWT lookup is deliberate. Supabase's own guidance is that "wrapping the function in some SQL causes an initPlan to be run by the optimizer which allows it to 'cache' the results versus calling the function on each row." An InitPlan is a small sub-plan that Postgres computes a single time up front, then compares against as a constant. On a million-row scan that is the difference between one function call and a million.

Second, the policy predicate is silently added to every query's WHERE clause, so every RLS-protected table needs tenant_id as the leading column of its hot indexes, or every tenant-scoped query degrades to fetching rows and filtering them after the fact.

One security note that surprises people: the docs state that "referential integrity checks, such as unique or primary key constraints and foreign key references, always bypass row security," which means a unique violation can reveal that a value exists in another tenant's rows. Design tenant-scoped unique keys ((tenant_id, order_number), never bare order_number) partly for this reason.

Window functions and CTEs: the reporting layer

A window function computes a value for each row using a whole group of related rows around it, without collapsing them. That is the difference from a normal aggregate: SUM(qty) with GROUP BY turns a hundred ledger rows into one total row, while SUM(qty) OVER (...) keeps all hundred rows and adds a running total to each one. The "window" is the set of rows each calculation is allowed to look at.

The ledger pattern from chapter 1 makes windows non-negotiable: state is derived, and windows are how you derive it in one pass instead of a hundred queries. The canonical query is the running on-hand balance per SKU, the backbone of the inventory movement report:

select l.sku_id,
       l.posted_at,
       l.movement_type,
       l.qty,
       sum(l.qty) over w                  as on_hand_after,
       sum(l.qty) filter
         (where l.movement_type = 'receipt') over w
                                          as received_to_date,
       lag(l.posted_at) over w            as prev_movement_at
  from inventory_ledger l
 where l.tenant_id = $1
window w as (partition by l.sku_id
             -- id tiebreak: ties MUST order deterministically
             order by l.posted_at, l.id
             rows between unbounded preceding and current row);

Reading the window definition

Start at the bottom. The window w as (...) clause defines the window once and names it w, so the three functions above can share it. Inside: partition by l.sku_id splits the ledger into independent groups, one per SKU, so a running total never bleeds across products; order by l.posted_at, l.id puts each group in chronological order; and rows between unbounded preceding and current row defines what each row can see: everything from the very first movement of that SKU up to and including this one, and nothing after.

What each output column computes

Now the output columns. sum(l.qty) over w is the running balance: for the third movement of a SKU it adds up movements one through three, which is exactly the on-hand quantity immediately after that movement, hence on_hand_after. sum(...) filter (where movement_type = 'receipt') over w is the same running sum with a filter applied first, giving a separate cumulative total of goods received to date, ignoring shipments.

lag(l.posted_at) over w reaches back exactly one row in the window and returns the previous movement's timestamp, which is how you compute "days since last movement" for a slow-mover report. One query, one pass over the table, three derived columns that would otherwise be three sub-queries run once per row.

ROWS vs. the default RANGE frame

The rows between frame matters more than it looks. The docs spell out the default: "The default framing option is RANGE UNBOUNDED PRECEDING ... With ORDER BY, this sets the frame to be all rows from the partition start up through the current row's last ORDER BY peer."

A peer is any row whose sort key ties with the current one. Two movements posted in the same microsecond would then each show the combined balance, and an auditor tracing the column line by line would find it jumping by two movements at once. ROWS plus a unique tiebreak column (l.id) gives the strict one-row-at-a-time running balance an auditor expects. The other workhorse is ranking, as in "current price per style from possibly-overlapping drafts" or "latest confirmed allocation attempt per order line":

with ranked as (
  select pl.*,
         row_number() over (
           partition by pl.tenant_id, pl.style_id
           order by lower(pl.valid_during) desc, pl.id desc
         ) as rn
    from price_lists pl
   where pl.tenant_id = $1
     and pl.status = 'published'
     and pl.valid_during @> current_date
)
select * from ranked where rn = 1;

Top one per group with row_number()

This is the "top one per group" pattern, and you will write it constantly. The CTE named ranked selects every published price list for this tenant whose validity range contains today (@> is the range operator meaning "contains") and stamps each row with a number.

row_number() counts 1, 2, 3 within each window: partition by tenant_id, style_id restarts the count for every style, and the order by puts the most recent window start first, breaking ties with the highest id (the most recently created row).

So the newest applicable price for each style gets rn = 1, the runner-up gets 2, and so on. The outer query then keeps only the winners. You can express the same thing with MAX() and a self-join (joining the table to itself), and it is harder to read and to get right when there are ties.

When a CTE is an optimization fence

On CTEs: PostgreSQL 12 changed the rules here. Its release notes say CTEs "are automatically inlined if they have no side-effects, are not recursive, and are referenced only once in the query," which means you can write single-use CTEs freely for readability and pay nothing for them.

A CTE referenced multiple times, or marked WITH ranked AS MATERIALIZED (...), is computed once and acts as an optimization fence, meaning the planner treats it as a sealed box and cannot push your outer WHERE conditions down inside it. Occasionally you want the fence on purpose (compute an expensive small set once, join it twice), but reach for it deliberately, not by default.

EXPLAIN ANALYZE literacy and index design

ERP query shapes are many-way joins over medium-cardinality tables: orders → order_lines → skus → styles, filtered by tenant, status, and a date range. Cardinality here means "how many distinct values a column has, or how many rows a step produces." A status column with five possible values is low-cardinality, and an id column is maximum-cardinality. It matters because the planner's every decision rests on estimating those numbers.

When a query is slow, EXPLAIN ANALYZE is the instrument. EXPLAIN alone asks the planner "what plan would you use?" and prints it without running anything. EXPLAIN ANALYZE actually runs the query and prints the plan annotated with what really happened: real row counts, real timings. That comparison, planned versus actual, is where the answer almost always lives. Add BUFFERS for how much data was read. On PostgreSQL 18 and later you can drop that option, because 18 made EXPLAIN ANALYZE include buffer counts automatically. Three things to read, in order:

explain (analyze, buffers)
select o.id, o.order_number, s.sku_code,
       ol.qty, ol.unit_price_cents
  from wholesale_orders o
  join order_lines ol on ol.order_id = o.id
  join skus s         on s.id = ol.sku_id
 where o.tenant_id = $1
   and o.status = 'confirmed'
   and o.confirmed_at >= now() - interval '90 days';

-- Nested Loop  (cost=0.86..1240.15 rows=12 width=41)
--              (actual time=0.9..2141.3 rows=48210 loops=1)
--   Buffers: shared hit=192411 read=8033
--   ->  Nested Loop
--         (actual time=0.9..190.4 rows=48210 loops=1)
--         ->  Seq Scan on wholesale_orders o
--               (cost=0..980 rows=3)
--               (actual rows=1607 loops=1)
--         ->  Index Scan using ol_order_id_idx on order_lines ol
--               (actual rows=30 loops=1607)
--   ->  Index Scan using skus_pkey on skus s
--         (actual time=0.040..0.042 rows=1 loops=48210)

Reading a plan as a tree

The query is ordinary: confirmed orders for one tenant in the last 90 days, joined out to their lines and the SKU on each line. Everything interesting is in the commented output beneath it, which is the plan Postgres printed.

Read a plan as a tree, from the inside out: indented lines starting with -> are child steps that feed their parent. cost= numbers are the planner's guesses in arbitrary units, and actual time= numbers are real milliseconds. A seq scan (sequential scan) is the all-900-pages read of an entire table. An index scan is the jump-to-page-412 alternative.

Nested Loop is one of the three join strategies: for each row on one side, go look up matching rows on the other. It is excellent when the outer side has a handful of rows and disastrous when it has fifty thousand.

• 1. Estimated vs. actual rows. rows=12 estimated vs rows=48210 actual is the whole story here: the planner chose a nested loop believing it would run the inner index scan a dozen times. Trace where the real number came from: the seq scan was estimated at 3 orders and returned 1,607, and each of those orders has about 30 lines, so 1,607 × 30 = 48,210 SKU lookups. The nested loop is a fine algorithm. It was chosen with bad information. Row mis-estimates cascade upward, because every decision above a wrong estimate is made on that wrong number, so fix them first (run ANALYZE to refresh statistics; correlated columns like (tenant_id, status) may need CREATE STATISTICS, which teaches the planner that the two columns are not independent).
• 2. Loops. Per-node numbers are per-loop, not totals, so you must multiply. The SKU lookup shows actual time=0.040 rows=1 loops=48210: cheap once and expensive 48 thousand times, at 0.040 ms × 48,210 ≈ 1,930 ms, which is most of the 2,141 ms the whole query took.
• 3. Buffers. A buffer is an 8 KB page of data. shared hit means it was already in memory, and read means it came off disk. This query touched 192,411 + 8,033 = 200,444 buffers, which is about 1.6 GB of pages, to return 48 thousand rows, a sign the access path is wrong regardless of how fast it looked on a warm cache (pages that happened to be in memory already). On a cold cache, where every page must come off disk, or on a bigger table, the same plan falls over.

Composite index column order

Index design for these shapes reduces to two rules. A composite index is an index on more than one column, and the column order is the entire game, like a phone book sorted by last name then first name, which is useless for finding everyone named "James" but perfect for finding "James Smith." First, composite column ordering: equality columns first, then the range or sort column. The B-tree (the standard sorted-tree index structure) can only use one range/sort column usefully, and it must come after all equality-matched columns:

-- where tenant_id = ? and status = ? and confirmed_at >= ?
--   order by confirmed_at desc
create index concurrently idx_orders_tenant_status_confirmed
  on wholesale_orders (tenant_id, status, confirmed_at desc);
-- (confirmed_at, tenant_id, status) would scan 90 days of
--   ALL tenants' orders.
-- (tenant_id, status, confirmed_at) descends straight to this
--   tenant's window, already sorted -- no Sort node in the plan.

-- Covering index: INCLUDE payload columns for an index-only
-- scan on the hot path.
create index concurrently idx_order_lines_order_covering
  on order_lines (tenant_id, order_id)
  include (sku_id, qty, unit_price_cents);

The first index matches the query above exactly. Because tenant_id and status are compared with =, they come first. Because confirmed_at is compared with a range operator (>=), it comes last. Postgres can then walk directly to the one contiguous stretch of the index holding this tenant's confirmed orders and read forward through the date window. The desc means that stretch is already in the order the query wants, so the plan contains no separate Sort step at all.

Put confirmed_at first instead, as the comment notes, and the index leads you to "all orders from the last 90 days across every tenant," which you must then filter down, an enormous amount of wasted reading on a busy multi-tenant table. concurrently builds the index without blocking writes, and the migration section covers it in detail.

Covering indexes and index-only scans

The second index shows the covering index idea. Normally an index gets you a pointer, and Postgres must then fetch the actual row from the table (a "heap fetch") to read the other columns. The docs describe INCLUDE as storing extra non-key columns in the index, so that "an index-only scan can return the contents of non-key columns without having to visit the index's table." Here, looking up an order's lines returns the SKU, quantity, and price without a single heap fetch.

Second, then, cover the hot read path, and stop there. Watch for Heap Fetches: 0 on the Index Only Scan line to confirm it is working. Index-only scans depend on Postgres knowing which pages hold only rows that are visible to everybody, and it records that in a small side structure called the visibility map, which the background cleanup process keeps current. That process is vacuum, the job that reclaims space left behind by deleted and updated rows.

The cost of every extra index

But every index taxes every ledger insert and allocation update, because each write must update each index. Audit with pg_stat_user_indexes.idx_scan, which counts how many times each index has actually been used, and drop the dead ones.

One version note: PostgreSQL 18 added B-tree skip scan, which per the release notes "allows multi-column btree indexes to be used in more cases such as when there are no restrictions on the first or early indexed columns." A query filtering only on status, confirmed_at under (tenant_id, status, confirmed_at) can now use that index, and it pays off best when the skipped leading column has few distinct values. Treat it as a safety net for the occasional cross-tenant admin query and keep ordering your indexes for the queries you actually run.

Migration discipline: changing the plane mid-flight

An ERP migrates constantly (every feature touches the schema) against tables that are both hot and financially load-bearing. DDL is the term for these statements: Data Definition Language, the subset of SQL that changes the shape of things (CREATE, ALTER, DROP) rather than the data inside them. Zero-downtime means running that DDL against a live production system without any user seeing an error or a hang. The plane keeps flying while you change the wing.

ACCESS EXCLUSIVE and the lock queue

Slow DDL is the obvious hazard, and it is the smaller one. The real danger is the lock queue. Postgres grants locks first-come, first-served, and requests line up in arrival order. That fairness is normally a virtue and here it is a landmine.

Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock, the strongest lock Postgres has: it conflicts with every other lock mode, including the tiny shared lock a plain SELECT takes.

So an ALTER cannot start while any query is still reading the table, and while it stands in line waiting its turn, every query that arrives after it waits behind it. Reads included. If your ALTER waits behind one long-running report query, the entire table goes dark for everyone until that report finishes.

GoCardless published a detailed post-mortem of exactly this failure: about 15 seconds of API downtime caused by a migration that added foreign-key constraints. As their write-up explains, "in order to add a foreign key constraint, Postgres takes AccessExclusive locks on both the table with the constraint, and the one it references," and "as AccessExclusive locks conflict with every other type of lock, having one sat in the queue blocks all other operations on that table." The DDL itself would have finished in milliseconds. The queue behind it took the API down.

Here is the safe playbook for the change you'll make most often: a new required column on a hot table (order_lines, tens of millions of rows). A backfill is the step in the middle: filling in the new column's value for every row that already existed, since only new rows get it automatically.

-- Every migration session:
set lock_timeout = '3s';
set statement_timeout = '15min';

-- 1. Add the column, nullable, with no default in the same
--    statement. Both statements are catalog-only and instant.
alter table order_lines add column discount_pct numeric(5,2);
alter table order_lines
  alter column discount_pct set default 0;
-- New rows now get 0. Existing rows stay NULL until step 2.
-- Deploy app code that handles both BEFORE running this.
--
-- Shortcut: if every existing row should get the same constant,
-- collapse steps 1 and 2 into one statement --
--   alter table order_lines
--     add column discount_pct numeric(5,2) default 0;
-- Since PG 11 a CONSTANT default is a catalog-only "fast
-- default": no rewrite, and existing rows read back as 0.
-- A VOLATILE default (gen_random_uuid(), now()) still rewrites
-- the whole table under ACCESS EXCLUSIVE. Never on a hot table.

-- 2. Backfill in batches, one transaction per batch, walking
--    the table by primary key. The driver starts with $1 = 0
--    and feeds each result back in as the next $1.
with batch as (
  select id from order_lines
   where id > $1
   order by id
   limit 10000
), touched as (
  update order_lines o
     set discount_pct = 0
    from batch b
   where o.id = b.id
     and o.discount_pct is null
  returning o.id
)
select max(id) as next_cursor from batch;
-- next_cursor is null => no rows left, backfill is done.

-- 3. Enforce NOT NULL without the full-table ACCESS EXCLUSIVE
--    scan. PG 12-17: the CHECK-constraint dance --
alter table order_lines
  add constraint order_lines_discount_nn
  check (discount_pct is not null) not valid;  -- instant
alter table order_lines
  validate constraint order_lines_discount_nn;
  -- full scan, but only SHARE UPDATE EXCLUSIVE:
  -- reads and writes proceed normally
alter table order_lines
  alter column discount_pct set not null;
  -- sees the valid CHECK, skips the scan: brief lock only
alter table order_lines
  drop constraint order_lines_discount_nn;

-- PG 18+: native NOT NULL constraints, two steps --
alter table order_lines
  add constraint order_lines_discount_nn
  not null discount_pct not valid;
alter table order_lines
  validate constraint order_lines_discount_nn;

The seatbelt: lock_timeout and statement_timeout

The two set lines at the top are the seatbelt. lock_timeout = '3s' tells Postgres: if I cannot get the lock within three seconds, give up and raise an error rather than sitting in the queue blocking the world. statement_timeout = '15min' caps how long any single statement may run once started. Together they mean the worst case of a bad migration is a failed deploy rather than an outage.

Step 1: add the column, then the default

Step 1 adds the column as nullable, then sets a default in a second statement. Both are changes to the system catalog (Postgres's own internal bookkeeping tables, where it records what your tables look like), so both finish instantly no matter how big the table is. Be precise about what each one does, because this trips people up. SET DEFAULT on an existing column only affects rows inserted from now on. The fifty million rows already there stay NULL until step 2 fills them in. That is why the comment insists application code must already tolerate both values before this migration runs.

The shortcut in the comment is worth knowing. Since PostgreSQL 11, ADD COLUMN ... DEFAULT <constant> in a single statement records the default once in the catalog and lets every existing row read back as that value without rewriting a byte. The release notes call it adding "a column with a non-null default without doing a table rewrite," and it applies "when the default value is a constant."

If your backfill value really is one constant for every row, use that form and skip step 2 entirely. The long form appears here because most real backfills compute a different value per row.

And a volatile default like gen_random_uuid() produces a different value per row, so Postgres has no choice but to rewrite all fifty million rows while holding ACCESS EXCLUSIVE. That is the difference between a millisecond and a twenty-minute outage.

Step 2: batched, resumable backfill

Step 2 fills in the old rows, and every word of the comment is a rule. In batches: one giant UPDATE would lock fifty million rows at once and hold them until it committed, blocking every concurrent order edit. Ten thousand at a time, one commit each, keeps the locked set small and short-lived.

Walking by primary key: the batch CTE takes the next 10,000 ids strictly greater than the last id you processed. Your driver keeps that last id as a cursor, passes it in as $1, and feeds the returned next_cursor back in on the next iteration, starting from 0. Each batch is an efficient indexed scan of one narrow id range, and progress survives an interruption: restart from the last cursor you recorded.

Compare that with OFFSET 10000000, which makes Postgres walk and discard ten million rows before it reaches the ones you want.

Two details in that statement are easy to miss. The touched CTE performs the UPDATE but is never referenced by the final select, and Postgres runs data-modifying CTEs regardless, so the update still happens. And the final select max(id) from batch reads the ids the batch considered, not the ones it changed, so the cursor advances past rows that were already filled in. Add a short sleep between batches so vacuum and ordinary traffic get room to breathe.

Step 3: NOT NULL without the full scan

Step 3 is the clever part. Marking the column NOT NULL directly forces Postgres to scan the entire table under ACCESS EXCLUSIVE to prove no NULLs remain, which is the outage we are avoiding. So instead: add a CHECK constraint that says the same thing, marked NOT VALID, which means "enforce this on all future writes, but do not check the existing rows." That is instant and takes only a brief lock.

Then VALIDATE CONSTRAINT performs the full scan under SHARE UPDATE EXCLUSIVE, a much weaker lock that allows normal reads and writes to continue throughout. Once the constraint is validated, SET NOT NULL sees the proof already exists and skips its own scan. PostgreSQL 12 added that optimization, described in its release notes as letting SET NOT NULL "avoid unnecessary table scans" when "the table's column constraints can be recognized as disallowing nulls." Then the temporary CHECK is dropped, since NOT NULL now says the same thing.

PostgreSQL 18 collapses that four-step dance into two. It stores NOT NULL specifications in the constraint catalog as first-class named constraints, and it allows the NOT VALID attribute on them, so you can add the real constraint unvalidated and validate it afterward under the weak lock. Same idea, less ceremony, and only on 18 and later.

Not valid, then validate: FKs and indexes

The same not-valid-then-validate shape applies to foreign keys: ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID skips checking existing rows (brief lock), then VALIDATE CONSTRAINT scans without blocking normal writes.

For indexes, CREATE INDEX CONCURRENTLY is mandatory on live tables, because a plain CREATE INDEX blocks all writes to the table for the entire build, which on a large table means minutes of failed orders.

Know its three quirks, all straight from the docs:

• It cannot run inside a transaction block, so it gets its own migration file with transactions disabled.
• Postgres "must perform two scans of the table, and in addition it must wait for all existing transactions that could potentially modify or use the index to terminate," so it takes far longer in wall-clock time than a plain build.
• And if it fails it will "leave behind an 'invalid' index" that the planner ignores while every write still pays to maintain it. The documented recovery is to drop the index and retry, or rebuild it with REINDEX INDEX CONCURRENTLY.

Codify all of this in tooling rather than memory. A linter is a program that reads your code and complains about known-bad patterns. strong_migrations is one for Ruby migrations and squawk is one for raw SQL, and both block the dangerous forms at review time. Migration tools like pgroll go further and automate the expand/contract flow by serving old and new schema versions side by side.

Expand, migrate, contract

Finally, the discipline that outranks every technique: migrations against financial data are never destructive. Expand, migrate, contract: add the new column or table, dual-write to both old and new, backfill the history, move readers over, and only drop the old structure releases later, after reconciliation reports prove nothing reads it.

The reason is rollback: if the new release is reverted an hour after deploy, the old code must still find its data where it left it. So:

• no DROP COLUMN in the same release that stops writing it;
• no in-place UPDATE that rewrites ledger history (post correcting entries instead, since the ledger is append-only precisely so history is never edited);
• renames are "add new + backfill + drop old eventually," never ALTER ... RENAME on a hot table racing deployed app code, because the instant the rename commits, every running instance of the old code starts erroring on a column that no longer exists.

Field notes & further reading

• PostgreSQL docs: Transaction Isolation. The anomaly table, READ COMMITTED's per-statement snapshots and UPDATE re-evaluation, SSI behavior and tuning (max_pred_locks_*, READ ONLY DEFERRABLE), and the 23505-instead-of-40001 caveat.
• PostgreSQL docs: Explicit Locking. Table/row/advisory lock modes, the conflict matrix, deadlock avoidance via consistent lock ordering, and the LIMIT warning for advisory lock functions.
• PostgreSQL docs: Row Security Policies. USING vs WITH CHECK, permissive vs restrictive, FORCE, and the referential-integrity bypass that creates a covert channel.
• PostgreSQL docs: CREATE INDEX. CONCURRENTLY semantics, INVALID index recovery, INCLUDE and index-only scans.
• PostgreSQL 18 release notes. B-tree skip scans, BUFFERS on by default in EXPLAIN ANALYZE, and NOT NULL constraints stored in pg_constraint with a NOT VALID attribute.
• GoCardless: Zero-downtime Postgres migrations — the hard parts. The lock-queue outage post-mortem and the lock_timeout playbook.
• strong_migrations. A catalog of unsafe DDL forms and their safe equivalents, worth reading even if you never run Rails.
• pgroll. A migration tool that serves multiple schema versions at once, automating the expand/contract flow.
• Supabase: RLS performance and best practices. Why to wrap auth.jwt() in a sub-select, and which columns to index for policies.