Module 05 · Lesson 2 of 12Free

Read replicas and replication lag

Level

Intermediate

Topic

Database scaling: read replicas, sharding and partitioning

Reading time

18 min

The saved price disappears

A seller on Kartly changes a product’s price from ₹1,200 to ₹1,000. The screen says “Saved”. She reloads after 100 ms and sees ₹1,200 again.

The write succeeded. The reload reached a different database that had not applied the change.

How can Kartly move reads off its primary database without making successful writes appear to disappear?

We will examine one catalog database group at Kartly’s Part 2 scale. Other services and database groups remain outside this example.

Copies remove reads, not writes

Start with every catalog request using one primary: the database that accepts writes. Before adding machines, check query plans and unnecessary work, as discussed in Scaling a relational database: the options.

A read replica is a separate database copy used to serve read-only queries. Moving suitable queries there reduces work on the primary.^{2Working with DB instance read replicasAmazon Web Services, Amazon RDS User Guide}

The simplest application still sends everything to the primary. It has one route and no cross-copy freshness problem. Keep it when measured demand fits comfortably.

For this lesson’s scenario, however, catalog browsing exceeds that capacity.

These calculations estimate CPU demand, not maximum throughput. Storage waits, locks and uneven queries can invalidate the estimate.

The capacity gain depends on moving reads. We now need to understand what those moved reads can observe.

A change travels through stages

For this lesson, use PostgreSQL 18 physical streaming replication. A standby starts from a base backup, receives write-ahead log records, then replays them. A readable standby serves queries from its local state.^{126.2. Log-Shipping Standby ServersPostgreSQL Global Development Group, PostgreSQL 18 Documentation}

The log mechanism builds on Write-ahead logging: recovering after a crash. Replication sends the record of changes, not another application request to “save this price”.

The following timeline is illustrative:

Receiving records is not the same as applying them. In this model, query-visible state changes only after replay reaches the commit.

Replication lag is the replica’s distance behind the primary. You can describe that distance as unapplied log bytes or as a delay in visibility. Those measurements answer different questions.

Do not assume every database uses PostgreSQL’s mechanism. MySQL 8.4 replication records changes in a binary log. A replica copies events into a relay log before processing them.^{319.2 Replication ImplementationOracle, MySQL 8.4 Reference Manual} The shared lesson is the separation between receiving changes and making them readable.

A replica can therefore be reachable and still be unsuitable for a particular screen.

Success needs an exact meaning

The tempting belief is: “If replication is enabled, a successful write is already visible everywhere.”

Our timeline disproves that belief. The seller received success before the replica applied the commit.

With asynchronous replication, the primary does not wait for a replica’s acknowledgment before returning success. PostgreSQL streaming replication uses this mode by default.^{126.2. Log-Shipping Standby ServersPostgreSQL Global Development Group, PostgreSQL 18 Documentation}

For Kartly, this avoids making every catalog save wait for a remote reader. The cost is the stale-read window we observed.

Synchronous replication adds a remote acknowledgment to the commit path. Its meaning matters: PostgreSQL can wait for remote log persistence, or use remote_apply to wait for replay on selected synchronous standbys.^{126.2. Log-Shipping Standby ServersPostgreSQL Global Development Group, PostgreSQL 18 Documentation}

Persistence protects a different property from read visibility. “The records survived on another machine” does not mean “every reader has applied them”.

Even an apply acknowledgment names particular replicas. It does not make an unrelated asynchronous reader current.

For our catalog scenario, keep asynchronous replication and control read routing. Requiring every browsing replica to participate in writes would make reader problems affect seller saves. If the product instead requires remote persistence before success, revisit the replication acknowledgment policy.

The remaining question belongs to the application: which reads are allowed to use an older copy?

Route by the screen’s promise

Kartly asks three product questions:

A GET request is not automatically suitable for a replica. The seller reload is read-only, but its promise requires freshness.

Nor does using the primary alone prevent overselling. Checkout must make the stock check and reservation part of one protected operation. That builds on Isolation levels and locking: handling concurrent writes.

Keep the rule in one place

The catalog service owns routing. It maintains separate connection pools for the primary and each reader.

Here is the catalog service’s routing policy. The scenario uses a two-second lag threshold and observations no older than one second.

from typing import Literal

Intent = Literal["browse", "fresh", "write"]
Route = Literal["primary", "replica", "unavailable"]


