beginnerintermediateadvanced

PostgreSQL · Section 15

Transactions — Core Competency

Level: intermediate
Read: 34 min
Concepts: 7

The mechanics of BEGIN/COMMIT/ROLLBACK and the atomicity guarantee they provide, the deliberate design choice of where a transaction boundary sits, PostgreSQL's autocommit default and why two unwrapped statements are never atomic with each other, savepoints as a way to recover from one failed step without losing an entire transaction, the three distinct isolation levels by name, and — tying the whole section together — why transaction duration itself, independent of the work done, is a real and compounding production risk through held locks and a stalled vacuum horizon.

PostgreSQL overview

Learn PostgreSQL Tutorial - Full Course for BeginnersfreeCodeCamp.org

What is true here

BEGIN opens an all-or-nothing block; COMMIT makes every change since BEGIN permanent at once; ROLLBACK discards all of it.
A transaction boundary should be drawn around exactly the invariant that must hold together — not wider, not narrower.
Autocommit means each unwrapped statement is its own transaction — two related statements need an explicit BEGIN to be atomic together.
SAVEPOINT + ROLLBACK TO SAVEPOINT recovers from one failed step without discarding the whole transaction.
A transaction's locks and its effect on the vacuum horizon both scale with how long it stays open, not how much work it does — this is a real, compounding production risk.

What you will be able to do

Use BEGIN/COMMIT/ROLLBACK and savepoints correctly, including recovering from a failed step mid-transaction
Choose a transaction boundary that matches a real invariant, neither too wide nor too narrow
Explain why two statements with no explicit BEGIN are not atomic with each other
Diagnose and explain why a long-running or idle-in-transaction session is a production risk, and find one via pg_stat_activity

From a single statement to a production risk

BEGIN / COMMIT / ROLLBACK

the all-or-nothing boundary

Isolation level

how much concurrent change is visible

Duration

locks + stalled vacuum, for as long as it is open

The transaction boundary

BEGIN/COMMIT/ROLLBACK, the atomicity they guarantee, and the design choice of where the boundary sits.

BEGIN, COMMIT and ROLLBACK

corebeginner

BEGIN starts a transaction block, grouping every following statement into one all-or-nothing unit. COMMIT makes every change in that block permanent and visible to other transactions. ROLLBACK discards every change made since BEGIN, as if none of it ever happened.

Think of it as

Think of BEGIN as opening a draft that only you can see, COMMIT as publishing the entire draft at once, and ROLLBACK as discarding the draft entirely. Nothing in between is visible to anyone else, and nothing is real until COMMIT actually runs — a crash, a ROLLBACK, or a disconnected client before COMMIT means every statement since BEGIN is undone, not just the last one.

sql

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
COMMIT;   -- both updates become permanent together, or...
-- ROLLBACK;   -- ...neither does

What we're doing: Move money between two accounts inside a transaction, then show that a ROLLBACK undoes both statements as one unit, not just the most recent one.

transfer_rollback.sqlsql

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
SELECT name, balance FROM accounts WHERE name IN ('Alice', 'Bob');
-- shows the updated balances -- visible inside this same transaction

ROLLBACK;
SELECT name, balance FROM accounts WHERE name IN ('Alice', 'Bob');
-- shows the ORIGINAL balances -- both updates were undone together

2–3: Both updates are part of the same transaction block — neither is committed yet.
7: ROLLBACK discards BOTH updates as a single unit, not just the second one.

Output

 name  | balance
-------+---------
 Alice |     900
 Bob   |    1100

ROLLBACK

 name  | balance
-------+---------
 Alice |    1000
 Bob   |    1000

Why this works: The whole point of wrapping both UPDATEs in one transaction is that they succeed or fail together — a money transfer where only one side happened would leave the books wrong, and ROLLBACK demonstrates that guarantee concretely: undoing "the last statement" is not a thing PostgreSQL transactions do, only undoing the entire block since BEGIN.

Assuming each statement inside a transaction block commits independently

Wrong

sql

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
-- "that update is probably saved by now, right?" -- NO
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
-- an error occurs here, or the client disconnects
-- (no ROLLBACK or COMMIT was ever issued)

Better

