Logical Replication speed-up initial data

[Nikhil Shetty]

Hi Team,

We have a highly transactional system as the source of logical replication
and the database size is 500GB+. We are replicating all tables from source
using logical replication.

For two tables the initial data load is very slow and it never completes
even after 24hrs+
Table size is under 100GB and index size is around 400GB.

How can we increase the speed of the initial data load without dropping the
indexes on destination?

We increased max_sync_workers_per_subscription to 3 but it didn't help much
for single tables

Thanks,
Nikhil

Re: Logical Replication speed-up initial data

[Hüseyin Demir]

Hello,

I also faced a similar issue. Try removing the indexes on the destination
first if possible. After that, you can add the indexes.

Regards.


Nikhil Shetty , 4 Ağu 2021 Çar, 18:07 tarihinde
şunu yazdı:

> Hi Team,
>
> We have a highly transactional system as the source of logical replication
> and the database size is 500GB+. We are replicating all tables from source
> using logical replication.
>
> For two tables the initial data load is very slow and it never completes
> even after 24hrs+
> Table size is under 100GB and index size is around 400GB.
>
> How can we increase the speed of the initial data load without dropping
> the indexes on destination?
>
> We increased max_sync_workers_per_subscription to 3 but it didn't help
> much for single tables
>
> Thanks,
> Nikhil
>


-- 
Hüseyin Demir

Senior Database Platform Engineer

Twitter:  https://twitter.com/d3rh5n
Linkedin: hseyindemir

Github: https://github.com/hseyindemir
Gitlab: https://gitlab.com/demirhuseyinn.94
Medium: https://demirhuseyinn-94.medium.com/

Re: Logical Replication speed-up initial data

[Stefano Amoroso]

Hello,
in my experience, to speed up the initial load, I had to drop UKs and FKs.
Unfortunately, the initial load doesn't work in parallel and, for each
table, there is only one sync worker.

Regards

Stefano Amoroso

Il giorno mer 4 ago 2021 alle ore 17:24 Hüseyin Demir <
demirhuseyinn...@gmail.com> ha scritto:

> Hello,
>
> I also faced a similar issue. Try removing the indexes on the destination
> first if possible. After that, you can add the indexes.
>
> Regards.
>
>
> Nikhil Shetty , 4 Ağu 2021 Çar, 18:07 tarihinde
> şunu yazdı:
>
>> Hi Team,
>>
>> We have a highly transactional system as the source of logical
>> replication and the database size is 500GB+. We are replicating all tables
>> from source using logical replication.
>>
>> For two tables the initial data load is very slow and it never completes
>> even after 24hrs+
>> Table size is under 100GB and index size is around 400GB.
>>
>> How can we increase the speed of the initial data load without dropping
>> the indexes on destination?
>>
>> We increased max_sync_workers_per_subscription to 3 but it didn't help
>> much for single tables
>>
>> Thanks,
>> Nikhil
>>
>
>
> --
> Hüseyin Demir
>
> Senior Database Platform Engineer
>
> Twitter:  https://twitter.com/d3rh5n
> Linkedin: hseyindemir
> 
> Github: https://github.com/hseyindemir
> Gitlab: https://gitlab.com/demirhuseyinn.94
> Medium: https://demirhuseyinn-94.medium.com/
>

Re: Logical Replication speed-up initial data

[Christophe Pettus]

> On Aug 4, 2021, at 08:06, Nikhil Shetty  wrote:
> 
> How can we increase the speed of the initial data load without dropping the 
> indexes on destination?

You can do the usual steps of increasing checkpoint_timeout and max_wal_size 
(since incoming logical replication changes are WAL logged) and setting 
synchronous_commit = off, but those will be modest improvements.  You will get 
an enormous benefit from dropping indexes and foreign key constraints, and 
those aren't much use during the initial sync anyway.

Re: Logical Replication speed-up initial data

[Nikhil Shetty]

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.

Thanks,
Nikhil

On Wed, Aug 4, 2021 at 8:54 PM Hüseyin Demir 
wrote:

> Hello,
>
> I also faced a similar issue. Try removing the indexes on the destination
> first if possible. After that, you can add the indexes.
>
> Regards.
>
>
> Nikhil Shetty , 4 Ağu 2021 Çar, 18:07 tarihinde
> şunu yazdı:
>
>> Hi Team,
>>
>> We have a highly transactional system as the source of logical
>> replication and the database size is 500GB+. We are replicating all tables
>> from source using logical replication.
>>
>> For two tables the initial data load is very slow and it never completes
>> even after 24hrs+
>> Table size is under 100GB and index size is around 400GB.
>>
>> How can we increase the speed of the initial data load without dropping
>> the indexes on destination?
>>
>> We increased max_sync_workers_per_subscription to 3 but it didn't help
>> much for single tables
>>
>> Thanks,
>> Nikhil
>>
>
>
> --
> Hüseyin Demir
>
> Senior Database Platform Engineer
>
> Twitter:  https://twitter.com/d3rh5n
> Linkedin: hseyindemir
> 
> Github: https://github.com/hseyindemir
> Gitlab: https://gitlab.com/demirhuseyinn.94
> Medium: https://demirhuseyinn-94.medium.com/
>

Re: Logical Replication speed-up initial data

[Nikhil Shetty]

Hi Stefano,

Thank you for the information.

Regards,
Nikhil

On Wed, Aug 4, 2021 at 9:25 PM Stefano Amoroso 
wrote:

> Hello,
> in my experience, to speed up the initial load, I had to drop UKs and FKs.
> Unfortunately, the initial load doesn't work in parallel and, for each
> table, there is only one sync worker.
>
> Regards
>
> Stefano Amoroso
>
> Il giorno mer 4 ago 2021 alle ore 17:24 Hüseyin Demir <
> demirhuseyinn...@gmail.com> ha scritto:
>
>> Hello,
>>
>> I also faced a similar issue. Try removing the indexes on the destination
>> first if possible. After that, you can add the indexes.
>>
>> Regards.
>>
>>
>> Nikhil Shetty , 4 Ağu 2021 Çar, 18:07 tarihinde
>> şunu yazdı:
>>
>>> Hi Team,
>>>
>>> We have a highly transactional system as the source of logical
>>> replication and the database size is 500GB+. We are replicating all tables
>>> from source using logical replication.
>>>
>>> For two tables the initial data load is very slow and it never completes
>>> even after 24hrs+
>>> Table size is under 100GB and index size is around 400GB.
>>>
>>> How can we increase the speed of the initial data load without dropping
>>> the indexes on destination?
>>>
>>> We increased max_sync_workers_per_subscription to 3 but it didn't help
>>> much for single tables
>>>
>>> Thanks,
>>> Nikhil
>>>
>>
>>
>> --
>> Hüseyin Demir
>>
>> Senior Database Platform Engineer
>>
>> Twitter:  https://twitter.com/d3rh5n
>> Linkedin: hseyindemir
>> 
>> Github: https://github.com/hseyindemir
>> Gitlab: https://gitlab.com/demirhuseyinn.94
>> Medium: https://demirhuseyinn-94.medium.com/
>>
>