9 ACID, honestly

We committed three rows, got COMMIT back three times, killed the server, started it again, and the rows were gone. Postgres was working exactly as configured. One session setting, synchronous_commit = off, had traded those rows for speed, and the trade was in the documentation all along.

ACID gets treated like a badge: the database “is ACID”, so your data is safe. It’s four separate promises, each kept by one specific mechanism, each with a price, a switch that turns it off, and an edge where it stops. Most data loss in Postgres-backed systems happens at those edges, not inside them.

Four promises, four mechanisms

Here’s the whole chapter on one table. The rest of it proves each row.

They fail independently, so we’ll take them one at a time.

Atomicity: nothing is ever undone

Atomicity means a transaction happens completely or not at all. You probably picture an undo log: on rollback, the database puts the old values back.

Postgres has no undo. Rollback doesn’t touch the table at all.

A rollback leaves its rows on the page

Every row version carries the ID of the transaction that created it (xmin), and whether it counts depends on what the commit log, pg_xact, says about that transaction (Updates are inserts). It works like a checkbook register: a cancelled check isn’t erased, it’s stamped VOID.

Insert three rows into a small credits table of account balances and roll back. pageinspect still finds all three tuples on the page, byte for byte what the insert wrote, and pg_xact_status() says their transaction aborted. Two bits in pg_xact are the only thing hiding them. Every query that reads them checks that status and skips them, and they stay until vacuum removes them.

That’s the whole of rollback: flip a status, leave the mess for later. It’s why a rollback takes the same time for one row or ten million.

A failed statement changes nothing, and still costs

Atomicity applies to every statement too. Charge every account 10 credits, where account 1000, the last row, only has 5:

update credits set balance = balance - 10;

ERROR:  new row for relation "credits" violates check constraint
        "credits_balance_check"
DETAIL:  Failing row contains (1000, -5).

Nothing changed: 999 balances are still 100. But the update wrote 999 new row versions before it hit row 1000, and they’re all still in the table, dead:

 pages | tuple_count | dead_tuple_count
-------+-------------+------------------
    11 |        1000 |              999

The table grew from 6 pages to 11 for a statement that changed nothing, and wrote over 100,000 bytes of WAL. A failed bulk update costs about as much I/O as a successful one and leaves a table’s worth of dead tuples for vacuum. A batch job that fails on its last row and retries every five minutes is a bloat machine.

Validate before you write when failure is likely (where balance >= 10, or a pre-check query), and run big changes in batches so a failure wastes one batch, not the table. Inside a transaction, a savepoint gives the same all-or-nothing at a finer grain (Savepoints contain the damage).

begin;
insert into credits values (5001, 50), (5002, 50), (5003, 50);
select pg_current_xact_id();   -- 854733
rollback;

select lp, t_xmin from heap_page_items(get_raw_page('credits', 5))
where t_xmin = '854733';

 lp | t_xmin
----+--------
 77 | 854733
 78 | 854733
 79 | 854733

begin;
insert into credits values (5001, 50), (5002, 50), (5003, 50);
select pg_current_xact_id() as aborted_xid \gset
rollback;

select lab.prove(
  'after rollback, all three row versions are still on the page',
  (select count(*) = 3
   from heap_page_items(get_raw_page('credits', (pg_relation_size('credits') / 8192 - 1)::int))
   where t_xmin::text = :'aborted_xid'));

select lab.prove(
  'pg_xact says their transaction aborted',
  pg_xact_status(:'aborted_xid') = 'aborted');

-- s1 holds a snapshot older than the failed UPDATE, so nothing prunes early.
call lab.on('s1', 'begin isolation level repeatable read');
select x as _ from dblink('s1', 'select 1') as t(x int) \gset

select lab.prove(
  'the failed UPDATE left 999 dead row versions behind',
  (select dead_tuple_count = 999 from pgstattuple('credits')));

select lab.prove(
  'the failed UPDATE grew the table from 6 to 11 pages',
  :pages_before = 6 and pg_relation_size('credits') / 8192 = 11);

select lab.prove(
  'the failed UPDATE still wrote over 100,000 bytes of WAL',
  lab.my_wal() - :wal_before > 100000);