sql

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
COMMIT;  -- only now are BOTH updates permanent -- explicitly, deliberately

What you see: A client disconnects or crashes partway through a multi-statement transaction, and the developer is surprised to find NONE of the statements took effect — including the first one, which "looked done" when it ran.

Why: Nothing inside a transaction block is durable until COMMIT actually executes — an interrupted session, a crash, or an unhandled error before COMMIT means PostgreSQL rolls the entire block back automatically, since a transaction that never reached COMMIT was never meant to be permanent in the first place.

BEGIN opens a draft; COMMIT or ROLLBACK closes it

No transaction

start

Transaction block open

Committed

end

Rolled back

end

BEGIN / COMMIT / ROLLBACK

BEGIN / COMMIT / ROLLBACK
Command	Effect
BEGIN	starts a transaction block
COMMIT	makes every change since BEGIN permanent, atomically
ROLLBACK	discards every change since BEGIN

Remember: BEGIN opens an all-or-nothing block; COMMIT makes every change since BEGIN permanent at once; ROLLBACK discards every change since BEGIN, not just the last statement. Nothing is durable until COMMIT actually runs.

Atomicity and Transaction Boundaries

coreintermediate

Atomicity means a transaction is all-or-nothing from the point of view of every other transaction — the intermediate states between its individual statements are never visible to anyone else, only the state before it started or the state after it fully commits. A "transaction boundary" is the deliberate choice of exactly which statements belong inside one BEGIN/COMMIT — drawn around whatever set of writes must succeed or fail together as a single unit.

Think of it as

Atomicity is a guarantee about visibility to others, not about the order statements run in — PostgreSQL still executes each statement in a transaction one after another, but no other session can observe it mid-way. Choosing the transaction boundary is a design decision: it should be drawn around exactly the invariant that must hold, no more and no less — too narrow and you lose atomicity where you needed it; too wide and you hold locks and resources longer than necessary for no additional correctness benefit.

sql

-- the boundary is drawn around exactly the invariant: "money moved" must be atomic
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- an unrelated, slow report query does NOT belong inside this same transaction

What we're doing: Contrast a correctly-scoped transaction boundary (just the invariant) with one that is too wide (bundling in unrelated slow work) and one that is too narrow (splitting the invariant across two transactions).

boundary_scoping.sqlsql

-- CORRECT: boundary matches the invariant exactly
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- TOO WIDE: an unrelated slow report is now holding the same locks
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
SELECT pg_sleep(30);  -- an unrelated slow report, wrongly bundled in
COMMIT;

-- TOO NARROW: the invariant itself is split across two transactions --
-- a crash between them leaves the money debited but never credited
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- ...crash here...
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

7–13: The report query has nothing to do with the money transfer, but now holds the same row locks for 30 extra seconds.
15–21: The debit and credit are no longer atomic with each other — a crash between the two commits leaves the invariant genuinely broken, with no transaction left to roll back.

Output

-- (illustrative -- the point is boundary placement, not a single runnable result)

Why this works: The "too wide" version holds the account rows locked for 30 extra seconds for work that has nothing to do with the transfer, hurting concurrency with no correctness benefit. The "too narrow" version is the more dangerous mistake: splitting a genuine invariant across two transactions means a crash between them leaves the system in a state the atomicity guarantee was specifically supposed to prevent — one account debited, the other never credited, with no transaction left to roll back and recover the missing credit.

Splitting a single business invariant across two separate transactions "to keep each one small"

Wrong

sql

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- if the process crashes between these two blocks, the debit is
-- permanently committed but the credit never happens

Better

sql

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- both changes are now genuinely atomic -- either both happen or neither does

What you see: Occasionally, under crashes or restarts, money appears to vanish from the system — debited from one account with no matching credit anywhere, and no transaction log entry showing an incomplete transfer because both halves individually committed successfully.

Why: Smaller transactions are not automatically safer — "small" is only good when the boundary still matches a real invariant. Splitting a debit and its matching credit into two separate COMMITs throws away the exact guarantee atomicity exists to provide: that the two changes are either both visible or neither is, with nothing in between, even across a crash.

Where should the transaction boundary go?

Too narrow — split invariant

+Debit and credit in two separate transactions
+A crash between them leaves money permanently missing
+No transaction left to roll back and recover

