From: =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <michal@kleczek.org>
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3776.700.51\))
Subject: Merging logical subscriptions
Message-Id: <E9B39C33-3673-4FE3-8B3D-432088499A16@kleczek.org>
Date: Thu, 17 Oct 2024 08:45:53 +0200
To: PG-General Mailing List <pgsql-general@postgresql.org>
Archived-At: <https://www.postgresql.org/message-id/E9B39C33-3673-4FE3-8B3D-432088499A16%40kleczek.org>
Precedence: bulk

Hi,

I have a question about an algorithm to =E2=80=9Cmerge=E2=80=9D two =
logical replication subscriptions. Here is the scenario:

We have a server with many tables replicated using logical replication =
to downstream replicas.
Each table is published with a separate publication.

At the moment each replica uses a single subscription with many =
publications.

The problem is that a subscription is a unit of concurrency in logical =
replication and sometimes we need to process replication streams in =
parallel.
=E2=80=9CSplitting=E2=80=9D subscriptions so that groups of publications =
are processed in parallel is (quite) easy:

1. Disable existing subscription
2. Clone replication slot
3. Create a new subscription with a subset of first subscription =
publications (don=E2=80=99t create a replication slot, use the =
replication slot created in step 2, don=E2=80=99t copy data)
4. Drop the subset of publications from the first subscription.
5. Enable and refresh the first subscription

But is there a procedure to =E2=80=9Cmerge=E2=80=9D subscriptions?

The problem I see is that there is no way to synchronise two =
subscriptions so that they stop at the same LSN.
We can skip transactions by advancing the LSN. But we cannot process =
transactions until a specific LSN.

Any ideas?

Thanks,
Michal=