On this page

OLTP Database Design: Principles & Patterns

An OLTP schema is judged by two things that pull against each other: it must never be wrong, and it must stay fast while thousands of users change it at once. A reporting schema that is a little slow is an annoyance. A transactional schema that oversells a concert, double-charges a card or locks up during a flash sale is an incident.

This page is about designing the transactional side well: how to go from requirements to tables, keys, constraints and indexes; how to keep transactions short and avoid hot-row contention; how to handle history, deletes, money and time; and how to evolve the schema without downtime. It ends with a worked ticketing design, from requirements to DDL to the hot-path queries with EXPLAIN reasoning, and a checklist you can use in design reviews.

Related pages

For why OLTP and analytics need different designs, see OLTP vs OLAP vs HTAP. For conceptual modelling (entities, cardinality, mapping to tables), see ER Modeling. For worked domain schemas, see the schema design series: E-commerce, SaaS, Social and Chat.

What Makes OLTP Design Different

Analytical schemas are designed for flexible reading: wide, denormalised, append-mostly, queried in unpredictable ways. OLTP schemas live under a different set of pressures:

PressureWhat it means for design
Known, repetitive queries20 query shapes are 95% of the load. Design tables and indexes for those, not for hypothetical ones.
Small transactions, high concurrencyEvery row lock and every long transaction is a potential queue. Design to minimise shared hot rows.
Correctness is non-negotiableInvariants (no overselling, balance never negative, one active subscription per user) belong in the database, where every code path must obey them.
Writes are random and constantEvery index costs write throughput. Key choice determines whether inserts are cache-friendly or scattered.
Schema must evolve while liveMigrations run against tables taking traffic. Design choices determine whether a change is a metadata tweak or a multi-hour rewrite.
Working set must fit in memoryLatency targets of a few milliseconds assume hot indexes and rows are cached. Table and index size directly affect latency.

The rest of the page is about responding to these pressures.

Designing from Access Patterns

Start with the questions, not the tables. Entities and relationships come from the domain (see ER Modeling), but physical design (keys, indexes, denormalisation, partitioning) comes from how the application will read and write.

Write the access patterns down as a table before writing DDL:

#OperationFrequency (peak)Latency targetRows touchedConsistency
A1Get order by id (with lines)3,000/sp99 < 10 ms1 + ~4Read-your-writes
A2List a customer's recent orders, newest first, 20 per page800/sp99 < 20 ms20Read-your-writes
A3Place order (reserve stock, insert order + lines, payment intent)150/s, 2,000/s on sale dayp99 < 150 ms~10Serializable effect on stock
A4Update order status (warehouse webhook)300/sp99 < 50 ms1Idempotent
A5Admin search orders by email or status5/sp99 < 500 msup to 100Stale ok
A6Daily revenue report1/dayminutes okmillionsStale ok → not OLTP, send to analytics

Each row tells you something concrete:

  • A1 needs a primary key lookup and a child index on order_lines(order_id).
  • A2 needs a composite index (customer_id, created_at DESC) and keyset pagination.
  • A3 is the hot write path; its contention point (stock rows) needs a deliberate pattern.
  • A4 needs idempotency (webhooks retry) and must not lock more than one row.
  • A5 is admin-only and low-volume; maybe one trigram index, maybe a search engine; not worth slowing A3 for.
  • A6 does not belong in the OLTP database at all. Ship it to a replica or warehouse.

Design for the hot path, tolerate the cold path

A common failure is adding an index or a denormalised column for every admin screen and report. Each one slows every write on the hot path. Low-frequency, latency-tolerant queries can use a replica, a slower plan or a separate store.

graph LR
    R["Requirements<br/>and invariants"] --> AP["Access patterns<br/>freq, latency, rows"]
    AP --> L["Logical model<br/>entities, 3NF"]
    L --> P["Physical design<br/>keys, types, constraints"]
    P --> I["Indexes for<br/>hot-path queries"]
    I --> C["Contention plan<br/>hot rows, locks"]
    C --> V["Verify with EXPLAIN<br/>and load test"]
    V -->|"miss target"| P

Normalization as the Default

For OLTP, start normalised, usually to third normal form (3NF): every non-key column depends on the key, the whole key, and nothing but the key. The reasons are practical, not academic:

  • Each fact is stored once, so an update changes one row. No update anomalies, no "the customer's email is different on these two tables" bugs.
  • Rows are narrow, so more fit per page and per cache line; indexes are smaller.
  • Constraints are expressible: a foreign key from order_lines.order_id to orders.id is only possible if orders are a separate table.

The theory (functional dependencies, normal forms, decomposition) is in Normalization vs Denormalization and Relational Model.

When to Denormalize

Denormalise deliberately, for a measured read problem, and with a plan for keeping the copy correct.

DenormalisationExampleWhyHow to keep it correct
Snapshot of a value at a point in timeorder_lines.unit_price_cents copied from products.price_centsThis is not really denormalisation: the price at purchase time is a different fact from the current priceWrite once at insert; never update
Counter cacheposts.comment_countAvoid COUNT(*) on every page viewUpdate in the same transaction as the insert, or async with periodic reconciliation (watch for hot rows)
Copied parent attribute for filteringorder_lines.customer_idLets one index serve "all lines for a customer" without a joinImmutable in practice (orders do not change customer)
Precomputed status/flagsaccounts.is_overdueTurn an expensive predicate into an indexable columnTrigger or job; or a generated column if derivable from the row
JSON blob of a read modelorders.summary jsonb for the order list pageOne-row read for a complex screenRebuilt on every relevant write; often better as a cache outside the DB

Rules of thumb:

  1. Copies of immutable facts are cheap. Copies of mutable facts are where bugs live.
  2. Prefer a generated column when the value derives from the same row: ALTER TABLE orders ADD COLUMN total_cents bigint GENERATED ALWAYS AS (subtotal_cents + tax_cents + shipping_cents) STORED;
  3. If two copies can disagree, decide which is the source of truth and write a reconciliation query you can run any time.

Primary Keys

The primary key is the most consequential physical decision in an OLTP table. It is in every foreign key that points at the table, in every secondary index entry (in InnoDB and SQL Server clustered tables), in URLs and APIs, and it decides where new rows land in the B-tree.

The Options

Key typeSizeOrdered?Generated whereGuessable?Notes
bigint identity / serial8 BYes, monotonicDatabase sequenceYes (enumerable)Simplest, fastest, smallest
int identity4 BYesDatabaseYesRuns out at 2.1 billion; not worth the saving
UUIDv416 BNo, randomAnywhereNo122 random bits; terrible insert locality
UUIDv716 BYes, by millisecondAnywhereTimestamp visible, rest random48-bit Unix ms timestamp + random; RFC 9562 (2024)
ULID16 B (26-char text)Yes, by millisecondAnywhereSame as UUIDv7Same idea as UUIDv7, predates it; store as uuid/binary(16)
Snowflake ID8 BRoughly, by msID service or library with worker IDTimestamp visible41-bit ms timestamp + 10-bit worker + 12-bit sequence; fits in bigint

Generation strategies and clock concerns for distributed IDs are covered in ID Generation.

Why Random Keys Hurt: Index Locality

