Multi-insert for logical replication apply - Mailing list pgsql-hackers

Hi,

I would like to propose using multi-inserts for logical replication apply for PG20. The attached patch is a WIP and I'm looking for feedback on the overall approach.

The logical replication apply worker currently inserts tuples one at a time. Each row writes its own WAL record and goes through a page lock/unlock cycle, so a query that inserts 100M rows on the publisher turns into 100M single-row inserts on the subscriber. The patch buffers consecutive inserts within a transaction on the subscriber and applies them with the existing multi-insert table AM, the same way COPY does. When several tuples fit on one page, that writes a single WAL record for all of them and locks the page once instead of once per row.

I ran a few insert-only experiments to see how much this helps (r7i.8xlarge, shared_buffers=16GB, streaming=parallel, checkpoints disabled). I also measured the time spent in the insert call itself, apart from total apply time:

row size    rows   WAL H      WAL P      WAL gain   WAL records H   WAL records P   records gain   insert time H   insert time P   insert gain   apply time H   apply time P   apply gain
----------  -----  --------   --------   --------   -------------   -------------   ------------   -------------   -------------   -----------   ------------   ------------   ----------
50 B/row    100M   7.5GB      2.9GB      2.6x       101060526       1798770         56x            61s             15s             4.0x          487s           469s           1.04x
128 B/row   100M   14.6GB     10.1GB     1.4x       102643991       4512768         23x            138s            92s             1.5x          508s           479s           1.06x
1.4 KB/row  10M    13.2GB     12.8GB     1.03x      10001543        2164961         4.6x           317s            309s            1.03x         334s           319s           1.05x

H = HEAD (single-insert), P = PATCHED (multi-insert). Gain = H / P.

The narrower the row, the more rows fit on a page and the bigger the saving. The WAL reduction is the main benefit - less I/O, fewer WAL files, faster crash recovery, and less WAL to ship for cascading replication. The apply-time change is small (under 5%). My hypothesis is that the other per-row work in the apply path outweighs the WAL write, so fewer WAL records alone doesn't move it much. I'll also test with streaming=off to see how that compares. (I also have small-scale results from 1 to 100K rows showing the gain grows with row count; happy to share if useful.)

For some background, I proposed multi-insert for CREATE TABLE AS and materialized views earlier [1]. That ran into two problems. The slot type there isn't known ahead of time because the top plan node decides it, and I had also bundled in table AM interface changes, which widened the scope. Logical replication apply avoids both - the slot type is always TTSOpsVirtual, and the existing multi-insert table AM API can be used as-is.

Here are the main design decisions I'd like feedback on.

It reuses COPY's existing checks for when multi-insert isn't safe, falling back to the single-row path in the same cases: before/instead-of row triggers, volatile defaults, foreign tables/partitions, and so on.

The patch buffers consecutive inserts to the same relation, in both leader and parallel apply workers. It flushes at 1000 tuples or 64KB (adjustable or configurable later), on a switch to a different relation, or when any non-insert message arrives, which keeps ordering correct in mixed transactions. The first insert always takes the existing single-row path; it switches to multi-insert only after consecutive inserts to the same relation. Inserts into several relations in turn flush on every switch; per-relation buffers could avoid that, but I've left that out for now.

Only the heap write goes through multi-insert. Constraint checks, index maintenance, conflict detection, and after-row triggers still run per row, exactly as the single-row path does, so visible behavior is unchanged. Constraints are evaluated per row before the multi-insert. Pushing these passes into the heap AM layer would avoid the extra loops but needs table AM changes I'd rather not get into here.

On conflict detection: today an insert conflict raises an ERROR, so a conflict on a buffered row rolls back the whole batch. Per-row resolutions (ON CONFLICT DO NOTHING/DO UPDATE style) would need more thought, since rows land in the heap before the per-row check runs.

Currently the subscriber batches single inserts (this patch) and does a multi-insert. Another idea is to add a new message type for multi-insert on the publisher when decoding XLOG_HEAP2_MULTI_INSERT, with the subscriber using multi-insert only on that path. That keeps things simpler, but it only kicks in when the publisher used multi-insert itself (e.g. COPY FROM), and it requires protocol changes.

This is still a WIP and I've likely missed some edge cases, so I appreciate feedback on the overall approach. Thank you!

[1] https://www.postgresql.org/message-id/flat/CALj2ACX9vVYHYkX8e6w058EuAs8JL5EsnzadTxGhpiE_Ep_ByA%40mail.gmail.com

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com