Transactions and ACID in PostgreSQL

A transaction is a group of statements that either all happen or none happen. That one guarantee is what lets you move money between accounts, or create an order and decrement inventory, without ever leaving the database in a half-finished state. Every serious application leans on transactions, usually without the developer thinking about them, because the ORM wraps each request in one. Understanding what they actually promise is what lets you reason about correctness under failure and concurrency.

This is part 17 of the PostgreSQL Masterclass and the start of the transactions module, following index maintenance.

The basic shape

You wrap statements in BEGIN and COMMIT. If anything goes wrong, ROLLBACK undoes everything since BEGIN:

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

If the second update fails, or the app crashes before COMMIT, the first update never took effect. The money is never in limbo, deducted from one account but not added to the other. That atomicity is the whole reason transactions exist.

What ACID actually means

ACID is four guarantees, and each one solves a specific real failure.

Atomicity is all-or-nothing. A transaction's changes apply completely or not at all. A crash mid-transaction rolls everything back on restart, so you never see a partial write.

Consistency means a transaction moves the database from one valid state to another, respecting all constraints. If a transaction would violate a CHECK, foreign key, or unique constraint, it fails and rolls back rather than committing invalid data. The constraints from earlier in the series are what enforce this.

Isolation governs what concurrent transactions see of each other. If two transactions run at once, isolation decides whether one sees the other's uncommitted or in-progress changes. This is the subtle one, and the whole next article is about it.

Durability means once a transaction commits, it survives a crash. Postgres achieves this with the write-ahead log: changes are written to the WAL and flushed to disk before COMMIT returns, so even if the server loses power a moment later, the committed data is recoverable.

Transactions are automatic, but boundaries matter

Every statement in Postgres runs in a transaction. If you don't write BEGIN, each statement is its own auto-committed transaction. That's why a single UPDATE affecting a thousand rows is atomic on its own: all thousand change or none do.

The decisions you make are about boundaries. A few principles keep transactions healthy:

Keep them short. A transaction holds locks and keeps old row versions alive until it ends. A transaction left open for minutes, especially an idle one, blocks other work and bloats the database. Open the transaction as late as possible, do the work, commit promptly.

Don't do slow non-database work inside one. Calling an external API or waiting on a user while a transaction is open holds resources the whole time. Read what you need, commit, then make the API call.

Group only what must be atomic. Wrap the operations that have to succeed or fail together, and nothing more. A transaction isn't a place to batch unrelated work for convenience.

Savepoints: partial rollback

Sometimes you want to try something inside a transaction and undo just that part if it fails, without losing the whole transaction. Savepoints give you that:

BEGIN;
  INSERT INTO orders (customer_id, total) VALUES (5, 100);
  SAVEPOINT before_discount;
  UPDATE orders SET total = total * 0.9 WHERE customer_id = 5;
  -- changed our mind about the discount
  ROLLBACK TO before_discount;
COMMIT;   -- the order is created, the discount is not

Savepoints are also how error handling works inside procedural code and how many frameworks implement nested transactions. Under the hood, a "nested transaction" in your ORM is usually a savepoint, since Postgres doesn't have true nested transactions.

Errors abort the whole transaction

One behavior surprises people coming from other databases. In Postgres, once any statement in a transaction raises an error, the entire transaction is aborted and every subsequent statement fails with "current transaction is aborted" until you roll back. You can't just catch the error and keep going in the same transaction. If you want to continue past a possible error, wrap the risky statement in a savepoint and roll back to it on failure. This is strict, and it's what prevents you from accidentally committing work built on top of a failed step.

Why this is the foundation

Transactions are the base layer under everything else in this module. Isolation levels are about what concurrent transactions see. Locks are about how transactions coordinate access. Deadlocks are transactions waiting on each other. None of it makes sense without the core idea that a transaction is an atomic, isolated, durable unit of work.

The practical takeaways are small but they matter: wrap operations that must be atomic together, keep transactions short and free of slow external calls, use savepoints when you need to recover from part of a transaction, and remember that any error aborts the whole thing. Get those right and the database quietly protects your data through crashes and concurrency.

Next, we get into isolation levels, the part of ACID that decides what one transaction sees of another's in-flight work, and the concurrency anomalies each level does and doesn't prevent.