activerecord-wait_for_lsn

Read-your-writes on Rails read replicas via PostgreSQL 19 WAIT FOR LSN.

After a write the resolver stores the primary's pg_current_wal_lsn() in the session. Before a read it runs WAIT FOR LSN on the replica. If the replica has caught up, the read goes to the replica, otherwise to the primary.

activerecord-wait_for_lsn is built by Evil Martians, an American design and engineering consultancy for developer tools, AI, and cybersecurity startups.

# Gemfile
gem "activerecord-wait_for_lsn"

# config/initializers/multi_db.rb
Rails.application.configure do
  config.active_record.database_selector = { wait_timeout: "50ms" } # default 20ms
  config.active_record.database_resolver = ActiveRecord::WaitForLSN::Resolver
  config.active_record.database_resolver_context = ActiveRecord::WaitForLSN::Session
end

Requires PostgreSQL 19, a streaming standby configured as replica: true, and Rails 7.2+.

Passed through the database_selector hash.

max_age is what keeps a session from running WAIT FOR LSN on every read for the rest of its life: once the last write is older than max_age the resolver stops checking and reads straight from the replica. Pick a value above the replication lag you are prepared to tolerate; the lag gauges below show what your standbys actually do. Rails' own delay option is not used.

Per write: one extra SELECT pg_current_wal_lsn() on the primary connection the request already holds. Per read inside max_age: one WAIT FOR LSN on the replica connection the read is about to use anyway. When the replica has already replayed the LSN, which is the common case, the statement returns immediately and costs the same as SELECT 1: a network round-trip and nothing else. Measured against a PostgreSQL 19 standby in Docker on the same machine (2000 iterations each):

Over a real network add your round-trip time. The only expensive case is a lagging replica, where the read waits up to wait_timeout and then falls back to the primary.

Rails has one reading role per class, so several standbys sit behind one replica entry: a TCP load balancer (HAProxy, PgBouncer in session mode) or libpq's own multi-host connection string. With PostgreSQL 16+ libpq the entry can spread connections itself:

replica:
  <<: *default
  host: standby1,standby2,standby3
  port: "5432,5432,5432" # quoted, or YAML reads it as one number
  load_balance_hosts: random
  replica: true

Every request leases one replica connection: the WAIT FOR LSN and all reads of the request run over that connection, hence on the same standby, whichever one the pool handed out. Transaction-pooling proxies that spread the statements of one client connection over several servers break the guarantee.

Note that load_balance_hosts balances connections, not requests: libpq picks a host when a connection is opened, and the pool then reuses its most recently returned connections first. Under light load a couple of pooled connections serve most requests, so the standbys they landed on see most of the reads. For an even spread per request put a round-robin TCP balancer in front of the standbys.

Which standby is behind and by how much is visible per standby through the lag metrics, which come from pg_stat_replication on the primary and cover every streaming standby, whether or not it takes reads.

The wait always uses MODE 'standby_replay': the LSN must be applied on the standby, so a following SELECT sees the write. The other modes (standby_write, standby_flush) only confirm the WAL has arrived on the standby, not that it is visible, so they are not exposed.

Each wait emits database_selector.active_record.wait_for_lsn with lsn, lsn_status and database (the database.yml entry of the standby) in the payload. On success the event duration is how long the replica took to replay your last write. database_selector.active_record.read_from_replica and database_selector.active_record.read_from_primary fire around every read, also with database in the payload, so per-replica counters are one subscriber away:

ActiveSupport::Notifications.subscribe("database_selector.active_record.wait_for_lsn") do |event|
  Rails.logger.info "WAIT FOR LSN on #{event.payload[:database]}: #{event.payload[:lsn_status]} in #{event.duration.round}ms"
end

An opt-in Yabeda integration exports replication lag per standby. Add Yabeda and an exporter to the Gemfile:

gem "yabeda"
gem "yabeda-prometheus" # or yabeda-datadog, yabeda-statsd, ...

Then require the integration from an initializer and opt in:

# config/initializers/multi_db.rb
require "active_record/wait_for_lsn/yabeda"
ActiveRecord::WaitForLSN::Yabeda.collect_replication_lag!

Before every export (each Prometheus scrape) it runs one query against the primary and sets two gauges per streaming standby, replay stage only, since that is the point at which a SELECT on the standby sees the write:

standby is the application_name the standby sets in primary_conninfo, which falls back to its cluster_name.

Why pg_stat_replication and not the wait itself: the duration of the wait_for_lsn event is how long a request waited for its own write, capped by wait_timeout. A replica that caught up before the next request arrives shows a wait of zero. The real lag is measured by PostgreSQL from standby feedback. Per-request counters (reads by role, wait statuses) are yours to build from the events above if you need them.

Requirements and caveats:

• The application's database role needs pg_read_all_stats (or superuser), otherwise the lag columns are NULL for every row:

  GRANT pg_read_all_stats TO app;

• PostgreSQL reports NULL once a standby is fully caught up and the primary is idle. Both gauges record it as 0.
• The query runs on the writing connection with prevent_writes: true and costs one round trip per scrape, not per request.

# Replay lag per standby, the number DBAs see in pg_stat_replication
wait_for_lsn_replication_lag_seconds

# Alert when a standby falls more than a second behind
max(wait_for_lsn_replication_lag_seconds) > 1

Unit tests need no database:

bundle exec rake test

Integration tests run the resolver and the DatabaseSelector middleware against a real PostgreSQL 19 primary and streaming standby. Start the cluster from the bundled compose file, then point the tests at it:

docker compose -f test/integration/compose.yml up -d --wait
PRIMARY_URL=postgres://postgres:postgres@localhost:55432/wait_for_lsn_test \
REPLICA_URL=postgres://postgres:postgres@localhost:55433/wait_for_lsn_test \
bundle exec rake test:integration

Both connections must be superuser: the tests set recovery_min_apply_delay on the standby to simulate lag. CI runs the suite against ActiveRecord 7.2, 8.0 and 8.1 via the gemfiles in gemfiles/:

BUNDLE_GEMFILE=gemfiles/activerecord_7.2.gemfile bundle install
BUNDLE_GEMFILE=gemfiles/activerecord_7.2.gemfile bundle exec rake test

MIT