Thread
-
Re: Conflict detection for update_deleted in logical replication
Nisha Moond <nisha.moond412@gmail.com> — 2025-07-18T10:55:16Z
On Thu, Jul 17, 2025 at 4:44 PM shveta malik <shveta.malik@gmail.com> wrote: > > On Thu, Jul 17, 2025 at 9:56 AM Dilip Kumar <dilipbalaut@gmail.com> wrote: > > > > On Fri, Jul 11, 2025 at 4:28 PM Amit Kapila <amit.kapila16@gmail.com> wrote: > > > > > > On Thu, Jul 10, 2025 at 6:46 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote: > > > > > > > > On Wed, Jul 9, 2025 at 9:09 PM Amit Kapila <amit.kapila16@gmail.com> wrote: > > > > > > > > > > > > > > > I think that even with retain_conflict_info = off, there is probably a > > > > > > point at which the subscriber can no longer keep up with the > > > > > > publisher. For example, if with retain_conflict_info = off we can > > > > > > withstand 100 clients running at the same time, then the fact that > > > > > > this performance degradation occurred with 15 clients explains that > > > > > > performance degradation is much more likely to occur because of > > > > > > retain_conflict_info = on. > > > > > > > > > > > > Test cases 3 and 4 are typical cases where this feature is used since > > > > > > the conflicts actually happen on the subscriber, so I think it's > > > > > > important to look at the performance in these cases. The worst case > > > > > > scenario for this feature is that when this feature is turned on, the > > > > > > subscriber cannot keep up even with a small load, and with > > > > > > max_conflict_retetion_duration we enter a loop of slot invalidation > > > > > > and re-creating, which means that conflict cannot be detected > > > > > > reliably. > > > > > > > > > > > > > > > > As per the above observations, it is less of a regression of this > > > > > feature but more of a lack of parallel apply or some kind of pre-fetch > > > > > for apply, as is recently proposed [1]. I feel there are use cases, as > > > > > explained above, for which this feature would work without any > > > > > downside, but due to a lack of some sort of parallel apply, we may not > > > > > be able to use it without any downside for cases where the contention > > > > > is only on a smaller set of tables. We have not tried, but may in > > > > > cases where contention is on a smaller set of tables, if users > > > > > distribute workload among different pub-sub pairs by using row > > > > > filters, there also, we may also see less regression. We can try that > > > > > as well. > > > > > > > > While I understand that there are some possible solutions we have > > > > today to reduce the contention, I'm not really sure these are really > > > > practical solutions as it increases the operational costs instead. > > > > > > > > > > I assume by operational costs you mean defining the replication > > > definitions such that workload is distributed among multiple apply > > > workers via subscriptions either by row_filters, or by defining > > > separate pub-sub pairs of a set of tables, right? If so, I agree with > > > you but I can't think of a better alternative. Even without this > > > feature as well, we know in such cases the replication lag could be > > > large as is evident in recent thread [1] and some offlist feedback by > > > people using native logical replication. As per a POC in the > > > thread[1], parallelizing apply or by using some prefetch, we could > > > reduce the lag but we need to wait for that work to mature to see the > > > actual effect of it. > > > > > > The path I see with this work is to clearly document the cases > > > (configuration) where this feature could be used without much downside > > > and keep the default value of subscription option to enable this as > > > false (which is already the case with the patch). Do you see any > > > better alternative for moving forward? > > > > I was just thinking about what are the most practical use cases where > > a user would need multiple active writer nodes. Most applications > > typically function well with a single active writer node. While it's > > beneficial to have multiple nodes capable of writing for immediate > > failover (e.g., if the current writer goes down), or they select a > > primary writer via consensus algorithms like Raft/Paxos, I rarely > > encounter use cases where users require multiple active writer nodes > > for scaling write workloads. > > Thank you for the feedback. In the scenario with a single writer node > and a subscriber with RCI enabled, we have not observed any > regression. Please refer to the test report at [1], specifically test > cases 1 and 2, which involve a single writer node. Next, we can test a > scenario with multiple (2-3) writer nodes publishing changes, and a > subscriber node subscribing to those writers with RCI enabled, which > can even serve as a good use case of the conflict detection we are > targeting through RCI enabling. > I did a workload test for the setup as suggested above - "we can test a scenario with multiple (2-3) writer nodes publishing changes, and a subscriber node subscribing to those writers with RCI enabled". Here are the results : Highlights ========== - Two tests were done with two different workloads - 15 and 40 concurrent clients, respectively. - No regression was observed on any of the nodes. Used source =========== pgHead commit 62a17a92833 + v47 patch set Machine details =============== Intel(R) Xeon(R) CPU E7-4890 v2 @ 2.80GHz CPU(s) :88 cores, - 503 GiB RAM 01. pgbench with 15 clients ======================== Setup: - Two publishers and one subscriber: pub1 --> sub pub2 --> sub - All three nodes have same pgbench tables (scale=60) and are configured with: autovacuum = false shared_buffers = '30GB' -- Also, worker and logical replication related parameters were increased as per requirement (see attached scripts for details). - The topology is such that pub1 & pub2 are independent writers. The sub acts as reader(no writes) and has subscribed for all the changes from both pub1 and pub2. Workload: - pgbench (read-write) was run on both pub1 and pub2 (15 clients, duration = 5 minutes) - pgbench (read-only) was run on sub (15 clients, duration = 5 minutes) - The measurement was repeated 2 times. Observation: - No performance regression was observed on either the writer nodes (publishers) or the reader node (subscriber) with the patch applied. - TPS on both publishers was slightly better than on pgHead. This could be because all nodes run on the same machine - under high publisher load, the subscriber's apply worker performs I/O more slowly due to dead tuple retention, giving publisher-side pgbench more I/O bandwidth to complete writes. We can investigate further if needed. Detailed Results Table: On publishers: #run pgHead_Pub1_TPS pgHead_Pub2_TPS patched_pub1_TPS patched_pub2_TPS 1 13440.47394 13459.71296 14325.81026 14345.34077 2 13529.29649 13553.65741 14382.32144 14332.94777 median 13484.88521 13506.68518 14354.06585 14339.14427 - No regression On subscriber: #run pgHead_sub_TPS patched_sub_TPS 1 127009.0631 126894.9649 2 127767.4083 127207.8632 median 127388.2357 127051.4141 - No regression ~~~~ 02. pgbench with 40 clients ====================== Setup: - same as case-01 Workload: - pgbench (read-write) was run on both pub1 and pub2 (40 clients, duration = 10 minutes) - pgbench (read-only) was run on sub (40 clients, duration = 10 minutes) - The measurement was repeated 2 times. Observation: - No performance regression was observed on any writer nodes, i.e, the publishers, or the reader node i.e., subscriber with the patch applied. - Similar to case-01, TPS on both publishers was slightly higher than on pgHead. Detailed Results Table: On publisher: #run pgHead_Pub1_TPS patched_pub1_TPS pgHead_Pub2_TPS patched_pub2_TPS 1 17818.12479 18602.42504 17744.77163 18620.90056 2 17759.3144 18660.44407 17774.47442 18230.63849 median 17788.7196 18631.43455 17759.62302 18425.76952 - No regression On subscriber: #run pgHead_sub_TPS patched_sub_TPS 1 281075.3732 279438.4882 2 275988.1383 277388.6316 median 278531.7557 278413.5599 - No regression ~~~~ The scripts used to perform above tests are attached. -- Thanks, Nisha