Matches the invariant

−Debit and credit in one BEGIN/COMMIT
−Other transactions see either both changes or neither
−Nothing unrelated bundled in to hold locks longer

Choosing a transaction boundary

Choosing a transaction boundary
Boundary choice	Risk
Too narrow (splits a real invariant across two transactions)	the invariant can be observed broken, or left broken by a crash between the two
Too wide (wraps unrelated work into one transaction)	locks held longer than necessary, more contention, harder to reason about
Just right (exactly the invariant, nothing more)	atomic where it matters, no wasted lock duration

Remember: Atomicity means other transactions see either the fully-committed result or nothing — never a partial state. Draw the transaction boundary around exactly the invariant that must hold together: too wide wastes lock duration, too narrow can leave the invariant genuinely broken across a crash.

Defaults and recovery

What happens with no explicit BEGIN, and how a savepoint recovers from one failed step.

Autocommit Behavior

standardbeginner

Every statement in PostgreSQL runs inside a transaction, even if you never type BEGIN. Without an explicit BEGIN, each individual statement gets its own implicit BEGIN and COMMIT wrapped around just that one statement — so a single UPDATE with no transaction block is automatically all-or-nothing on its own, but two separate UPDATEs with no BEGIN between them are two separate, independently-committed transactions.

Think of it as

There is no such thing as a PostgreSQL statement running "outside" a transaction — autocommit just means the transaction boundary defaults to a single statement when you have not drawn one yourself with BEGIN. This is why a bare UPDATE is always safe on its own (it either fully applies or fully does not), but two bare UPDATEs that are supposed to happen together are NOT safe on their own — each one commits independently the instant it finishes, with no atomicity between them unless BEGIN wraps both.

sql

-- autocommit: each statement is its own transaction
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- committed already, the instant it succeeded

UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- a SEPARATE transaction -- no atomicity connects it to the statement above

-- explicit BEGIN: both statements become one transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

What we're doing: Demonstrate that two statements run without an explicit BEGIN are NOT atomic with each other, by simulating a failure between them.

autocommit_gap.sqlsql

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- this is already permanently committed -- autocommit closed it out instantly

-- (imagine the application crashes here, before running the next statement)

UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- never runs -- the debit from the first statement is now permanently
-- stranded with no matching credit

1: With no BEGIN, this UPDATE is its own complete transaction — it commits the instant it finishes.
5: This statement never runs, but the first one already committed permanently — the two were never atomic with each other.

Output

UPDATE 1

-- (crash before the second statement runs)

Why this works: Autocommit closes out each statement's transaction the instant it succeeds, which is exactly the right default for a single ad-hoc UPDATE — but it means two statements that must succeed or fail as a unit need an explicit BEGIN to actually get that guarantee, since autocommit gives each of them its own independent transaction boundary by default.

Assuming an ORM or client library groups related statements automatically without an explicit transaction

Wrong

sql

-- application code, no explicit transaction:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- (a network hiccup, a crash, or another request interleaves here)
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

Better

sql

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- most ORMs expose this as an explicit transaction/session block --
-- it must be opted into, it is not automatic just because two
-- statements appear next to each other in application code

What you see: Under normal, low-traffic testing, the two statements always appear to happen together — then under real production load or a real crash, they occasionally do not, and money (or inventory, or any two related values) drifts out of sync with no obvious cause.

Why: Application code that issues two related statements back-to-back looks atomic to a developer reading it top to bottom, but without an explicit BEGIN, PostgreSQL treats them as two entirely separate, independently-committed transactions — the only way to get the atomicity the code visually implies is to actually open a transaction block around both statements.

Remember: Autocommit means the default transaction boundary is one statement — not that statements skip transactions entirely. Two statements with no explicit BEGIN between them are two independent, separately-committed transactions with no atomicity connecting them.

Savepoints and Partial Rollback

coreintermediate

A SAVEPOINT marks a named point inside a transaction that you can roll back to without discarding the whole transaction — ROLLBACK TO SAVEPOINT undoes everything since that point while keeping everything before it, and the transaction stays open, able to continue and eventually COMMIT.

Think of it as

A savepoint is a checkpoint inside a single transaction, not a second transaction — rolling back to one undoes exactly the statements after it, exactly like a full ROLLBACK undoes everything since BEGIN, just scoped narrower. This is the mechanism that lets a transaction recover from one failed step (a constraint violation, a caught application error) without losing everything else it had already done in the same transaction.

sql

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
SAVEPOINT before_credit;
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
-- oops, wrong recipient
ROLLBACK TO SAVEPOINT before_credit;
UPDATE accounts SET balance = balance + 100 WHERE name = 'Wally';
COMMIT;  -- Alice's debit and Wally's credit are both committed; Bob's never happened

What we're doing: Use a savepoint to recover from one failed statement inside a larger transaction, keeping the earlier work instead of losing the whole transaction to a full ROLLBACK.

savepoint_recovery.sqlsql

BEGIN;
INSERT INTO orders (id, total) VALUES (1, 100);

SAVEPOINT before_risky_insert;
INSERT INTO orders (id, total) VALUES (1, 200);
-- ERROR: duplicate key value violates unique constraint "orders_pkey"

ROLLBACK TO SAVEPOINT before_risky_insert;
-- the transaction is usable again -- the first INSERT is still queued

INSERT INTO orders (id, total) VALUES (2, 200);
COMMIT;
SELECT * FROM orders;

4: The savepoint marks a recoverable point before the risky statement.
5: This INSERT fails — without a savepoint, the entire transaction would now be unusable until a full ROLLBACK.
8: Rolling back only to the savepoint discards just the failed INSERT, keeping the first one intact and the transaction still open.

Output

 id | total
----+-------
  1 |   100
  2 |   200

Why this works: Without the savepoint, the duplicate-key error would have left the entire transaction in an aborted state — the only way out would be a full ROLLBACK, discarding the first INSERT too, even though it had nothing wrong with it. The savepoint scopes the recovery to exactly the statement that actually failed.

Believing an error inside a transaction can simply be ignored and the transaction continued, without a savepoint

Wrong

sql

BEGIN;
INSERT INTO orders (id, total) VALUES (1, 100);
INSERT INTO orders (id, total) VALUES (1, 200);
-- ERROR: duplicate key value
INSERT INTO orders (id, total) VALUES (2, 200);
-- ERROR: current transaction is aborted, commands ignored until end of transaction block
COMMIT;  -- effectively a ROLLBACK -- nothing in this transaction was saved

Better

sql

BEGIN;
INSERT INTO orders (id, total) VALUES (1, 100);
SAVEPOINT before_second_insert;
INSERT INTO orders (id, total) VALUES (1, 200);
-- ERROR: duplicate key value
ROLLBACK TO SAVEPOINT before_second_insert;
INSERT INTO orders (id, total) VALUES (2, 200);
COMMIT;  -- the first and third INSERTs are both saved

What you see: A multi-step transaction that hits one recoverable error (a duplicate key, a check constraint) ends up discarding ALL of its work, not just the step that actually failed, because the whole transaction was left in the aborted state with no savepoint to recover to.

Why: Once any statement inside a transaction raises an unhandled error, PostgreSQL marks the entire transaction as aborted — every subsequent command is rejected with "current transaction is aborted" until an explicit ROLLBACK (full or to a savepoint) actually clears that state. A savepoint taken before the risky statement is what makes a full ROLLBACK avoidable.

Recovering from one failed statement without losing the whole transaction

Client

Transaction

1. BEGIN
2. INSERT (id=1)
3. SAVEPOINT before_risky_insert
4. INSERT (id=1) — duplicate key
5. ROLLBACK TO SAVEPOINT before_risky_insert
6. transaction usable again — first INSERT still queued
7. COMMIT

Savepoint commands

Savepoint commands
Command	Effect
SAVEPOINT name	marks a point in the current transaction
ROLLBACK TO SAVEPOINT name	undoes changes since that point; transaction stays open
RELEASE SAVEPOINT name	discards the savepoint marker, keeping all changes since it

Remember: SAVEPOINT marks a recoverable point inside a transaction; ROLLBACK TO SAVEPOINT undoes only what happened since that point, keeping earlier work and leaving the transaction open. Without a savepoint, any unhandled error inside a transaction aborts the whole thing — only a full ROLLBACK (discarding everything) can recover.

