Databases3 min read
Reading EXPLAIN ANALYZE: how to find the actual slow part of a query
Everyone runs EXPLAIN. Fewer people read the row estimates, which is where the actual answer usually is.
Isolation levels define which concurrency anomalies a transaction can observe. PostgreSQL defaults to Read Committed, which prevents dirty reads but allows lost updates and non-repeatable reads. Prevent those explicitly with SELECT FOR UPDATE, an optimistic version column, or by raising the isolation level to Repeatable Read or Serializable and handling the retries.
| Level | Dirty read | Non-repeatable read | Phantom read | Lost update |
|---|---|---|---|---|
| Read Uncommitted | Possible* | Possible | Possible | Possible |
| Read Committed (default) | No | Possible | Possible | Possible |
| Repeatable Read | No | No | No (in PostgreSQL) | Detected — error |
| Serializable | No | No | No | Detected — error |
The column that matters in practice is the last one. Read Committed — what almost every application runs by default — allows two concurrent transactions to read the same value, both modify it, and one to silently overwrite the other.
Two requests read a balance of 100, both subtract 80, and both succeed — leaving 20 instead of rejecting the second.
-- Broken: read and write in separate statements
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- both see 100
-- application computes 100 - 80 = 20
UPDATE accounts SET balance = 20 WHERE id = 1;
COMMIT;
-- Fix 1: let the database do the arithmetic atomically
UPDATE accounts SET balance = balance - 80
WHERE id = 1 AND balance >= 80; -- affected rows tells you if it worked
-- Fix 2: lock the row for the duration of the transaction
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = $1 WHERE id = 1;
COMMIT;Fix 1 is best where the update is expressible as arithmetic — it is a single atomic statement with no lock held across application logic. Fix 2 is needed when the new value depends on computation the database cannot do.
| Pessimistic (FOR UPDATE) | Optimistic (version column) | |
|---|---|---|
| How | Lock the row until commit | Compare version on write, fail if changed |
| Best for | High contention, short transactions | Low contention, long user sessions |
| Failure mode | Waiting, potential deadlock | Write rejected; caller retries |
| User experience | Slower under contention | 'This record changed, please review' |
-- Optimistic: the update only applies if nobody else changed it
UPDATE documents
SET content = $1, version = version + 1
WHERE id = $2 AND version = $3;
-- 0 rows affected means someone else won; surface a conflict to the userWhen correctness under concurrency matters more than throughput and the invariants span multiple rows — inventory allocation, seat booking, ledger entries. PostgreSQL's serializable snapshot isolation gives you true serialisability without explicit locking, at the cost of aborted transactions you must retry.
Design the retry loop before you enable it: catch the serialisation failure code, retry a bounded number of times with jitter, and make the transaction idempotent so a retry is safe.
Read Committed is a sensible default. Raise it for the specific transactions with invariants that span rows, rather than globally — Serializable everywhere costs throughput and produces retries you may not be handling.
It manages transaction boundaries, not concurrency correctness. A read-modify-write through an ORM is exactly as vulnerable to lost updates as raw SQL.
When a query re-run inside the same transaction returns rows that were not there before, because another transaction inserted them. Repeatable Read in PostgreSQL prevents this via snapshot isolation.
Always acquire locks in the same order, keep transactions short, and retry on the deadlock error. A consistent ordering convention across the codebase eliminates most of them.
Harshal Patel
Founder & Lead Engineer, ROVQIX
Harshal leads engineering at ROVQIX, where he has shipped production Next.js, Node.js and PostgreSQL systems for startups, SaaS teams and ecommerce brands. He writes about the trade-offs behind architecture decisions rather than the framework of the week.
ROVQIXdesigns and builds production web platforms — Next.js front ends, Node.js APIs and the infrastructure behind them. Tell us what you're building and we'll scope it with you.
Everyone runs EXPLAIN. Fewer people read the row estimates, which is where the actual answer usually is.
Every PostgreSQL connection is a process. Ten containers with a pool of twenty is two hundred processes, and your database was configured for one hundred.
Every request that sends an email, generates a file or calls a third party is a request that should have returned already.
No spam. Just the occasional case study and craft breakdown.