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?
