On Tue, Oct 27, 2026 at 2:56 AM Justin <[email protected]> wrote:
> Hi Manu, and Durga, > > We need to identify the cause of the slow replication, guessing and > throwing solutions at the wall to see what sticks only slows the process > down. > > Durga, have you looked at what the publisher is sending to the subscriber? > What is the structure of the updates/deletes? > > Inserts have little to no overhead for LR. > > Do you have any table triggers with ENABLE REPLICA that are set on the > subscriber that are not on the primary? > > Would be nice to see the DDL of the tables being published, and the > structure of the tables on the subscriber, including indexes and triggers. > > Partitioning the tables: the answer is yes, because you can break each > partition into its own publication and subscription that allows for > parallelization. There are hackish ways around having to reseed the tables. > But if you change the publisher table structure, just change the subscriber > at the same time. > > I doubt bandwidth is the problem unless PG is using a NAS/SAN to hold the > database and the subscriber is using a 1gb ethernet connection and all the > network traffic goes across it. > > I do not recall seeing any conversation regarding the volume of data being > replicated; only 330 GB of WAL lag. Over what period of time does this lag > occur? > > I've worked on Logical Replication where the servers generated 1TB of WAL > traffic a day. The trick in these conditions is identifying which tables > required their own publication and subscriber to prevent replication lag. > > LR ignores unrelated WAL entries to what it is publishing, so is the > entire database being published over a single publication? > Hi Manu and justin Entire database being published over a single publication. Each table has a primary key to replicate the data efficiently rather than replica identity full which generates huge wal generation.Over 23 tables only one table has huge updates rest of tables have very few changes to replicate Here 4 publications with a hash option on a single non partitioned table which has huge update ops to distribute data over slot to subscriptions. So LR uses workers efficiently to flush the data to disk in parallel at target. Need to check how this works over the weekend when data load increases Need to create hash based partitions then create publications on individual child tables to check how this works. I think Justin might know the first approach. which one would be good and work efficiently 1st or 2nd. Regards, Durga Mahesh
