Replication Strategies: Statement-Based, WAL, Logical-Log, and Trigger-Based Approaches

Good to see you again. In the previous lesson, you traced a write through a single-leader system: the leader commits the write, followers reproduce the leader’s ordered history, and failover becomes dangerous if followers do not share the same durable prefix of that history.

This lesson focuses on a question left deliberately open: what exactly is in that replication history? A leader can send followers SQL statements, low-level storage records, row-level data changes, or changes captured by database triggers. These mechanisms are often confused because each can keep replicas synchronized, but they make very different promises about portability, flexibility, operational cost, and failure modes.

By the end, you should be able to compare statement-based, write-ahead-log, logical-log, and trigger-based replication—and explain why a system chooses one rather than another.

---

Replication is about the representation of a change

Suppose the leader receives this request:

UPDATE accounts
SET status = 'overdue',
    reviewed_at = CURRENT_TIMESTAMP
WHERE balance < 0;

The leader may find that 8,000 accounts qualify. Once it has committed the transaction, it needs to convey the change to its followers. There are four broad choices:

Method	The leader sends	What the follower does
Statement-based replication	The original SQL statement	Executes the statement itself
Write-ahead-log replication	Low-level storage-engine log records	Replays physical database changes
Logical-log replication	High-level row or record changes	Applies the specified inserts, updates, and deletes
Trigger-based replication	Changes captured by database triggers, usually into a change table	Reads and applies those captured changes

The methods differ most clearly if we ask one question:

Does the follower reproduce the computation, the physical storage changes, or the resulting data changes?

Statement-based replication reproduces the computation. WAL replication reproduces storage changes. Logical and trigger-based replication reproduce data changes at a level closer to tables and rows.

A second important distinction: the replication format and the acknowledgement policy are separate choices. A database can ship a write-ahead log synchronously or asynchronously; it can also stream logical row changes synchronously or asynchronously. The mechanisms in this lesson tell us what moves between nodes, not whether the leader waits for a follower before acknowledging the client.

---

Statement-based replication: replay the command

In statement-based replication, the leader records each write statement it executes and sends that statement to followers. The follower then runs the same SQL against its own local database.

For the example above, the replication record may be essentially the UPDATE statement itself, rather than 8,000 individual row updates. This can be compact: one command can represent a very large change. It also offers an intuitive audit trail because the log says what the application asked the database to do.

The central weakness is that “run the same statement” does not necessarily mean “produce the same result.” A statement may depend on information that differs across replicas:

• CURRENT_TIMESTAMP, random-number generation, or UUID generation;
• the current database user, server settings, or locale;
• a stored procedure with nondeterministic behavior;
• an UPDATE or DELETE whose row selection is ambiguous without a stable ordering;
• differences in schema, data, extensions, or execution behavior.

For example, if every follower evaluates CURRENT_TIMESTAMP independently, they may store different timestamps. Worse, a nondeterministic statement can cause replicas to update different rows, allowing them to silently diverge.

A replication system can mitigate some of these problems by recording generated values centrally or restricting unsafe statements. But those fixes reduce the simplicity that made statement replication attractive in the first place. The basic requirement remains demanding: followers must begin from equivalent states and execute the operation in a sufficiently equivalent environment.

Best fit: controlled environments with deterministic writes, where compact logs and SQL-level auditability matter.

Main hazard: a command records intent, not its exact outcome. If the follower evaluates that intent differently, replica state diverges.

---

Write-ahead-log replication: replay the storage engine’s history

Databases typically write changes to a write-ahead log, often abbreviated WAL, before treating a transaction as committed. This local durable log is essential for crash recovery: after a crash, the database can replay log records to restore committed changes.

With WAL shipping or physical replication, the leader sends those same low-level records to followers. A follower replays them in the same order, creating a near-exact physical copy of the leader’s database state.

At this level, a replication record might describe modifications to pages, indexes, or other internal storage structures. The follower does not need to re-run the original SQL statement and does not need to decide which rows qualify. It simply reproduces the leader’s physical changes.

That avoids the nondeterminism problem of statement replay. If the leader’s transaction changed exactly 8,000 rows, the WAL records embody the resulting physical state changes; followers replay those records rather than independently evaluating balance < 0.

This tight coupling is also WAL replication’s limitation. Because a physical log describes internal implementation details, leader and follower generally need compatible database software, storage formats, and configuration. PostgreSQL’s documentation also highlights a practical scope limitation: physical WAL streaming is normally for the entire database server, not a selected table.

Think of WAL replication as copying an internal machine state, not exporting a portable data feed.

Best fit: high-availability standby replicas that should closely mirror a leader and can run the same database system.

Main strength: exact, efficient reproduction of the leader’s storage-level history.

Main limitation: low-level format coupling makes selective replication, migrations, heterogeneous consumers, and cross-version transitions harder.

---

Logical-log replication: send the data change, not the storage detail

A logical log moves one level upward in abstraction. Instead of sending physical page-level changes, the leader sends a sequence of meaningful data modifications, such as:

• insert a row with a specified primary key and values;
• update this row’s identified columns;
• delete this row;
• commit these changes as one transaction.

For the accounts example, a logical stream may contain row-change records for each affected account, associated with the transaction that made them durable. The recipient applies those records to its own representation of the table.

