Physical versus logical

The senior course "Database architecture: replicas" described physical replication. Logical replication is a different mechanism with a different purpose, and the difference is the whole subject.

What physical replication copies

Bytes. The primary writes changes to the write-ahead log, and a physical replica replays those byte-level changes onto an identical copy of the data files.

That has three consequences worth stating.

The replica is an exact copy: every table, every index, every row. You can't replicate part of it.

It must run the same major Postgres version, because the byte format is specific to the version.

And it's read-only. Replaying WAL onto files that something else is also writing would corrupt them.

What logical replication copies

Decoded row changes. Postgres reads the same write-ahead log but decodes it into logical operations, such as "insert this row into courses" or "update the row with this key", and sends those.

The three consequences flip.

You choose what to replicate: specific tables, and even specific operations.

The versions can differ. A Postgres 14 publisher can feed a Postgres 17 subscriber, because rows are being described rather than bytes copied.

And the subscriber is a normal, writable database. It has its own indexes and its own extra tables, and it accepts writes of its own.

flowchart TB
  W["the write-ahead log"] --> P["physical: replay the bytes"]
  W --> L["logical: decode into row changes"]
  P --> P1["exact copy, same version, read-only"]
  L --> L1["chosen tables, any version, writable subscriber"]

The trade

Physical replication is cheaper and more faithful. It uses less CPU, has less to go wrong, and the replica is guaranteed to be identical.

Logical replication is more flexible and more work. Decoding costs CPU on the publisher, and the subscriber can drift: it accepts writes, so nothing guarantees the two agree beyond the changes being shipped.

That drift is a feature, not a defect. It's what lets a subscriber add its own indexes for reporting, hold extra columns, or merge streams from several publishers.

Where this application stands

Neither is configured, and one of them isn't currently possible:

wal_level          replica
publications       0
subscriptions      0
replication slots  0

wal_level is replica, not logical. Physical replication would work today. Logical replication can't start until that setting changes, and changing it requires a restart. Lesson 3 covers what that costs.

Key takeaways

• Physical replication replays WAL bytes, and logical replication decodes them into row-level operations.
• Physical gives an exact, read-only copy on the same major version. Logical gives chosen tables on any version.
• A logical subscriber is a normal writable database and can drift, which is the point, not a flaw.
• Physical is cheaper and more faithful. Logical costs decoding CPU and gives up the guarantee that the copies are identical.
• Here wal_level is replica, so logical replication isn't possible without a configuration change and a restart.