Build Your Own PostgreSQL
Replication and Streaming Change
What You Will Learn
- Understand one history, another machine in a PostgreSQL-like database
- Understand replication stream in a PostgreSQL-like database
- Understand why not replay SQL? in a PostgreSQL-like database
- Understand lag is part of the contract in a PostgreSQL-like database
One history, another machine
Replication begins with a practical concern. A database should not be a single fragile point. A standby can take over after failure, serve read traffic, back up data, or feed downstream systems. In a Postgre-like architecture, physical streaming replication grows out of WAL. The primary already writes an ordered log of storage changes for crash recovery, so a standby can receive that log and replay it to maintain a copy.
The primary produces WAL as transactions modify data. A replication sender streams WAL records to a standby. The standby writes the received WAL and replays it against its own data files. In recovery mode, the standby is always applying the primary's history. It does not plan SQL changes on its own. It follows the physical change stream. This keeps replicas close to storage truth and avoids re-executing user statements in a different environment.
Replication stream
Why not replay SQL?
Statement replication sounds compact because it sends SQL and re-runs it on replicas, but that compactness is fragile. Functions may be volatile. Execution order may differ. The schema or configuration may not match exactly. Timing-dependent behavior can diverge. Physical WAL replication sends the effects at the storage level, which aligns with crash recovery and is much harder to reinterpret incorrectly.
Physical replication has its own constraints. The standby is tied closely to the primary's storage format, major version compatibility, and timeline. It is good at maintaining a faithful copy, but it is not a flexible transformation stream. Logical replication takes a different route by decoding committed row-level changes into table-oriented messages. That can replicate selected tables or feed other systems, but it depends on primary keys, replica identity, and higher-level interpretation.
Lag is part of the contract
Replication lag is the distance between a change on the primary and its replay on the standby. The primary may commit a transaction before the standby receives it. A read from the standby can therefore be stale. For many workloads, slightly stale reads are fine. For others, they are dangerous. The application and the database configuration must agree on whether read scaling is worth delayed visibility.
Synchronous replication tightens the promise by making commit wait for one or more standbys to confirm receipt, flush, or apply of WAL, depending on configuration. This reduces the risk of data loss if the primary fails right after commit. It also adds network and standby health to commit latency. A synchronous standby that slows down can slow the primary. Replication does not give you free durability. It gives you durability with a wider critical path.
Replication slots solve a retention problem. A standby or logical consumer may fall behind. The primary must keep WAL long enough for that consumer to catch up, otherwise the consumer hits a gap and needs reinitialization. A slot records how far the consumer has progressed. This protects the consumer, but it can fill the primary's disk if the consumer stops for too long. Retention is safety that comes with a cost.
Leadership creates timelines
Failover introduces timelines. When a standby is promoted, it stops replaying and becomes a new primary that generates its own WAL. The old primary, if it returns, may have a divergent history. Timeline identifiers distinguish these branches. Recovery tools need to know which history is authoritative and how far to follow it. A replicated system is more than two servers. It is a managed history of leadership.
Hot standby reads bring MVCC questions to replicas. A standby applying WAL may need to remove old tuple versions, but a read query on the standby might still need them. The standby can delay replay, cancel the query, or use feedback to tell the primary about old snapshots. Each choice trades replica freshness against query stability and primary cleanup. Even read scaling loops back to vacuum and transaction horizons.
Backups also rely on replication concepts. A base backup captures data files while WAL continues. To make the backup consistent, restore must replay WAL from the right point. Continuous archiving stores WAL segments so recovery can proceed to a chosen time. The log stream is the thread that connects crash recovery, standby maintenance, and backup strategy.
Into partitioning
Replication extends the database promise beyond one process tree and one disk set. It turns WAL into a shared timeline. The next scaling shape is not another copy of the whole database. It is a division of data into parts. Partitioning asks how a single logical table can be split while remaining queryable.
The distinction matters when systems grow. Copies preserve and multiply the same history. Partitions divide responsibility for different slices of that history. Both depend on keeping metadata, planning, and recovery rules explicit.
A growing system needs to copy state when it must and to divide it deliberately when one machine is no longer enough.