PostgreSQL replication: read-replica and query routing

← Back to the section

The application grows, and the primary database starts choking on heavy SELECTs — reports, analytics, search. While an analytical query scans millions of rows, ordinary OLTP operations wait on locks and slow down. The fix is to put a replica next to it and route reads there.

What kinds of replication exist

"Replication" in PostgreSQL is an umbrella word for several different mechanisms, and in practice it matters which one you mean.

Physical streaming replication is the main one. The replica receives a stream of WAL — the change log at the level of page bytes — and replays it on its side. The result is an exact copy of the whole cluster: the same databases, the same data, the same major PostgreSQL version. You cannot write to the replica; you can read from it (hot standby). It comes in several flavours:

• asynchronous (the default) — the primary does not wait for the replica: fast, but if the primary dies, the last acknowledged transactions may not have made it across;
• synchronous — the primary waits for the replica to confirm every COMMIT: what was acknowledged is not lost, but every write costs one extra network round-trip;
• cascading — a replica feeds WAL to further replicas and takes that traffic off the primary;
• replication slots — the primary keeps WAL until the replica has fetched it, so a lagging replica cannot fall off the edge; a dead one, though, silently piles up the log and eats the disk. The cap for that is max_slot_wal_keep_size, -1 by default, meaning no cap at all.

Log shipping is the historical relative of streaming: finished WAL files are handed over through an archive (archive_command → restore_command). The replica lags by a whole log segment; these days the mechanism is the basis of point-in-time backups rather than of live replication.

Logical replication copies row changes, not bytes. The source declares a PUBLICATION, the target a SUBSCRIPTION. The key difference: you can replicate individual tables, across different major PostgreSQL versions, and the target stays an ordinary writable database with tables and indexes of its own. You pay for that flexibility with limits: DDL is not replicated (a new column is added by hand on both sides), sequences are not replicated, and tables need a primary key or a REPLICA IDENTITY.

Logical decoding / CDC is the same logical mechanism, but the stream of changes goes somewhere other than another PostgreSQL: Debezium reads changes through a slot and publishes them to Kafka for analytics and integrations.

The rest of the article is about the most common scenario: streaming replication, a read-replica, and routing queries between the primary and the replica.

How streaming replication works

PostgreSQL writes every change to the WAL (Write-Ahead Log). The replica continuously receives this log from the primary and replays it on its side — this way the data on the replica mirrors the data on the primary.

A few concepts worth knowing:

• Master (primary) — the single node that accepts writes: INSERT, UPDATE, DELETE.
• Replica (standby, hot standby) — replays WAL from the primary, answers reads only.
• Replication lag — the delay between a write on the primary and the data appearing on the replica. Under normal conditions — 50–500 milliseconds; under load or with large transactions — up to several seconds.

By default, replication is asynchronous: the primary does not wait for confirmation from the replica before responding to the client. This is fast, but it means the replica lags slightly behind.

Why you need a read-replica

Three main scenarios:

Offloading the primary. Heavy SELECTs move to the replica and don't interfere with OLTP operations. Analogy: opening a second checkout lane for slow shoppers so the fast queue doesn't stall.

A long read on the replica has its own limit. When WAL replay runs into row versions the query is reading, the replica waits no longer than max_standby_streaming_delay (30 seconds by default) and then cancels the query with a conflict-with-recovery error. The cures are either more headroom in that parameter or hot_standby_feedback = on, which makes the primary hold back cleanup of old row versions while the replica is reading them.

High availability (HA). If the primary goes down, the replica can be promoted to a new primary (failover) and the application keeps working. But there is an honest price to pay: with ordinary asynchronous replication the last few transactions the primary had already acknowledged to the client may not have reached the replica — after the switch they are gone. Only synchronous replication guarantees "nothing is lost", and it slows every write down by the exchange with the replica.

Geo-distribution. A replica is brought up in another data center or region, close to users — read latency drops.

What you shouldn't do with a replica: read data right after a write expecting a fresh result — the replica lags behind and may not know about the row you just inserted. More on this below.

Query routing

