Logical replication looks like data being copied from one database to another, which is a useful mental model right up until you start paying attention to how much work it hides.

Postgres is not simply taking a row from one machine and placing it on another. The publisher first writes a physical change to WAL. A separate process later decodes that storage-level change back into a logical row, reconstructs its transaction, filters it, serialises it, and sends it over the replication protocol. The subscriber then finds the corresponding local relation, applies a new write, maintains its indexes, checks constraints and produces another set of WAL records.

Every replicated change is therefore paid for several times.

This is easy to miss because logical replication is normally asynchronous. The application can continue committing on the publisher whilst the expensive part quietly moves into a walsender, an apply worker, or an ever-growing replication lag graph.

Let’s dig into where that CPU goes, and then measure how much difference a few seemingly harmless schema choices can make.

Glossary

  • WAL - the write-ahead log; the physical record of every change made to heap and index pages
  • logical decoding - the process of reading physical WAL records and reconstructing them as row-level changes grouped into transactions
  • walsender - the publisher-side process which streams decoded changes to a subscriber
  • pgoutput - the built-in output plugin which serialises decoded changes into logical replication protocol messages
  • ReorderBuffer - publisher-side structure holding decoded changes until their transaction commits, so they can be emitted in commit order
  • apply worker - the subscriber-side process which maps incoming messages to local tables and executes the writes
  • replica identity - the columns a publisher sends so the subscriber can locate the row to UPDATE or DELETE
  • replication slot - publisher-side bookmark which retains WAL until a consumer confirms it has been received
  • HOT update - a heap-only tuple update; a new row version written to the same page without touching any index

A Row Becomes Physical Before It Becomes Logical Again

The strange part of logical replication is that the source database does not start with a logical stream.

Postgres executes the original INSERT, UPDATE or DELETE as normal. It modifies heap and index pages and writes WAL describing those storage-level changes. Logical decoding then has to reverse some of that process: it reads the WAL and turns it back into a coherent stream of transactions and tuples.

The full path looks roughly like this:

Publisher
Application DML
↓
Heap + indexes
↓
WALwal_level = logical
↓
Walsenderlogical decoding
↓
ReorderBufferholds txn until commit
↓
pgoutputfilter + serialise
Replication
protocol
Subscriber
Apply workermaps to local relation
↓
Replica identity lookupUPDATE and DELETE only
↓
Heap + indexes+ constraints
↓
WALa second write workload
work which exists only because the stream is logical physical writes paid for at both ends

The built-in implementation uses a walsender on the publisher and an apply worker on the subscriber. The standard pgoutput plugin transforms decoded changes into logical replication protocol messages, and the apply worker maps those messages to local tables in transactional order. This is described in the PostgreSQL logical replication architecture.

Each arrow represents work which can become the bottleneck independently.

Where the Publisher Spends CPU

Writing enough WAL to decode later

Logical replication requires wal_level = logical, which contains everything in replica WAL plus the information needed to extract logical change sets, and Postgres explicitly warns that this can increase your WAL volume, particularly for UPDATE and DELETE operations on tables using REPLICA IDENTITY FULL.

This cost can exist even before a consumer starts reading the stream, because the publisher has to preserve enough information for a future decoder to understand what happened. See the wal_level documentation for the exact distinction.

Decoding and rebuilding transactions

WAL describes changes at the storage layer, so logical decoding has to turn those records back into an application-specific stream of tuples and transactions, which means identifying the relation, reconstructing tuple data, preserving transaction order and keeping changes around until it knows whether the transaction commits.

Postgres uses a ReorderBuffer for this. Large transactions can exceed logical_decoding_work_mem and spill decoded changes to disk, exchanging a memory problem for CPU and I/O. The spill is visible in pg_stat_replication_slots through spill_txns, spill_count and spill_bytes.

SELECT
    slot_name,
    spill_txns,
    pg_size_pretty(spill_bytes) AS spilled,
    stream_txns,
    pg_size_pretty(stream_bytes) AS streamed
FROM pg_stat_replication_slots;