A B-tree keeps keys sorted. Where a new key lands determines which page is modified.

  • Monotonic keys (identity, UUIDv7, Snowflake) always land at the right edge of the index. One "hot" leaf page receives all inserts, stays in cache, fills completely, and then a new page is started. PostgreSQL has a fast path for this: it caches the rightmost leaf and, when it splits it, leaves the left page full (at the index's fillfactor, 90% by default for B-tree leaves) rather than splitting 50/50.
  • Random keys (UUIDv4) land anywhere. Each insert modifies a random leaf page. When the index is larger than memory, each insert needs a random page read first. Leaves split 50/50 when full, and with random inserts the steady-state average fill settles around 70% (the classical result is ln 2 ≈ 69%), so the index is ~30% bigger than it needs to be, and stays bigger.
graph TB
    subgraph SEQ["Sequential keys (bigint, UUIDv7)"]
        S1["Leaf 1<br/>full"] --> S2["Leaf 2<br/>full"] --> S3["Leaf 3<br/>full"] --> S4["Leaf N<br/>HOT: all inserts here<br/>always cached"]
    end
    subgraph RND["Random keys (UUIDv4)"]
        R1["Leaf 1<br/>~70% full<br/>insert"] --- R2["Leaf 2<br/>~70% full"] --- R3["Leaf 3<br/>~70% full<br/>insert"] --- R4["Leaf N<br/>~70% full<br/>insert"]
    end

Rough numbers for a table receiving 100 million inserts, on PostgreSQL:

Metricbigint identityUUIDv7UUIDv4
PK index size~2.1 GB~3.0 GB~4.0 GB
Leaf pages touched per 1,000 inserts~3~5~1,000
Full-page writes to WAL after checkpointFew (one hot page)FewOne per touched page: WAL volume multiplies
Insert throughput once index exceeds RAMFlatFlatDrops sharply (random reads per insert)

The WAL point is often the biggest surprise. With full_page_writes = on (the default, and needed for crash safety), the first modification of each page after a checkpoint writes the whole 8 KB page to WAL. Sequential inserts touch a handful of pages per checkpoint; random inserts touch a different page almost every time, so WAL volume, replication traffic and backup size can grow several-fold.

InnoDB makes this worse, because the table is the clustered primary-key B+tree. A random PK means rows themselves are scattered, every page split moves actual row data, and since every secondary index stores the PK as its row pointer, a 16-byte UUID inflates every index by 8 bytes per entry compared to a bigint. On MySQL, if you must use UUIDs, store them as BINARY(16) and use time-ordered ones (MySQL's UUID_TO_BIN(UUID(), 1) reorders the time fields of v1 UUIDs; better, generate UUIDv7 in the application).

Choosing

  • Default: bigint GENERATED ALWAYS AS IDENTITY internally. It is the smallest and fastest. Expose it, or a separate public identifier, depending on whether enumeration matters.
  • Need IDs generated outside the database (offline clients, idempotent creation, merging data from shards, IDs known before insert): UUIDv7. PostgreSQL 18 includes uuidv7(); on older versions generate in the application or use an extension. Never UUIDv4 as a clustered or high-insert-rate key.
  • Need 64-bit and distributed: Snowflake-style IDs.
  • Public identifiers: if you do not want competitors to read your order volume from /orders/1048576, add a separate public_id uuid column with a unique index, or use UUIDv7 (which reveals creation time, but not volume).
-- PostgreSQL 18+
CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id   uuid   NOT NULL DEFAULT uuidv7() UNIQUE,
    customer_id bigint NOT NULL REFERENCES customers (id),
    ...
);

Natural keys

Natural keys (email, ISBN, country code) are tempting but change more often than anyone expects (emails change, ISBNs get reissued). Use a surrogate key as the PK and put a UNIQUE constraint on the natural key. Exceptions: small, truly stable reference data like ISO currency codes (char(3)), and junction tables whose PK is the pair of foreign keys.

Constraints Are Correctness Tools

Application validation is necessary for user experience; database constraints are necessary for correctness. Every service, script, migration, admin console and future engineer writes through the database. Only constraints apply to all of them, and they apply atomically under concurrency, which application checks cannot.

ConstraintUse forExample
NOT NULLRequired fields; also helps the plannercustomer_id bigint NOT NULL
UNIQUENatural keys, idempotency keys, "one per" rulesUNIQUE (tenant_id, email)
Partial UNIQUE index"At most one active X per Y"CREATE UNIQUE INDEX ... ON subscriptions (user_id) WHERE status = 'active'
CHECKRanges, enums, cross-column rulesCHECK (quantity > 0), CHECK (ends_at > starts_at)
FOREIGN KEYReferential integrityREFERENCES orders (id) ON DELETE CASCADE
EXCLUDE (PostgreSQL)No overlapping rangesNo double-booking a room for overlapping times
-- No two bookings for the same room may overlap in time
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE room_bookings (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id  bigint    NOT NULL REFERENCES rooms (id),
    during   tstzrange NOT NULL,
    CONSTRAINT no_overlap EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

The EXCLUDE constraint is enforced with an index and correct under concurrency: two transactions inserting overlapping bookings cannot both commit. The equivalent application-level "check then insert" has a race between the check and the insert unless you add locking. In MySQL, which lacks exclusion constraints, you need a lock (e.g. SELECT ... FOR UPDATE on the room row) to serialise bookings for the same room.

Foreign Keys: Keep Them, Index Them

Foreign keys are sometimes dropped "for performance". The cost is real but small: the child insert checks the parent's primary key (an index lookup the database almost certainly has cached), and takes a FOR KEY SHARE lock on the parent row in PostgreSQL. The benefit is that orphaned rows become impossible.

Two practical rules:

  1. Index the referencing column. PostgreSQL does not create an index on order_lines.order_id automatically (MySQL InnoDB does). Without it, deleting or updating a parent key scans the whole child table, and joins from parent to children are slow.
  2. Choose ON DELETE deliberately. CASCADE for owned children (order lines), RESTRICT (the default NO ACTION) for references that should block deletion (an order referencing a customer), SET NULL rarely.
-- Find foreign keys without a supporting index (PostgreSQL)
SELECT c.conrelid::regclass AS table_name, c.conname, a.attname AS column_name
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
WHERE c.contype = 'f'
  AND NOT EXISTS (
      SELECT 1 FROM pg_index i
      WHERE i.indrelid = c.conrelid AND i.indkey[0] = c.conkey[1]);

Where foreign keys genuinely cannot be used (across shards, across services, on very large partitioned tables in older PostgreSQL versions), replace them with a periodic orphan-detection query and alerting, not with hope.

Indexing for Transactional Queries

The mechanics of B-trees, GIN, GiST and BRIN are covered in Indexing Deep Dive. Here the focus is OLTP-specific choices.

Composite Index Column Order

For a composite B-tree index, the rule is: equality columns first, then the range or sort column, and within equality columns, the order that serves the most queries.

-- Access pattern A2: a customer's recent orders, newest first
SELECT id, status, total_cents, created_at
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 20;

CREATE INDEX orders_customer_created_idx ON orders (customer_id, created_at DESC);

The index lets the database jump to customer_id = $1 and read 20 entries in order, stopping early: no sort, no scan of the customer's entire history. Reversing the order (created_at, customer_id) would make it useless for this query.

An index on (a, b, c) serves filters on a, (a, b) and (a, b, c), but not b alone (PostgreSQL 18 can sometimes use a "skip scan" for a low-cardinality leading column, and MySQL 8.0.13+ has a skip scan too, but do not design around it).

Covering Indexes

If the index contains every column the query needs, the database can answer from the index alone (an index-only scan in PostgreSQL, a covering index in InnoDB) without visiting the table.

-- PostgreSQL 11+: INCLUDE adds payload columns to leaf entries without affecting order
CREATE INDEX orders_customer_created_cov
    ON orders (customer_id, created_at DESC)
    INCLUDE (status, total_cents);

In PostgreSQL, index-only scans also depend on the visibility map: pages modified since the last VACUUM still require a heap check. On heavily updated tables the benefit is smaller. In InnoDB, every secondary index already contains the primary key, so a query needing only indexed columns plus the PK is covered automatically.

Partial Indexes

Index only the rows the hot query cares about.

-- Workers poll only pending jobs; 99.9% of rows are 'done'
CREATE INDEX jobs_pending_idx ON jobs (run_at) WHERE status = 'pending';

-- Most queries exclude soft-deleted rows
CREATE INDEX users_email_live ON users (lower(email)) WHERE deleted_at IS NULL;

A partial index on a status that covers 0.1% of rows is 1,000× smaller than a full index, stays in cache, and is cheap to maintain. MySQL does not support partial indexes; the closest equivalents are functional indexes on generated columns or separating hot rows into their own table.

The Write Cost of Indexes

Every index is updated on every insert, on every delete, and on every update that changes an indexed column. A table with 12 indexes does roughly 13 B-tree modifications per insert.

PostgreSQL has a specific optimisation worth designing for: HOT (Heap-Only Tuple) updates. If an update changes no indexed column and the new row version fits on the same heap page, PostgreSQL skips updating any index. Indexing a frequently updated column (last_seen_at, view_count, updated_at) disables HOT for those updates, and every update then writes to every index.

-- How many updates are HOT? Low ratio on a hot table = investigate indexed columns
SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC
LIMIT 10;

-- Indexes that are never used (since stats reset): candidates for removal
SELECT relname AS table_name, indexrelname AS index_name, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Setting a lower fillfactor (e.g. 80–90) on heavily updated tables leaves room on each page for new versions, increasing the HOT ratio.

Transactions: Short, Explicit, Deliberate

Keep Transactions Short

A transaction holds its row locks until commit, and its MVCC snapshot until it ends. Long transactions cause three kinds of damage:

  1. Lock queues: anything wanting the same rows waits.
  2. Bloat: in PostgreSQL, VACUUM cannot remove row versions newer than the oldest open snapshot; in MySQL, the undo history list grows.
  3. Connection exhaustion: a connection sitting idle in transaction holds a pool slot doing nothing. See Connection Pooling.

Rules:

  • Never hold a transaction open across network calls to other services, payment providers, or the user. Call the payment provider, then open a short transaction to record the result (with an idempotency key), or use a state machine with an outbox.
  • Do the reads and computation first, then a short write transaction.
  • Set timeouts as a guard:
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE app_user SET statement_timeout = '5s';
ALTER ROLE app_user SET lock_timeout = '2s';

Choosing Isolation

LevelPreventsAllowsUse for
READ COMMITTED (PostgreSQL, Oracle, SQL Server default)Dirty readsNon-repeatable reads, lost updates with read-modify-write, write skewMost OLTP; combine with atomic updates or row locks
REPEATABLE READ (MySQL default; snapshot isolation in PostgreSQL)Above + non-repeatable reads; PostgreSQL also aborts on concurrent update of the same rowWrite skewConsistent multi-query reads (reports within a transaction)
SERIALIZABLEAll anomaliesNothing; but transactions may abort with serialization failuresInvariants spanning multiple rows that you cannot express as a constraint

The full treatment, including write skew and how each engine implements these levels, is in Isolation Levels and MVCC.

Practical approach: run at READ COMMITTED, and for each invariant pick one explicit mechanism: a constraint, an atomic conditional update, a row lock, or SERIALIZABLE for that transaction with a retry loop. Do not rely on the default isolation level to protect an invariant you have not thought about.

Hot Rows and Contention Patterns

A hot row is one that many concurrent transactions update: a stock counter during a flash sale, a global sequence table, a popular post's like count, a merchant's balance. Row locks serialise those updates. If each transaction holds the lock for 5 ms, the row's throughput ceiling is 200 updates per second, no matter how many cores you have.

sequenceDiagram
    participant T1 as Txn 1
    participant R as Row stock(sku=42)
    participant T2 as Txn 2
    participant T3 as Txn 3
    T1->>R: UPDATE (lock acquired)
    T2->>R: UPDATE (waits)
    T3->>R: UPDATE (waits)
    Note over T1: other work, then COMMIT after 5 ms
    T1->>R: COMMIT releases lock
    R-->>T2: lock granted
    Note over T2,T3: Throughput ceiling is 1 / lock hold time

Two levers: hold the lock for less time, or spread the updates over more rows.

Pattern 1: Atomic Conditional UPDATE

The single most useful OLTP pattern. Instead of read-check-write in the application (which has a race), let the database check and modify in one statement:

-- Reserve 2 units only if available. One statement, one row lock, no race.
UPDATE inventory
SET on_hand = on_hand - 2
WHERE sku_id = 42 AND on_hand >= 2
RETURNING on_hand;
-- 1 row returned: reserved. 0 rows: out of stock.

Back it with a constraint so a bug elsewhere cannot violate the invariant:

ALTER TABLE inventory ADD CONSTRAINT on_hand_non_negative CHECK (on_hand >= 0);

Put this statement last in the transaction, just before COMMIT, so the lock is held for the shortest possible time. In MySQL the same pattern works; check ROW_COUNT() or the driver's affected-rows count.

Pattern 2: Pessimistic Locking with SELECT ... FOR UPDATE

When the decision needs more logic than fits in a WHERE clause, lock the row explicitly:

BEGIN;
SELECT balance_cents, status FROM accounts WHERE id = $1 FOR UPDATE;
-- application checks status, limits, fraud rules ...
UPDATE accounts SET balance_cents = balance_cents - $2 WHERE id = $1;
INSERT INTO ledger_entries (...) VALUES (...);
COMMIT;

To avoid deadlocks when locking several rows (e.g. a transfer between two accounts), always lock in a consistent order, such as ascending id:

SELECT id FROM accounts WHERE id IN ($from, $to) ORDER BY id FOR UPDATE;

See Deadlocks for detection and prevention. FOR UPDATE NOWAIT fails immediately instead of waiting, useful when you prefer to tell the user "try again" than to queue.

Pattern 3: Optimistic Locking with a Version Column

When conflicts are rare and the "transaction" spans a user think-time (edit a form, save), do not hold locks. Read a version, and make the write conditional on it:

-- Read
SELECT id, title, body, version FROM documents WHERE id = 7;   -- version = 12

-- Write (later, possibly a different request)
UPDATE documents
SET title = $1, body = $2, version = version + 1
WHERE id = 7 AND version = 12;
-- 0 rows updated => someone else saved first: reload and merge, or show a conflict

Optimistic locking costs nothing when there is no conflict and never blocks. It is the wrong tool for genuinely hot rows: under heavy contention most attempts fail and retry, wasting more work than waiting would. More on both approaches in Transactions Basics.

Pattern 4: Sharded (Striped) Counters

For counters updated far more often than they are read exactly (likes, views, API usage), split the one hot row into N rows and sum on read:

CREATE TABLE post_like_counts (
    post_id bigint   NOT NULL,
    shard   smallint NOT NULL,
    likes   bigint   NOT NULL DEFAULT 0,
    PRIMARY KEY (post_id, shard)
);

-- Write: pick a random shard 0..15, upsert
INSERT INTO post_like_counts (post_id, shard, likes)
VALUES ($1, floor(random() * 16)::smallint, 1)
ON CONFLICT (post_id, shard) DO UPDATE SET likes = post_like_counts.likes + 1;

-- Read
SELECT sum(likes) FROM post_like_counts WHERE post_id = $1;

With 16 shards, the lock ceiling rises 16×. Variants: buffer increments in Redis and flush every few seconds; or insert one row per event into an append-only table and aggregate periodically (append-only inserts do not contend at all).

Pattern 5: Queue Tables with SKIP LOCKED

Using a table as a job queue is a classic source of contention: every worker runs SELECT ... WHERE status = 'pending' ORDER BY run_at LIMIT 1 FOR UPDATE and they all queue behind the same first row. SKIP LOCKED (PostgreSQL 9.5+, MySQL 8.0+) makes each worker skip rows other workers have locked:

-- Claim up to 10 jobs; concurrent workers get disjoint sets
WITH claimed AS (
    SELECT id FROM jobs
    WHERE status = 'pending' AND run_at <= now()
    ORDER BY run_at
    LIMIT 10
    FOR UPDATE SKIP LOCKED
)
UPDATE jobs j
SET status = 'running', locked_by = $worker, locked_at = now(), attempts = attempts + 1
FROM claimed
WHERE j.id = claimed.id
RETURNING j.id, j.kind, j.payload;

Pair it with the partial index from earlier (ON jobs (run_at) WHERE status = 'pending'), a reaper for jobs stuck in running past a lease timeout, and deletion or partitioning of completed jobs so the table does not bloat. PostgreSQL queue tables comfortably handle thousands of jobs per second. Beyond that, or for fan-out, use a real broker. Patterns for workers and retries are in the Job Queue blueprint.

Pattern 6: Reservations and Append-Only Ledgers

For money, the safest design avoids updating a balance row as the source of truth at all: record immutable ledger entries (double-entry, each transfer is a pair summing to zero) and derive balances. A cached balance row can be maintained alongside, updated with an atomic conditional UPDATE for overdraft checks. Ledger design is covered in depth in Ledger Design.

Contention Pattern Summary

ProblemPatternThroughput effectTrade-off
Decrement stock/seat countAtomic conditional UPDATE ... WHERE qty >= nLock held for one statementLogic must fit in SQL
Complex checks before writeSELECT ... FOR UPDATE, consistent lock orderLock held for the transactionKeep transaction tiny; deadlock risk
Rare conflicts, long think timeOptimistic version columnNo locksRetries under contention
High-frequency countersSharded counter rows or append + aggregateN× ceilingReads sum N rows; slightly stale if buffered
Job queuesFOR UPDATE SKIP LOCKEDWorkers never block each otherNeeds cleanup and lease handling
Global sequences / "next invoice number"Database sequences (non-transactional, no lock)UnlimitedGaps on rollback; if gapless is legally required, allocate in batches per issuer

History, Deletes and Auditing

Soft Deletes

A soft delete marks a row as deleted instead of removing it: deleted_at timestamptz NULL. Reasons: undo, legal retention, keeping foreign keys from historical records valid.

Costs, which teams often underestimate:

  • Every query must filter WHERE deleted_at IS NULL. Forgetting it once is a data leak. Mitigate with a view (CREATE VIEW live_users AS SELECT * FROM users WHERE deleted_at IS NULL), row-level security, or ORM default scopes.
  • Unique constraints break: a deleted user with email a@x.com blocks a new signup with the same email. Use a partial unique index: CREATE UNIQUE INDEX ON users (lower(email)) WHERE deleted_at IS NULL;
  • Tables grow forever and indexes include dead rows unless partial.
  • Privacy law: "soft deleted" is not deleted under GDPR erasure requests. You still need a hard-delete or anonymisation path.

An alternative that avoids most of this: hard delete from the live table and move the row to an archive table (deleted_users) in the same transaction.

WITH gone AS (
    DELETE FROM users WHERE id = $1 RETURNING *
)
INSERT INTO deleted_users SELECT gone.*, now() AS deleted_at FROM gone;

Audit and History Tables

For "who changed what, when", a trigger-maintained history table is simple and reliable:

CREATE TABLE order_history (
    history_id  bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id    bigint      NOT NULL,
    op          char(1)     NOT NULL CHECK (op IN ('I','U','D')),
    changed_at  timestamptz NOT NULL DEFAULT now(),
    changed_by  text        NOT NULL DEFAULT current_setting('app.user_id', true),
    old_row     jsonb,
    new_row     jsonb
);

CREATE OR REPLACE FUNCTION audit_orders() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO order_history (order_id, op, old_row, new_row)
    VALUES (coalesce(NEW.id, OLD.id), left(TG_OP, 1),
            CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END,
            CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END);
    RETURN NULL;
END $$;

CREATE TRIGGER orders_audit AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_orders();

The application sets SET LOCAL app.user_id = '...' at the start of each transaction so the trigger knows the actor. Note that triggers add write cost (one extra insert per change) and live in the same transaction, which is exactly what you want for an audit trail. For very high write rates, CDC (reading the WAL) gives you a change history with no write overhead on the primary; see CDC Patterns.

Temporal Tables

When you need to query the state "as of" a past time (prices, contracts, insurance policies, employee assignments), model validity explicitly:

  • System-versioned (transaction time): when the database knew a fact. SQL Server (SYSTEM_VERSIONING = ON), MariaDB (WITH SYSTEM VERSIONING) and Oracle Flashback support this natively. PostgreSQL does not have built-in system versioning; use a history table maintained by triggers with a tstzrange validity column, or an extension such as temporal_tables.
  • Application-versioned (valid time): when a fact is true in the real world, which may differ (a price change scheduled for next month). Model with a range column and an exclusion constraint:
CREATE TABLE product_prices (
    product_id  bigint    NOT NULL REFERENCES products (id),
    price_cents bigint    NOT NULL CHECK (price_cents >= 0),
    valid       tstzrange NOT NULL,
    EXCLUDE USING gist (product_id WITH =, valid WITH &&)   -- no overlapping prices
);

-- Price at a given moment
SELECT price_cents FROM product_prices
WHERE product_id = $1 AND valid @> $2::timestamptz;
-- SQL Server: built-in system-versioned temporal table
CREATE TABLE dbo.prices (
    product_id  bigint NOT NULL PRIMARY KEY,
    price_cents bigint NOT NULL,
    valid_from  datetime2 GENERATED ALWAYS AS ROW START,
    valid_to    datetime2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.prices_history));

SELECT * FROM dbo.prices FOR SYSTEM_TIME AS OF '2026-10-01T00:00:00';

Types That Prevent Bugs

Money: Never Floating Point

0.1 + 0.2 is 0.30000000000000004 in IEEE 754 doubles. In a ledger, those errors accumulate and reconciliation fails by fractions of a cent.

OptionExampleNotes
Integer minor unitsamount_cents bigintFast, exact, simple. Store the currency alongside; not every currency has 2 decimals (JPY has 0, KWD has 3).
numeric(19,4) / DECIMAL(19,4)Exact decimalExact, handles fractional cents (FX rates, interest). Slower than integers but rarely a bottleneck.
float / double / realNever for moneyInexact
PostgreSQL money typeAvoidDepends on lc_monetary locale setting; awkward with multiple currencies
CREATE TABLE payments (
    id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    amount_minor  bigint  NOT NULL CHECK (amount_minor > 0),
    currency      char(3) NOT NULL REFERENCES currencies (code),  -- 'USD', 'JPY'
    ...
);

Time: timestamptz, Stored in UTC

  • In PostgreSQL, use timestamptz (timestamp with time zone). Despite the name, it stores an absolute instant (UTC internally) and converts on input/output using the session's TimeZone. Plain timestamp stores a wall-clock reading with no zone, and you will eventually misinterpret it.
  • In MySQL, TIMESTAMP converts to and from UTC using the session time zone but is limited to 1970–2038; DATETIME has no zone. A common convention is DATETIME(6) with the application always writing UTC.
  • Store the user's IANA time zone ('Asia/Kolkata', 'America/New_York') separately when you need local-time semantics (a 9 am daily reminder must follow DST changes).
  • Future local events (a concert at 20:00 local time in Lisbon next year) are best stored as local time plus zone, because time zone rules can change before the event.
  • Use date for dates without time (birthdays, billing periods), not midnight timestamps.

Enums vs Lookup Tables vs CHECK

ApproachExampleAdding a valueExtra data per valueBest for
Native enum (PostgreSQL CREATE TYPE, MySQL ENUM)status order_statusPostgreSQL: ALTER TYPE ... ADD VALUE (cheap; removing values is hard). MySQL: ALTER TABLE, may rebuildNoSmall, stable sets with order meaning
text + CHECKCHECK (status IN ('placed','paid','shipped'))Replace the constraint (NOT VALID then VALIDATE)NoMost status columns; easy to evolve
Lookup table + FKstatus_id smallint REFERENCES order_statusesINSERT a rowYes (labels, sort order, flags)Sets that change, need metadata, or are user-configurable

A sensible default: text with a CHECK constraint for code-defined states, lookup tables for anything business users configure.

JSON Columns: When and When Not

PostgreSQL jsonb and MySQL JSON are excellent for:

  • Genuinely variable attributes: product specs that differ by category, integration-specific metadata, webhook payloads you store for debugging.
  • Data you read and write as a unit and never query inside, or query only by a few keys you can index (GIN index on jsonb, or expression indexes on specific paths).

They are a poor choice for:

  • Fields with relationships: anything you would foreign-key, join on, or enforce uniqueness on.
  • Fields you filter, sort or aggregate on frequently: promote them to real columns.
  • Large documents updated piecemeal: any update rewrites the whole value (and in PostgreSQL, large values are TOASTed: compressed and stored out of line, making partial updates expensive).
  • Avoiding schema design: a data jsonb column with everything in it moves validation from the database to every reader.
-- Good: core fields as columns, variable attributes as jsonb, one indexed path
CREATE TABLE products (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    category_id bigint NOT NULL REFERENCES categories (id),
    name        text   NOT NULL,
    price_cents bigint NOT NULL CHECK (price_cents >= 0),
    attributes  jsonb  NOT NULL DEFAULT '{}'::jsonb
);
CREATE INDEX products_attr_gin ON products USING gin (attributes jsonb_path_ops);

Multi-Tenancy

Most B2B systems serve many customers (tenants) from one database. The three classic models are shared tables with a tenant_id column, schema per tenant, and database per tenant, each with different isolation, cost and operational trade-offs. These are covered in SaaS Schema Design and Multi-Tenancy Database Strategies; noisy-tenant effects are in Noisy Neighbor.

Design consequences of the shared-table model for OLTP:

  • tenant_id is the leading column of almost every index and part of unique constraints: UNIQUE (tenant_id, email).
  • Include tenant_id in foreign keys (composite FKs) if you want the database to prevent cross-tenant references.
  • Use row-level security as a safety net against a forgotten WHERE tenant_id = ...:
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON invoices
    USING (tenant_id = current_setting('app.tenant_id')::bigint);
-- Application, per transaction:  SET LOCAL app.tenant_id = '42';
  • With PgBouncer in transaction mode, use SET LOCAL (transaction-scoped) rather than SET, or the setting leaks to the next client on that server connection.
  • tenant_id is the natural shard key if you outgrow one node. See Sharding.

Partitioning and Archiving

Partitioning splits one logical table into physical child tables by a key. In OLTP systems it is primarily a tool for data lifecycle and maintenance, and only secondarily for query speed.

TypeKeyTypical OLTP use
RangeTime (created_at)Orders, events, logs, jobs: drop or archive old months cheaply
ListRegion, tenant tier, statusSeparate large tenants, regional data
HashHash of id or tenantSpread a huge table evenly so each partition's indexes and VACUUM stay small
CREATE TABLE orders (
    id          bigint      GENERATED ALWAYS AS IDENTITY,
    customer_id bigint      NOT NULL,
    status      text        NOT NULL,
    total_cents bigint      NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (id, created_at)          -- must include the partition key
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2026_11 PARTITION OF orders
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
CREATE TABLE orders_2026_12 PARTITION OF orders
    FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');

Partition Pruning

When the WHERE clause constrains the partition key, the planner skips irrelevant partitions (at plan time for constants, at execution time for parameters):

EXPLAIN SELECT count(*) FROM orders
WHERE created_at >= '2026-11-05' AND created_at < '2026-11-06';
-- Aggregate
--   ->  Index Only Scan using orders_2026_11_created_at_idx on orders_2026_11
--         Index Cond: ((created_at >= ...) AND (created_at < ...))
-- Only one partition appears in the plan.

The catch for OLTP: queries that do not include the partition key must check every partition. SELECT * FROM orders WHERE id = $1 on a table partitioned by month with 36 partitions does 36 index lookups. Design the partition key around your hot paths, or carry the key in the request (UUIDv7 and Snowflake IDs embed the creation time, so the application can derive the partition range from the id).

Other constraints to know:

  • In PostgreSQL, primary keys and unique constraints on a partitioned table must include the partition key. Global uniqueness of id alone cannot be enforced by the database across partitions.
  • Keep partition counts in the tens to low hundreds. Thousands of partitions slow planning and use more memory and locks.
  • Create future partitions ahead of time (a cron job or pg_partman), or inserts fail. A DEFAULT partition catches strays but makes later attaches slower.
  • In MySQL, partitioned InnoDB tables do not support foreign keys.

Archiving

Range partitioning makes archiving a metadata operation instead of a massive DELETE:

-- Detach last year's partition without blocking queries (PostgreSQL 14+)
ALTER TABLE orders DETACH PARTITION orders_2025_10 CONCURRENTLY;
-- Then: dump it to object storage, move it to cheaper storage, or drop it
DROP TABLE orders_2025_10;

Compare with DELETE FROM orders WHERE created_at < '2025-11-01' on 200 million rows: hours of WAL generation, replication lag, bloat that needs VACUUM, and long-held locks. For tables that are not partitioned, archive in small batches (DELETE ... WHERE id IN (SELECT id ... LIMIT 5000)) with pauses, and monitor replica lag.

Read Replicas and Connection Pooling

Two operational tools shape OLTP design decisions:

  • Read replicas take read load off the primary, but are asynchronous by default: a user who just placed an order and is redirected to the order page might not see it on a replica. Route read-your-writes paths (anything right after a write by the same user) to the primary, or wait for the replica to catch up to the write's LSN. Send reports, search indexing and exports to replicas. Details in Replication and Database Scaling.
  • Connection pooling is mandatory for PostgreSQL at any real scale, because each connection is a process. Transaction-mode pooling (PgBouncer) means session state (SET, prepared statements in older setups, advisory locks, temp tables) does not survive between transactions. Design for it: transaction-scoped settings (SET LOCAL), no session-level locks. See Connection Pooling.

Schema Migrations Without Downtime

An OLTP schema evolves under traffic. The danger is not usually the change itself, but the lock it takes: many ALTER TABLE forms take an ACCESS EXCLUSIVE lock, and even a fast one queues behind any running transaction on the table, while every new query queues behind it. A 2-millisecond ALTER waiting for a 10-minute report can block all traffic for 10 minutes.

Always:

SET lock_timeout = '3s';          -- give up rather than block traffic; retry later
SET statement_timeout = '15min';  -- for long-running but non-blocking operations

Expand / Contract

Never change a column in place. Expand to support both old and new, migrate, then contract:

graph LR
    E1["1. Expand<br/>add new column/table,<br/>nullable, no default rewrite"] --> E2["2. Dual write<br/>app writes old + new"]
    E2 --> E3["3. Backfill<br/>in batches"]
    E3 --> E4["4. Switch reads<br/>to new"]
    E4 --> E5["5. Stop writing old"]
    E5 --> E6["6. Contract<br/>drop old column"]

Safe and unsafe operations in PostgreSQL:

OperationSafe?Safe approach
Add nullable columnYes (metadata only)
Add column with constant defaultYes since PG 11 (metadata only)Volatile defaults (random(), clock_timestamp()) still rewrite
Add NOT NULL to existing columnScans table under lockAdd CHECK (col IS NOT NULL) NOT VALID, VALIDATE CONSTRAINT, then SET NOT NULL (PG 12+ uses the validated check to skip the scan)
Create indexBlocks writesCREATE INDEX CONCURRENTLY (cannot run in a transaction; check for INVALID indexes if it fails)
Add foreign keyScans and locks both tablesADD CONSTRAINT ... NOT VALID, then VALIDATE CONSTRAINT
Change column typeUsually full rewrite under exclusive lockExpand/contract with a new column
Rename columnMetadata only, but breaks running app codeExpand/contract, or a view during transition
Drop columnMetadata onlyDeploy code that no longer reads it first

In MySQL, many operations support ALGORITHM=INSTANT (adding columns since 8.0.12, at any position since 8.0.29) or ALGORITHM=INPLACE, LOCK=NONE; for the rest, use online schema change tools like gh-ost or pt-online-schema-change. The full playbook, including backfill strategies and tooling, is in Database Migrations.

Worked Example: A Ticketing System

Bring it together with a concrete design. A ticketing platform sells reserved seats for concerts. On-sale moments for popular events are brutal: 200,000 people trying to buy from 20,000 seats in the first minute.

Requirements and Invariants

Functional:

  1. Browse events and see seat availability.
  2. Hold seats for 10 minutes while the user pays.
  3. Confirm the purchase on successful payment; release the hold on expiry or failure.
  4. View my orders and tickets.

Invariants (must never be violated):

  • A seat is sold at most once.
  • A seat is held by at most one user at a time.
  • An order's total equals the sum of its tickets.
  • Payment confirmation is idempotent (the payment provider retries webhooks).

Non-functional: hold p99 < 200 ms during on-sale; 5,000 hold attempts per second peak for one event; 1 million events per year; ~500 million seats per year.

A full system design, including queueing users before on-sale and caching availability, is in Ticket Booking. Here we focus on the database.

Access Patterns

#OperationPeak rateLatencyNotes
T1Seat map for an event (status per seat)50,000/s (mostly cached)p99 < 100 msCache with a 1–2 s TTL; DB serves misses
T2Hold N specific seats (N ≤ 8)5,000/s per hot eventp99 < 200 msThe contention point
T3Confirm order after payment300/sp99 < 300 msIdempotent by payment id
T4Expire stale holdsContinuousWithin 30 s of expiryBackground
T5My orders, newest first2,000/sp99 < 50 msRead-your-writes
T6Sales report per eventRareMinutes→ replica / analytics

Modelling the Contention

The naive model has a counter: events.seats_available, decremented on every hold. That turns every hold for an event into an update of one row: at 5 ms per transaction, a ceiling of ~200 holds per second against 5,000 attempted. The queue grows without bound.

The better model makes each seat its own row. Holds for different seats lock different rows and proceed in parallel. Contention only happens when two users want the same seat, which is a real conflict and should fail fast for the loser.

erDiagram
    VENUES ||--o{ EVENTS : hosts
    VENUES ||--o{ VENUE_SEATS : has
    EVENTS ||--o{ EVENT_SEATS : "inventory for"
    VENUE_SEATS ||--o{ EVENT_SEATS : "instance of"
    CUSTOMERS ||--o{ ORDERS : places
    ORDERS ||--o{ TICKETS : contains
    EVENT_SEATS ||--o| TICKETS : "sold as"
    ORDERS ||--o{ PAYMENTS : "paid by"

Schema DDL

CREATE TABLE venues (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name      text NOT NULL,
    time_zone text NOT NULL                     -- IANA, e.g. 'Europe/Lisbon'
);

CREATE TABLE venue_seats (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    venue_id  bigint   NOT NULL REFERENCES venues (id),
    section   text     NOT NULL,
    row_label text     NOT NULL,
    seat_no   smallint NOT NULL,
    UNIQUE (venue_id, section, row_label, seat_no)
);

CREATE TABLE events (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id   uuid        NOT NULL DEFAULT uuidv7() UNIQUE,   -- PG 18+; generate in app otherwise
    venue_id    bigint      NOT NULL REFERENCES venues (id),
    title       text        NOT NULL,
    starts_at   timestamptz NOT NULL,
    on_sale_at  timestamptz NOT NULL,
    status      text        NOT NULL DEFAULT 'draft'
                CHECK (status IN ('draft','on_sale','sold_out','cancelled','finished'))
);

-- One row per seat per event: the unit of contention
CREATE TABLE event_seats (
    event_id       bigint      NOT NULL REFERENCES events (id),
    venue_seat_id  bigint      NOT NULL REFERENCES venue_seats (id),
    price_cents    bigint      NOT NULL CHECK (price_cents >= 0),
    currency       char(3)     NOT NULL,
    status         text        NOT NULL DEFAULT 'available'
                   CHECK (status IN ('available','held','sold')),
    held_by        bigint      NULL,          -- customer id
    hold_expires_at timestamptz NULL,
    order_id       bigint      NULL,
    version        integer     NOT NULL DEFAULT 0,
    PRIMARY KEY (event_id, venue_seat_id),
    CONSTRAINT hold_fields_consistent CHECK (
        (status = 'available' AND held_by IS NULL AND hold_expires_at IS NULL) OR
        (status = 'held'      AND held_by IS NOT NULL AND hold_expires_at IS NOT NULL) OR
        (status = 'sold'      AND order_id IS NOT NULL)
    )
) WITH (fillfactor = 80);   -- room for HOT updates: seat rows are updated 2-3 times each

CREATE TABLE customers (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email      text        NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX customers_email_uq ON customers (lower(email));

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    public_id   uuid        NOT NULL DEFAULT uuidv7() UNIQUE,
    customer_id bigint      NOT NULL REFERENCES customers (id),
    event_id    bigint      NOT NULL REFERENCES events (id),
    status      text        NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending','paid','cancelled','refunded')),
    total_cents bigint      NOT NULL CHECK (total_cents >= 0),
    currency    char(3)     NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE tickets (
    id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id      bigint NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
    event_id      bigint NOT NULL,
    venue_seat_id bigint NOT NULL,
    price_cents   bigint NOT NULL,      -- snapshot at purchase time
    FOREIGN KEY (event_id, venue_seat_id) REFERENCES event_seats (event_id, venue_seat_id),
    UNIQUE (event_id, venue_seat_id)    -- THE invariant: a seat is sold at most once
);

CREATE TABLE payments (
    id               bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id         bigint      NOT NULL REFERENCES orders (id),
    provider_payment_id text     NOT NULL UNIQUE,      -- idempotency for webhooks
    amount_cents     bigint      NOT NULL CHECK (amount_cents > 0),
    currency         char(3)     NOT NULL,
    status           text        NOT NULL CHECK (status IN ('succeeded','failed')),
    received_at      timestamptz NOT NULL DEFAULT now()
);

Design notes:

  • The "sold at most once" invariant is enforced twice: by the state machine on event_seats and by UNIQUE (event_id, venue_seat_id) on tickets. If a bug in the hold logic ever lets two orders reach confirmation for one seat, the second insert fails rather than issuing a duplicate ticket.
  • The CHECK on event_seats makes impossible states (held with no holder, sold with no order) unrepresentable.
  • tickets.price_cents is a snapshot, not a denormalisation bug.
  • payments.provider_payment_id UNIQUE makes webhook processing idempotent: the retried webhook's insert conflicts and the handler returns success without double-processing.
  • No seats_available counter. Availability counts come from the cache or a count(*) over a partial index, and are allowed to be a second stale.
  • Keys: bigint identity internally; UUIDv7 public_id in URLs so order volume is not enumerable.

Indexes

-- PK (event_id, venue_seat_id) already serves seat map (T1) and holds (T2)

-- T4: find expired holds quickly; tiny because few seats are held at any moment
CREATE INDEX event_seats_expiring ON event_seats (hold_expires_at)
    WHERE status = 'held';

-- T5: my orders, newest first (covering the list page)
CREATE INDEX orders_customer_recent ON orders (customer_id, created_at DESC)
    INCLUDE (status, total_cents, event_id);

-- FK support (PostgreSQL does not create these automatically)
CREATE INDEX tickets_order_idx ON tickets (order_id);
CREATE INDEX payments_order_idx ON payments (order_id);
CREATE INDEX event_seats_order_idx ON event_seats (order_id) WHERE order_id IS NOT NULL;
CREATE INDEX events_venue_idx ON events (venue_id);

Deliberately not indexed: event_seats.status alone (low cardinality, changes constantly, would disable HOT updates on every status change, and the PK already narrows to one event), orders.status (admin-only; served from a replica).

Note the trade-off on event_seats_expiring: it indexes status and hold_expires_at, both of which change on every hold and release, so those updates are not HOT. That is accepted because it is one small partial index, and the alternative (scanning all seats of all live events every few seconds) is worse.

Hot Path T2: Holding Seats

The user selects seats 101, 102 and 103 for event 5001. One statement claims all of them or none:

BEGIN;
WITH wanted AS (
    SELECT venue_seat_id
    FROM event_seats
    WHERE event_id = $1
      AND venue_seat_id = ANY ($2::bigint[])        -- {101,102,103}
      AND (status = 'available'
           OR (status = 'held' AND hold_expires_at < now()))   -- reclaim expired holds
    ORDER BY venue_seat_id                          -- consistent lock order: no deadlocks
    FOR UPDATE SKIP LOCKED
)
UPDATE event_seats s
SET status = 'held', held_by = $3,
    hold_expires_at = now() + interval '10 minutes',
    version = version + 1
FROM wanted
WHERE s.event_id = $1 AND s.venue_seat_id = wanted.venue_seat_id
RETURNING s.venue_seat_id;
-- If fewer rows than requested were returned: ROLLBACK and tell the user which seats are gone.
-- Otherwise: COMMIT.
COMMIT;

Why each part is there:

  • FOR UPDATE SKIP LOCKED: if another user is mid-transaction on seat 102, we do not wait; we simply do not get it, return fewer rows, roll back and tell the user immediately. Waiting would pile up lock queues during on-sale. (Use plain FOR UPDATE with NOWAIT if you prefer an error to a short count.)
  • ORDER BY venue_seat_id: if two users request overlapping seat sets, they lock in the same order, so they cannot deadlock.
  • Reclaiming expired holds inline: the expiry job (T4) can lag; a seat whose hold has expired is available in practice, so the hold query treats it that way.
  • All-or-nothing: the application compares the returned count with the requested count and rolls back on mismatch. Users asked for 3 adjacent seats; giving them 2 is a different product decision.
  • The transaction contains only this statement: lock time is roughly the statement time, around a millisecond.

EXPLAIN reasoning:

EXPLAIN (ANALYZE, BUFFERS)
SELECT venue_seat_id FROM event_seats
WHERE event_id = 5001 AND venue_seat_id = ANY ('{101,102,103}')
  AND (status = 'available' OR (status = 'held' AND hold_expires_at < now()))
ORDER BY venue_seat_id
FOR UPDATE SKIP LOCKED;

-- LockRows  (actual time=0.061..0.074 rows=3 loops=1)
--   Buffers: shared hit=9
--   ->  Index Scan using event_seats_pkey on event_seats
--         (actual time=0.031..0.040 rows=3 loops=1)
--         Index Cond: ((event_id = 5001) AND (venue_seat_id = ANY ('{101,102,103}'::bigint[])))
--         Filter: ((status = 'available') OR ((status = 'held') AND (hold_expires_at < now())))
--         Buffers: shared hit=9
-- Planning Time: 0.15 ms   Execution Time: 0.10 ms

What to check in this plan:

  • Index Scan on the primary key with both columns in the Index Cond: three B-tree descents, no scan of the event's 20,000 other seats.
  • Filter, not Index Cond, for status: correct. The status check is applied to 3 rows; indexing it would not help and would cost HOT updates.
  • shared hit=9: all pages came from cache (roughly 3 levels of index × 3 lookups, plus heap pages). No disk reads on the hot path.
  • Execution time 0.1 ms: the lock is held for about the duration of the statement plus commit (WAL flush, ~0.5–2 ms with synchronous_commit = on).

Capacity reasoning: different seats are different rows, so 5,000 holds/s for one event spread over 20,000 rows. The limit becomes WAL throughput and CPU, not locking. Group commit batches the fsyncs of concurrent commits; a single modern PostgreSQL primary handles tens of thousands of such small write transactions per second. Real contention happens only on the best seats, where it is genuine and the losers get an instant "seat taken" response.

Hot Path T3: Confirming an Order

The payment provider's webhook says payment pi_3Nx... for order 9001 succeeded. The handler must be idempotent and must not sell a seat whose hold expired and was taken by someone else.

BEGIN;
-- 1. Idempotency: the second delivery of the same webhook stops here
INSERT INTO payments (order_id, provider_payment_id, amount_cents, currency, status)
VALUES ($order_id, $provider_payment_id, $amount, $currency, 'succeeded')
ON CONFLICT (provider_payment_id) DO NOTHING
RETURNING id;
-- 0 rows returned => already processed: COMMIT and return 200.

-- 2. Convert holds to sales, only if still held by this customer
UPDATE event_seats
SET status = 'sold', order_id = $order_id,
    held_by = NULL, hold_expires_at = NULL, version = version + 1
WHERE event_id = $event_id
  AND venue_seat_id = ANY ($seat_ids)
  AND status = 'held' AND held_by = $customer_id
RETURNING venue_seat_id;
-- Fewer rows than seats => a hold was lost: ROLLBACK, then in a separate
-- transaction record the payment, mark the order for refund, and alert.

-- 3. Issue tickets; the UNIQUE constraint is the last line of defence
INSERT INTO tickets (order_id, event_id, venue_seat_id, price_cents)
SELECT $order_id, es.event_id, es.venue_seat_id, es.price_cents
FROM event_seats es
WHERE es.event_id = $event_id AND es.venue_seat_id = ANY ($seat_ids);

UPDATE orders SET status = 'paid' WHERE id = $order_id AND status = 'pending';
COMMIT;

Note that the hold does not check hold_expires_at > now() at confirmation time: if the user paid within a few seconds past expiry and nobody else claimed the seat, it is still held by them and selling it is the right outcome. If someone else claimed it, held_by no longer matches. The rule is "still held by me", not "clock says ok".

Payment calls happen outside the database transaction: the user pays at the provider, the webhook triggers this short transaction. To guarantee follow-on actions (emailing tickets, updating search) happen exactly when the order commits, insert an outbox row in the same transaction; see Transactional Outbox.

T4: Expiring Holds

-- Every few seconds, in batches, without blocking user transactions
WITH expired AS (
    SELECT event_id, venue_seat_id FROM event_seats
    WHERE status = 'held' AND hold_expires_at < now()
    ORDER BY hold_expires_at
    LIMIT 1000
    FOR UPDATE SKIP LOCKED
)
UPDATE event_seats s
SET status = 'available', held_by = NULL, hold_expires_at = NULL, version = version + 1
FROM expired
WHERE s.event_id = expired.event_id AND s.venue_seat_id = expired.venue_seat_id;

The partial index event_seats_expiring makes the inner query read only held seats in expiry order. SKIP LOCKED means the job never waits on a seat a user is confirming.

T5: My Orders

EXPLAIN (ANALYZE, BUFFERS)
SELECT public_id, status, total_cents, event_id, created_at
FROM orders
WHERE customer_id = 77812
ORDER BY created_at DESC
LIMIT 20;

-- Limit  (actual time=0.031..0.087 rows=20 loops=1)
--   ->  Index Scan using orders_customer_recent on orders
--         Index Cond: (customer_id = 77812)
--         Buffers: shared hit=24

The index provides both the filter and the order (no Sort node, and the Limit stops after 20 entries), but the plan is an Index Scan, not an Index Only Scan: public_id is not in the index, so each of the 20 rows needs a heap fetch (the ~20 extra buffer hits). That is still well under a millisecond when cached. If this page is hot enough to matter, add public_id to the INCLUDE list and the plan becomes an Index Only Scan with Heap Fetches near zero on a well-vacuumed table. This is exactly the kind of detail EXPLAIN catches and design reviews miss: verify each hot-path query's plan against the real index definitions.

For page 2 onwards, use keyset pagination rather than OFFSET:

SELECT public_id, status, total_cents, event_id, created_at
FROM orders
WHERE customer_id = $1 AND created_at < $last_seen_created_at
ORDER BY created_at DESC
LIMIT 20;

OFFSET 10000 reads and discards 10,000 index entries; the keyset version reads 20 regardless of depth.

Growth Plan

  • event_seats grows by ~500M rows a year. Partition by range on event date (or list/hash by event_id) and detach finished events' partitions after a retention period, moving them to the warehouse. Since every hot-path query includes event_id, partition pruning applies naturally if the partition key is derived from it.
  • orders and tickets are partitioned by created_at once they reach hundreds of millions of rows; T5 includes no date, so either keep a non-partitioned recent table or accept per-partition lookups bounded by LIMIT with a date range hint.
  • Reports (T6) go to a replica, then via CDC to the warehouse. See OLTP vs OLAP.
  • Seat map reads (T1) are cached; the DB sees a small fraction of them.

War Story (illustrative)

A ticketing startup's first schema had events.remaining_seats and a bookings table. The hold endpoint did UPDATE events SET remaining_seats = remaining_seats - $n WHERE id = $1 AND remaining_seats >= $n, then inserted bookings, then called the payment provider inside the same transaction "so it would roll back if payment failed". Average transaction time was 1.8 seconds. On the first big on-sale, every hold for the event queued on that single row; lock waits exceeded the 30-second request timeout within the first minute, the connection pool exhausted, and the whole site, including events not on sale, went down. The rewrite moved to per-seat rows, SKIP LOCKED holds, and payment outside the transaction. The next on-sale of similar size held seats at a p99 of 40 ms.

OLTP Design Review Checklist

Use this list when reviewing a new schema or a migration. Each item is a question with a concrete check.

Access Patterns

  • Are the top queries and writes listed with frequency, latency target and rows touched?
  • Does every hot-path query have an index that serves its WHERE and ORDER BY, verified with EXPLAIN (ANALYZE, BUFFERS) on production-like data?
  • Are reporting/analytical queries routed away from the primary?

Keys and Types

  • Primary keys are bigint identity or time-ordered (UUIDv7/Snowflake); no UUIDv4 on high-insert tables?
  • Money stored as integer minor units or numeric, never float; currency stored with it?
  • Timestamps are timestamptz (PostgreSQL) or UTC by convention (MySQL); user time zones stored as IANA names where needed?
  • Status columns constrained (CHECK or lookup FK)?
  • JSON used only for genuinely variable or opaque data, with indexed paths for any queried key?

Correctness

  • Every invariant is enforced by a constraint (NOT NULL, UNIQUE, partial unique, CHECK, EXCLUDE, FK) or a documented locking pattern?
  • Foreign keys present and their referencing columns indexed?
  • External callbacks (webhooks, retries) are idempotent via a unique key?
  • Each invariant spanning multiple rows has an explicit mechanism: atomic conditional update, FOR UPDATE in consistent order, or SERIALIZABLE with retries?

Concurrency

  • No transaction spans a network call to another service or the user?
  • Hot rows identified, with a pattern chosen (per-entity rows, sharded counters, SKIP LOCKED, append-only)?
  • Lock acquisition order consistent across code paths that lock multiple rows?
  • statement_timeout, lock_timeout and idle_in_transaction_session_timeout set for application roles?

Write Cost

  • Every index justified by a query; unused indexes scheduled for removal?
  • Frequently updated columns not indexed unless necessary (HOT updates preserved); fillfactor lowered on update-heavy tables?
  • Bulk operations (backfills, archiving) batched?

Lifecycle

  • Large append-heavy tables have a partitioning and retention plan?
  • Soft-delete semantics defined, with partial unique indexes and a hard-delete path for privacy requests?
  • Audit or history requirements met (trigger history table, temporal table, or CDC)?

Operations

  • Connection pooling in place and session state compatible with the pool mode?
  • Read-your-writes paths go to the primary; other reads may use replicas?
  • Migrations follow expand/contract, use CREATE INDEX CONCURRENTLY, NOT VALID + VALIDATE, and lock_timeout?
  • Multi-tenant tables lead indexes with tenant_id and have RLS or equivalent guard?

Summary

  • Design OLTP schemas from written-down access patterns; optimise the hot path and push cold queries elsewhere.
  • Normalise by default; denormalise only for measured needs, preferring copies of immutable facts.
  • Use bigint identity or time-ordered IDs; random UUIDv4 keys inflate indexes ~30%, scatter writes and multiply WAL.
  • Put invariants in constraints. Index foreign keys.
  • Index for equality-then-range, cover hot queries, use partial indexes, and remember every index taxes writes.
  • Keep transactions short and free of network calls. Break hot rows with per-entity rows, atomic conditional updates, sharded counters and SKIP LOCKED.
  • Integer cents or numeric for money, timestamptz for time, CHECK for statuses, JSON only for variable data.
  • Partition for lifecycle, archive by detaching partitions, and migrate with expand/contract under a lock_timeout.

Interview Questions

1. Why is UUIDv4 a poor primary key for a high-insert table, and what would you use instead? Random keys insert into random B-tree leaves. Once the index exceeds memory, each insert needs a random read; leaves split 50/50 and settle around 70% full, making the index ~30% larger; and with full-page writes, touching a different page per insert multiplies WAL volume. In InnoDB the table itself is clustered on the PK, so rows scatter and every secondary index carries the 16-byte key. Use bigint identity, or UUIDv7/ULID/Snowflake IDs if they must be generated outside the database; all are time-ordered, so inserts hit the rightmost leaf.

2. How do you prevent overselling inventory under high concurrency? Make the check and the decrement one atomic statement: UPDATE inventory SET on_hand = on_hand - $n WHERE sku_id = $1 AND on_hand >= $n, and treat zero affected rows as out of stock. Add CHECK (on_hand >= 0) as a backstop. Keep the statement at the end of a short transaction. If one SKU is extremely hot, split stock into multiple rows (buckets) or model individual units (per-seat rows) so concurrent buyers lock different rows.

3. Explain FOR UPDATE SKIP LOCKED and where you would use it. It locks the selected rows but silently skips rows already locked by other transactions instead of waiting. It turns a table into a contention-free work queue: concurrent workers claiming jobs each get a disjoint set. It is also useful for claiming any pool of interchangeable or user-chosen resources where waiting is worse than "not available", such as seat holds.

4. Optimistic vs pessimistic locking: when each? Optimistic (version column, conditional update) when conflicts are rare and the edit spans user think-time; it never blocks and costs nothing without conflict, but retries waste work under contention. Pessimistic (SELECT ... FOR UPDATE) when conflicts are likely and the transaction is short; it serialises access and guarantees the read value is still current when you write. For hot counters, neither: use atomic updates or sharded counters.

5. Your orders table has 14 indexes and inserts are slow. How do you approach it? Check pg_stat_user_indexes for indexes with zero or few scans and drop them (concurrently). Look for redundant indexes (an index on (a) alongside (a, b)). Check whether frequently updated columns are indexed, killing HOT updates (n_tup_hot_upd ratio). Consolidate several single-column indexes into composite indexes that serve the real queries. Move admin/reporting query needs to a replica with its own indexes, or to a warehouse.

6. Why should money never be stored as a float? What do you use? Binary floating point cannot represent most decimal fractions exactly (0.1 + 0.2 ≠ 0.3), so sums drift and reconciliation fails. Use integer minor units (bigint cents) with an explicit currency, remembering currencies have 0, 2 or 3 decimal places; or numeric/DECIMAL with fixed scale when fractional minor units are needed (FX, interest).

7. What are the problems with soft deletes and how do you mitigate them? Every query must remember the filter (risk of leaking deleted data); unique constraints collide with deleted rows; tables and indexes grow; and soft delete does not satisfy erasure requests. Mitigate with views or row-level security for the filter, partial unique indexes WHERE deleted_at IS NULL, partial indexes for hot queries, and a separate hard-delete/anonymise path. Or move deleted rows to an archive table instead.

8. How does partitioning help an OLTP table, and what does it cost? It makes retention cheap (detach and drop a partition instead of deleting millions of rows), keeps per-partition indexes and VACUUM small, and prunes partitions for queries filtered on the key. Costs: queries without the partition key check every partition; primary keys and unique constraints must include the partition key, so global uniqueness on other columns is not enforced; too many partitions slow planning; future partitions must be created ahead of time.

9. How would you add a NOT NULL column with a foreign key to a 500M-row table with no downtime? Add the column as nullable (metadata-only). Deploy code that writes it for new rows. Backfill in batches with pauses, watching replica lag. Add the FK as NOT VALID then VALIDATE CONSTRAINT (validation does not block writes). Add CHECK (col IS NOT NULL) NOT VALID, validate it, then SET NOT NULL, which PostgreSQL 12+ completes without a scan. Index the column with CREATE INDEX CONCURRENTLY. Set lock_timeout on every DDL step so it never queues behind a long transaction while blocking traffic.

10. Why is keeping transactions short so important, and what are the classic violations? Locks are held until commit, so lock hold time sets the throughput ceiling of contended rows; open snapshots block VACUUM and grow undo, causing bloat; idle-in-transaction connections waste pool slots. Classic violations: calling a payment provider or another service inside a transaction, waiting for user input, processing a large batch in one transaction, and ORMs that open a transaction per request and hold it through view rendering.

11. Design the core tables for a seat-reservation system. What enforces "a seat is sold at most once"? Model each seat per event as its own row with a status (available/held/sold), holder and expiry, keyed by (event_id, seat_id). Holds use a single UPDATE ... FROM (SELECT ... FOR UPDATE SKIP LOCKED) statement over the requested seats in id order. Tickets have UNIQUE (event_id, seat_id), so even if the hold logic were buggy, a second ticket for the seat cannot be inserted. Payment webhooks are idempotent via UNIQUE (provider_payment_id). No global availability counter, which would become a hot row.

12. What would you look for in EXPLAIN (ANALYZE, BUFFERS) output for a hot OLTP query? An index scan (or index-only scan) whose Index Cond uses the leading columns, not a sequential scan or a filter over many rows; rows estimates close to actual; no Sort node when the index could provide order; Heap Fetches low for index-only scans; shared hit buffers with few or no read; and execution time in tens to hundreds of microseconds. Then check the same plan with realistic parameter values, including the largest tenant or customer.