Scaling Postgres with Read Replicas & Using WAL to Counter Stale Reads

Read Replicas and the Stale Read Problem

When an application outgrows a single Postgres instance, one of the first scalability moves is to offload read traffic to read-only replicas. Writes must still go to the primary — Postgres follows a single-master model — but reads can be distributed across any number of secondaries that are tracking the primary's changes. This spreads load across more nodes and, crucially, frees the primary from expensive analytical or multi-join queries that would otherwise contend with write traffic.

The tradeoff is that replicas are asynchronous. At any given moment, a replica may be behind the primary by some amount of time, and a read routed to that replica may return data that doesn't yet reflect recently committed writes. A user who updates their profile and then immediately views it could see the old values — the classic stale read.

In practice, with modern infrastructure and low-latency connections, replicas trail the primary by well under a second, and stale reads are rare. But "rare" is not the same as "never." For applications where consistency matters, there's a more rigorous approach: use Postgres's own replication metadata to guarantee that a read only goes to a replica that has already applied the relevant write.

How Postgres Replication Works

Postgres maintains a write-ahead log (WAL) for durability. Every change is appended to the WAL as a new entry, each assigned a log sequence number (LSN). The WAL is the canonical record of whether a change has been committed — a transaction is visible to new reads only if the WAL confirms its commit. WAL entries are batched into 16 MB segments.

Replicas are initialized from a base backup of the primary, which includes a pointer to the primary's current LSN so the replica knows where to begin consuming the WAL. From there, the replica stays current in one of two ways.

In log shipping, completed WAL segments are copied in bulk to replicas. It's efficient, but secondaries can lag by up to a full segment. In streaming replication, WAL is pushed to replicas over an open connection, keeping them nearly in lockstep with the primary. Streaming replicas are often called "hot standbys" and are the typical production choice because they can be promoted to primary with minimal data loss.

WAL-Position-Aware Read Routing

The LSN gives us a precise way to measure replica freshness. The idea, sometimes called "sticky connections," is to record the primary's LSN at the moment a user makes a change, then only route that user's subsequent reads to replicas whose WAL replay has passed that LSN. If no replica has caught up yet, the read falls back to the primary.

This technique was described by GitLab in their writeup on scaling their own Postgres installation, and it requires two pieces of bookkeeping: the replication status of every replica, and a minimum LSN per user.

Tracking Replica Status with an Observer

Rather than having every application process connect to every replica to check its position, a dedicated observer process periodically polls each replica and records the results. The status is stored in a replica_statuses table with a common name for each replica and a last_lsn field using Postgres's native pg_lsn type.

CREATE TABLE replica_statuses (
    id       BIGSERIAL    PRIMARY KEY,
    last_lsn PG_LSN       NOT NULL,
    name     VARCHAR(100) NOT NULL UNIQUE
);

The observer's main loop is straightforward. It opens a connection to each replica and calls pg_last_wal_replay_lsn() to get the replica's current WAL replay position. Once all positions are collected, it stamps the entire set into replica_statuses with a single upsert (INSERT INTO ... ON CONFLICT ...).

# exclude :default at the zero index
replica_names = DB.servers[1..-1]

last_lsns = replica_names.map do |name|
  DB.with_server(name) do
    DB[Sequel.lit(<<~eos)].first[:lsn]
      SELECT pg_last_wal_replay_lsn() AS lsn;
    eos
  end
end

insert_tuples = []
replica_names.each_with_index do |name, i|
  insert_tuples << { name: name.to_s, last_lsn: last_lsns[i] }
end

# update all replica statuses at once with upsert
DB[:replica_statuses].
  insert_conflict(target: :name,
    update: { last_lsn: Sequel[:excluded][:last_lsn] }).
  multi_insert(insert_tuples)

$stdout.puts "Updated replica LSNs: results=#{insert_tuples}"

The status information could be cached elsewhere — in Redis, or in-process per API worker — but storing it in Postgres makes lookup queries elegant, as we'll see momentarily.

Recording a Minimum LSN Per User

On the write side, we need to remember how far along the WAL was when a user last made a change. In the demo application, this is a min_lsn column on the users table, again using pg_lsn.

CREATE TABLE users (
    id      BIGSERIAL    PRIMARY KEY,
    email   VARCHAR(255) NOT NULL UNIQUE,
    min_lsn PG_LSN
);

Whenever a user performs an action that will later affect reads, we set their min_lsn to the primary's current WAL position via pg_current_wal_lsn(). A helper function update_user_min_lsn encapsulates this.

post "/rides" do
  user = authenticate_user(request)
  params = validate_params(request)

  DB.transaction(isolation: :serializable) do
    ride = Ride.create(
      distance: params["distance"],
      user_id: user.id,
    )
    update_user_min_lsn(user)

    [201, JSON.generate(serialize_ride(ride))]
  end
end

def update_user_min_lsn(user)
  User.
    where(id: user.id).
    update(Sequel.lit("min_lsn = pg_current_wal_lsn()"))
end

Selecting an Eligible Replica

On the read side, the application needs to pick a replica that is far enough along. The query compares each replica's recorded last_lsn against the requesting user's min_lsn using pg_wal_lsn_diff(), which returns the difference between two LSNs. A non-negative result means the replica has consumed the WAL beyond the user's minimum — it's safe to read from. The application then selects a random replica from that eligible set; if the set is empty, it falls back to the primary.