This is why one transaction containing a million changes behaves differently from a million one-row transactions, even when both modify the same number of rows.

Filtering and serialising rows

pgoutput still has to turn decoded tuples into protocol messages, and by default a subscription requests those values in text format, which invokes type output on the publisher and type input on the subscriber. binary = true can avoid some of that conversion, but it is less portable between PostgreSQL versions, machine architectures and differing source/target types.

Binary mode can be faster, but it is not a free switch. PostgreSQL documents the trade-off directly under CREATE SUBSCRIPTION.

Publication filtering also executes here, so whilst column lists can reduce the data sent, row filters will consume publisher CPU to do it. UPDATE is the awkward case: Postgres evaluates the filter against both the old and new row and may transform the change into an INSERT or DELETE when the row moves across the filter boundary. The behaviour is covered in the row-filter documentation.

Where the Subscriber Spends CPU

The subscriber is not replaying the publisher’s page changes, it is applying new logical writes against its own physical layout, which is where most of the surprise comes from.

For every incoming change it may need to:

  • parse the replication message and map remote columns to a local relation
  • locate the target row for an UPDATE or DELETE
  • create a new MVCC tuple version
  • maintain every affected local index
  • enforce applicable local constraints such as NOT NULL, CHECK, unique and exclusion constraints
  • write heap and index changes into local WAL

Normal triggers and rules do not fire because the apply worker runs with session_replication_role = replica. This includes the triggers used to enforce foreign keys. Triggers can be explicitly enabled for replica execution, so they should not be assumed to be free in every schema. Constraints enforced by the executor and indexes still matter: an incoming unique or exclusion conflict can stop replication. PostgreSQL’s conflict documentation describes the failure modes.

Index maintenance is often the dominant subscriber cost, because a subscriber with one primary key and a subscriber with six secondary indexes receive exactly the same logical row whilst doing nowhere near the same amount of work. The second schema produces more page changes, consumes more CPU and writes more local WAL.

This is also why HOT eligibility matters, given that an update can only remain HOT when no indexed value needs changing and there is room on the heap page. Adding an index to a frequently changed subscriber column can turn one heap update into a heap update plus several B-tree writes.

Subscriptions default their apply workers to synchronous_commit = off. This avoids waiting for every subscriber flush and lets Postgres resend changes after a subscriber crash, but it does not eliminate local WAL generation.

Logical Versus Physical Replication

Physical replication gets summarised as “copy bytes” and logical replication as “execute queries”, which is not quite right, because a physical standby still spends CPU replaying WAL and modifying heap and index pages. It is not free.

The difference is that physical replication sends the existing physical WAL records and replays them against an identical physical structure. Logical replication decodes those records into row-level meaning and then generates new local changes on a potentially different schema.

Work Physical replication Logical replication
Publisher writes WAL Yes Yes, with extra logical information
Publisher decodes WAL into rows No Yes
Transfer format Physical WAL records Relation and tuple messages
Receiver locates rows by key No For UPDATE and DELETE
Receiver maintains its own indexes WAL redo changes index pages Executor maintains local indexes
Receiver checks local constraints No SQL-level recheck Yes
Receiver generates new WAL for the replicated change No Yes
Publisher and subscriber schemas may differ No Yes, within logical replication rules

Logical replication buys per-table selection, row and column filtering, cross-version replication and independently designed subscribers. The additional CPU is the price of that flexibility.

Measuring the Cost

I ran two isolated PostgreSQL 18.1 clusters on the same 10-core Apple M1 Pro with 32 GB of memory. Keeping both clusters on one machine is not representative of a production topology, but it makes the CPU competition visible and keeps the test reproducible.

The harness is run-benchmark.sh. It builds two disposable clusters, runs every scenario and tears them down again, so the numbers below can be reproduced directly. The full per-run figures are on the benchmark results page.

The main table was:

CREATE TABLE events (
    id          bigint PRIMARY KEY,
    account_id  integer NOT NULL,
    status      smallint NOT NULL,
    payload     text NOT NULL,
    updated_at  timestamptz NOT NULL
);

