PostgreSQL Logical Replication for Read Scale-Out: An Architecture Note

The problem this design solves

A single PostgreSQL primary handles writes fine, but read traffic — reporting queries, dashboard refreshes, API reads — starts competing with write transactions for connections, buffer cache, and I/O. The smallest fix that preserves consistency semantics is logical replication: one publisher, one or more read-only subscribers, and application routing that sends reads to the subscribers.

Logical replication (built into PostgreSQL since version 10) streams row-level changes decoded from the write-ahead log (WAL), rather than copying disk blocks like physical streaming replication. That means subscribers can have their own indexes, extra tables, and different vacuum tuning — but it also means more operational surface area, which is what this note covers.

Requirements

• A genuinely read-heavy workload. If your bottleneck is writes, this design does nothing for you.
• Publisher and subscriber on PostgreSQL 10 or later. Keep major versions aligned unless you have verified cross-version replication works for your data types; the supported path is subscriber major version equal to or newer than the publisher.
• Tolerance for read-your-writes lag. Subscribers are asynchronous by default, so a read immediately after a write may return stale data. If that breaks your application, route those reads to the primary or reconsider the design.
• Every replicated table needs a replica identity — normally the primary key. Tables without one can only replicate inserts.

The smallest suitable design

One publication on the primary, one subscription per read replica, no middleware. On the publisher:

-- run on the publisher as a superuser or table owner
CREATE PUBLICATION app_read FOR TABLE orders, customers, line_items;

The publisher needs wal_level = logical in postgresql.conf (requires a restart) and a replication user:

CREATE ROLE repl_user WITH REPLICATION LOGIN PASSWORD '...';
GRANT SELECT ON orders, customers, line_items TO repl_user;

On the subscriber, create matching empty tables first (logical replication does not copy the schema for you), then:

-- run on the subscriber as a superuser
CREATE SUBSCRIPTION app_read_sub
CONNECTION 'host=primary.example.com dbname=app user=repl_user sslmode=verify-full'
PUBLICATION app_read;

Creating the subscription triggers an initial table copy, then switches to streaming changes. Verify with SELECT * FROM pg_stat_subscription; on the subscriber and SELECT * FROM pg_replication_slots; on the publisher.

Trust and data boundaries

The replication connection carries row data over the network. Without TLS it is plaintext, so either the network path is trusted (same private subnet with security groups) or you set sslmode=verify-full with proper certificates. Do not rely on sslmode=require without certificate verification if the path crosses anything untrusted.

The replication role needs only REPLICATION plus SELECT on published tables — not superuser, not write access. On the subscriber side, the subscription applies changes as the subscription owner, so make that owner a dedicated role rather than a broad admin account. Subscribers are not enforced read-only; anything with write access to the replicated tables can cause conflicts that break apply. Revoke writes from application roles on subscriber tables.

Operational checks

• Lag: query pg_stat_subscription on the subscriber and compare latest_end_lsn against the publisher's current LSN. Alert when lag in bytes exceeds a threshold you choose based on acceptable staleness (100 MB is a reasonable starting point, not a rule).
• Slot health: on the publisher, SELECT slot_name, active, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained FROM pg_replication_slots;. A logical slot retains WAL until the subscriber consumes it — a stalled subscriber will fill the publisher's disk. This is the single most common way logical replication takes down a primary, so alert on retained WAL size, not just slot existence.
• Apply errors: check pg_stat_subscription_stats for error counts and the subscriber logs for constraint or conflict messages.

Failure modes

• Subscriber down longer than WAL tolerance: the slot keeps retaining WAL. Either the publisher disk fills (outage) or you drop the slot and reinitialize the subscriber with a fresh copy. Size publisher disk headroom around your worst-case subscriber outage.
• DDL divergence: ALTER TABLE is not replicated. Adding a column on the publisher without applying the same change on the subscriber can halt apply or silently mismatch. Make schema migrations a two-step runbook: subscriber first (if the change is additive), then publisher.
• Write conflicts on the subscriber: a local write that collides with a replicated row stops apply for that table until resolved manually.

When to change the design

If writes become the bottleneck, read replicas no longer help — look at sharding or partitioning instead. If you need zero staleness for specific reads, route them to the primary rather than forcing synchronous logical replication. If you need multi-region writes, that is a different problem (bidirectional replication with conflict handling) and should not be bolted onto this design. Finally, if you do not need per-subscriber indexes or extra tables, physical streaming replication with hot standby is simpler to operate and replicates DDL automatically.