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

At Thu, 14 Jul 2022 13:51:55 +0200, hubert depesz lubaczewski <depesz@depesz.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