select lab.prove(
  'no query sees them',
  (select count(*) = 0 from credits where account_id > 5000));
select lab.prove(
  'the charge failed on its last row, and all 999 other rows are unchanged',
  (select count(*) filter (where balance = 100) = 999 from credits));

Commit is one small record

So what makes a transaction committed? The 34-byte COMMIT record from A WAL record is small; a page image is not: write it, flush the WAL through it, mark the transaction committed in pg_xact, then make it visible. The moment the flush returns is the moment of commit. A crash a microsecond later replays the record and the commit stands; a microsecond earlier, the transaction never happened (Recovery only redoes). A rollback’s ABORT record isn’t even flushed: a transaction with no commit record counts as aborted anyway.

Consistency is the part you write

The C is the odd letter out. Atomicity, isolation, and durability are things the database does. Consistency means “the transaction moves the data from one valid state to another”, and “valid” is defined by you: not null, check, unique, foreign keys, and exclusion constraints (Constraints, chosen on purpose is their home). Postgres enforces exactly the rules you declare, and not one more.

Constraints fire per statement, or at commit

Postgres checks most constraints as it writes each row, and foreign keys at the end of each statement. A unique, primary key, foreign key, or exclusion constraint declared deferrable initially deferred waits until COMMIT instead, so a transaction can insert invoice lines before their invoice. If the invoice never arrives, the COMMIT fails. (check and not null can’t be deferred.)

So COMMIT can fail. Deferred constraints, serialization failures, and lost connections all surface there, and after any earlier error COMMIT quietly comes back as ROLLBACK (One error aborts the whole transaction). Code that ignores the result of commit will tell users their data is saved when it isn’t.

create table invoice_lines (
  invoice_id bigint not null
    references invoices (id) deferrable initially deferred,
  amount     int not null
);

begin;
insert into invoice_lines values (777, 10);   -- no invoice 777: fine, for now
commit;

INSERT 0 1
ERROR:  insert or update on table "invoice_lines" violates foreign key
        constraint "invoice_lines_invoice_id_fkey"
DETAIL:  Key (invoice_id)=(777) is not present in table "invoices".

Rules across rows are invisible to constraints

Most real business rules span rows: “a free account has at most 3 seats”, “debits equal credits”. A check constraint sees only the row being written, so the usual next move is a trigger that counts the account’s seats and raises an exception at 3.

It works, one request at a time. Now two requests add a seat to an account that has 2, at the same moment. Each trigger counts 2, because the other insert hasn’t committed. Both commit. The account has 4 seats, and the trigger never raised.

Nothing was wrong with the code. A rule that reads other rows is only as strong as the isolation it runs under. Three fixes, strongest first:

1. Make it a constraint. “No two rows conflict” is an exclusion constraint (An exclusion constraint makes overlaps impossible), and a count limit can become one: a seat_number with check (seat_number between 1 and 3) and unique (account_id, seat_number) caps seats at three.
2. Run the writers at serializable, which aborts one of them with a retryable error.
3. Serialize on a parent row: perform 1 from accounts where id = new.account_id for no key update at the top of the trigger. Use for no key update, not for update: for update also blocks the foreign key checks of every insert, into any table, that references that account, until the trigger’s transaction ends.

create function seats_limit() returns trigger language plpgsql as $$
begin
  if (select count(*) from seats where account_id = new.account_id) >= 3 then
    raise exception 'account % already has 3 seats', new.account_id;
  end if;
  return new;
end $$;

insert into seats values (1, 1), (1, 2);

call lab.on('s1', 'begin');
call lab.on('s1', 'insert into seats values (1, 3)');
call lab.on('s2', 'begin');
call lab.on('s2', 'insert into seats values (1, 4)');
call lab.on('s1', 'commit');
call lab.on('s2', 'commit');

select lab.prove(
  'two concurrent inserts each counted 2 seats, so the trigger let both in: 4 seats',
  (select count(*) = 4 from seats where account_id = 1));

Isolation is a dial, not a guarantee

Isolation decides which effects of concurrent transactions yours can see. The ideal is that transactions behave as if they ran one at a time. Postgres offers that, at a price, and defaults to something weaker. Here’s the model; Pick an isolation level on purpose is the practical side.

