Thread: Reducing bandwidth usage of database replication

P {margin-top:0;margin-bottom:0;}

Hi all,

I want to use the postgres-native logical replication to have multiple clients receive and send data to a central database.

Real-time is far less important than network usage, and with my current test setup it appears both instances communicate frequently if a subscription is active, even if nothing is happening.

Is there a good way to reduce data usage, for example by limiting the amount of keep-alive messages? One database will likely be idle most of the time.

I estimated the current solution to idle at around 1.4MiB per day. Ideally it would use less than 100KiB a day.

On Thu, 3 Nov 2022 at 15:28, Ben Chobot <bench@silentmedia.com> wrote:

> I want to use the postgres-native logical replication to have multiple clients receive and send data to a central
database.
> Real-time is far less important than network usage, and with my current test setup it appears both instances
communicatefrequently if a subscription is active, even if nothing is happening. 
>
> Is there a good way to reduce data usage, for example by limiting the amount of keep-alive messages? One database
willlikely be idle most of the time. 

Keep-alive messages are the largest concern on an otherwise quiet link.

Probably makes sense to let the connection time out, then set a large
wal_retrieve_retry_interval, so it doesn't immediately reconnect.


> It sounds like you are trying to use logical replication to give yourself a multi-master database setup, and that
you'vesqueezed as much optimization as you can from logical replication and found it to be unworkable. If that's a fair
assessment,you might look into something like bucardo instead. I haven't done the network comparison but it is a
differentsolution that might meet your goals. 

The overhead of Bucardo has been measured as being more than 3x that
of logical replication.

--
Simon Riggs                http://www.EnterpriseDB.com/

At Fri, 4 Nov 2022 09:25:44 +0000, Sascha Zenglein <zenglein@gessler.de> wrote in 
> I have set the status interval to 600s and it still sends a status message every 30s.
> I also tried setting every available option higher, with no success:

I guess you are not setting wal_sender_timeout on the primary side.

Status messages are also sent in response to sender pings that is
controlled by that variable.  Wal sender sends a ping after a half of
that setting since the last status message to request walreceiver to
send a response.

> checkpoint_warning = 93s
> max_standby_streaming_delay = 130s
> max_standby_archive_delay = 45s
> wal_receiver_status_interval = 600s
> wal_receiver_timeout = 1200s
> wal_receiver_timeout = 65s
> recovery_min_apply_delay = 600s
> 
> The random values were to see which setting is limiting if I got above the 30s limit.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center