Postgres Pro
Downloads
Home   >   mailing lists

Re: Logical Replication speed-up initial data - Mailing list pgsql-performance

From Nikhil Shetty
Subject Re: Logical Replication speed-up initial data
Date
Msg-id CAFpL5Vy9EdvXEc_=0NHErg_sx7RVdmsXEwON4Bp-S9LPKOk5DQ@mail.gmail.com
Whole thread Raw
In response to Re: Logical Replication speed-up initial data  (Hüseyin Demir <demirhuseyinn.94@gmail.com>)
Responses Re: Logical Replication speed-up initial data
Re: Logical Replication speed-up initial data
Re: Logical Replication speed-up initial data
List pgsql-performance
Tree view
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 <demirhuseyinn.94@gmail.com> 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 <nikhil.dba04@gmail.com>, 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

Linkedin: hseyindemir

pgsql-performance by date:

Previous
From: Christophe Pettus
Date:
Subject: Re: Logical Replication speed-up initial data
Next
From: Nikhil Shetty
Date:
Subject: Re: Logical Replication speed-up initial data