Re: Logical Replication Speed

The speed is going to be governed by the available hardware, so improve the
hardware to improve the speed of the initial data copy. You should also be
able to add space to the file system for your wal logs without too much
difficulty in RHEL7.

On Wed, Sep 29, 2021 at 9:26 AM Nikhil Shetty <nikhil(dot)dba04(at)gmail(dot)com>
wrote:

> 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
>

--
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'