Only serializable rules out every anomaly

An anomaly is a result no serial order of the transactions could produce. The SQL standard defines its levels by three of them; Berenson et al. showed in 1995 that the list was incomplete. What Postgres does at each level:

• There’s no dirty read anywhere. Postgres accepts read uncommitted and gives you read committed.
• Read committed takes a snapshot per statement, repeatable read one per transaction (A snapshot picks one version). Request handlers want the first. A report that runs five queries and adds them up wants the second, or its totals won’t reconcile.
• Only serializable catches the bottom two rows, by making you retry. Lost update and write skew are demonstrated in Read committed loses updates computed in the app and Serializable catches the write skew repeatable read misses. The last row is here.

Even a read-only transaction can see the impossible

Here’s the anomaly that surprises people most, because the victim never writes anything. Billing works in periods. A control row holds the current period; charges are recorded against it; at month end someone closes the period by bumping the number, and a report totals the closed period. Three transactions:

T1: add a charge         T2: close period 1       T3: report
-----------------------  -----------------------  -----------------------
begin;
insert into charges
  select current_period
  ... (reads period 1)
                         begin;
                         update billing_period
                           set current_period
                             = 2;
                         commit;
                                                  begin;
                                                  select current_period;
                                                    -> 2: 1 is closed
                                                  select count(*),
                                                    sum(amount)
                                                    from charges
                                                    where period = 1;
                                                    -> 3, 600
                                                  commit;
commit;

At repeatable read, everything succeeds. T3 reported period 1 final at 3 charges totalling 600. Then T1 committed its charge into period 1, which now holds 4 charges and 700. No serial order produces that: if T1 ran before T2, the report would include its charge; if after, the charge would land in period 2.

At serializable, the report gets the same 3 charges, but T1’s commit fails with could not serialize access due to read/write dependencies among transactions. T1 retries, reads period 2, and its charge lands where it belongs. No two transactions wrote the same row, so only a level that tracks reads can catch it.

call lab.run_billing('repeatable read');
select lab.prove(
  'then T1 commits fine',
  lab.try_on('s1', 'commit') = 'ok');
select lab.prove(
  'and the closed period 1 now has 4 charges, 700',
  (select count(*) = 4 and sum(amount) = 700 from charges where period = 1));

call lab.run_billing('serializable');
select lab.prove(
  'but now T1''s commit fails with a serialization error',
  lab.try_on('s1', 'commit') like 'could not serialize access%');

Durability is an fsync

Durability means a committed transaction survives a crash: its commit record reached disk before COMMIT returned. Commit means flush measured what that costs: one fsync per commit, and about 130 commits per second for one client on a disk with 7.7 ms flushes. Here we break it on purpose.

synchronous_commit = off loses a window

With synchronous_commit = off, COMMIT returns before its record is flushed, so a crash can take up to about 600 ms of acknowledged commits (synchronous_commit = off can lose about 600 ms of commits). The risk is loss, never corruption. The subtle part: the transaction is visible before it’s durable. Other sessions can read its rows, act on them, send an email about them, and then the crash erases them.

A real crash took three acknowledged commits

This chapter’s lab runs its own server so it can kill it. It widens the window (wal_writer_delay at 10 seconds, the maximum), commits three rows with synchronous_commit = off, and checks their WAL is still only in shared memory. Then it sends SIGKILL to the container: no shutdown checkpoint, no final flush. On restart, recovery replays to the end of valid WAL, and select count(*) from crash_test returns 0.

Three acknowledged commits, gone. The same inserts with the default synchronous_commit = on, and the same kill, come back with all three rows. And with wal_writer_delay at 1 second, rows committed more than 3 seconds before the kill survive. The window is real, and exactly as wide as the docs say.

So use it per transaction, only for writes you could lose.

LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 0/1796688
LOG:  invalid record length at 0/1796758: expected at least 24, got 0
LOG:  redo done at 0/17966E0 ...

prove "three async commits returned, and their WAL was still only in shared memory" $lost_ok
prove "after SIGKILL and restart, all three async-committed rows are gone" \
  holds "select count(*) = 0 from crash_test"
prove "after SIGKILL and restart, all three sync-committed rows are there" \
  holds "select count(*) = 3 from crash_test where how = 'sync'"
