Replication — Copies of Your Data

What is it? #

Replication keeps copies of your database on other servers, kept up to date as the primary changes.

It buys three things. Read capacity, because queries can be spread across replicas. Availability, because a replica can be promoted if the primary fails. Safety, because the data exists in more than one place.

The standard arrangement is one primary that accepts writes and several replicas that serve reads only.

The catch is lag. A replica is always slightly behind — usually milliseconds, sometimes much more — which means a read straight after a write may not see it.

Think of it like this #

A head office ledger with copies sent to branch offices. Branches can answer questions from their copy, which takes load off head office.

But a change made at head office a second ago may not be in the branch copy yet. If someone updates their address and immediately asks a branch to confirm it, they may see the old one.

Simple example #

Reporting queries are heavy and slow down the main application. Pointing them at a read replica removes that load from the primary. A user updating their profile, though, must read from the primary to see the change immediately.

Code #

                     writes
   application ────────────────▶  PRIMARY
        │                            │  streams changes
        │                            ├──────────────▶ replica 1  (reads)
        └── reads ───────────────────┴──────────────▶ replica 2  (reads, reporting)

If the primary fails, a replica is promoted and becomes the new primary.

# Routing reads and writes to different connections
class Database:
    def __init__(self, primary_pool, replica_pool):
        self.primary = primary_pool
        self.replica = replica_pool

    def write(self, sql, params=()):
        with self.primary.connection() as conn:
            return conn.execute(sql, params)

    def read(self, sql, params=(), *, fresh: bool = False):
        # fresh=True for reads that must see the very latest data
        pool = self.primary if fresh else self.replica
        with pool.connection() as conn:
            return conn.execute(sql, params).fetchall()


db = Database(primary_pool, replica_pool)

db.write("UPDATE users SET city = %s WHERE id = %s", ("Pune", 7))
db.read("SELECT city FROM users WHERE id = %s", (7,), fresh=True)   # after a write
db.read("SELECT COUNT(*) FROM orders WHERE created_at > %s", (last_month,))  # replica

-- Check how far behind a PostgreSQL replica is
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

Synchronous vs asynchronous

asynchronous   primary commits immediately, replicas catch up
               fast writes, small window where recent writes could be lost

synchronous    primary waits for at least one replica to confirm
               no data loss on failover, slower writes

How it works #

The primary records every change in a write-ahead log and streams it to the replicas, which apply the same changes in the same order. That is why replicas are exact copies rather than approximations.

Asynchronous replication is the default because it does not slow down writes. The trade-off is a small window: if the primary dies before a change reaches a replica, that change is lost.

Synchronous replication closes that window by waiting for a replica to confirm before the commit returns. Writes become slower by the network round trip, which is why it is usually reserved for data where loss is unacceptable.

Replication lag is the measurable version of this. Normally milliseconds; under heavy write load or a long-running query on the replica, it can grow to seconds or minutes.

The application-level answer is read-your-writes routing, shown in the code. After a user changes something, read from the primary for a short period so they see their own change. Everything else can use a replica.

Failover promotes a replica to primary. Doing this safely requires care: the application must be redirected, and the old primary must not come back and accept writes as well — that produces split brain, with two divergent copies.

Real-world use #

Read replicas are the standard first step when a database is read-heavy, which most applications are. Analytics and reporting queries in particular belong on a replica, away from user traffic.

Managed database services make this a configuration option, including automated failover with a stable endpoint that always points at the current primary.

Cross-region replicas serve both latency and disaster recovery: users read from a nearby copy, and an entire region can be lost without losing data.

Lag-related bugs are the common surprise. A user updates a setting, the page reloads from a replica, and the old value appears. The fix is routing, not more replicas.

Replication is not a backup. It faithfully copies a mistaken DELETE to every replica within milliseconds. Backups protect against errors; replication protects against machine failure.

Common mistakes #

• Treating replication as a backup — it replicates mistakes perfectly.
• Reading from a replica immediately after a write and showing stale data.
• Ignoring replication lag until it causes a visible bug.
• Allowing a failed primary to rejoin and accept writes, causing split brain.
• Adding replicas to fix a write bottleneck, which they do nothing for.

Practice #

Sketch the read and write paths for a profile page where users can edit their details. Mark which reads must go to the primary and which can go to a replica. Then write down what the user would see if you got one of those wrong.