MIME-Version: 1.0
References: 
 <CA+-JvFsMHckBMzsu5Ov9HCG3AFbMh056hHy1FiXazBRtZ9pFBg@mail.gmail.com>
 <OSCPR01MB149667506BCFD684FEFDCB919F511A@OSCPR01MB14966.jpnprd01.prod.outlook.com>
 <CALj2ACUmPbkcj4y4oeXvzUkBejG68QDtrFF7QHDC_qz2vQcTCg@mail.gmail.com>
 <CAHg+QDfnK7tQxsEZox=kOkYfqANmL70mwn0N=eRrJxE1Z+1ygg@mail.gmail.com>
 <CALj2ACX_o+dKeAaK76mpAtG646UnDHpGUWziUkCvicVz8mz6=A@mail.gmail.com>
 <CAD21AoATM=Un8ejnbcDQ7q+CaCoxpkA7Ln9bacvQEoymqvjPug@mail.gmail.com>
 <CALj2ACUmUb=CLEyfsQrW0WAkF6Y9fiBfG6pnPjepfOM7A1XReA@mail.gmail.com>
 <CAD21AoAg6x57a8LoP2s+0vgizp9QGHcLGJL9bwh7kzEJq3arBg@mail.gmail.com>
 <CALj2ACWcaSkfMAQu3s6ZkTZuoFvVRD=DkxXbBwC33PL9+kzsqw@mail.gmail.com>
 <CAD21AoBEBqQONiZxvnUYOu814yB2tRPrmX=7KqvL+f3ae7250w@mail.gmail.com>
 <CALj2ACUenekLgjMr8x4DyuU=zKZ4eNqW9XF-1PovSctkY2VA0Q@mail.gmail.com>
 <CAFC+b6pO44=zGqwijzrcyGGTYCM51Y7zS5uQX0_nWjsxW9i3QQ@mail.gmail.com>
 <CALj2ACU=A31kqHELyaF-=2vnyed=cO2JNQt+vY92KtHLF-m8sg@mail.gmail.com>
 <CAD21AoB4MsTpG5JEkifght_tQ91VHJO_8kpsDCrG-z9qkkmN5g@mail.gmail.com>
 <CALj2ACVGpVHuqchPPFWdiLDN-PDPCEe=sU43YB7nqafE+VMXaQ@mail.gmail.com>
 <CAD21AoB61CgA921J2FwXzxfxUsxQzi7GYAZaH05PHmb6HFe_hg@mail.gmail.com>
 <CALj2ACV724Yr2=5Jun4XGQyGhvCiXo+7GXJUnybqf9SYqQA6gw@mail.gmail.com>
 <CALj2ACXR=5SoApm6duEOc6pi=-KaBqSxuSP6XNQot2LywrqtAA@mail.gmail.com>
 <CAD21AoABTzYoe99gmdvs4D8kwfD=AtaaWFb8C2biMkUncmcXDA@mail.gmail.com>
 <CALj2ACV6jYzaMZD3jF3nTj_bLoUZw8+UwucVKTK1kVCwXYVQ8w@mail.gmail.com>
 <CAD21AoArh6-RfOGGY6GfWqqHv0qUJEMwXVmGQk=29uYiKFLSgA@mail.gmail.com>
 <CALj2ACV_PV5LiL55O02UYJ_zfZw0CUKnR_nW83mpm0ZuYkbM8g@mail.gmail.com>
 <CALj2ACU7nVkqWx+jxmaZ-V4txqKdhKmeXHcZa+kkyPeOFYSQPA@mail.gmail.com>
 <CAD21AoDmNdKrbLJJvU8f5v=6LPJ50DUoQP+ONG5oicD+m-hGUw@mail.gmail.com>
 <CALj2ACWmrz0+9YUv6gfxim4f+WbuR-K820e8OWY4G=4Mt7wr3g@mail.gmail.com>
In-Reply-To: 
 <CALj2ACWmrz0+9YUv6gfxim4f+WbuR-K820e8OWY4G=4Mt7wr3g@mail.gmail.com>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Thu, 9 Jul 2026 14:52:44 -0700
Message-ID: 
 <CAD21AoCPMHdu2G3uEK7d7t7_akW6OF5MUHKwBRJfwFzfC=bc-Q@mail.gmail.com>
Subject: Re: Introduce XID age based replication slot invalidation
To: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Cc: Srinath Reddy Sadipiralla <srinath2133@gmail.com>,
	SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>,
	"Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>,
 John H <johnhyvr@gmail.com>,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAD21AoCPMHdu2G3uEK7d7t7_akW6OF5MUHKwBRJfwFzfC%3Dbc-Q%40mail.gmail.com>
Precedence: bulk

