Savepoints

A savepoint is a named bookmark inside a transaction. ROLLBACK TO SAVEPOINT rewinds to the bookmark — undoing everything after it — while the transaction itself stays alive and can continue. This is also the escape hatch from the "one error aborts everything" rule you saw in the previous lesson.

If you've ever used "nested transactions" in an ORM, you were using savepoints — PostgreSQL has no actual nested transactions. Rails' transaction(requires_new: true), Django's atomic() inside atomic(), and SQLAlchemy's begin_nested() all emit SAVEPOINT under the hood. Mind the Rails default, though: a nested transaction do block without requires_new: true creates no savepoint at all — it merely joins the outer transaction, and a raise ActiveRecord::Rollback inside it is swallowed without rolling anything back (the Rails docs warn about exactly this).

Recovering from an error mid-transaction

A> BEGIN;
BEGIN

A> INSERT INTO items VALUES (2, 'gadget');
INSERT 0 1

A> SAVEPOINT before_risky;
SAVEPOINT

A> INSERT INTO items VALUES (3, 'widget'); -- unique_violation — the transaction is aborted
ERROR:  23505: duplicate key value violates unique constraint "items_name_key"

Without the savepoint this transaction would be doomed. With it, we rewind past the failure and carry on.

A> ROLLBACK TO SAVEPOINT before_risky;
ROLLBACK

A> INSERT INTO items VALUES (3, 'doohickey');
INSERT 0 1

A> COMMIT;
COMMIT

A> SELECT id, name FROM items ORDER BY id; -- survived — it predates the savepoint
 id |   name    
----+-----------
  1 | widget    
  2 | gadget    
  3 | doohickey 
(3 rows)

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

Nesting and RELEASE

Savepoints stack. Rolling back to an outer savepoint destroys the inner ones — and RELEASE keeps the work but removes the bookmark:

A> BEGIN;
BEGIN

A> INSERT INTO steps VALUES (1);
INSERT 0 1

A> SAVEPOINT outer_sp;
SAVEPOINT

A> INSERT INTO steps VALUES (2);
INSERT 0 1

A> SAVEPOINT inner_sp;
SAVEPOINT

A> INSERT INTO steps VALUES (3);
INSERT 0 1

Rolling back to the OUTER savepoint discards rows 2 and 3 — and inner_sp itself.

A> ROLLBACK TO SAVEPOINT outer_sp;
ROLLBACK

A> ROLLBACK TO SAVEPOINT inner_sp; -- no such savepoint — it was destroyed by the outer rollback
ERROR:  3B001: savepoint "inner_sp" does not exist

A> ROLLBACK TO SAVEPOINT outer_sp; -- recover from that error, same trick as before
ROLLBACK

RELEASE keeps the work done after the savepoint, but you can no longer rewind to it.

A> INSERT INTO steps VALUES (4);
INSERT 0 1

A> RELEASE SAVEPOINT outer_sp;
RELEASE

A> COMMIT;
COMMIT

A> SELECT n FROM steps ORDER BY n;
 n 
---
 1 
 4 
(2 rows)

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

The through-line: ROLLBACK TO SAVEPOINT un-aborts a failed transaction, losing only the work after the savepoint, and rolling back to an outer savepoint destroys the inner ones (touch one afterwards and you get 3B001). RELEASE SAVEPOINT says "I no longer need to rewind here" while keeping the changes as part of the transaction. None of this is free, though: every savepoint starts a subtransaction, and once you pass 64 open subtransactions per backend, the manual warns that "the storage I/O overhead increases significantly". A savepoint per row in a hot loop is a known performance trap — fine in moderation, ruinous in bulk.

Further reading

• PostgreSQL docs: SAVEPOINT · ROLLBACK TO SAVEPOINT · RELEASE SAVEPOINT
• PostgreSQL docs: Subtransactions — what a savepoint actually costs
• The same lesson on MySQL