Hello, I am trying to speed up the initial logical replication sync process. The database being replicated is dominated by one table that is 750GB (heap). The process quickly boils down to a single COPY writing into the subscriber. We have dropped all indexes and key constraints in the subscriber but we are seeing the sync take >24 hours before we have to kill the process (to avoid so much WAL being reserved on the publisher).
I haven’t done a huge amount of performance tuning at the Linux level before as I’m used to working with cloud-managed installations where you obviously don’t have access to the underlying host. However, in this case, the subscriber instance is not a cloud-managed one. Can anyone give comment on what might be a reasonable throughput in MB/s for a single COPY operation? The material I’ve read on I/O talks about saturating the device … I’m pretty sure that a single COPY operation is not capable of doing this. It’s therefore one thing to see the advertised top-line figures about IOPS and throughput, vs what you can actually do with the single COPY. I’d be interested in hearing what other people are able to get as a throughput figure for COPY. -Joe
