1. WAL-Based Replication

Every change (INSERT, UPDATE, DELETE) is first written to a sequential log (the WAL) before it touches the actual data files. The primary server commits the transaction only after the WAL entry is safely on disk. Then, it streams those WAL segments to one or more replicas in real time.

The replica reads and replays the same log entries in order — byte for byte.

Pros

• Blazing fast (native to the database engine)
• Strong durability and consistency
• Minimal overhead
• Built into PostgreSQL, MySQL, Oracle, etc.

Cons

• Binary format → tied to the same DB version and hardware
• No schema flexibility across major versions
• Harder to filter or transform data mid-stream

Best for: High-availability setups, failover clusters, zero-downtime reads.

2. Logical Row-Based Replication

The primary captures logical changes (e.g., “INSERT into users (id, name) VALUES (1, 'Alice')”) and sends them as structured events via a binlog (MySQL) or logical decoding stream (PostgreSQL pgoutput).

Replicas receive these row events and apply them using SQL or direct row operations.

Pros