pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Reducing bandwidth usage of database replication
5+ messages / 4 participants
[nested] [flat]

* Reducing bandwidth usage of database replication
@ 2022-11-02 14:56  Sascha Zenglein <zenglein@gessler.de>
  0 siblings, 2 replies; 5+ messages in thread

From: Sascha Zenglein @ 2022-11-02 14:56 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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.

I'm also open for other solutions if anything comes to mind!

Thanks for the help!

Sascha Zenglein

Produktentwicklung


[cid:gessler_email_logo_23bb5200-2c8c-4c2d-a63e-71d5bf29d89f.gif]



________________________________

Gessler GmbH
Gutenbergring 14
63110 Rodgau
Deutschland

Tel.:  +49 6106 8709 693<tel:+49%206106%208709%20693>
Fax:  +49 6106 8709 50

E-Mail: zenglein@gessler.de
Web: www.gessler.de <http://www.gessler.de/;

________________________________


Geschaeftsfuehrer: Helmut Gessler, Dipl.-Ing. Marcus Gessler
Gerichtsstand: Offenbach/Main, Amtsgericht Offenbach HRB 20857
USt.-IdNr.: DE 113 551 141
Sparkasse Dieburg, IBAN DE94 5085 2651 0057 0025 03, SWIFT HELADEF1DIE


Diese E-Mail enthaelt vertrauliche und/oder rechtlich geschuetzte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtuemlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Mail.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail
und/oder der Inhalte dieser Mail ist nicht gestattet.

This e-mail may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this e-mail in error)
please notify the sender immediately and destroy this e-mail.
Any unauthorised copying, disclosure or distribution of the material of this e-mail is strictly forbidden.

Attachments:

gessler_email_logo_23bb5200-2c8c-4c2d-a63e-71d5bf29d89f.gif (image/gif, 1.4K) download view image
^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: Reducing bandwidth usage of database replication
@ 2022-11-02 16:29  Ron <ronljohnsonjr@gmail.com>
  parent: Sascha Zenglein <zenglein@gessler.de>
  1 sibling, 1 reply; 5+ messages in thread

From: Ron @ 2022-11-02 16:29 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 11/2/22 09:56, Sascha Zenglein wrote:
> 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.

1.4MiB/day is 17 *bytes* per second.  That's not too much.

-- 
Angular momentum makes the world go 'round.

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Reducing bandwidth usage of database replication
@ 2022-11-03 15:28  Ben Chobot <bench@silentmedia.com>
  parent: Sascha Zenglein <zenglein@gessler.de>
  1 sibling, 1 reply; 5+ messages in thread

From: Ben Chobot @ 2022-11-03 15:28 UTC (permalink / raw)
  To: Sascha Zenglein <zenglein@gessler.de>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

Sascha Zenglein wrote on 11/2/22 7:56 AM:
> 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.
>
> I'm also open for other solutions if anything comes to mind!

It sounds like you are trying to use logical replication to give 
yourself a multi-master database setup, and that you've squeezed 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 
different solution that might meet your goals.

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Reducing bandwidth usage of database replication
@ 2022-11-04 09:16  Simon Riggs <simon.riggs@enterprisedb.com>
  parent: Ron <ronljohnsonjr@gmail.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Simon Riggs @ 2022-11-04 09:16 UTC (permalink / raw)
  To: Sascha Zenglein <zenglein@gessler.de>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

On Thu, 3 Nov 2022 at 08:40, Sascha Zenglein <zenglein@gessler.de> wrote:

> It is, unfortunately, too much for our requirements.
> I was able to increase wal_receiver_status_interval so that the publisher
> only contacts the subscriber every 30 seconds instead of every 10.
> But I am not able to increase it further.
>

The max value is much higher than 30. Why can you not increase it further?

-- 
Simon Riggs                http://www.EnterpriseDB.com/
<http://www.enterprisedb.com/;

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Reducing bandwidth usage of database replication
@ 2022-11-04 09:20  Simon Riggs <simon.riggs@enterprisedb.com>
  parent: Ben Chobot <bench@silentmedia.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Simon Riggs @ 2022-11-04 09:20 UTC (permalink / raw)
  To: Ben Chobot <bench@silentmedia.com>; +Cc: Sascha Zenglein <zenglein@gessler.de>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>

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

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've squeezed 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 different solution 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/





^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2022-11-04 09:20 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-02 14:56 Reducing bandwidth usage of database replication Sascha Zenglein <zenglein@gessler.de>
2022-11-02 16:29 ` Ron <ronljohnsonjr@gmail.com>
2022-11-04 09:16   ` Simon Riggs <simon.riggs@enterprisedb.com>
2022-11-03 15:28 ` Ben Chobot <bench@silentmedia.com>
2022-11-04 09:20   ` Simon Riggs <simon.riggs@enterprisedb.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox