ENGINEERING NOTES
PostgreSQL for Backend Engineers: Transactions, Indexes and Pools
Connect atomic writes, query plans and pool capacity to application correctness and latency.
What problem does this solve?
A fast API handler can still fail under concurrent updates, unbounded queries or connection starvation. Database correctness and capacity are part of backend design, not a separate deployment concern.
What is it?
Transactions group changes into an atomic unit. Indexes provide access paths at a write and storage cost. Pools reuse a bounded number of connections and queue callers when those connections are busy.
Mental model
A transaction is a controlled workspace on one connection. An index is a maintained lookup structure, not a free speed switch. A pool is a fixed set of workbenches; adding more waiting requests does not create more database capacity.
How it works internally
PostgreSQL uses MVCC for row visibility. Read Committed normally gives each statement a fresh snapshot, so a transaction alone does not eliminate all read/modify/write races. Constraints, conditional updates or locking may be required. Serializable transactions can fail with serialization errors and need bounded whole-transaction retries. EXPLAIN shows an estimated plan; EXPLAIN ANALYZE executes the query.
Production example
Prevent negative stock with an atomic UPDATE whose WHERE clause checks available quantity. Keep a transaction short, write the order and outbox record on the same client, then perform external work after commit.
Node.js / TypeScript implementation
A pg adapter excerpt; pool must be a configured pg.Pool. Parameterization protects values. The same checked-out client executes BEGIN, writes and COMMIT, and is released on both success and failure.
import type { Pool } from "pg";
async function reserve(pool: Pool, id: string, quantity: number) {
if (!Number.isInteger(quantity) || quantity < 1) throw new Error("Bad quantity");
const client = await pool.connect();
try {
await client.query("BEGIN");
const result = await client.query(
"UPDATE stock SET quantity = quantity - $1 WHERE id = $2 AND quantity >= $1 RETURNING id",
[quantity, id]
);
if (result.rowCount !== 1) throw new Error("Unavailable stock");
await client.query("COMMIT");
} catch (error) {
await client.query("ROLLBACK");
throw error;
} finally { client.release(); }
}Common mistakes
Do not mix pool.query calls inside a checked-out client's transaction. Do not concatenate user input into SQL. Do not create a new pool per request. An index on every column can slow writes without helping the real query workload.
Performance / reliability considerations
Budget total connections across replicas and workers. Observe pool wait time, lock waits and query latency separately. Match indexes to filters and ordering, use bounded pagination, and run executing diagnostics safely. Retries must restart the complete transaction and preserve external side-effect safety.
Interview questions
Why is one client required per transaction? What race remains under Read Committed? When does a composite index help? How can autoscaling exhaust database connections? What makes EXPLAIN ANALYZE operationally different from EXPLAIN?
Quick Revision
Use one client per transaction and always release it. Parameterize values. Keep transactions short. Combine constraints with atomic writes for races. Index actual query shapes. EXPLAIN ANALYZE executes. Pool limits multiply across instances. Retry complete serialization-failed transactions, with bounded backoff and no unsafe repeated side effects.