Isolation and duration

The three isolation levels by name, and why duration itself — independent of isolation level — is a real production risk.

Transaction Isolation Levels

standardintermediate

PostgreSQL offers Read Committed (the default — each statement sees a fresh snapshot of committed data), Repeatable Read (the whole transaction sees one snapshot taken at its start), and Serializable (the strongest — behaves as if transactions ran one at a time, detecting and rejecting any execution that could not have happened serially). Read Uncommitted is accepted as a name but behaves identically to Read Committed.

Think of it as

Each level answers "how much can concurrent activity change what I see mid-transaction." Read Committed lets every new statement see the latest committed data, which is usually what you want for independent statements but can mean two SELECTs in the same transaction disagree with each other. Repeatable Read freezes your view as of the transaction's start, so your own transaction is internally consistent — but that consistency has a cost: it can fail with a serialization error rather than silently proceed on stale data. Serializable goes further still, guaranteeing the outcome is equivalent to some serial ordering, at the highest overhead. This section is the roadmap's own explicit split — the levels are introduced by name here, with the concurrency phenomena and locking mechanics they interact with covered in depth next in Isolation Levels and Concurrency.

sql

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- or, in one line:
BEGIN ISOLATION LEVEL SERIALIZABLE;
...
COMMIT;

What we're doing: Show the same two SELECTs behaving differently under Read Committed (can see a concurrent commit in between) vs Repeatable Read (frozen at transaction start), as a concrete illustration of the naming.

level_comparison.sqlsql

-- Session A, Read Committed (the default):
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- reads 1000
-- (Session B commits an UPDATE setting balance to 900, in between)
SELECT balance FROM accounts WHERE id = 1;  -- reads 900 -- DIFFERENT from the first read

-- Session A, Repeatable Read:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;  -- reads 1000
-- (Session B commits the same UPDATE, in between)
SELECT balance FROM accounts WHERE id = 1;  -- STILL reads 1000 -- frozen snapshot

5: Under Read Committed, the second SELECT sees Session B's committed change — each statement gets a fresh snapshot.
11: Under Repeatable Read, the second SELECT still sees the original value — the whole transaction shares one snapshot taken at its start.

Output

-- Read Committed: 1000, then 900
-- Repeatable Read: 1000, then 1000

Why this works: Neither behavior is "wrong" — Read Committed's per-statement freshness is exactly right for most simple, independent operations, while Repeatable Read's frozen snapshot is what a report or a multi-step calculation needs to stay internally consistent with itself, at the cost of possibly needing to retry if a real conflict is detected.

Assuming Read Committed (the default) gives a transaction a consistent view of the whole database for its entire duration

Wrong

sql

BEGIN;  -- defaults to Read Committed
SELECT sum(balance) FROM accounts;  -- reads one snapshot
-- ... other work happens, other transactions commit changes ...
SELECT sum(balance) FROM accounts;  -- reads a DIFFERENT, newer snapshot
-- the two sums may not agree, even inside "one transaction"

Better

sql

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT sum(balance) FROM accounts;  -- reads one snapshot
-- ... other work happens ...
SELECT sum(balance) FROM accounts;  -- reads the SAME snapshot, guaranteed
COMMIT;

What you see: A multi-step report or calculation run inside one Read Committed transaction produces internally inconsistent numbers — a running total that does not match a later recomputation within the same transaction — because concurrent commits were visible partway through.

Why: Read Committed deliberately gives each statement the freshest possible view, which is a feature for most workloads but a trap for any transaction that assumes its own view of the database stays fixed for its whole duration — that specific guarantee is what Repeatable Read (or Serializable) exists to provide, not the default level.

The three distinct levels

The three distinct levels
Level	Snapshot taken	Can fail with a serialization error?
Read Committed (default)	fresh, per statement	no
Repeatable Read	once, at transaction start	yes
Serializable	once, at transaction start, plus conflict detection	yes

Remember: Read Committed (default): fresh snapshot per statement. Repeatable Read: one snapshot for the whole transaction. Serializable: same snapshot guarantee plus a guarantee the outcome is equivalent to some serial execution. Repeatable Read and Serializable can both require the application to retry on a serialization error — expected, not a bug.

