ORM pitfalls
ORMs are fine. What bites is the transaction machinery they hide. The three failure modes below account for most "the database is slow / the table keeps growing / everything is locked" incidents in ORM codebases — and every one of them is a lesson this site has already proven, wearing a nicer API.
Pitfall #1: the transaction that outlives the query
The classic: transaction-per-request middleware (or an explicit transaction { ... } block) opens a transaction, and the handler then calls a payment API, renders a template, awaits something slow. The database sees a session that is idle in transaction — holding row locks, blocking DDL, and pinning VACUUM's horizon — while your code isn't talking to it at all. Here is that story end to end, including the guardrail that ends it:
An ORM opens a transaction, updates a row… and then the request handler calls a slow external API.
A> SET idle_in_transaction_session_timeout = '500ms';
SET
A> BEGIN;
BEGIN
A> UPDATE orders SET status = 'paid' WHERE id = 1;
UPDATE 1From the outside this is the classic pathology — a session holding locks while doing nothing:
M> SELECT state FROM pg_stat_activity WHERE application_name = 'A';
state
---------------------
idle in transaction
(1 row)…the API call drags on past the timeout. The server kills the session (FATAL 25P03).
M> SELECT state FROM pg_stat_activity WHERE application_name = 'A'; -- the backend is gone
(0 rows)The app finds out only when it finally comes back to commit:
A> COMMIT;
ERROR: ERR_POSTGRES_CONNECTION_CLOSED: Connection closed
M> SELECT status FROM orders WHERE id = 1; -- the UPDATE was rolled back with the killed session
status
---------
pending
(1 row)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
The timeout is idle_in_transaction_session_timeout: "Terminate any session that has been idle (that is, waiting for a client query) within an open transaction for longer than the specified amount of time." Note terminate, not "cancel a query" — there is no query. The server logs a FATAL with SQLSTATE 25P03 (idle_in_transaction_session_timeout) and hangs up; the client, as the transcript shows, finds a dead connection on its next statement, and the uncommitted UPDATE is gone. That rollback is the point: better a failed request than a database-wide pileup. Keep transactions free of network I/O, and set this timeout as a seatbelt — alongside transaction_timeout and statement_timeout, both proven in chapter 8.
Pitfall #2: no transaction where you assumed one
The inverse failure. Without an explicit transaction block, most ORMs run each save() / update() as its own small transaction — so load entity, change field in memory, save is exactly chapter 2's read-modify-write lost update: the save writes every stale value the object was loaded with. The fixes are the previous lesson's, and ORMs ship two of them under friendlier names: "optimistic locking" (a version column, fix #3) and SELECT ... FOR UPDATE (usually a lock/forUpdate query option, fix #2). They only work if you turn them on.
Pitfall #3: trusting default isolation
An ORM transaction block gives you the database's default — READ COMMITTED, with every anomaly chapter 2 demonstrated at that level. If a unit of work needs REPEATABLE READ or SERIALIZABLE, you must say so (every serious ORM lets you set the isolation level per transaction) — and then you own the 40001 retry loop, because the ORM won't rerun your business logic for you.
The through-line across all three is the same: an ORM transaction is open from its first statement until your code returns, so every await inside it holds locks and pins VACUUM — keep network I/O out, and set idle_in_transaction_session_timeout as the backstop. Object-style read-modify-write is a lost update by default, so turn on your ORM's version-column support or lock the row at the read. And the isolation level and the 40001 retry are your job, not the ORM's; left alone, it will happily run write-skewed logic at READ COMMITTED forever.