Explain Logical vs Physical Replication.

Logical replication replicates data changes (inserts, updates, deletes) at the row or statement level, while physical replication duplicates the entire database files at the storage level for a byte-for-byte copy.

When to Use

• Logical replication: Ideal for selective table replication, cross-version upgrades, and analytics pipelines.
• Physical replication: Best for full database backups, disaster recovery, or read replicas for scaling.

Example

A company replicates only the orders table to a reporting database using logical replication, but uses physical replication to maintain a hot standby for failover.

Want to learn how replication fits into large-scale architectures? Explore Grokking System Design Fundamentals, Grokking the Coding Interview, and Mock Interviews with ex-FAANG engineers.

Why Is It Important

Replication ensures high availability, data durability, and read scalability.

Logical replication offers flexibility for evolving systems, while physical replication provides faster recovery during outages.

Interview Tips

Be ready to define both types clearly. Use examples, mention replication lag, and highlight trade-offs between flexibility and consistency.

Trade-offs

• Logical: + selective control, cross-version support; – higher overhead, schema sync required.
• Physical: + fast failover, exact copy; – version lock-in, more storage I/O.

Pitfalls

• Assuming logical replication copies schema (it only syncs data).
• Using physical replication across different database versions.
• Ignoring overhead costs and network load.

For deeper understanding, check Grokking the System Design Interview and Grokking Database Fundamentals for Tech Interviews.