Fieldnotes / engineering

Replication and consistency

Explanation

Keep another copy

Replication maintains another copy of stored data. In this single-writer example the application writes to the primary.

A replica is another data copy. Keeping it updated requires data to travel and be applied.

  1. 0s The primary stores application data. The replica holds another copy.
  2. 7s A change is written to the primary. The primary now has revision 8.
  3. 14s The change travels to the replica. The replica must apply it.
  4. 21s The replica reaches revision 8. Both copies now contain the change.

A replica can lag

With asynchronous replication, the primary can acknowledge a write while the replica is behind. A read from that replica can return old data.

A stale read does not prove the write failed. It tells you which data that particular reader observed.

  1. 0s The primary has saved revision 8. The replica is still at revision 7.
  2. 7s The application reads the replica. That query sees the older revision.
  3. 14s Revision 7 comes back successfully. The read is stale, not a failed write.
  4. 21s The primary still holds revision 8. Different copies can answer differently.

Check the revision you need

A release assistant needs to verify its own change. It checks the returned application revision rather than treating any successful response as confirmation.

In this example, a fresh primary query sees the saved revision. Transaction snapshots and concurrent writers still matter in real systems.

  1. 0s The verification expects revision 8. A replica response says revision 7.
  2. 7s Use a fresh read from the primary. Verify against the required revision.
  3. 14s The primary returns revision 8. This matches the saved change.
  4. 21s Match the observation to the decision. A successful query alone is not enough.

Received is not applied

PostgreSQL sends write-ahead log records to standbys. Receiving or flushing those records differs from replaying them into query-visible state.

What an acknowledgement proves depends on the configured replication mode. Do not equate received bytes with a query seeing the change.

  1. 0s The primary sends a change record. The replica receives the record.
  2. 7s Receipt does not mean replay finished. Queries may still observe revision 7.
  3. 14s The replica replays the change. Its query-visible state advances.
  4. 21s Now a fresh query can see revision 8. Check what your acknowledgement means.

Failover can lose data

If an asynchronous primary fails before a recent change reaches the standby, promoting that standby can lose the acknowledged change.

Replication modes trade latency, availability and durability. This illustrates an asynchronous failure window, not every replication configuration.

  1. 0s Async replication: v8 was acknowledged. The standby only has revision 7.
  2. 7s The primary fails before shipping v8. Its newest change is absent elsewhere.
  3. 14s The standby is promoted. It can serve the data it actually has.
  4. 21s Revision 8 is missing after failover. Choose durability requirements explicitly.
Read the guide

Helpful first: Tool design, Timeouts and retries.

The idea

Replication maintains copies of data. Consistency describes what reads may observe. PostgreSQL asynchronous streaming replication can leave a read replica behind the primary; failover can lose changes absent from the promoted standby. PostgreSQL ships its write-ahead log (WAL), the record used to replay database changes. A replication acknowledgement does not necessarily mean a query can see the change. These distinctions matter when an agent treats tool output as evidence.

Where it helps

A person might pause after seeing an old dashboard value. An automated workflow needs an explicit decision rule: accept the observation, verify elsewhere, or stop. Otherwise a successful write followed by a stale read can become an unnecessary second mutation.

Teams adopting agents need this when an automated decision depends on a recently changed record exposed through multiple read paths. Keep it outside the first adoption lessons, but consult it while designing mutation tools and diagnosing contradictory verification results.

An internal release assistant changes release R42 from draft to ready, producing application revision v8. Its verification tool reads a reporting replica still at v7. In this illustrative single-writer scenario, the write succeeded; the observation is stale. The tool reads the primary using a fresh query and compares the returned revision before the assistant continues.

What you will be able to check

  • Draw the actual write and verification paths for one tool.
  • Distinguish durable acknowledgement from query visibility.
  • Demonstrate that a stale observation does not trigger a second mutation.

What changes in the team

Today
An engineer saves a release, refreshes a dashboard, and manually checks the database if the status looks wrong.
With an agent
An assistant calls a mutation tool whose wrapper records the committed revision and uses an explicit verification route before proceeding.
Still your responsibility
Engineers choose freshness requirements, implement routing and permissions, and review unresolved results. Persistent prompts alone do not enforce permissions or database guarantees.

Work through an example

  1. Define evidence. For R42, specify that the write response includes the committed application revision. Log the operation ID, returned revision, and read endpoint. These are proposed tool-contract fields, not automatic PostgreSQL features; distinguish an accepted asynchronous job from a completed database transaction.
  2. Expose the race. Hold a test replica response at v7 while the primary commits v8. Read through the reporting route. Compare the observed and expected revisions, label the mismatch unresolved, and preserve the successful write receipt. Do not infer that the mutation failed.
  3. Resolve deliberately. Issue a fresh Read Committed query on the primary after commit. PostgreSQL takes a new snapshot for each such command; an older Repeatable Read transaction can retain an earlier view. Compare the intended fields as well as the revision. With concurrent writers, a newer revision needs separate reconciliation.
  4. Review alternatives. With configured synchronous standbys, PostgreSQL synchronous_commit=on waits for durable standby WAL, while remote_apply also waits for replay. This applies to the acknowledging standbys, not every possible read target. Decide whether that commit wait is justified; a fixed sleep is not a freshness contract.

The engineering decision

Use this as reference material when tools read replicas, caches, or derived indexes. Choose acceptable staleness, data-loss exposure, and verification cost per operation. Routing verification to a primary is one design choice, not a universal consistency guarantee; concurrent writers, caches, transaction snapshots, and failover still need explicit handling.

  • Repeating the mutation because a replica returns an older revision; design retry safety separately.
  • Calling every replica consistent because one synchronous standby acknowledged.
  • Assuming a primary read proves external deployment completion or bypasses an application cache.
  • Treating a matching revision as an atomic guard for a later action; another writer can change the record after verification. Enforce any required revision precondition when the action executes.

Try it with your team

Catch a false verification failure

  1. Build a tiny fake tool service with a primary record and a separately advanced replica record.
  2. Write R42 once from v7 to v8; keep the replica at v7 and record the write count.
  3. Run verification against the replica, then implement a fresh primary verification path with an explicit unresolved result on failure.
  4. Replay the scenario and finally advance the replica to v8. Save the tool trace for team review.

You have an answer when…

  • The trace contains exactly one mutation and identifies which read returned v7.
  • Verification succeeds only after matching evidence, or returns unresolved when the primary cannot be checked.

Read further

Trace one write and its verification read. Which server and transaction snapshot answer the read, and what exactly must happen before the write tool reports success?

Original video comparisons