Re: Logical Replication speed-up initial data

On Thu, Aug 5, 2021 at 12:57 AM Nikhil Shetty <nikhil(dot)dba04(at)gmail(dot)com>
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