ACID, the log and durability

• A crash can stop the server between any two disk writes.
• The log says what the transaction planned and whether it committed.

A transfer changes two pages. Only a log written first can say, after a crash, whether both changes count or neither.

The C in ACID is the application's invariant. The database enforces only the part you declare as constraints.

Atomicity and durability come from the WAL and the commit log. Consistency comes from constraints. Isolation comes from snapshots and locks.

At commit, Postgres flushes only the WAL. The changed data pages stay in memory, and a checkpoint writes them later.

Until COMMIT, other sessions see none of the transfer. A failed statement aborts the whole transaction, so COMMIT becomes ROLLBACK.

start():
  read pg_control1
  IF the last shutdown was clean: RETURN ready
  FOR EACH WAL record from the redo point to the end2:
    IF it carries a page image: copy it over the page3
    ELSE IF page LSN < record LSN4: apply it to the page
  a transaction with no COMMIT record stays aborted5
  write an end-of-recovery checkpoint
  RETURN ready

// Crash kills every server process with SIGKILL, as a power cut or an out-of-memory kill would.
// It first stops all of them with SIGSTOP, so no process can write anything between the kills.
// Shared memory, and the WAL buffers in it, is gone.
func (c *Cluster) Crash() error {
  pm, err := c.postmasterPID()
  if err != nil {
    return err
  }
  out, err := exec.Command("pgrep", "-P", strconv.Itoa(pm)).Output()
  if err != nil {
    return fmt.Errorf("list server processes: %w", err)
  }
  pids := []int{pm}
  for f := range strings.FieldsSeq(string(out)) {
    pid, err := strconv.Atoi(f)
    if err != nil {
      return fmt.Errorf("read pid %q: %w", f, err)
    }
    pids = append(pids, pid)
  }
  for _, sig := range []syscall.Signal{syscall.SIGSTOP, syscall.SIGKILL} {
    for _, pid := range pids {
      if err := syscall.Kill(pid, sig); err != nil && !errors.Is(err, syscall.ESRCH) {
        return fmt.Errorf("signal %v to %d: %w", sig, pid, err)
      }
    }
  }
  deadline := time.Now().Add(10 * time.Second)
  for c.running() {
    if time.Now().After(deadline) {
      return fmt.Errorf("postmaster %d still runs 10 s after SIGKILL", pm)
    }
    time.Sleep(10 * time.Millisecond)
  }
  return nil
}

On restart Postgres replays the WAL from the last checkpoint. Anything not flushed before the crash never happened.

The test kills every server process at once, as an out-of-memory kill or a power cut would. The WAL writer was set to wake every 10 s, so the unflushed records stay in memory.

With synchronous_commit off, COMMIT returns before the flush. A crash in that window loses the transaction as a whole.

first order

8,632 bytes of WAL: a 7,588-byte heap page image plus an index page image.

next order

168 bytes: the row, the index entry and a 34-byte commit record.

page

8 kB. A drive can write it partly before a crash.

• Frequent checkpoints mean more page images and more WAL.
• Turn full_page_writes off only on storage that writes 8 kB atomically.
• wal_compression makes the images smaller.

The first change to each page after a checkpoint logs the whole page. That costs WAL, and it lets recovery repair a page that a crash tore.

Postgres gives me a write-ahead log, a commit log, a per-transaction durability setting and group commit. The drive must give me an honest flush.

commit(tx):
  append the COMMIT record1 to the WAL buffers
  IF synchronous_commit = off:
    RETURN ok                           // the WAL writer flushes it soon2
  flush the WAL up to the COMMIT record   // one flush serves every waiter3
  IF local, or no synchronous standby4:
    RETURN ok
  wait until a standby has the record:
    remote_write: written to its OS
    on:           flushed to its disk
    remote_apply5: replayed, visible to reads
  RETURN ok

// InsertOrder inserts one order in its own transaction at a synchronous_commit level, and reads
// the WAL positions right after COMMIT returns. With "on", COMMIT waits until the commit record
// is flushed. With "off", it returns at once and the WAL writer flushes the record later.
func InsertOrder(ctx context.Context, conn *pgx.Conn, id int, level string) (Commit, error) {
  var c Commit
  if _, err := conn.Exec(ctx, "SET synchronous_commit = "+pgx.Identifier{level}.Sanitize()); err != nil {
    return c, fmt.Errorf("set synchronous_commit %s: %w", level, err)
  }
  var flush string
  if err := conn.QueryRow(ctx, schema["positions"]).Scan(&c.Start, &flush); err != nil {
    return c, fmt.Errorf("read wal positions: %w", err)
  }
  err := pgx.BeginFunc(ctx, conn, func(tx pgx.Tx) error {
    if _, err := tx.Exec(ctx, schema["insert_order"], id); err != nil {
      return fmt.Errorf("insert order %d: %w", id, err)
    }
    return tx.QueryRow(ctx, "SELECT pg_current_xact_id()::text").Scan(&c.XID)
  })
  if err != nil {
    return c, fmt.Errorf("order %d: %w", id, err)
  }
  if err := conn.QueryRow(ctx, schema["positions"]).Scan(&c.Insert, &c.Flush); err != nil {
    return c, fmt.Errorf("read wal positions: %w", err)
  }
  if err := conn.QueryRow(ctx, "SELECT $1::pg_lsn >= $2::pg_lsn, $2::pg_lsn - $3::pg_lsn",
    c.Flush, c.Insert, c.Start).Scan(&c.OnDisk, &c.Bytes); err != nil {
    return c, fmt.Errorf("compare wal positions: %w", err)
  }
  return c, nil
}

COMMIT appends a commit record, then waits for the flush that synchronous_commit names. Off waits for nothing; remote_apply waits for a standby to replay.

• "Primary lost" assumes a failover to the synchronous standby. With no standby, on means local.
• Median commit time for 1 client, on one laptop with the standby on the same machine. A real network adds its round trip.
• Off never corrupts data. A lost transaction is lost as a whole.

I keep on for money. I use off per transaction for data I can lose, and a synchronous standby when losing the machine must lose nothing.

• Measured with a full drive-cache flush: 1 commit per flush with 1 client, 11 with 16 clients, 35 with 64.
• Latency for each client stays near 1 to 2 flushes: 20 ms alone, 40 ms with 64 clients.

Only one WAL flush runs at a time. Commits that arrive during it share the next flush, so throughput grows with clients.

• Full flush: wal_sync_method = fsync_writethrough, which empties the drive cache on macOS.
• Drive cache: the macOS default. The write reaches the drive's cache, not durable storage.
• commit_delay = 1 ms cut 16 clients from 24,604 to 9,325 commits a second. On the full flush it changed little.

On an honest flush, one client gets about 50 commits a second. Group commit takes 64 clients to about 1,500. A flush that stops at the drive cache is 100 times faster and not durable.

Drive cache: the macOS default flush, which stops at the drive's cache. Here it stands in for a PLP drive. Do not turn fsync off to go faster. A crash then corrupts the database, not only the last commits. synchronous_commit = off gives most of the speed with a bounded loss.

I start with synchronous_commit on and an honest flush. I batch and add concurrency before I trade durability, and I trade it per transaction, never for the whole database.