The application keeps two connection pools — one to the primary, the other to the replica. Queries within a read-only transaction go to the replica, the rest go to the primary.

An important subtlety: the choice of source must be deferred until the first query, not until the connection is opened. Otherwise the "read-only" flag isn't known yet and routing won't work correctly.

// HikariCP + AbstractRoutingDataSource + LazyConnectionDataSourceProxy
public enum DataSourceType { MASTER, REPLICA }

@Component
public class TransactionRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return TransactionSynchronizationManager.isCurrentTransactionReadOnly()
            ? DataSourceType.REPLICA
            : DataSourceType.MASTER;
    }
}

@Configuration
public class DataSourceConfig {

    @Bean @Primary
    public DataSource routingDataSource(DataSource master, DataSource replica) {
        var routing = new TransactionRoutingDataSource();
        routing.setTargetDataSources(Map.of(
            DataSourceType.MASTER, master,
            DataSourceType.REPLICA, replica
        ));
        routing.setDefaultTargetDataSource(master);
        return new LazyConnectionDataSourceProxy(routing);
    }
}

// Usage:
@Transactional(readOnly = true)
public List<OrderView> findOrders(long customerId) {
    // goes to the replica
}

@Transactional
public OrderId createOrder(CreateOrderCommand cmd) {
    // goes to the primary
}

// pgxpool: two pools, selected via context
type DB struct {
    Master  *pgxpool.Pool
    Replica *pgxpool.Pool
}

type ctxKey string
const readOnlyKey ctxKey = "readOnly"

func WithReadOnly(ctx context.Context) context.Context {
    return context.WithValue(ctx, readOnlyKey, true)
}

func (db *DB) Pool(ctx context.Context) *pgxpool.Pool {
    if v, ok := ctx.Value(readOnlyKey).(bool); ok && v {
        return db.Replica
    }
    return db.Master
}

// Usage:
func (r *OrderRepo) FindOrders(ctx context.Context, customerID int64) ([]Order, error) {
    rows, err := r.db.Pool(WithReadOnly(ctx)).Query(ctx,
        "SELECT id, status FROM orders WHERE customer_id = $1", customerID)
    // ...
}

func (r *OrderRepo) CreateOrder(ctx context.Context, cmd CreateOrderCmd) (int64, error) {
    var id int64
    err := r.db.Pool(ctx).QueryRow(ctx,
        "INSERT INTO orders (customer_id) VALUES ($1) RETURNING id", cmd.CustomerID,
    ).Scan(&id)
    return id, err
}

// node-postgres (pg): two Pools, selected by a function
const pools = {
    master:  new Pool({ connectionString: process.env.DB_MASTER_URL }),
    replica: new Pool({ connectionString: process.env.DB_REPLICA_URL }),
};

function getPool(readOnly: boolean): Pool {
    return readOnly ? pools.replica : pools.master;
}

// Usage:
export async function findOrders(customerId: bigint): Promise<Order[]> {
    const { rows } = await getPool(true).query<Order>(
        'SELECT id, status FROM orders WHERE customer_id = $1',
        [customerId],
    );
    return rows;
}

export async function createOrder(cmd: CreateOrderCmd): Promise<bigint> {
    const { rows } = await getPool(false).query<{ id: bigint }>(
        'INSERT INTO orders (customer_id) VALUES ($1) RETURNING id',
        [cmd.customerId],
    );
    return rows[0].id;
}

# psycopg (v3): two pools via AsyncConnectionPool
master_pool  = AsyncConnectionPool(conninfo=MASTER_DSN, open=False)
replica_pool = AsyncConnectionPool(conninfo=REPLICA_DSN, open=False)

def get_pool(read_only: bool) -> AsyncConnectionPool:
    return replica_pool if read_only else master_pool

# Usage:
async def find_orders(customer_id: int) -> list[dict]:
    async with get_pool(read_only=True).connection() as conn:
        async with conn.cursor() as cur:
            await cur.execute(
                "SELECT id, status FROM orders WHERE customer_id = %s",
                (customer_id,),
            )
            return await cur.fetchall()