On Mon, Jul 6, 2026 at 11:34=E2=80=AFPM Bharath Rupireddy
<bharath.rupireddyforpostgres@gmail.com> wrote:
>
> Hi,
>
> On Thu, Jul 2, 2026 at 4:18=E2=80=AFPM Masahiko Sawada <sawada.mshk@gmail=
.com> wrote:
> >
> > > Thank you Sawada-san. I've been thinking more about it and I agree we
> > > need to address this. While I still think the scenario is unlikely in
> > > practice (SIGTERM would have to take a long time, the slot's xmin
> > > would have to be very old while the walsender is still running, etc.)=
,
> > > I think it's worth handling.
> > >
> > > I can think of a couple of approaches:
> > >
> > > 1. Use ConditionVariableTimedSleep instead of ConditionVariableSleep
> > > when called from an autovacuum worker. Workers don't block forever,
> > > but they still wait for the timeout duration, still send redundant
> > > SIGTERMs, and a correct timeout value needs to be chosen. When it
> > > expires, the worker either retries (still stuck) or gives up (same as
> > > approach 2).
> > >
> > > 2. Make the vacuum path non-blocking when another process is already
> > > invalidating the same slot. The first process to attempt invalidation
> > > proceeds normally: it sends SIGTERM and waits on
> > > ConditionVariableSleep for the process holding the slot to exit. But
> > > if a subsequent autovacuum worker finds that another process has
> > > already initiated invalidation of this slot, it skips the slot and
> > > proceeds with vacuum instead of waiting on the same
> > > ConditionVariableSleep.
> > >
> > > I think approach 2 is simple. If another process is already
> > > invalidating the slot, there's no reason for the autovacuum worker to
> > > also block. The tradeoff is that this vacuum cycle's OldestXmin won't
> > > move forward and it will need another cycle for this relation. But
> > > that's fine given that the scenario as explained above is unlikely to
> > > happen in practice.
> > >
> > > Please let me know if my thinking sounds reasonable. I'm open to othe=
r
> > > ideas too.
> >
> > The third idea I came up with is that (auto)vacuum behaves differently
> > in terms of XID-aged slot invalidation depending on the slot being
> > used or not; (auto)vacuum invalidate the XID-aged slot if no one is
> > holding the slot, and it just wakes up the checkpointer to invalidate
> > the slot if a process is still holding the slot. If the XID-aged slot
> > is not held by any process, (auto)vacuum simply invalidates the slot.
> > I believe that while the former case happens in most cases in
> > practice, delegating the checkpointer to invalidate XID-aged slots
> > might help avoid vacuum from being blocked.
> >
> > What do you think about the above idea?
>
> I see two cases here.
>
> 1/ No one is holding the slot. The connection is gone for whatever
> reason. Here vacuum just acquires and invalidates the slot right away,
> no signal, no waiting, and this already works.
>
> 2/ The slot is held but has aged out, due to a slow connection,
> replica lag, or slow decoding/apply. This is the case that involves
> terminating the holder and waiting, which is where blocking can
> happen.
>
> I partially agree with your suggestion for case 2. My preference is to
> not add any blockers for vacuum. It does opportunistic XID-age
> invalidation, invalidates the slots it can take without waiting, and
> leaves the held ones to the checkpointer, which is the guaranteed
> path. It is also easier to reason about and to explain to users.
>
> The tradeoff is that vacuum won't invalidate a held slot in the same
> pass, so the relation being vacuumed right then doesn't get the
> advanced horizon. Once the checkpointer has invalidated the slot,
> later relations pick it up, which may well be in the same vacuum cycle
> or next. I am fine with that.
>
> Given this, I do not think 0003 is needed anymore. Its whole purpose
> was to stop multiple vacuum processes piling onto the same slot's
> condition variable. If the vacuum never waits there, that contention
> cannot happen. There is only one checkpointer. The unheld case is
> already safe. The check runs under the slot's spinlock, so the first
> process marks the slot invalidated and any others see it is done and
> move on. No signal, no waiting. I will drop 0003.

Agree to drop 0003 patch.

> I would skip having the vacuum explicitly wake the checkpointer when
> it sees a held slot. Imagine one walsender holding the slot's xmin and
> 8 autovacuum workers running. Each worker hits that same held slot and
> would signal the checkpointer, so we would get a signal per worker for
> a single slot, repeated every cycle until the checkpointer acts. If
> the checkpointer is already running, another request gets queued, and
> this can happen repeatedly, creating a flood of checkpoint requests.

I agree that we should avoid requesting checkpoints just to invalidate
XID-aged slots.

> Since 0001 already invalidates aged slots during every checkpoint, the
> slot gets cleaned up on the checkpointer's regular pass anyway. I
> would rather rely on that than send extra signals to perform
> checkpoints.

If we rely on the checkpointer for held slot cases, XID-aged slots
held by someone could be left for up to one day, the maximum value of
checkpoint_timeout, in the worst case. I considered an alternative
idea: (auto)vacuum sets a flag on shmem for XID-based slot
invalidation and has the checkpointer perform the invalidation upon
request, without creating a checkpoint. But that seems beyond the
checkpointer's role. Given that it's not common for XID-aged slots to
be still held by someone, it would be okay to skip the held slots. The
same applies on replicas, where the restartpoint pass is the only
mechanism anway. So I agree to simply skip held slots.

Regards,


--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com