pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedReducing 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