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:
| Pressure | What it means for design |
|---|---|
| Known, repetitive queries | 20 query shapes are 95% of the load. Design tables and indexes for those, not for hypothetical ones. |
| Small transactions, high concurrency | Every row lock and every long transaction is a potential queue. Design to minimise shared hot rows. |
| Correctness is non-negotiable | Invariants (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 constant | Every index costs write throughput. Key choice determines whether inserts are cache-friendly or scattered. |
| Schema must evolve while live | Migrations 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 memory | Latency 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:
| # | Operation | Frequency (peak) | Latency target | Rows touched | Consistency |
|---|---|---|---|---|---|
| A1 | Get order by id (with lines) | 3,000/s | p99 < 10 ms | 1 + ~4 | Read-your-writes |
| A2 | List a customer's recent orders, newest first, 20 per page | 800/s | p99 < 20 ms | 20 | Read-your-writes |
| A3 | Place order (reserve stock, insert order + lines, payment intent) | 150/s, 2,000/s on sale day | p99 < 150 ms | ~10 | Serializable effect on stock |
| A4 | Update order status (warehouse webhook) | 300/s | p99 < 50 ms | 1 | Idempotent |
| A5 | Admin search orders by email or status | 5/s | p99 < 500 ms | up to 100 | Stale ok |
| A6 | Daily revenue report | 1/day | minutes ok | millions | Stale 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_idtoorders.idis 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.
| Denormalisation | Example | Why | How to keep it correct |
|---|---|---|---|
| Snapshot of a value at a point in time | order_lines.unit_price_cents copied from products.price_cents | This is not really denormalisation: the price at purchase time is a different fact from the current price | Write once at insert; never update |
| Counter cache | posts.comment_count | Avoid COUNT(*) on every page view | Update in the same transaction as the insert, or async with periodic reconciliation (watch for hot rows) |
| Copied parent attribute for filtering | order_lines.customer_id | Lets one index serve "all lines for a customer" without a join | Immutable in practice (orders do not change customer) |
| Precomputed status/flags | accounts.is_overdue | Turn an expensive predicate into an indexable column | Trigger or job; or a generated column if derivable from the row |
| JSON blob of a read model | orders.summary jsonb for the order list page | One-row read for a complex screen | Rebuilt on every relevant write; often better as a cache outside the DB |
Rules of thumb:
- Copies of immutable facts are cheap. Copies of mutable facts are where bugs live.
- 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; - 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 type | Size | Ordered? | Generated where | Guessable? | Notes |
|---|---|---|---|---|---|
bigint identity / serial | 8 B | Yes, monotonic | Database sequence | Yes (enumerable) | Simplest, fastest, smallest |
int identity | 4 B | Yes | Database | Yes | Runs out at 2.1 billion; not worth the saving |
| UUIDv4 | 16 B | No, random | Anywhere | No | 122 random bits; terrible insert locality |
| UUIDv7 | 16 B | Yes, by millisecond | Anywhere | Timestamp visible, rest random | 48-bit Unix ms timestamp + random; RFC 9562 (2024) |
| ULID | 16 B (26-char text) | Yes, by millisecond | Anywhere | Same as UUIDv7 | Same idea as UUIDv7, predates it; store as uuid/binary(16) |
| Snowflake ID | 8 B | Roughly, by ms | ID service or library with worker ID | Timestamp visible | 41-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:
| Metric | bigint identity | UUIDv7 | UUIDv4 |
|---|---|---|---|
| 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 checkpoint | Few (one hot page) | Few | One per touched page: WAL volume multiplies |
| Insert throughput once index exceeds RAM | Flat | Flat | Drops 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 IDENTITYinternally. 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 separatepublic_id uuidcolumn 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.
| Constraint | Use for | Example |
|---|---|---|
NOT NULL | Required fields; also helps the planner | customer_id bigint NOT NULL |
UNIQUE | Natural keys, idempotency keys, "one per" rules | UNIQUE (tenant_id, email) |
Partial UNIQUE index | "At most one active X per Y" | CREATE UNIQUE INDEX ... ON subscriptions (user_id) WHERE status = 'active' |
CHECK | Ranges, enums, cross-column rules | CHECK (quantity > 0), CHECK (ends_at > starts_at) |
FOREIGN KEY | Referential integrity | REFERENCES orders (id) ON DELETE CASCADE |
EXCLUDE (PostgreSQL) | No overlapping ranges | No 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:
- Index the referencing column. PostgreSQL does not create an index on
order_lines.order_idautomatically (MySQL InnoDB does). Without it, deleting or updating a parent key scans the whole child table, and joins from parent to children are slow. - Choose
ON DELETEdeliberately.CASCADEfor owned children (order lines),RESTRICT(the defaultNO ACTION) for references that should block deletion (an order referencing a customer),SET NULLrarely.
-- 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:
- Lock queues: anything wanting the same rows waits.
- Bloat: in PostgreSQL, VACUUM cannot remove row versions newer than the oldest open snapshot; in MySQL, the undo history list grows.
- Connection exhaustion: a connection sitting
idle in transactionholds 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
| Level | Prevents | Allows | Use for |
|---|---|---|---|
| READ COMMITTED (PostgreSQL, Oracle, SQL Server default) | Dirty reads | Non-repeatable reads, lost updates with read-modify-write, write skew | Most 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 row | Write skew | Consistent multi-query reads (reports within a transaction) |
| SERIALIZABLE | All anomalies | Nothing; but transactions may abort with serialization failures | Invariants 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
| Problem | Pattern | Throughput effect | Trade-off |
|---|---|---|---|
| Decrement stock/seat count | Atomic conditional UPDATE ... WHERE qty >= n | Lock held for one statement | Logic must fit in SQL |
| Complex checks before write | SELECT ... FOR UPDATE, consistent lock order | Lock held for the transaction | Keep transaction tiny; deadlock risk |
| Rare conflicts, long think time | Optimistic version column | No locks | Retries under contention |
| High-frequency counters | Sharded counter rows or append + aggregate | N× ceiling | Reads sum N rows; slightly stale if buffered |
| Job queues | FOR UPDATE SKIP LOCKED | Workers never block each other | Needs cleanup and lease handling |
| Global sequences / "next invoice number" | Database sequences (non-transactional, no lock) | Unlimited | Gaps 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.comblocks 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 atstzrangevalidity column, or an extension such astemporal_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.
| Option | Example | Notes |
|---|---|---|
| Integer minor units | amount_cents bigint | Fast, 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 decimal | Exact, handles fractional cents (FX rates, interest). Slower than integers but rarely a bottleneck. |
float / double / real | Never for money | Inexact |
PostgreSQL money type | Avoid | Depends 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'sTimeZone. Plaintimestampstores a wall-clock reading with no zone, and you will eventually misinterpret it. - In MySQL,
TIMESTAMPconverts to and from UTC using the session time zone but is limited to 1970–2038;DATETIMEhas no zone. A common convention isDATETIME(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
datefor dates without time (birthdays, billing periods), not midnight timestamps.
Enums vs Lookup Tables vs CHECK
| Approach | Example | Adding a value | Extra data per value | Best for |
|---|---|---|---|---|
Native enum (PostgreSQL CREATE TYPE, MySQL ENUM) | status order_status | PostgreSQL: ALTER TYPE ... ADD VALUE (cheap; removing values is hard). MySQL: ALTER TABLE, may rebuild | No | Small, stable sets with order meaning |
text + CHECK | CHECK (status IN ('placed','paid','shipped')) | Replace the constraint (NOT VALID then VALIDATE) | No | Most status columns; easy to evolve |
| Lookup table + FK | status_id smallint REFERENCES order_statuses | INSERT a row | Yes (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 jsonbcolumn 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_idis the leading column of almost every index and part of unique constraints:UNIQUE (tenant_id, email).- Include
tenant_idin 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 thanSET, or the setting leaks to the next client on that server connection. tenant_idis 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.
| Type | Key | Typical OLTP use |
|---|---|---|
| Range | Time (created_at) | Orders, events, logs, jobs: drop or archive old months cheaply |
| List | Region, tenant tier, status | Separate large tenants, regional data |
| Hash | Hash of id or tenant | Spread 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
idalone 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. ADEFAULTpartition 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:
| Operation | Safe? | Safe approach |
|---|---|---|
| Add nullable column | Yes (metadata only) | |
| Add column with constant default | Yes since PG 11 (metadata only) | Volatile defaults (random(), clock_timestamp()) still rewrite |
Add NOT NULL to existing column | Scans table under lock | Add CHECK (col IS NOT NULL) NOT VALID, VALIDATE CONSTRAINT, then SET NOT NULL (PG 12+ uses the validated check to skip the scan) |
| Create index | Blocks writes | CREATE INDEX CONCURRENTLY (cannot run in a transaction; check for INVALID indexes if it fails) |
| Add foreign key | Scans and locks both tables | ADD CONSTRAINT ... NOT VALID, then VALIDATE CONSTRAINT |
| Change column type | Usually full rewrite under exclusive lock | Expand/contract with a new column |
| Rename column | Metadata only, but breaks running app code | Expand/contract, or a view during transition |
| Drop column | Metadata only | Deploy 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:
- Browse events and see seat availability.
- Hold seats for 10 minutes while the user pays.
- Confirm the purchase on successful payment; release the hold on expiry or failure.
- 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
| # | Operation | Peak rate | Latency | Notes |
|---|---|---|---|---|
| T1 | Seat map for an event (status per seat) | 50,000/s (mostly cached) | p99 < 100 ms | Cache with a 1–2 s TTL; DB serves misses |
| T2 | Hold N specific seats (N ≤ 8) | 5,000/s per hot event | p99 < 200 ms | The contention point |
| T3 | Confirm order after payment | 300/s | p99 < 300 ms | Idempotent by payment id |
| T4 | Expire stale holds | Continuous | Within 30 s of expiry | Background |
| T5 | My orders, newest first | 2,000/s | p99 < 50 ms | Read-your-writes |
| T6 | Sales report per event | Rare | Minutes | → 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_seatsand byUNIQUE (event_id, venue_seat_id)ontickets. 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
CHECKonevent_seatsmakes impossible states (held with no holder, sold with no order) unrepresentable. tickets.price_centsis a snapshot, not a denormalisation bug.payments.provider_payment_id UNIQUEmakes webhook processing idempotent: the retried webhook's insert conflicts and the handler returns success without double-processing.- No
seats_availablecounter. Availability counts come from the cache or acount(*)over a partial index, and are allowed to be a second stale. - Keys:
bigintidentity internally; UUIDv7public_idin 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 plainFOR UPDATEwithNOWAITif 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, notIndex 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_seatsgrows by ~500M rows a year. Partition by range on event date (or list/hash byevent_id) and detach finished events' partitions after a retention period, moving them to the warehouse. Since every hot-path query includesevent_id, partition pruning applies naturally if the partition key is derived from it.ordersandticketsare partitioned bycreated_atonce 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 byLIMITwith 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
WHEREandORDER BY, verified withEXPLAIN (ANALYZE, BUFFERS)on production-like data? - Are reporting/analytical queries routed away from the primary?
Keys and Types
-
Primary keys are
bigintidentity 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 (
CHECKor 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 UPDATEin 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_timeoutandidle_in_transaction_session_timeoutset 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);
fillfactorlowered 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, andlock_timeout? -
Multi-tenant tables lead indexes with
tenant_idand 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
bigintidentity 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
numericfor money,timestamptzfor time,CHECKfor 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.