async def create_order(customer_id: int) -> int:
    async with get_pool(read_only=False).connection() as conn:
        async with conn.cursor() as cur:
            await cur.execute(
                "INSERT INTO orders (customer_id) VALUES (%s) RETURNING id",
                (customer_id,),
            )
            row = await cur.fetchone()
            return row[0]

Read-after-write: a common trap

A typical scenario: a user creates an order, and the application immediately shows them the list of orders. The write goes to the primary, while the read goes to the replica, which hasn't received the fresh WAL yet. The order that was just created won't appear in the response.

createOrder(req)   → primary  ✓
listOrders(userId) → replica  ✗  (the order may be missing)

The mechanics are easy to see on a small model: the log is a queue between the primary and the replica, and the read lands exactly in the window while that queue has not been drained yet.

live example

import java.util.ArrayDeque;
import java.util.ArrayList;
import java.util.Deque;
import java.util.List;

public class ReplicaLag {
    public static void main(String[] args) {
        List<String> primaryRows = new ArrayList<>();
        List<String> replicaRows = new ArrayList<>();
        Deque<String> wal = new ArrayDeque<>();

        commit(primaryRows, wal, "order-1");
        replay(replicaRows, wal);
        commit(primaryRows, wal, "order-2");

        System.out.println("read from primary: " + primaryRows);
        System.out.println("read from replica: " + replicaRows + "  <- order-2 still in flight");

        replay(replicaRows, wal);
        System.out.println("replica caught up: " + replicaRows);
    }

    static void commit(List<String> rows, Deque<String> wal, String orderId) {
        rows.add(orderId);
        wal.addLast(orderId);
    }

    static void replay(List<String> rows, Deque<String> wal) {
        while (!wal.isEmpty()) {
            rows.add(wal.removeFirst());
        }
    }
}

The primary sees both orders right after commit, the replica only the first one: the second is still sitting in the log queue. After replay the two lists agree. The gap between the second and the third line of output is the window a read straight after a write falls into.

Three ways to work around this:

Read from the primary after a write

The simplest option for pages where the user expects fresh data immediately after their action.

@Transactional   // no readOnly=true — will go to the primary
public List<Order> myOrdersFromMaster(long customerId) {
    return orderRepo.findByCustomerId(customerId);
}

// ctx without WithReadOnly — the primary pool is selected
func (r *OrderRepo) MyOrdersFromMaster(ctx context.Context, customerID int64) ([]Order, error) {
    rows, err := r.db.Pool(ctx).Query(ctx,
        "SELECT id, status FROM orders WHERE customer_id = $1", customerID)
    // ...
}

// getPool(false) — explicitly the primary
export async function myOrdersFromMaster(customerId: bigint): Promise<Order[]> {
    const { rows } = await getPool(false).query<Order>(
        'SELECT id, status FROM orders WHERE customer_id = $1',
        [customerId],
    );
    return rows;
}

# read_only=False — explicitly the primary
async def my_orders_from_master(customer_id: int) -> list[dict]:
    async with get_pool(read_only=False).connection() as conn:
        async with conn.cursor() as cur:
            await cur.execute(
                "SELECT id, status FROM orders WHERE customer_id = %s",
                (customer_id,),
            )
            return await cur.fetchall()

Return the data directly from the write operation

The data is already in memory after the INSERT — there's no need for a separate SELECT. RETURNING in PostgreSQL returns the inserted row right within the same transaction on the primary.

// jOOQ: INSERT ... RETURNING returns the record from the primary
public OrderResponse createOrder(CreateOrderCommand cmd) {
    OrdersRecord saved = dsl
        .insertInto(ORDERS)
        .set(ORDERS.CUSTOMER_ID, cmd.customerId())
        .returning()
        .fetchOne();
    return OrderResponse.from(saved);
}

func (r *OrderRepo) CreateOrder(ctx context.Context, cmd CreateOrderCmd) (*Order, error) {
    var o Order
    err := r.db.Pool(ctx).QueryRow(ctx,
        `INSERT INTO orders (customer_id) VALUES ($1)
         RETURNING id, customer_id, created_at`,
        cmd.CustomerID,
    ).Scan(&o.ID, &o.CustomerID, &o.CreatedAt)
    return &o, err
}

