Logical Replication Speed

From: Nikhil Shetty <nikhil(dot)dba04(at)gmail(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: Logical Replication Speed
Date: 2021-09-29 14:26:41
Message-ID: CAFpL5VxM9rBPAwnCUk_TmrkLYsfGAYOSVDrUcY-JPCdfzpN52Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Andrew Kerber 2021-09-29 14:32:31 Re: Logical Replication Speed
Previous Message Axel Rau 2021-09-29 13:37:22 Re: [FIXED] Can't drop logical replication slot