prove "after SIGKILL, async commits older than 3 x wal_writer_delay survive" \
  holds "select count(*) = 2 from crash_test where id in (4, 5)"

fsync = off and full_page_writes = off belong only in CI

Two settings remove durability for the whole server. fsync = off stops Postgres forcing anything to disk, and a power failure can leave data files and WAL out of step: “unrecoverable data corruption”, in the docs’ words. full_page_writes = off drops the page images that repair torn pages (The first change after a checkpoint logs the whole page), safe only on storage that can’t tear an 8 KB write, such as ZFS. Both make benchmarks look great. Use them on a throwaway CI database, and nowhere else.

fsync is only as honest as the drive

fsync only works if the drive tells the truth when it says “done”. Many consumer drives acknowledge a write once it’s in their own volatile cache, which a power cut erases. Use storage with power-loss protection, run pg_test_fsync on the volume, and distrust flushes faster than the hardware could physically manage (Where the time goes).

Keep data checksums on, too (the initdb default since PostgreSQL 18). A flipped bit from a disk or controller becomes an error instead of silently wrong rows. They detect damage; backups repair it (With checksums, even a read writes WAL has their WAL cost).

Durable here is not durable everywhere

Everything so far is about one machine’s disk. With default asynchronous replication, a commit returns once it’s flushed on the primary, and replicas get it a moment later. Fail over inside that moment and commits your users saw succeed are gone, with the new primary perfectly consistent (Asynchronous is the default, and it can lose commits). Synchronous replication closes the window for a network round trip per commit (Synchronous commit costs a round trip).

Decide how many acknowledged commits you can lose, as a number, before the incident decides for you.

ACID stops at one transaction in one database

Every guarantee above has the same boundary: one transaction, in one database. Your business operations rarely respect it.

Autocommit splits one operation into two

A transfer of 30 credits from account 1 to account 2 is one business operation. Written as two statements with no begin, it’s two transactions, and many ORMs work this way by default: each save() is its own commit. Crash the app between the two:

update credits set balance = balance - 30 where account_id = 1;
-- the process dies here
update credits set balance = balance + 30 where account_id = 2;

sum(balance) dropped by exactly 30. Thirty credits vanished, and no database guarantee was broken: each statement was atomic, consistent, isolated, and durable. The operation wasn’t, because nobody told the database it was one. Wrapped in begin and commit, the same crash rolls everything back.

Put every business operation in one explicit transaction, and find out what your ORM does by default. Keep it short: no HTTP calls or waiting on a user inside it, because an open transaction holds locks and holds back vacuum.

call lab.on('s1', 'update credits set balance = balance - 30 where account_id = 1');
-- The app crashes here, before its second statement runs.
select dblink_disconnect('s1') = 'OK' as gone \gset
select lab.prove(
  'two autocommit statements, a crash between them: 30 credits vanished',
  (select sum(balance) = :total_before - 30 from credits));

-- ... restore, then the same with an explicit transaction:
call lab.on('s1', 'begin');
call lab.on('s1', 'update credits set balance = balance - 30 where account_id = 1');
select dblink_disconnect('s1') = 'OK' as gone \gset
select lab.prove(
  'one explicit transaction, the same crash: nothing changed',
  (select sum(balance) = :total_before from credits)
  and (select balance = 100 from credits where account_id = 1));

Two-phase commit is rarely the answer

When one operation spans two databases, the textbook answer is two-phase commit. Postgres supports its side (prepare transaction, then commit prepared), but a prepared transaction belongs to no session: if the coordinator dies, it sits there through restarts, holding locks and holding back vacuum. That’s why max_prepared_transactions defaults to 0. And a message broker, a payment API, or an email service doesn’t speak two-phase commit at all.

begin;
insert into crash_test values (6, 'prepared');
update crash_test set how = 'locked by a prepared transaction' where id = 1;
prepare transaction 'transfer-42';

crash
prove "after SIGKILL and restart, the prepared transaction is still in pg_prepared_xacts" \
  holds "select count(*) = 1 from pg_prepared_xacts where gid = 'transfer-42'"