Eight clients committed 20,000 transactions in total, each of them inserting 25 rows with a 128-character payload, which comes to 500,000 rows per scenario.

To focus the test on CPU rather than storage durability, both clusters used fsync = off, synchronous_commit = off, full_page_writes = off and autovacuum = off. Every publisher scenario used wal_level = logical, including the no-consumer baseline, and there was no initial table copy.

I sampled CPU time for the PostgreSQL server processes, excluding pgbench and the pg_recvlogical client. Each logical scenario waited for an explicit marker row to become queryable on the subscriber; “caught up” therefore means applied, not merely received. The values below are medians from three clean runs; the raw per-run figures are on the results page.

Scenario Publisher CPU Subscriber CPU Total server CPU Relative to baseline Post-load drain
No consumer 4.71 s 0.00 s 4.71 s 1.00x 0.03 s
Decode to /dev/null 7.92 s 0.00 s 7.92 s 1.68x 4.35 s
Logical apply, text, primary key only 7.69 s 2.94 s 10.62 s 2.25x 2.49 s
Logical apply, binary, primary key only 7.74 s 2.64 s 10.50 s 2.23x 2.12 s
Logical apply, text, four extra subscriber indexes 6.09 s 4.66 s 10.71 s 2.27x 3.60 s

Each column is calculated independently. Total server CPU is the median of the publisher-plus-subscriber total for each run, so it does not always equal the sum of the two independently calculated CPU medians shown beside it.

Decoding the stream without applying it increased publisher-side server CPU from 4.71 to 7.92 seconds. Applying the text stream end-to-end more than doubled total server CPU compared with the no-consumer case.

Binary transfer helped, but not dramatically for these data types. It reduced subscriber CPU by roughly 10%, whilst total measured server CPU fell by about 1%. Measure it against your real column types and version topology before turning it on.

The four additional indexes increased subscriber CPU by 59% and extended the drain from 2.49 to 3.60 seconds. The lower publisher CPU in that row should not be interpreted as an optimisation: both clusters were competing for the same cores, which is one reason publisher TPS alone is a poor logical-replication health signal.

The subscriber WAL counter makes the index cost even clearer. These values are from one representative run, with pg_stat_wal reset immediately before the workload:

Subscriber schema Subscriber WAL
Primary key only, text transfer 136.8 MiB
Primary key only, binary transfer 136.8 MiB
Primary key plus four indexes 271.7 MiB

Binary encoding changed the wire representation, not the local tuple or index layout, so subscriber WAL remained almost identical. Four additional indexes almost exactly doubled it.

These numbers are not a universal logical-replication multiplier, they are a CPU-focused local experiment with durability disabled and no network latency. The receiver client’s CPU is also excluded, so this undercounts total system cost. The shape is what matters: decoding costs something measurable, applying creates a second write workload, and subscriber schema design changes how expensive that is.

The REPLICA IDENTITY FULL Trap

INSERT is straightforward because the subscriber only needs to create a row. UPDATE and DELETE first need to find one.

Postgres normally uses the primary key as the replica identity, though you can explicitly select another suitable unique index instead. When neither exists, REPLICA IDENTITY FULL sends the old row as its identity.

On PostgreSQL 15 and earlier, applying this meant sequentially scanning the subscriber table for a match, and whilst PostgreSQL 16 added the ability to use a suitable B-tree index for a FULL identity lookup, without one the sequential path still exists.

The apply worker makes the choice explicitly: FindReplTupleInLocalRel calls RelationFindReplTupleByIndex when it has a usable local index, and otherwise falls back to RelationFindReplTupleSeq. You can see both branches in the PostgreSQL apply-worker source.

I created 50,000 identical rows on publisher and subscriber, configured the publisher table with REPLICA IDENTITY FULL, paused the subscription and queued updates to the final 2,000 rows. Updating rows near the end is deliberately adversarial: a sequential lookup has to walk most of the table for each change.

The only difference between the two subscriber runs was:

CREATE INDEX full_events_lookup_idx ON full_events (id);
Subscriber lookup path Apply time, run 1 Apply time, run 2
No usable index 4.565 s 4.777 s
B-tree on id 0.149 s 0.151 s

