Hi Team,
We have a 10 TB database most of which is occupied by a single table. This table is around 9.5TB. Bloat is 3.5TB for this table. So, the total table size without bloat is 6TB.
We are using native logical replication to remove the bloat for this table to another instance but it takes more than a day to copy 1.5TB of data. With this speed it will take 4-5 days for the initial sync itself. During this initial sync, the replication slot holds WAL and creates disk usage problems on the primary, so we had to stop the replication. We cannot remove the PK since it is the Replica identity.Other indexes and FK have been removed from the destination(secondary).
Other changes we have made in the destination(secondary) instance but still no luck
- fsync=off,synchronous_commit=off, archive_mode=off.
Since it is a single table, we cannot increase worker_processes. Source database is highly transactional and we do not need any downtime.
Is there any other option to increase the speed of initial data copy in logical replication?
Postgres Version - 11.7
OS - OEL7
Thanks and Regards,
Nikhil