Postgres Scale-Out, Replication & Data Movement (deep dive)

Updated Jul 23, 2026 History

Postgres Scale-Out, Replication & Data Movement

When to use it: one Postgres box isn't enough — scaling reads, sharding writes, streaming
changes out (CDC), moving/denormalizing derived data, or planning HA/failover. Companion to
Push Compute to the Datastore (§4b routes analytics off the primary); this is the next
rung. Version-gated items flagged.¹

First: exhaust the cheap levers — indexing, query fixes, a bigger box, read replicas,
in-Postgres columnar (see Push Compute to the Datastore §4b) — before sharding. Sharding is
a complexity step-change, not a first move.

1. Replication

Physical streaming replicas scale reads only (every replica applies the same write
stream — they do nothing for write throughput). Hot standby enforces read-only: DML/DDL,
SELECT FOR UPDATE, sequences, LISTEN/NOTIFY, and SERIALIZABLE all error — route only
plain SELECTs there. Standby reads are eventually consistent.¹

Sync vs async via synchronous_commit: off → local → remote_write → on (standby
fsynced) → remote_apply (standby replayed — a read there sees the write). Sync's latency
floor = RTT to the standby, and a sync standby outage stalls primary writes — reserve
sync for low-RTT/same-AZ, use async for cross-region DR and read-scaling.
Replication lag & read-your-own-writes — fix, cheapest first: (1) route a write + its
immediate follow-up read to the primary; (2) capture pg_current_wal_lsn() after commit,
wait until the replica's pg_last_wal_replay_lsn() catches up; (3) remote_apply to a
designated standby. (WAIT FOR/pg_wal_replay_wait() is PG19-targeted — don't recommend
it on current stable.) Default to (1).
Abandoned replication slots retain WAL forever and can fill the disk (and even force a
wraparound shutdown) — cap with max_slot_wal_keep_size, drop dead slots.
Logical replication (publications/subscriptions) does what physical can't: table-level
selective replication, cross-major-version (the near-zero-downtime upgrade path), and
cross-org data sharing. Limits: DDL and sequences are not replicated (schema must
pre-exist; bump sequences manually after failover), no large objects. Use physical for HA;
logical for moving specific data.

2. Sharding (Citus) — and when you actually need it

Citus distributes a table on a distribution column; a shard is just a normal Postgres
table. The whole game is co-location: put the same distribution key (e.g. tenant_id) in
every table's PK/FK, and queries filtered by it get full SQL, transactions, and local joins
routed to one node. Cross-shard joins/txns use network shuffles + 2PC and do not get faster
as you add nodes (latency is RTT-bound).²

Reference tables (small lookups) are copied to every node — any join against them is
local. Keep them small/low-write (writes are 2PC to all copies).
Good fit: multi-tenant SaaS with never-cross-tenant-joined data; a bounded, curated
analytics query surface. Bad fit: frequent cross-shard joins, ad-hoc analytics, small data.
Citus 11+ citus_rebalance_start is non-blocking (moves shards via logical replication).
Shard vs bigger-box+replicas: replicas fix reads only. Consider sharding when write
IOPS saturate and replication lag grows despite replicas, or the working set far exceeds RAM.
Even a 10TB multi-tenant DB is often Zipfian — the largest tenant fits on one node, so
single-node Postgres lasts longer than intuition says. DIY app-level sharding means
scatter-gather for keyless queries and no native rebalancer (painful resharding) — prefer
Citus if you must shard.

3. CDC / streaming changes out

Logical decoding turns WAL into a change stream via a replication slot + output plugin
(pgoutput — built-in, what Debezium targets; wal2json is deprecated in Debezium). Same
slot danger as §1: an unconsumed slot pins WAL and can shut the DB down — max_slot_wal_keep_size
and drop dead slots.³

Transactional outbox solves the dual-write problem (DB write + broker publish aren't
atomic): write the business row and an outbox row in the same local transaction; a CDC
relay (log-tailing, not polling) publishes them in commit order. Delivery is at-least-once
→ downstream consumers must be idempotent (see Postgres Concurrency & Throughput §6).
CDC vs polling vs LISTEN/NOTIFY: poll an updated_at column for low volume/relaxed
latency; log-based CDC when every intermediate change matters or you feed another datastore;
LISTEN/NOTIFY only as a "something changed, go re-read durable state" ping — it's not
durable (a disconnected listener misses events with no replay), same-database only, payload
<8000 bytes, and if the ~8GB shared queue fills, committing NOTIFY fails.

4. Foreign Data Wrappers

postgres_fdw queries a remote server as local tables; join/sort/UPDATE/DELETE pushdown landed
in PG9.6, aggregates PG10. Traps: a nested-loop-driven foreign scan does N+1 remote round
trips; use_remote_estimate defaults off so plans use bad local guesses (a wrong estimate
can pull the whole remote table over the wire); volatile functions like now() and
non-built-in functions in predicates don't push down. Check EXPLAIN (VERBOSE)'s "Remote SQL"
line — don't assume what pushed down. Multi-server writes are not atomic (no 2PC).⁴

5. Archival & denormalization

Retention via partitions: ALTER TABLE ... DETACH PARTITION / DROP a whole partition
is near-instant and skips the VACUUM overhead of a bulk DELETE (which leaves dead tuples
scaling with row count). DETACH ... CONCURRENTLY (PG14+) avoids the exclusive lock.³
Cold data to object storage: no native S3; use pg_parquet (COPY TO 's3://…' WITH (format 'parquet')) or an external uploader; reserve pg_dump/one-off COPY for
point-in-time archival, not continuous sync (that's CDC's job).
Derived/denormalized data, strongest→cheapest consistency: triggers (synchronous,
same txn — strongest, but hidden latency); generated columns (STORED, PG12+ —
same-row-only, no subqueries/other tables); materialized views (arbitrary cross-table,
but you must REFRESH — use CONCURRENTLY, which needs a UNIQUE index, for live-read views);
CDC-driven async (fully decoupled, eventual, highest ops cost). A likes_count column
beats COUNT(*)-per-read, but every increment is a new row version — shard the counter or
aggregate async under viral write-fanout.

6. HA/failover

Default async replication means a failover can lose the last unreplicated committed
transactions; sync trades that for commit-latency and a write-availability risk if the standby
is unreachable. Patroni automates failover/leader-election over etcd/Consul/k8s with a
DCS leader lock + optional watchdog to prevent split-brain. Managed: RDS Multi-AZ replicates
synchronously with a documented 60–120s failover; Aurora decouples storage across 3 AZs.⁵