The index made this deliberately bad case approximately 31 times faster.

This is not an argument that every REPLICA IDENTITY FULL workload will be 31x slower. Row order, table width, cache state, update distribution and candidate indexes all matter. What it shows is narrower: the wrong replica identity changes the lookup algorithm for every replicated update.

PostgreSQL’s publication documentation describes which indexes can be used with FULL. PostgreSQL 16’s release notes are also worth reading if you operate a mixture of pre-16 and newer subscribers.

What to Monitor

Logical replication lag is the final symptom, not the diagnosis. I normally want to answer four separate questions.

Is the publisher retaining WAL?

SELECT
    slot_name,
    active,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
    ) AS unconfirmed_wal,
    restart_lsn,
    confirmed_flush_lsn
FROM pg_replication_slots
WHERE slot_type = 'logical';

An inactive or stalled slot can retain WAL indefinitely, and it will also hold back catalog cleanup through catalog_xmin, so disk usage is not the only thing you are risking.

Is decoding spilling?

Use pg_stat_replication_slots and watch spill_bytes as shown earlier. A growing value says the publisher is no longer keeping the decoded transaction in memory.

Is the subscriber receiving or actually applying?

pg_stat_subscription shows the worker type, received LSN and message times, whilst pg_replication_origin_status.remote_lsn records how far changes have actually been replayed for an origin. That distinction matters, because a subscriber can happily receive data faster than it can update its tables.

What is the subscriber writing?

Use pg_stat_wal for local WAL generation, pg_stat_user_tables for tuple and HOT-update behaviour, and normal OS process metrics for the logical replication workers. pg_stat_subscription_stats exposes apply errors and conflicts which can otherwise look like a performance stall.

Practical Ways to Reduce the Cost

Use a narrow, indexed replica identity

This is the single biggest win for update-heavy workloads, so you want either a primary key or a unique index you have deliberately chosen for the job. Treat REPLICA IDENTITY FULL as a fallback, and remember that pre-16 subscribers cannot use the newer indexed FULL lookup path.

Design the subscriber as a write-heavy database

A read replica tends to accumulate indexes because they make analytical queries faster, but under logical replication every one of those indexes joins the write path. Remove the redundant ones, avoid indexing frequently changed columns where you can, and check whether your subscriber updates are still HOT-eligible.

Publish less data

Column lists reduce serialisation, transfer and apply work. Row filters can reduce downstream work too, but they execute on the publisher and an update may evaluate them twice, so you are moving the cost rather than making it disappear. See the PostgreSQL column-list documentation for the replica-identity requirements.

Test binary transfer against the actual topology

binary = true can reduce type conversion, but it is more restrictive across versions and types, and it did not reduce subscriber WAL in this test because the wire encoding does not alter the local write. Use it when your measured CPU or bandwidth justifies the compatibility trade-off.

Watch transaction shape

logical_decoding_work_mem and streaming settings matter for large in-progress transactions, and PostgreSQL 16 introduced parallel application for streamed large ones, controlled by max_parallel_apply_workers_per_subscription. That does not turn every ordinary transaction in your subscription into a freely parallel workload, since transactional ordering still constrains apply.

Capacity-plan both ends

An asynchronous publisher can report perfectly healthy commit latency whilst the subscriber quietly burns CPU and falls behind, which is why your capacity planning needs publisher decode CPU, subscriber apply CPU, subscriber WAL and retained publisher WAL - not only application TPS.

Conclusion

Logical replication is not a cheap copy of data. It is a pipeline which turns a physical change back into a logical one and then turns it into a new physical change somewhere else.

That extra interpretation is what makes it useful. It allows different versions, different schemas, filtered datasets and writable subscribers. It also means that a wide row, an unnecessary index or the wrong replica identity is multiplied across every change in the stream.

Treat a logical subscriber as another write-heavy database, not as passive storage. Measure decoding and apply separately, give updates an efficient identity lookup, and include the subscriber’s indexes and WAL in the cost of every design decision.

References