Guarantee Read-After-Write Consistency

Route reads from the same session to the primary or use a causal token so a user sees their own write immediately.

~25 mindatabaseconsistencyread-write

Task

Apply SET session_replication_role = replica; -- or: SELECT * FROM orders WHERE id = $1; -- via primary so the schema change is safe, reversible, and does not lock the table during peak traffic.

When to use this

A migration deploys SET session_replication_role = replica; -- or: SELECT * FROM orders WHERE id = $1; -- via primary during peak hours, takes a 40-second lock, and causes a connection pool exhaustion.

Prerequisites

A database with migration tooling configured
A staging environment mirroring production schema

Steps

1. Configure SET session_replication_role = replica; -- or: SELECT * FROM orders WHERE id = $1; -- via primary in the project configuration file with the recommended defaults.

SET session_replication_role = replica; -- or: SELECT * FROM orders WHERE id = $1; -- via primary

Expected output: The configuration is accepted and SET session_replication_role = replica; -- or: SELECT * FROM orders WHERE id = $1; -- via primary runs without errors on the first invocation.

2. Run the check locally to verify SET session_replication_role = replica; -- or: SELECT * FROM orders WHERE id = $1; -- via primary produces the expected result before pushing.

pnpm test

Expected output: Local run matches CI behavior; no surprises after push.

3. Add the gate to the CI pipeline so SET session_replication_role = replica; -- or: SELECT * FROM orders WHERE id = $1; -- via primary blocks merge on failure.

- name: Run add read after write consistency
  run: pnpm test

Expected output: Pull requests with failing SET session_replication_role = replica; -- or: SELECT * FROM orders WHERE id = $1; -- via primary are blocked from merging.

Troubleshooting

Problem

The check passes locally but fails in CI

Solution

Pin dependency versions and the runtime to match CI exactly; reproduce with the same Docker image or act.

Problem

Configuration is rejected with a schema error

Solution

Check the tool version matches the config schema; older versions may not support newer fields.

Limitations

The gate only runs on events that trigger the workflow; locally pushed commits bypass it.
Configuration drift is possible if someone edits settings outside version control.

Make migrations safe

Start by measuring the problem. Route reads from the same session to the primary or use a causal token so a user sees their own write immediately. The most common mistake is adding the gate without first confirming it catches the issue you care about. Run SET session_replication_role = replica; – or: SELECT * FROM orders WHERE id = $1; – via primary against the current codebase and inspect the output before wiring it into CI.

SET session_replication_role = replica; -- or: SELECT * FROM orders WHERE id = $1; -- via primary

Verify and roll back

Once the gate is in place, make failures actionable. A failing SET session_replication_role = replica; – or: SELECT * FROM orders WHERE id = $1; – via primary should point to the exact file and line, not a generic error. Pin the tool version in the lockfile so a upstream release does not silently change behavior. Review the gate output in every pull request just like a test result.