def select_replica(user)
  # If the user's `min_lsn` is `NULL` then they haven't performed an operation
  # yet, and we don't yet know if we can use a replica yet. Default to the
  # primary.
  return :default if user.min_lsn.nil?

  # exclude :default at the zero index
  replica_names = DB.servers[1..-1].map { |name| name.to_s }

  res = DB[Sequel.lit(<<~eos), replica_names, user.min_lsn]
    SELECT name
    FROM replica_statuses
    WHERE name IN ?
      AND pg_wal_lsn_diff(last_lsn, ?) >= 0;
  eos

  # If no candidates are caught up enough, then go to the primary.
  return :default if res.nil? || res.empty?

  # Return a random replica name from amongst the candidates.
  candidate_names = res.map { |res| res[:name].to_sym }
  candidate_names.sample
end

In the API layer, this selection happens per request:

get "/rides/:id" do |id|
  user = authenticate_user(request)

  name = select_replica(user)
  $stdout.puts "Reading ride #{id} from server '#{name}'"

  ride = Ride.server(name).first(id: id)
  if ride.nil?
    halt 404, JSON.generate(wrap_error(
      Messages.error_not_found(object: "ride", id: id)
    ))
  end

  [200, JSON.generate(serialize_ride(ride))]
end

That's the entire mechanism. Stale reads become impossible by construction: a read will never be served by a replica whose WAL replay position is behind the user's last write, regardless of how far behind that replica is.

Building a Local Test Cluster

To exercise this setup locally, the demo repository includes a script that bootstraps a small cluster. It initializes a primary, then performs a base backup for each replica specified by the NUM_REPLICAS environment variable. All Postgres daemons are started as child processes with Ruby's Process.spawn, and the entire setup is ephemeral — data is wiped and re-created on the next run.

git clone https://github.com/brandur/rocket-rides-scalable.git

The repository also ships a simulator that creates a ride and immediately tries to read it back. Running the cluster, observer, API, and simulator together shows that reads are usually served from a replica, but occasionally from default — Sequel's name for the primary — when replication hasn't caught up yet or the observer hasn't completed its latest polling cycle.

$ forego start | grep 'Reading ride'
api.1       | Reading ride 96 from server 'replica0'
api.1       | Reading ride 97 from server 'replica0'
api.1       | Reading ride 98 from server 'replica0'
api.1       | Reading ride 99 from server 'replica1'
api.1       | Reading ride 100 from server 'replica4'
api.1       | Reading ride 101 from server 'replica2'
api.1       | Reading ride 102 from server 'replica0'
api.1       | Reading ride 103 from server 'default'
api.1       | Reading ride 104 from server 'default'
api.1       | Reading ride 105 from server 'replica2'

The approach requires streaming replication (or at least sufficiently frequent WAL shipping) to be practical, and it adds modest bookkeeping overhead on writes and reads. But for applications where read consistency after a user's own writes is non-negotiable, WAL-position-aware read routing is a clean and reliable answer to the stale read problem.

Is read scaling right for you?

The main trade-off of this approach is that every min_lsn must be refreshed whenever a user performs an action that affects their read results. In practice, this closely resembles cache invalidation — a technique notorious for behaving well until it suddenly doesn't. In larger codebases, save hooks and update triggers can help maintain correctness, but as code and team size grow, perfect correctness becomes increasingly hard to guarantee.

Most projects — those with only moderate database load — should skip replicas entirely and keep things simple by sending all queries to the primary. If you expect storage needs to outgrow a single node, a partitioning solution like Citus is a better fit.

The real sweet spot is for projects that can keep their dataset on one node but want to scale compute. Moving reads to replicas extends your scalability runway significantly while avoiding the substantial overhead and operational complexity of partitioning.

Data loss staleness: reading from replicas

One of the primary concerns when directing reads to replicas is staleness. In Postgres, this is managed through the Write-Ahead Log (WAL) and its replication role.

The WAL's replication role

Each change to the primary is first recorded in the WAL before being applied to data files. Replicas consume this WAL stream to replay changes and stay in sync. Because the WAL is a sequential, append-only log, it provides a convenient way to measure how far a given replica has caught up to the primary at any moment.

Routing reads based on replica WAL position

A practical implementation needs a few moving parts operating together: a cluster, a way to observe replication progress, and application logic that selects replicas based on that progress.

Bootstrapping a cluster

For the purposes of this design, imagine a small Postgres cluster with a primary and multiple replicas. Physical streaming replication keeps the replicas in sync. Application traffic is split so that writes always go to the primary and reads are dispatched to eligible replicas.

The Observer: tracking replication status

A key piece is the Observer — a mechanism that periodically polls the cluster and records each replica's current WAL position. This gives the application a consistent snapshot of which replicas are safely ahead of any given write.

Saving minimum LSN

Each user session carries a min_lsn value: the WAL position that must be present on a replica before that replica is allowed to serve reads for that user. Every write performed on the primary advances this stored position. A session is only routed to a replica whose known WAL position is at or beyond that threshold.

Selecting an eligible replica

The routing logic is straightforward: query the Observer's data, throw away any replicas that are behind the user's min_lsn, and pick one from the remaining list. If no replica is eligible, fall back to the primary to preserve correctness.