Date: Mon, 18 Jul 2022 15:23:13 +0100
From: Rory Campbell-Lange <rory@campbell-lange.net>
To: Rick Otten <rottenwindfish@gmail.com>
Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: data consolidation: logical replication design considerations
Message-ID: <YtVs0blyucDTEfRF@campbell-lange.net>
References: <YtLiLaDdvhQk9ZzB@campbell-lange.net>
 <CAMAYy4+-s2m495E2HViif73tuPy=pp9V0j99Es1ZZ+eWwOjnsg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAMAYy4+-s2m495E2HViif73tuPy=pp9V0j99Es1ZZ+eWwOjnsg@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/YtVs0blyucDTEfRF%40campbell-lange.net>
Precedence: bulk

On 17/07/22, Rick Otten (rottenwindfish@gmail.com) wrote:
> On Sat, Jul 16, 2022 at 12:07 PM Rory Campbell-Lange <
> rory@campbell-lange.net> wrote:
> 
> > I'd be grateful for some comments on the advisability of using a large
> > number of concurrent logical replication publications/subscriptions.
> > Below I've set out the current environment and a suggested design.
> > Apologies for the length of this email.
> 
> Another possibility is to use SymmetricDS for this.  [
> https://symmetricds.org ]  SymmetricDS was originally developed to keep
> databases on thousands of Point-of-Sale databases (in cash registers) in
> sync with pricing and inventory data for large international retailers.
> 
> There are lots of other use cases, but even 10-12 years ago it was scalable
> to the extent you are describing you need here.
> 
> The main drawback is that it is trigger based, so there is some slight
> latency introduced for insert/update/delete actions on the tables on the
> appropriate master, but it usually isn't significant.

Thanks very much for the pointer to SymmetricDS. I haven't come across
it before. The architecture, configuration and use look very
straightforward, although using java would be new to our production
environment, and SymmetricDS doesn't seem to be in Debian.

I'd be grateful to know if 500 odd publishers/subscribers is "out of the
park" or reasonable for a reasonably powerful machine (as described in
my original email). I would have thought using the native logical
replication capabilities would be much more scalable and efficient than
stepping outside of postgresql.

Regards,
Rory