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.