Explain the difference between physical (block/WAL-shipping) replication and logical (row-level) replication between relational databases, and what each produces on the receiving side.
answer
- Physical = page/byte deltas from the log → exact clone, same version
- Logical = decoded row events → selective, writable, cross-version
- Physical: DDL free, indexes free, read-only, corruption copied
- Logical: needs row identity, DDL manual, conflicts possible
- Row-based beat statement-based because of nondeterminism
basics
~20 sPhysical replication ships the low-level change log describing byte and page edits, so the replica is a block-for-block clone — same version, all objects, read-only. Logical replication decodes changes into row-level events (insert/update/delete with column values) that the target executes, so it can differ in version, schema and contents.
solid answer
~60 s**Physical replication** streams the engine's write-ahead log — records like "on file X, page Y, at offset Z, write these bytes". The receiver replays them onto its own copy of the same files. The result is a **byte-identical clone**: every table, every index, every schema, the same physical layout, the same major version, typically read-only while in recovery. It is cheap (no per-row interpretation), preserves everything automatically, and is the standard basis for a hot standby and for failover. **Logical replication** decodes the same log into **row-level change events** — "in table orders, the row identified by id=42 changed these columns to these values" — and the target applies them as ordinary DML. The target is an independent, writable database: it can run a different major version, hold only some tables, have different indexes, and carry local tables of its own. The trade: physical gives you an exact copy for free but couples the two nodes tightly (same version, all-or-nothing, no writes). Logical decouples them at the cost of per-row apply work, no automatic DDL propagation, and conflicts that a writable target can create.
code
text · 14 linesStatement executed on the source:
UPDATE orders SET status = 'SHIPPED' WHERE id = 42;
Physical stream carries (conceptually):
file 16384 block 912 : byte delta on heap tuple
file 16391 block 44 : index page split, key moved
visibility/bookkeeping: page changes
Logical stream carries:
BEGIN
UPDATE relation public.orders
identity: id = 42
new: status = 'SHIPPED'
COMMITgo deeper
Give the one-line contrast — page-level byte changes versus row-level change events — and name one consequence of each, such as exact clone versus selective tables.
Cover version coupling, read-only versus writable target, DDL propagation, and the cost difference in apply.
Discuss when to run both, the operational burden logical adds (identity, DDL coordination, conflicts, slot retention), and why physical remains the HA default.
Frame it as a coupling decision: physical buys completeness and throughput at the price of lockstep versions and all-or-nothing scope; logical buys independence at the price of ongoing coordination — and most estates need both.
## Two different things being copied Every relational engine records changes in an append-only log before applying them to data files — the write-ahead log (WAL) in PostgreSQL, the redo log in Oracle/InnoDB, and MySQL additionally maintains a separate binary log. Both replication styles are fed from a log; they differ in **what level of abstraction they copy**. **Physical replication copies the storage-level change record.** A WAL record says, in effect: "in relation file 16384, block 912, apply this byte delta". Replaying it requires that the receiving node have byte-identical files to begin with and understand the same on-disk format. What arrives is not "an UPDATE" — it is the *consequence* of an update on physical pages, and the same is true of the index pages the update touched, the visibility bookkeeping, and everything else. **Logical replication copies row-level change events.** A decoding step reads the same log and reconstructs, per committed transaction, a stream of operations at the level a person would recognise: INSERT into table T with these column values; UPDATE the row identified by *this* key, setting these columns; DELETE the row identified by *this* key. The receiver executes those as normal writes against its own tables, building its own indexes as a side effect. ## What each implies **Physical:** - **Exact clone.** All databases, tables, indexes, sequences — everything, always, with no configuration of what to include. - **Version and platform lock-in.** Both nodes must run the same major version and a compatible architecture, because the format of pages and log records is internal and changes between releases. - **Read-only receiver.** The node is in continuous recovery; permitting local writes would diverge the byte-level state that replay assumes. (It can be *promoted* to become writable, which ends replication.) - **Cheap.** No parsing, no planning, no per-row lookup — replay writes pages. Throughput is high and predictable. - **Indexes come free.** Index maintenance was already recorded in the log; the receiver does not rebuild indexes, it replays the page changes. - **DDL is automatic.** A schema change is just more page changes, so it propagates with no coordination. - **Corruption propagates.** A physically damaged page is faithfully copied. A physical standby is not a substitute for backups. **Logical:** - **Selective.** A publication/subscription model lets you choose which tables (and often which operations, and sometimes which rows or columns) flow. The target may hold a subset. - **Version- and platform-independent.** Row events are engine-internal but not page-format-internal, so the target may run a newer major version, or even a different system entirely if something translates the stream. This is what makes near-zero-downtime major-version upgrades possible. - **Writable target.** It can hold local tables, extra indexes, different storage parameters, its own users. It can also receive from multiple sources. - **Expensive per change.** Each row event is an actual write on the target: index maintenance, constraint checks, its own logging. Sustained throughput is well below physical for the same workload. - **Row identity required.** To apply an UPDATE or DELETE the target must locate the affected row, which needs a primary key or designated unique identity. - **DDL does not propagate.** Schema changes must be applied to both sides by an operator, in the right order. - **Conflicts are possible.** Because the target accepts local writes, a locally inserted row can collide with a replicated one; typically the subscription stalls until a human resolves it. ## Choosing | Need | Fits | |---|---| | Hot standby for failover, exact copy, minimal lag | Physical | | Read scaling with identical schema and data | Physical | | Replicate a subset of tables | Logical | | Target must be writable / hold local data | Logical | | Cross-major-version replication or upgrade | Logical | | Different index set on the target | Logical | | Feed a change stream to another system | Logical | Many production estates run both: physical standbys for high availability, and a logical stream to a reporting database or a downstream consumer. They are complements, not competitors. ## One clarifying point about "row-based" Within logical replication there is a second axis: whether the stream carries **the statement** or **the resulting rows**. MySQL's binary log historically supported statement-based, row-based, and mixed formats. Statement-based is compact but unsafe with nondeterministic constructs (functions that return different values on each node, order-dependent updates with limits), so row-based became the default and is what modern logical replication universally means. When an interviewer asks about "logical replication", assume row-level events unless they say otherwise.
- Why can a physical replica not run a different major version of the engine than its primary?Physical replication replays storage-level records that reference page layouts, tuple headers and internal catalog structures whose formats are private to a major version and change between releases. A node running a different major version could not interpret those records, and even if it could, its own files would not be byte-compatible with the ones the records assume. Logical replication avoids this because row events describe data, not pages.
- If logical replication is more flexible, why is physical still the default for high availability?Physical replay is dramatically cheaper — it writes pages rather than executing DML with constraint checks and index maintenance — so it sustains higher write rates with lower lag, which is what a failover candidate needs. It also copies everything automatically, including DDL, sequences and every table, so the standby is a complete, promotable copy with no ongoing coordination. Logical would require you to keep schema, sequences and coverage in sync by hand to get an equivalent failover target.
Physical replication is photocopying a book page by page — you get every mark exactly, but only onto the same size paper. Logical replication is dictating the edits — 'on the customers list, change row 42's city to Berlin' — which the other side can write into a differently formatted, differently sized book.
saying these in an interview costs you the question
- Saying physical replication ships SQL statements
- Claiming a physical standby can run a newer version for an upgrade
- Assuming logical replication propagates DDL automatically
- Thinking logical replication is always faster because 'it sends less data'
- Treating a physical standby as a backup — corruption and erroneous deletes replicate faithfully