Re: Excessive number of replication slots for 12->14 logical replication

At Thu, 14 Jul 2022 13:51:55 +0200, hubert depesz lubaczewski <depesz(at)depesz(dot)com> wrote in
> Hi,
> We're using logical replication to replicate database from pg 12.9 to
> 14.4.
>
> Given the number of objects we made decision to use 10 separate
> publications, each containing subset of tables.
>
> So, we have:

(10 publications with an average of 15000 talbes)
>
> On both sides we have max_sync_workers_per_subscription set to 2.
>
> So, I would assume that during initial subscription, we can go up to 30
> connections. To make it safe we set limit to 50, but apparently it's not
> enough.

> I'm getting *LOTS* of errors about number of replication slots:

Yeah, the limitation by max_sync_workers_per_subscription is performed
on subscriber, but replication slot drops happen not on the
subscriber, but at the termination of corresponding walsender process
on publisher. So, there's a lag between the finish of subscription
worker and the corresponding slot's drop. Thus, a new sync worker can
be created while the walsenders corresponding to some already finished
sync workers is still going to finish.

> What is happening, and why Pg is trying to get more than 30
concurrent
> slots in this case?

Thus, not just about logical replication, some spare slots are
necessary to keep things running smoothly. I guess that it is
widely(?) recommended to provide at least one or two spare slots to
allow replication reconnection in case of silent disconnections, but I
haven't found something like that in the documentation.. In your case,
it seems to me larger number of spare slots makes the server run more
smoothly, *I* don't think 10 spare slots affect anything badly.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center