The term logical is important: it does not necessarily mean that the leader sends the original SQL statement. A logical log captures the outcome at the data-model level. A follower need not re-evaluate the predicate balance < 0; it receives the identity and new values of the rows the leader actually changed.

This makes logical replication more flexible than physical WAL shipping:

• It can replicate selected tables rather than an entire server.
• A target can have a different physical layout or storage engine.
• It can power derived systems, such as search indexes, analytics stores, caches, or event-stream consumers.
• It can support some migration scenarios where the destination schema is compatible but not physically identical.

However, “more flexible” does not mean “free of constraints.” The target must still be able to interpret and apply the records correctly. If a source column has no meaningful destination mapping, or if transactions depend on schema changes that the target lacks, logical replication cannot preserve the intended state automatically. In practice, schema evolution, primary-key identity, transaction boundaries, ordering, and delete handling all need deliberate design.

Logical replication also often creates more network and log volume than statement replication. One statement that changes millions of rows may become millions of row-level change records. That expense buys a useful property: the follower receives the leader’s actual data changes rather than re-executing a potentially ambiguous computation.

Best fit: selective replication, data integration, upgrades and migrations, or derived-data pipelines where recipients need data changes in a portable form.

Main strength: decouples replication from physical storage while preserving the leader’s determined row-level outcome.

Main limitation: potentially larger streams and a continuing need for compatible schemas and carefully defined change semantics.

---

Trigger-based replication: capture changes at the table boundary

Trigger-based replication is usually chosen when the database does not provide an adequate built-in replication log, or when an application needs highly customized capture behavior.

A database trigger is code that runs when a row is inserted, updated, or deleted. A trigger-based system attaches triggers to source tables. Each trigger writes a change record to a separate change or log table; a replication process then reads that table and delivers the captured records to a destination.

The diagram shows why trigger-based replication resembles logical replication: both operate around table and row changes. But they obtain those changes differently. Logical replication generally derives records from a database’s native commit or log machinery. Trigger-based replication makes the capture mechanism explicit in database-level application logic.

For example, a trigger on accounts could record:

• the operation type: insert, update, or delete;
• the account identifier;
• selected old and new column values;
• a transaction identifier or sequence number;
• metadata needed by downstream consumers.

That customizability is valuable. A change table can contain exactly the fields required for a warehouse, a legacy system, or an audit feed. It can also work across products whose physical WAL formats are incompatible.

But triggers add work to every write transaction. They must be maintained as tables change, and a faulty trigger can cause incorrect or incomplete replication data. Trigger systems must also handle difficult details explicitly:

• transactional correctness: a change record should not be delivered as committed if its source transaction later rolls back;
• ordering: consumers need a stable order, particularly for several changes in one transaction;
• schema evolution: triggers and log-table definitions must change alongside application tables;
• operational coverage: bulk loads, maintenance jobs, or administrative paths must not bypass the intended capture mechanism.

Because trigger-based replication often delivers changes asynchronously, it can also have the same failover exposure discussed in the previous lesson: a committed source change may be missing from a destination if the source fails before the change is propagated.

Best fit: custom change capture, heterogeneous integration, or databases lacking a suitable native logical replication feed.

Main strength: maximum control over what is captured and how it is represented.

Main limitation: added write overhead and substantial correctness and maintenance responsibility.

---

Choosing a format: identify the boundary you need

The four mechanisms form a useful spectrum of abstraction:

Format	Replication boundary	Coupling to source database	Typical granularity
Statement-based	SQL computation	High: equivalent execution environment needed	One statement
WAL-based	Physical storage engine	Very high: compatible internals required	Whole server or database cluster
Logical-log	Tables, rows, and transactions	Moderate: compatible data semantics required	Selected tables or records
Trigger-based	Custom table-level capture	Defined by the trigger design	Selected tables, columns, or business events

A practical selection process begins with the intended destination:

1. Choose WAL replication when the destination is primarily a standby that must closely mirror the same database implementation.
2. Choose logical replication when the destination needs selected table changes or must be decoupled from the source’s storage internals.
3. Choose trigger-based capture when built-in logical capture is unavailable or when the change feed needs custom semantics.
4. Choose statement replication cautiously when writes are demonstrably deterministic and the compactness of command logs outweighs the risk of replay differences.

The choice should never rest only on raw throughput. A compact statement log is not useful if it can make replicas diverge. A portable trigger feed is not useful if its capture records do not preserve transaction boundaries. And a robust physical standby may still be the wrong tool for feeding an analytics system that needs only two tables.

---

Key takeaways

Replication always depends on an ordered history, but that history can be represented at very different levels:

• Statement-based replication sends SQL commands. It can be compact and auditable, but nondeterministic statements or differing environments can make followers diverge.
• Write-ahead-log replication ships low-level storage records. It creates close physical replicas and suits standby systems, but tightly couples replicas to the database’s internal format and typically replicates the whole server.
• Logical-log replication sends table- and row-level changes. It is more portable and selective, but may generate more data and still requires compatible schemas and semantics.
• Trigger-based replication captures changes through triggers into a separate log table. It is flexible and customizable, but adds overhead and shifts much of the correctness burden to the trigger design and delivery process.

Next, you will examine what users actually observe when asynchronous replicas lag: stale reads after a write, reads that appear to move backward, and operations that appear in an impossible order.