PostgreSQL

PostgreSQL is useful when relationships, constraints, and transactional state transitions matter. The interview question is not whether a relational database can scale in the abstract. It is whether the selected query and write boundaries fit the workload, and whether the application respects finite connections, lock time, storage, and replication capacity.

Learning goals

Transactions; constraints; MVCC basics; isolation; row locks; composite indexes; connection pooling; replica lag; partitioning; operational limits.

The mechanism at a glance

Figure — API replicas → Bounded pool (acquire connection); Bounded pool → Short transaction (deadline); Short transaction → Primary rows (constraints + commit); Primary rows → Read replica (replication); Read replica → Tolerant reads (may lag)

The numbered components identify responsibilities. Follow the labeled arrows rather than treating the numbers as a global execution order. The scenario later in this lesson shows one concrete sequence.

Step-by-step reasoning

1. Put invariants in the database

Use primary keys, unique constraints, foreign keys, and check constraints for rules they can express. For a scarce resource, use a conditional update or a short transaction with appropriate locking. A SELECT followed by an unconditional UPDATE is not an atomic reservation even when both statements read current data.

2. Choose isolation deliberately

Read Committed gives statement-level snapshots and is not a promise that every multi-statement business rule is serializable. Stronger isolation or explicit locks may be needed for cross-row invariants. Serializable transactions can abort and require a retry of the whole transaction. Keep external effects out of retryable transactions unless separately protected.

3. Bound connection and lock occupancy

A connection pool limits concurrent database sessions; it does not manufacture database throughput. Keep transactions short and release connections promptly. A thousand serverless invocations each opening several connections can exhaust the database before CPU is fully used. Add queue deadlines and reject work when waiting becomes useless.

4. Observe maintenance and growth

Use query plans and representative data to tune indexes. Track slow queries, lock waits, connection wait time, disk growth, replication lag, and vacuum health. Long transactions can hold old snapshots and complicate cleanup. Partitioning can help retention and pruning but does not automatically distribute writes across independent servers.

Contracts and state

The following sketch makes the decision boundary concrete. Field names and capacity assumptions are illustrative; adapt them to the stated product contract.

UPDATE seat
SET state = 'held', hold_id = :hold, expires_at = :expiry
WHERE event_id = :event AND seat_id = :seat
  AND state = 'available'
RETURNING seat_id;
-- one returned row: acquired; zero: conflict
-- commit before returning successful acquisition

Worked example

Two buyers attempt the conditional reservation. One update obtains the row and commits the held state. The other does not obtain an available seat and must return a conflict, not success. Keep payment authorization outside the row-locking transaction. A later confirmation checks the hold identity and validity, so a slow payment cannot claim a seat that has since changed owners.

Failure walkthrough

Increasing application replicas increases outstanding queries and lock contention. Successful throughput can fall while connection count rises. Bound the pool, inspect the query and transaction duration, and apply backpressure before adding more callers. A replica can serve tolerant reads, but a stale replica must never decide whether the last seat is available.

Figure — Buyer A conditional update → A commits held state → Buyer B checks same row → Condition no longer matches → B receives conflict

Decisions and trade-offs

Check your understanding

Why should a provider payment call not run while holding a seat row lock?

Transfer to a new scenario

Acquire a reservation with a conditional update and keep transaction duration short.

Why can increasing serverless concurrency reduce successful database throughput?

Primary documentation

Read the first-party engineering account or official technical reference. Company engineering posts describe the scope and date of that publication; the interview reconstruction and scenarios here are original teaching examples.

Use transactions to enforce a business rule

For a reservation, one transaction can update a resource and insert its ownership record. A unique constraint is stronger than checking existence in application code before inserting. A conditional update can enforce available >= quantity without exposing a read/write race. Keep the transaction short and choose indexes that let it find the intended rows quickly.

MVCC lets readers observe a snapshot while writers create new row versions. Isolation levels define what anomalies remain possible; a transaction does not automatically imply serializable behavior. Serializable transactions may abort and need a bounded retry of the whole transaction. Locks, dead rows and long-running snapshots can create operational pressure even when query volume is modest.

Connection pooling limits concurrent database sessions; it does not make arbitrary parallel application work cheap. A thousand serverless invocations each opening ten connections can exhaust a database rapidly. Budget active transactions, statement timeouts and pool wait separately. Read replicas can offload acceptable-stale queries, but recent-write reads and ownership decisions need an appropriate authority.

Figure — A decision worksheet for PostgreSQL: read the mechanism and its guarantee together.

Operational sketch

BEGIN;
UPDATE inventory SET available=available-1
WHERE sku=$1 AND available>=1 RETURNING sku;
-- require one returned row
INSERT INTO reservations(id,sku,state) VALUES($2,$1,'held');
COMMIT;

A tempting mistake

Never hold row locks while waiting on a remote payment API. Persist a workflow state, commit, then make the remote call with a stable identity and reconcile its result.

Transfer exercise

Why can a unique constraint still be needed when application code checks for duplicates?