On Thu, Aug 5, 2021 at 12:57 AM Nikhil Shetty <nikhil.dba04@xxxxxxxxx> wrote:
Hi,Thank you for the suggestion.We tried by dropping indexes and it worked faster compared to what we saw earlier. We wanted to know if anybody has done any other changes that helps speed-up initial data load without dropping indexes.
If index maintenance is the bottleneck, nothing but dropping the indexes is likely to be very effective. Just make sure not to drop the replica identity index. If you do that, then the entire sync will abort and rollback once it gets to the end, if the master had had any UPDATE or DELETE activity on that table during the sync period. (v14 will remove that problem--replication still won't proceed until you have the index, but previous synced work will not be lost while it waits for you to build the index.)
Syncing with the index still in place might go faster if shared_buffers is large enough to hold the entire incipient index(es) simultaneously. It might be worthwhile to make shared_buffers be a large fraction of RAM (like 90%) if doing so will enable the entire index to fit into shared_buffers and if nothing else significant is running on the server. You probably wouldn't want that as a permanent setting though.
Cheers,
Jeff