def choose_route(
    intent: Intent,
    replica_up: bool,
    lag_seconds: float | None,
    observation_age: float,
) -> Route:
    if intent != "browse":
        return "primary"

    eligible = (
        replica_up
        and lag_seconds is not None
        and 0.0 <= lag_seconds <= 2.0
        and 0.0 <= observation_age <= 1.0
    )
    return "replica" if eligible else "unavailable"


print(choose_route("fresh", True, 0.1, 0.2))

type Intent = "browse" | "fresh" | "write";
type Route = "primary" | "replica" | "unavailable";

function chooseRoute(
  intent: Intent,
  replicaUp: boolean,
  lagSeconds: number | null,
  observationAge: number,
): Route {
  if (intent !== "browse") {
    return "primary";
  }

  const eligible =
    replicaUp &&
    lagSeconds !== null &&
    lagSeconds >= 0 &&
    lagSeconds <= 2 &&
    observationAge >= 0 &&
    observationAge <= 1;

  return eligible ? "replica" : "unavailable";
}

console.log(chooseRoute("fresh", true, 0.1, 0.2));

The code selects a role, not a database connection. For browsing, evaluate each replica and choose among eligible readers. An unavailable result maps to a temporary browsing error in this design.

This health policy is best effort. A replica can fall behind immediately after observation. It does not prove a strict two-second freshness bound.

Follow one save and reload

1. The catalog service validates the seller’s edit.
2. It updates the primary and waits for commit.
3. It returns the saved representation to the seller.
4. Replication independently transfers and applies the change.
5. A seller reload still reads the primary.
6. Ordinary browsing can use an eligible replica.

Returning the saved representation avoids an unnecessary immediate reread. Primary routing protects later reloads too.

If the save response is lost, reading a replica cannot prove the write failed. Do not repeat a non-repeatable action based on absence there. Safe retry behavior belongs in Idempotency: making retries safe.

Routing now protects the workflow, but the replicas still need enough capacity to stay useful.

A backlog needs spare capacity

Suppose a festival sale increases the primary’s log generation rate. A replica receives changes faster than it can apply them.

A faster network will not solve an apply bottleneck. The pending records already reached the reader; replay needs more processing or storage capacity.

Use log bytes rather than transaction counts for this estimate. Transactions can produce very different amounts of work.

Matching arrival capacity only stops further growth. Recovery requires spare capacity.

Possible responses depend on the bottleneck. Reduce browsing work on an overloaded reader. Give replay more resources. Separate heavy reporting from latency-sensitive browsing.

Adding a reader can spread query work, but each new reader must still process this group’s change stream. At much larger write volume, revisit Sharding: splitting data across databases.

Failure should not flood the primary

Consider one reader disconnecting during the sale. The surviving reader cannot accept all displaced traffic within its budget.

Blind fallback would send thousands of browsing queries to the primary. That could turn a browsing outage into failed seller saves and checkout requests.

Our scenario therefore reserves primary capacity for writes and fresh reads. Optional browsing fails temporarily when no reader qualifies. A different product might choose capped primary fallback, but that cap must fit measured spare capacity.

Promotion is not ordinary read routing. It changes which database accepts writes. Asynchronous replication can leave acknowledged commits missing after failover.^{126.2. Log-Shipping Standby ServersPostgreSQL Global Development Group, PostgreSQL 18 Documentation} Kartly must not promote a reader merely because one request timed out.

Observe progress, not just reachability

PostgreSQL exposes sent, received and replayed log positions. Comparing those stages helps distinguish transport delay from replay delay.^{126.2. Log-Shipping Standby ServersPostgreSQL Global Development Group, PostgreSQL 18 Documentation}

For our routing policy, monitor each reader separately. A fleet average can hide the one reader returning old prices.

A useful operational check writes a changing marker on the primary and observes it through each reader. Its visibility delay tests the path users depend on. Treat the marker as a diagnostic, not proof that every later read meets a bound.

Re-admit a recovered reader after sustained acceptable progress, rather than after its first successful connection. That avoids repeatedly moving traffic onto a reader still catching up.

Where this shows up

• Design: a URL shortener for millions of users: a newly created link must not look missing immediately afterward.
• Design: a fast product catalog page: browsing freshness and checkout correctness need different promises.

Replicas increase the number of database endpoints, so the next problem is controlling how many connections the application opens.

Common mistakes

• Route every read to replicas. Classify the workflow’s freshness requirement, not just the SQL statement.
• Treat a timer as proof of replay. A replication stall can last longer than the timer.
• Use receipt as proof of visibility. Pending records still need to be applied.
• Fallback without a capacity budget. Protect essential primary traffic before accepting displaced browsing work.
• Assume more copies divide writes. Each reader still processes the group’s changes.

Check your understanding

Summary