How Transaction Duration Affects Locks, Vacuum, Bloat and Concurrency

coreintermediate

A transaction holds its row/table locks for its entire duration, not just for the instant each statement runs — so a slow transaction blocks other writers for as long as it stays open. Separately, a long-running transaction holds back the oldest snapshot VACUUM must respect, meaning VACUUM cannot reclaim dead row versions that transaction could still theoretically need to see, letting dead tuples (and table bloat) accumulate for as long as the transaction stays open.

Think of it as

Two independent costs scale directly with how long a transaction stays open, not with how much work it does. Locks are held from acquisition until COMMIT or ROLLBACK, so a transaction that acquires a row lock and then sits idle for ten minutes blocks other writers for ten minutes, regardless of how fast the actual UPDATE itself ran. Separately, PostgreSQL cannot vacuum away a dead row version if some open transaction's snapshot might still need to see it — so one old, idle transaction can single-handedly stall cleanup for the entire table, even for rows that have nothing to do with what that transaction touched.

sql

-- find long-running / idle-in-transaction sessions
SELECT pid, state, now() - xact_start AS duration, query
  FROM pg_stat_activity
 WHERE state IN ('active', 'idle in transaction')
   AND xact_start IS NOT NULL
 ORDER BY duration DESC;

What we're doing: Show a session left "idle in transaction" after a SELECT, and how its open snapshot blocks VACUUM from reclaiming dead tuples in an unrelated table.

idle_in_transaction_vacuum_block.sqlsql

-- Session A:
BEGIN;
SELECT 1;  -- does nothing further -- sits open, "idle in transaction"

-- Session B, meanwhile, deletes and reinserts rows in a completely
-- different, unrelated table many times:
DELETE FROM logs WHERE created_at < now() - interval '1 day';
VACUUM logs;
-- VACUUM logs cannot reclaim dead tuples newer than Session A's snapshot,
-- even though Session A never touched the logs table at all

2–3: Session A has an open transaction but is doing nothing — this is the "idle in transaction" state, the most common real-world cause of this problem.
8–9: VACUUM on logs is held back by Session A's open snapshot, even though the two have no table in common.

Output

DELETE 15000
VACUUM
-- (dead tuple count remains high until Session A commits or rolls back)

Why this works: VACUUM must be conservative: it cannot remove a row version until it is certain no open transaction's snapshot could still need to see it, and PostgreSQL has no way to know in advance which tables a long-idle transaction might query next — so it must assume the worst and hold back cleanup everywhere, not just on tables the idle transaction has already touched.

Leaving a transaction open while waiting on something outside the database (a network call, user input, a slow external API)

Wrong

sql

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- application now calls a slow external fraud-check API before
-- deciding whether to proceed -- the transaction sits open the
-- entire time the network call is in flight
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Better

sql

-- do the external call BEFORE opening the transaction
-- (fraud check happens here, outside any transaction)
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- transaction is only open for the database work itself

What you see: Table bloat grows steadily worse over time despite autovacuum running on schedule, row locks are held far longer than the actual database work requires, and pg_stat_activity shows sessions stuck in "idle in transaction" for minutes at a time correlating with external API latency.

Why: A network call to an external service can take anywhere from milliseconds to a full timeout, and none of that time has anything to do with database work — but as long as the transaction stays open around it, both its locks and its effect on the vacuum horizon are fully in force, for a duration the database itself has zero control over. Moving anything that is not database work outside the transaction boundary bounds both costs to actual database work time.

One long-open transaction, two independent costs

Transaction stays open

idle, or genuinely slow

Locks held the whole time

blocks other writers

Vacuum horizon held back

dead tuples accumulate, table bloats

Remember: A transaction holds its locks for its whole duration, not just per-statement — and a long-running or idle-in-transaction session holds back the oldest snapshot VACUUM must respect, stalling cleanup across the WHOLE database, not just tables it touched. Never leave a transaction open around a network call or other non-database work.

Why Long-Running Transactions Are Dangerous in Production

standardintermediate