export async function createOrder(cmd: CreateOrderCmd): Promise<Order> {
    const { rows } = await getPool(false).query<Order>(
        `INSERT INTO orders (customer_id) VALUES ($1)
         RETURNING id, customer_id, created_at`,
        [cmd.customerId],
    );
    return rows[0];
}

async def create_order(customer_id: int) -> dict:
    async with get_pool(read_only=False).connection() as conn:
        async with conn.cursor(row_factory=dict_row) as cur:
            await cur.execute(
                """INSERT INTO orders (customer_id) VALUES (%s)
                   RETURNING id, customer_id, created_at""",
                (customer_id,),
            )
            return await cur.fetchone()

Wait until the replica catches up to the primary

A more complex approach: after a write, get the current WAL position on the primary (LSN) and poll the replica until it has replayed up to that position. Suitable for rare, specific cases where neither the first nor the second option applies.

String lsn = masterJdbc.queryForObject(
    "SELECT pg_current_wal_lsn()", String.class);

do {
    String replayLsn = replicaJdbc.queryForObject(
        "SELECT pg_last_wal_replay_lsn()", String.class);
    if (lsnGte(replayLsn, lsn)) break;
    Thread.sleep(50);
} while (true);

func waitForReplica(ctx context.Context, db *DB, lsn string) error {
    for {
        var replayLSN string
        err := db.Replica.QueryRow(ctx,
            "SELECT pg_last_wal_replay_lsn()").Scan(&replayLSN)
        if err != nil {
            return err
        }
        if lsnGte(replayLSN, lsn) {
            return nil
        }
        select {
        case <-ctx.Done():
            return ctx.Err()
        case <-time.After(50 * time.Millisecond):
        }
    }
}

async function waitForReplica(lsn: string, timeoutMs = 5000): Promise<void> {
    const deadline = Date.now() + timeoutMs;
    while (Date.now() < deadline) {
        const { rows } = await getPool(true).query<{ replay: string }>(
            'SELECT pg_last_wal_replay_lsn() AS replay',
        );
        if (lsnGte(rows[0].replay, lsn)) return;
        await new Promise(r => setTimeout(r, 50));
    }
    throw new Error('replica catch-up timeout');
}

async def wait_for_replica(lsn: str, timeout: float = 5.0) -> None:
    loop = asyncio.get_running_loop()
    deadline = loop.time() + timeout
    while loop.time() < deadline:
        async with get_pool(read_only=True).connection() as conn:
            async with conn.cursor() as cur:
                await cur.execute("SELECT pg_last_wal_replay_lsn()")
                (replay_lsn,) = await cur.fetchone()
        if lsn_gte(str(replay_lsn), lsn):
            return
        await asyncio.sleep(0.05)
    raise TimeoutError("replica catch-up timeout")

Synchronous replication

By default, the primary responds to the client right after writing to the WAL, without waiting for the replica. What switches the waiting on is not synchronous_commit — that is already on — but the list of replicas: while synchronous_standby_names is empty, there is no synchronous replication at all.

# postgresql.conf on the primary
synchronous_commit = on
synchronous_standby_names = 'replica1'

In this mode, the primary waits for confirmation from the replica before responding to COMMIT. The guarantee is stronger, but the price is that the latency of every transaction increases by a network round-trip plus the replica's fsync (1–5 ms locally, tens of milliseconds when geo-distributed).

For most tasks, synchronous replication isn't needed: the asynchronous scheme with proper routing covers 99% of cases. Sync makes sense only where the data is critically important and losing even a millisecond's worth of writes is unacceptable.

Failover

If the primary goes down, tools like Patroni or repmgr detect it and promote the replica to a new primary. DNS or a load balancer switches to the new address, and the connection pools reconnect.

During the switchover (usually 10–60 seconds) writes fail with errors. For critical operations, it's worth adding a retry with exponential backoff:

@Retryable(
    retryFor = SQLException.class,
    maxAttempts = 5,
    backoff = @Backoff(delay = 1000, multiplier = 2, random = true)
)
public OrderId createOrder(CreateOrderCommand cmd) {
    // the method has to be idempotent — see the note below
}

