Hi Durga,
On Tue, Oct 27, 2026 at 10:35 AM Durgamahesh Manne
<[email protected]> wrote:
> which one would be good and work efficiently 1st or 2nd.
The first, the row filters you already have. In my lab they drained a
backlog at 1.9x and 2.0x the rate of one subscription, the partitions
at 1.8x and 1.9x, and they spare you rebuilding the table.
Both share two costs: each walsender decodes all the WAL before its
filter drops the other rows (together the four used about 5x the CPU
of one, for twice the drain), and a transaction that spans filters is
applied in pieces, not atomically.
If your filters use abs() on a hash, use (hashint8(id) & 3) = 0, 1, 2
and 3 instead: a row hashing to -2147483648 makes abs() fail and stops
all four.
As Justin says, check where the time goes first (CPU and wait_event as
in my last mail): splitting only helps if the apply worker is near
100% CPU.
With 4 CPU threads per side and two runs per case, take the ratios
rather than the rates. Details in the attached measurements.txt.
Regards,
Manu
Row filters vs hash partitions for 4 subscriptions -- PostgreSQL 16.15
=====================================================================
Same lab, table and workload as the measurements.txt of Oct 26: one
host, publisher + pgbench on 4 hardware threads, subscriber on another
4 (2 cores each), logical_decoding_work_mem=64MB, subscriptions with
binary=true, streaming=on. One hot table of 1M rows,
hot(id bigint PRIMARY KEY, k bigint, v bigint, pad text), 16 clients:
UPDATE hot SET k = k + 1, v = :a WHERE id BETWEEN :a AND :a + 19;
Absolute rates are only comparable within this file.
Layouts
- base: plain table, 1 publication, 1 subscription.
- option 1: plain table, 4 publications, 4 subscriptions, row filter
CREATE PUBLICATION pK FOR TABLE hot WHERE ((hashint8(id) & 3) = K);
with K = 0..3.
- option 2: hash partitioned in 4, one publication per partition,
4 subscriptions.
1. Drain rate with the publisher idle
-------------------------------------
30 s of load with the subscriptions disabled, then enabled with the
load stopped. Drain = advance of confirmed_flush_lsn of the slowest
slot, MB of WAL per second. CPU = % of one CPU, averaged while
draining. Two alternating rounds (a, b).
base, round a: 68.7 MB/s; walsender 30%; apply worker 97%
base, round b: 69.5 MB/s; walsender 30%; apply worker 99%
option 1, round a: 132.0 MB/s (1.92x); walsenders 162% in total,
busiest 42%; apply workers 364% in total, busiest 92%
option 1, round b: 136.7 MB/s (1.97x); walsenders 169%, busiest 43%;
apply workers 377%, busiest 95%
option 2, round a: 127.8 MB/s (1.86x); walsenders 149%, busiest 39%;
apply workers 382%, busiest 96%
option 2, round b: 124.7 MB/s (1.79x); walsenders 152%, busiest 39%;
apply workers 377%, busiest 95%
In all of them the apply workers stayed at 92-99% CPU and filled the
4 threads of the subscriber, so about 1.9x is the ceiling of this lab,
not of the method. Every run ended with the same row count and sum(k)
on both sides.
2. Drain with the publisher saturated
-------------------------------------
15 s of backlog, then the subscriptions are enabled while the same
load keeps running for 120 s. Round a started with the CPU lane
already 22% slower than idle (another process); round b was clean and
gave the same numbers.
base, round a: consumed 34.3 MB/s, WAL generated 211.0 MB/s,
48315 commits/s on the publisher
base, round b: consumed 32.7 MB/s, generated 220.9 MB/s, 50908/s
option 1, round a: consumed 66.1 MB/s, generated 155.2 MB/s, 36694/s
option 1, round b: consumed 66.1 MB/s, generated 156.1 MB/s, 36898/s
option 2, round a: consumed 53.9 MB/s, generated 96.9 MB/s, 21775/s
option 2, round b: consumed 52.2 MB/s, generated 102.5 MB/s, 22858/s
None of the three kept up: the backlog grew in every run. Here the
walsenders compete with the load for the same 4 threads.
3. Update rate on the publisher, no subscription
-----------------------------------------------
pgbench, 16 clients, 20 s, two alternating rounds (a, b), transactions
per second.
range updates (the workload above), plain: 55756, 55240
range updates, hash partitioned in 4: 31157, 30974 (0.56x)
(a range of ids cannot be pruned to one hash partition)
single-row updates by primary key, plain: 131553, 130161
UPDATE hot SET k = k + 1, v = :a WHERE id = :a;
single-row updates by primary key, partitioned: 103092, 102573 (0.79x)
4. Updates that change the primary key across filters (option 1)
---------------------------------------------------------------
100k rows, 4 publications with the row filter above, 4 subscriptions
into the same table on the target, a trigger (ENABLE ALWAYS) on the
target logging which subscription applied each change.
One transaction adding 10000000 to every id divisible by 1000: 100
rows, 70 of them land in another filter. Applied on the target: 72
DELETEs and 73 INSERTs spread over the 4 subscriptions (the 70 moves,
the single row below twice, one plain INSERT), and 31 UPDATEs.
A single row moved and moved back, in apply order:
DELETE id 1 via s2, INSERT id 20000001 via s1,
DELETE id 20000001 via s1, INSERT id 1 via s2.
Target and publisher identical at the end (count and md5 of all rows).
Every key value belongs to exactly one filter, so all changes to one
key go through the same subscription in order.
5. Row filter with abs()
------------------------
The same test with
WHERE (abs(hashint8(id)) % 4 = K)
and one row with id 963809240, whose hashint8 is -2147483648.
abs(-2147483648) raises "integer out of range". All 4 walsenders fail
on that row (each one evaluates its filter on every row), their
subscriptions reconnect and fail again: 28 errors in about 30 s,
all 4 slots stuck at the same LSN, the row never reaches the target.
Between 0 and 2^31-1 exactly two ids hash to it: 963809240, 1784257536.
With (hashint8(id) & 3) the same row replicates.
6. From the code (REL_16_STABLE 6ceac2e1250)
--------------------------------------------
Each walsender decodes every change of the database and queues it in
its reorder buffer (decode.c:956 only checks the database,
decode.c:1001). At commit each change is passed to pgoutput
(reorderbuffer.c:1989), which skips tables not in its publication
(pgoutput.c:1476, option 2) or evaluates the row filter
(pgoutput.c:1545, option 1). So with 4 subscriptions the decoding is
done 4 times in both options; only the last check differs.
An UPDATE whose old row matches the filter and new row does not is
sent as a DELETE, and the reverse as an INSERT (pgoutput.c:1408-1424).