In production, a long-running transaction compounds every cost the previous concept described (held locks, blocked vacuum, growing bloat) with two additional production-specific risks: it makes an eventual rollback or crash more expensive to recover from, and — under sustained load — a pile-up of long transactions can push the whole database toward exhausting connections or, in extreme cases, transaction ID wraparound protection kicking in and refusing new writes entirely.

Think of it as

Every cost of a long transaction gets worse, not just longer, as production load increases: more concurrent sessions means more contention for whatever locks it holds, more write traffic means more dead tuples accumulate behind its held-back vacuum horizon, and connection pools sized for many short transactions choke when transactions start staying open far longer than assumed. None of this is visible from a single developer's test — it only shows up as an emergent, load-dependent failure mode in production.

sql

-- production triage: find and (if safe) terminate the worst offenders
SELECT pid, usename, state, now() - xact_start AS duration, query
  FROM pg_stat_activity
 WHERE xact_start IS NOT NULL
 ORDER BY duration DESC
 LIMIT 10;

-- SELECT pg_terminate_backend(pid);  -- only once confirmed safe to kill

What we're doing: Diagnose a production incident where a background job's forgotten transaction accumulates enough held locks and bloat to slow down unrelated requests.

production_triage.sqlsql

SELECT pid, usename, state, now() - xact_start AS duration, query
  FROM pg_stat_activity
 WHERE state = 'idle in transaction'
 ORDER BY duration DESC
 LIMIT 5;

--  pid  | usename |        state        | duration |          query
-- ------+---------+----------------------+----------+--------------------------
-- 41213 | worker  | idle in transaction | 02:14:07 | SELECT * FROM jobs FOR UPDATE...

SELECT pg_terminate_backend(41213);  -- after confirming it is safe to kill

1–5: This is the standard first move in a production "why is everything slow" incident touching the database — find what has been idle-in-transaction the longest.
10: Terminating the offending backend releases its locks and lets vacuum finally catch up — the fix is operational, not a schema change.

Output

pg_terminate_backend
-----------------------
 t

Why this works: A background worker that opened a transaction with FOR UPDATE and then, due to a bug, never reached its COMMIT is a common, realistic production incident shape — it accumulates locks and stalled vacuum for over two hours before anyone notices the broader slowdown, which is exactly the load-dependent, delayed-symptom failure mode that makes long transactions dangerous specifically in production rather than in development testing.

Treating a long-running transaction as merely "a bit slow" rather than an active production risk

Wrong

sql

-- a background job holds a transaction open for hours while it slowly
-- processes a large batch one row at a time, each iteration doing an
-- external API call inside the SAME transaction
BEGIN;
-- ... hours of slow, per-row external calls, all inside one transaction ...
COMMIT;

Better

sql

-- batch the work into many short transactions instead
-- (each iteration: open, do the DB work, commit, THEN do the external call)
FOR each_item IN batch LOOP
    BEGIN;
    UPDATE jobs SET status = 'processing' WHERE id = each_item.id;
    COMMIT;
    -- external API call happens here, OUTSIDE any open transaction
END LOOP;

What you see: A batch job that "works fine" in isolation causes a general, hard-to-diagnose production slowdown across unrelated tables and requests for its entire multi-hour run, and the connection pool occasionally runs out of capacity during the job's execution window.

Why: A transaction that stays open for hours is not merely doing slow work — for that entire duration it is holding locks other sessions may need and holding back the vacuum horizon for the whole database, which is a categorically different risk profile than "a slow query" that at least releases its resources when it finishes. Batching the same work into many short transactions bounds both costs to each individual step's actual duration.

Compounding production risks of a long transaction

Compounding production risks of a long transaction
Risk	Why it is worse under real load
Lock contention	more concurrent sessions queue behind the same held locks
Bloat	higher write throughput means more dead tuples accumulate behind the stalled vacuum horizon
Connection exhaustion	pools sized for short transactions run out of capacity
Wraparound protection	in extreme, sustained cases, the database can refuse new writes entirely

Remember: Long-running transactions in production compound every cost of transaction duration under real concurrency and write volume — lock contention, bloat, connection pool exhaustion, and in extreme sustained cases, transaction ID wraparound protection. Find offenders via pg_stat_activity's xact_start/backend_xmin age, and fix by keeping transactions short and never leaving one open around external work.