func withRetry(ctx context.Context, maxAttempts int, fn func() error) error {
    delay := time.Second
    for attempt := range maxAttempts {
        err := fn()
        if err == nil {
            return nil
        }
        if attempt == maxAttempts-1 {
            return err
        }
        select {
        case <-ctx.Done():
            return ctx.Err()
        case <-time.After(delay):
            delay *= 2
        }
    }
    return nil
}

async function withRetry<T>(
    fn: () => Promise<T>,
    maxAttempts = 5,
    delayMs = 1000,
): Promise<T> {
    for (let attempt = 0; attempt < maxAttempts; attempt++) {
        try {
            return await fn();
        } catch (err) {
            if (attempt === maxAttempts - 1) throw err;
            await new Promise(r => setTimeout(r, delayMs * 2 ** attempt));
        }
    }
    throw new Error('unreachable');
}

from tenacity import retry, retry_if_exception_type, stop_after_attempt, wait_exponential
from psycopg import OperationalError

@retry(
    retry=retry_if_exception_type(OperationalError),
    stop=stop_after_attempt(5),
    wait=wait_exponential(multiplier=1, min=1, max=16),
)
async def create_order(customer_id: int) -> int:
    async with get_pool(read_only=False).connection() as conn:
        async with conn.cursor() as cur:
            await cur.execute(
                "INSERT INTO orders (customer_id) VALUES (%s) RETURNING id",
                (customer_id,),
            )
            row = await cur.fetchone()
            return row[0]

A retry has a catch worth keeping in mind. The connection can drop after the database has committed the transaction but before the answer reaches the application. The retry then creates a second identical order. So you may only retry what the database itself can recognise as a duplicate: give the order a key supplied by the client and a unique index over it, and the second attempt trips over the database instead of creating a spare row.

Logical replication

Besides streaming replication, PostgreSQL also has logical replication. It copies not the entire WAL stream but changes for specific tables — you can replicate a subset of tables, change the schema, and route data into another system.

Typical uses:

• Piping data from PostgreSQL into an analytical store or Kafka.
• Online migration between two PostgreSQL instances.
• Two-way replication between two nodes — with the caveat that PostgreSQL will not resolve write conflicts for you.

For the "offload the primary via a read-replica" task, ordinary streaming replication is a better fit — it's simpler and faster. Logical has higher overhead.

Monitoring replica lag

You can inspect replica lag directly in PostgreSQL.

On the primary — the state of all replicas:

SELECT application_name, state, replay_lag
FROM pg_stat_replication;

On the replica itself — how much time has passed since the last replayed transaction:

live example

SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

On the primary this function returns NULL — it only answers where log replay is going on.

It's worth setting up an alert if the lag exceeds 30 seconds or if more than 1 GB of WAL has accumulated in the queue — that's a sign of a performance or network problem.

In short

• Kinds of replication: physical streaming (the main one; async, sync, cascading, slots), log shipping through an archive, logical (per table and across versions; DDL and sequences stay behind), and CDC on top of logical decoding — a stream of changes to Kafka.
• Streaming replication: the primary writes WAL, the replica replays it. Normal lag — 50–500 ms; the replica takes heavy SELECTs off the primary and serves as a standby in case of failure.
• The application keeps two connection pools; the primary/replica choice is made by the read-only flag, and it must be deferred until the first query, not until the connection is opened.
• Read-after-write through the replica doesn't work: the replica lags behind. The fixes are — read from the primary, return data via RETURNING, or wait for catch-up by LSN.
• Synchronous replication slows down every COMMIT — it's needed only in rare, critical cases.
• Monitoring: pg_stat_replication on the primary, pg_last_xact_replay_timestamp() on the replica, alert when lag > 30 sec.

What to read next

• Connection pools in PostgreSQL — how to configure two pools for the primary and the replica.
• Transaction isolation levels — how read-only transactions interact with isolation.
• WAL and write performance — what exactly gets replicated and how it affects speed.
• PostgreSQL backups — pg_basebackup and PITR: the same log, used for recovery.