prove "it still holds its row lock: an update of row 1 gives up waiting" \
  fails_with "lock timeout" -c "set lock_timeout = '500ms'" -c "update crash_test set how = 'x' where id = 1"
prove "it holds back vacuum: a row deleted after it was prepared stays dead, not removed" \
  holds "select dead_tuple_count >= 1 from pgstattuple('crash_test')"

Put the message in the same transaction as the order

Here’s the case that bites almost every application. Placing an order writes the order and publishes an “order placed” message for other services. Two systems, so whichever you do first, a crash between them leaves them disagreeing: an order nobody hears about, or a message about an order that doesn’t exist.

The fix is to stop writing to two systems. Write the message into a table, in the same transaction as the order, and let a separate process deliver it:

with o as (
  insert into orders (account_id, total_cents) values (42, 1999)
  returning id, account_id, total_cents
)
insert into outbox (topic, payload)
select 'order.placed',
       jsonb_build_object('order_id', id, 'account_id', account_id,
                          'total_cents', total_cents)
from o;

Now atomicity covers both: the order and its message commit together or not at all. A relay process claims a batch with for update skip locked, publishes it, and deletes what it sent, in one transaction. That transaction is the one deliberate exception to “no network calls inside a transaction”: keep its batches small and give the relay’s role an idle_in_transaction_session_timeout, so a stuck broker can’t hold it open.

If the relay dies after publishing and before its delete commits, the next relay sends those messages again. That’s at-least-once delivery, and the deal is that consumers deduplicate by message id. It beats “sometimes the message is lost”. The same shape works for emails and charges: record the intent in the transaction, act after commit, and make the action safe to repeat (Retry the whole transaction).

begin;
select id, topic, payload from outbox
order by id limit 100
for update skip locked;
-- publish each message, and wait for the broker's acknowledgment
delete from outbox where id = any ($1);   -- the ids you published
commit;

select lab.prove(
  'every order has its message, and a rolled-back order has neither',
  (select count(*) = 2 from orders) and (select count(*) = 2 from outbox)
  and not exists (select 1 from orders o
                  where not exists (select 1 from outbox m
                                    where (m.payload ->> 'order_id')::bigint = o.id)));

select lab.prove(
  'two relays with FOR UPDATE SKIP LOCKED claim disjoint batches of 5',
  (select count(*) = 10 and count(distinct id) = 10 from claimed));

select lab.prove(
  'the relay that died after publishing left its batch in the outbox: it gets sent again',
  (select count(*) = 5 from outbox where id in (select id from claimed where relay = 's2')));

The short version

• Rollback undoes nothing: aborted row versions stay on the page until vacuum. A failed statement still writes its rows, its WAL, and its bloat.
• A transaction is committed the moment its commit record is flushed. Check the result of every COMMIT.
• Consistency is only the rules you declare. A check that reads other rows is a race; use a constraint, serializable, or a lock on a parent row.
• Read uncommitted is read committed. Only serializable catches write skew and the read-only anomaly, and it needs retries.
• synchronous_commit = off can lose about 600 ms of acknowledged, already-visible commits. Use it per transaction, for data you can lose.
• Never run production with fsync = off or full_page_writes = off. Use storage with power-loss protection and keep checksums on.
• A commit is durable on one machine. With asynchronous replicas, a failover can lose acknowledged commits; pick your number before an incident does.
• Put each business operation in one explicit transaction. To notify another system, write an outbox row in the same transaction and relay it.

Further reading

• Transaction isolation, PostgreSQL documentation. The exact guarantees of each level, including serializable’s read-only deferrable mode.
• Reliability, PostgreSQL documentation. What a durable commit requires from disks, caches, and controllers, and how to test yours.
• Asynchronous commit, PostgreSQL documentation. The risk window of synchronous_commit = off, in the developers’ own words.
• A Critique of ANSI SQL Isolation Levels, Berenson, Bernstein, Gray, Melton, O’Neil, and O’Neil, SIGMOD 1995. The paper that showed the standard’s anomaly list was incomplete and defined snapshot isolation.
• Serializable Snapshot Isolation in PostgreSQL, Ports and Grittner, PVLDB 2012. How Postgres’s serializable level works, from the people who built it. The SSI wiki page has the batch-closing example this chapter adapts.