PostgreSQL Logical Replication Internals for Multi-Region Read Scaling

---
title: "PostgreSQL Logical Replication Internals for Multi-Region Read Scaling"
published: true
description: "Master PostgreSQL logical replication slots, WAL configuration, and lag monitoring for multi-region read scaling. Keep your API p99 latency honest before replication lag breaks your SLA."
tags: postgresql, architecture, api, cloud
canonical_url: https://mvpfactory.co/blog/postgresql-logical-replication-multi-region
---

## What We Will Build

By the end of this workshop you will understand the full lifecycle of a PostgreSQL logical replication slot, know which three configuration knobs govern everything, and have the monitoring queries running on your primary dashboard — before an inactive slot eats your disk.

This is not a getting-started post. It is the post you read after streaming replication stopped being enough.

---

## Prerequisites

- PostgreSQL 13+ (some features like `max_slot_wal_keep_size` require it)
- A primary you can connect to with superuser privileges
- Basic familiarity with WAL and streaming replication concepts

---

## Why Logical, Not Physical

Physical replication ships byte-for-byte page images. Fast and simple, but it replicates everything — bloat, temporary tables, operations your read replicas do not need. More critically, it cannot skip or filter, which makes selective multi-region fan-out impossible.

Logical replication decodes WAL into row-level change streams. Each subscriber receives only the tables it subscribes to, in a format it can apply independently. That selectivity is the entire value proposition for multi-region read scaling.

---

## Step 1: Understand the Slot Lifecycle

A replication slot is a server-side cursor into the WAL stream. The primary guarantees it will retain WAL segments until every slot has consumed them.

Three lifecycle states matter in production:

| State | Risk |
|---|---|
| Active, low lag | Healthy — WAL is consumed and released promptly |
| Active, high lag | Disk pressure — WAL accumulates faster than the slot advances |
| Inactive (subscriber down) | Critical — WAL retention grows unbounded until `max_slot_wal_keep_size` triggers forced invalidation |

Here is the gotcha that will save you hours: an inactive slot is not a monitoring alert — it is a ticking disk bomb. Treat slot inactivity as a P1 incident, not a background task.

---

## Step 2: Set the Three Configuration Knobs

Here is the minimal setup to get this working in `postgresql.conf`:

`hot_standby_feedback` deserves more than a comment. It prevents the primary from vacuuming rows that a long-running replica query still needs — eliminating the dreaded `ERROR: canceling statement due to conflict with recovery`. But it also pins dead row versions on the primary, inflating table bloat under heavy OLAP workloads.

Let me show you a pattern I use in every project: if p99 replica queries are under two seconds, enable it. If you are running analytics joins that run for minutes, use a dedicated replica with a relaxed `max_standby_streaming_delay` instead.

---

## Step 3: Handle Conflicts When Bi-Directional Writes Enter the Picture

Logical replication is single-primary by default. If you allow writes on a subscriber via `pglogical` or Postgres 16's native bi-directional support, conflicts are inevitable.

| Conflict type | Default resolution | Production recommendation |
|---|---|---|
| INSERT on existing key | Error, halt replication | Use `SKIP` with application-layer reconciliation |
| UPDATE on missing row | Skip silently | Log and alert — data drift indicator |
| DELETE on missing row | Skip silently | Generally safe to ignore |

The docs do not mention this, but the safest posture for production bi-directional replication is last-write-wins based on an `updated_at` timestamp column, enforced via a conflict trigger on the subscriber side — not at the replication layer. The replication layer's job is delivery, not business logic.

---

## Step 4: Define Your Replication Lag Budget

Define your lag budget before your SLA is at risk. This query surfaces per-slot lag in bytes and estimated seconds:

For a p99 API budget of 50ms on a read-replica path, lag exceeding 500ms on OLTP workloads warrants immediate investigation. For async reporting reads, 30 seconds may be acceptable. Encode the number — do not leave it implicit.

---

## Gotchas

**Unlimited WAL retention by default.** `max_slot_wal_keep_size` defaults to unlimited in PostgreSQL 13+. Set it to 20–40% of your WAL partition before you need it, not after a 3am disk-full page.

**You cannot observe what you do not monitor.** Replication lag is not a metric for weekly reviews. Add `confirmed_flush_lsn` lag to your primary dashboard today and alert on it continuously.

**Lag is latency, exactly.** If your read path uses replica data, replication lag is not approximately your users' latency — it is exactly their latency. Treat it as a first-class SLO tied to your API p99 target.

---

## Conclusion

Logical replication slots give you fine-grained, per-consumer WAL delivery across regions. Mismanage them and you will blow your disk, stall your primary, and watch p99 latency climb. The slot lifecycle, the three `postgresql.conf` knobs, and the lag monitoring queries above are the foundation. Inactive slots with unbounded WAL retention have caused more surprise outages than misconfigured `wal_level` — now you have the tools to prevent that.

**Further reading:**
- [PostgreSQL: Logical Replication](https://www.postgresql.org/docs/current/logical-replication.html)
- [pg_replication_slots system view](https://www.postgresql.org/docs/current/view-pg-replication-slots.html)
- [max_slot_wal_keep_size](https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE)