BUG #19628: Uninterruptible vacuum during hash index processing

Started by PG Bug reporting form3 months ago8 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

The following bug has been logged on the website:

Bug reference: 19628
Logged by: Sergei Kornilov
Email address: sk@zsrv.org
PostgreSQL version: 16.14
Operating system: ubuntu
Description:

Hello!

During the installation of minor update (from 16.14 to 16.15), I discovered
that PostgreSQL was shutting down for a long time (about 6 minutes) because
it was waiting for the autovacuum process to terminate.

I was able to find the cause in the vacuum implementation for hash indexes
(this database has a fairly big hash index):

1) vacuum executes hashbulkdelete ( src/backend/access/hash/hash.c )
2) LockBufferForCleanup in loop_top calls HOLD_INTERRUPTS() under the hood
3) then we execute hashbucketcleanup , which can take a long time. Here, the
loop calls vacuum_delay_point (which calls CHECK_FOR_INTERRUPTS), but it
does nothing because HOLD_INTERRUPTS is still in effect.
4) Only at the end of hashbucketcleanup RESUME_INTERRUPTS is called from
LWLockRelease

I checked with gdb: hashbucketcleanup is actually executed with a non-zero
InterruptHoldoffCount.

Breakpoint 1, hashbucketcleanup (rel=rel@entry=0x7f1cf95ec080,
cur_bucket=cur_bucket@entry=0,
bucket_buf=bucket_buf@entry=11847, bucket_blkno=bucket_blkno@entry=1,
bstrategy=0x55f74ae62ca8, maxbucket=32573,
highmask=32767, lowmask=16383, tuples_removed=0x7fff95dc5a98,
num_index_tuples=0x7fff95dc5a90, split_cleanup=false,
callback=0x55f7395a6fa8 <vac_tid_reaped>, callback_state=0x7f1ce9b6d048)
at hash.c:691
691 {
(gdb) n
700 if (split_cleanup)
(gdb) n
717 vacuum_delay_point();
(gdb) p InterruptHoldoffCount
$1 = 1

I'm not sure how to properly fix hashbucketcleanup. At the very least, an
additional vacuum_delay_point in hashbulkdelete before LockBufferForCleanup
would improve the situation slightly.

regards, Sergei

#2mostafa nabil
mostafa.nabil.nafie@gmail.com
In reply to: PG Bug reporting form (#1)
Re: BUG #19628: Uninterruptible vacuum during hash index processing

Hi Sergei,

Thanks for the report and root cause analysis.

Attached is a patch adding a vacuum_delay_point() call at the top of
hashbulkdelete()'s per-bucket loop, before any buffer lock is taken.
The function currently has no interrupt check outside the one inside
hashbucketcleanup(), which is ineffective for the reason you already
found (InterruptHoldoffCount stays > 0 for the whole bucket once
LockBufferForCleanup() is called). This adds a check at the one spot
where nothing is locked yet, so a pending shutdown or cancel is
noticed at the next bucket boundary instead of only after the whole
index scan finishes.

No automated test: both patched and unpatched code eventually honor
the cancel, so a TAP test would need a hardcoded time threshold,
which risks flaking on slower CI hosts. Verified manually instead
(script attached): on an 8M-row table with ~90% dead tuples, cancelling
a VACUUM during the "vacuuming indexes" phase took ~2.08s on unpatched
master vs ~0.014s with the patch.

Not addressed: a single bucket with a very long overflow chain.
hashbucketcleanup() uses lock chaining (locks the next overflow page
before releasing the current one) to prevent a race with concurrent
scans overtaking a partially vacuumed bucket, per the hash AM README.
So interrupts are never truly clear during one bucket's own cleanup,
and this patch can't help there without changing the locking scheme.
I'd treat that as a separate, riskier follow-up.

Likely a backpatch candidate (real bug in shipped versions), but
I'll leave that call to whoever reviews this.

Regards,
Mostafa

On Sat, Aug 22, 2026 at 5:34 PM PG Bug reporting form <
noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 19628
Logged by: Sergei Kornilov
Email address: sk@zsrv.org
PostgreSQL version: 16.14
Operating system: ubuntu
Description:

Hello!

During the installation of minor update (from 16.14 to 16.15), I discovered
that PostgreSQL was shutting down for a long time (about 6 minutes) because
it was waiting for the autovacuum process to terminate.

I was able to find the cause in the vacuum implementation for hash indexes
(this database has a fairly big hash index):

1) vacuum executes hashbulkdelete ( src/backend/access/hash/hash.c )
2) LockBufferForCleanup in loop_top calls HOLD_INTERRUPTS() under the hood
3) then we execute hashbucketcleanup , which can take a long time. Here,
the
loop calls vacuum_delay_point (which calls CHECK_FOR_INTERRUPTS), but it
does nothing because HOLD_INTERRUPTS is still in effect.
4) Only at the end of hashbucketcleanup RESUME_INTERRUPTS is called from
LWLockRelease

I checked with gdb: hashbucketcleanup is actually executed with a non-zero
InterruptHoldoffCount.

Breakpoint 1, hashbucketcleanup (rel=rel@entry=0x7f1cf95ec080,
cur_bucket=cur_bucket@entry=0,
bucket_buf=bucket_buf@entry=11847, bucket_blkno=bucket_blkno@entry=1,
bstrategy=0x55f74ae62ca8, maxbucket=32573,
highmask=32767, lowmask=16383, tuples_removed=0x7fff95dc5a98,
num_index_tuples=0x7fff95dc5a90, split_cleanup=false,
callback=0x55f7395a6fa8 <vac_tid_reaped>,
callback_state=0x7f1ce9b6d048)
at hash.c:691
691 {
(gdb) n
700 if (split_cleanup)
(gdb) n
717 vacuum_delay_point();
(gdb) p InterruptHoldoffCount
$1 = 1

I'm not sure how to properly fix hashbucketcleanup. At the very least, an
additional vacuum_delay_point in hashbulkdelete before LockBufferForCleanup
would improve the situation slightly.

regards, Sergei

--
Mostafa Nabil Software Engineer

Attachments:

t253470_2
0001-Check-for-interrupts-between-hash-index-vacuum-bucke.patchtext/x-patch; charset=US-ASCII; name=0001-Check-for-interrupts-between-hash-index-vacuum-bucke.patchDownload+6-1
test_hash_vacuum_cancel.shapplication/x-shellscript; name=test_hash_vacuum_cancel.shDownload
#3Kirill Reshke
reshkekirill@gmail.com
In reply to: mostafa nabil (#2)
Re: BUG #19628: Uninterruptible vacuum during hash index processing

On Sat, 22 Aug 2026 at 19:54, mostafa nabil
<mostafa.nabil.nafie@gmail.com> wrote:

Hi Sergei,

Thanks for the report and root cause analysis.

Attached is a patch adding a vacuum_delay_point() call at the top of
hashbulkdelete()'s per-bucket loop, before any buffer lock is taken.
The function currently has no interrupt check outside the one inside
hashbucketcleanup(), which is ineffective for the reason you already
found (InterruptHoldoffCount stays > 0 for the whole bucket once
LockBufferForCleanup() is called). This adds a check at the one spot
where nothing is locked yet, so a pending shutdown or cancel is
noticed at the next bucket boundary instead of only after the whole
index scan finishes.

No automated test: both patched and unpatched code eventually honor
the cancel, so a TAP test would need a hardcoded time threshold,
which risks flaking on slower CI hosts. Verified manually instead
(script attached): on an 8M-row table with ~90% dead tuples, cancelling
a VACUUM during the "vacuuming indexes" phase took ~2.08s on unpatched
master vs ~0.014s with the patch.

Not addressed: a single bucket with a very long overflow chain.
hashbucketcleanup() uses lock chaining (locks the next overflow page
before releasing the current one) to prevent a race with concurrent
scans overtaking a partially vacuumed bucket, per the hash AM README.
So interrupts are never truly clear during one bucket's own cleanup,
and this patch can't help there without changing the locking scheme.
I'd treat that as a separate, riskier follow-up.

Likely a backpatch candidate (real bug in shipped versions), but
I'll leave that call to whoever reviews this.

Regards,
Mostafa

I think our fix is fine, we also need to remove vacuum_delay_point
from hashbucketcleanup function, since it is ineffective if called was
Interrupt holdoff. This will follow existing coding practice, see also
how GIN vacuum works with buffer lock/vacuum_delay_point

Also, we can actually test this deterministically using injection
points, but I dont think this test is worth cpu cycles in buildfarm.
Too much for this.

--
Best regards,
Kirill Reshke

#4mostafa nabil
mostafa.nabil.nafie@gmail.com
In reply to: PG Bug reporting form (#1)
Re: BUG #19628: Uninterruptible vacuum during hash index processing

Hi Kirill,

Thanks for the review.

v2 attached, i removed the vacuum_delay_point() call inside
hashbucketcleanup()'s per-page loop as you suggested. Good catch because
it's dead code.

Regards,
Mostafa

On Fri, Sep 4, 2026 at 12:42 PM mostafa nabil <mostafa.nabil.nafie@gmail.com>
wrote:

Hi Kirill,

Thanks for the review.

v2 attached, i removed the vacuum_delay_point() call inside
hashbucketcleanup()'s per-page loop as you suggested. Good catch because
it's dead code.

Regards,
Mostafa

On Thu, Sep 3, 2026 at 9:48 AM Kirill Reshke <reshkekirill@gmail.com>
wrote:

On Sat, 22 Aug 2026 at 19:54, mostafa nabil
<mostafa.nabil.nafie@gmail.com> wrote:

Hi Sergei,

Thanks for the report and root cause analysis.

Attached is a patch adding a vacuum_delay_point() call at the top of
hashbulkdelete()'s per-bucket loop, before any buffer lock is taken.
The function currently has no interrupt check outside the one inside
hashbucketcleanup(), which is ineffective for the reason you already
found (InterruptHoldoffCount stays > 0 for the whole bucket once
LockBufferForCleanup() is called). This adds a check at the one spot
where nothing is locked yet, so a pending shutdown or cancel is
noticed at the next bucket boundary instead of only after the whole
index scan finishes.

No automated test: both patched and unpatched code eventually honor
the cancel, so a TAP test would need a hardcoded time threshold,
which risks flaking on slower CI hosts. Verified manually instead
(script attached): on an 8M-row table with ~90% dead tuples, cancelling
a VACUUM during the "vacuuming indexes" phase took ~2.08s on unpatched
master vs ~0.014s with the patch.

Not addressed: a single bucket with a very long overflow chain.
hashbucketcleanup() uses lock chaining (locks the next overflow page
before releasing the current one) to prevent a race with concurrent
scans overtaking a partially vacuumed bucket, per the hash AM README.
So interrupts are never truly clear during one bucket's own cleanup,
and this patch can't help there without changing the locking scheme.
I'd treat that as a separate, riskier follow-up.

Likely a backpatch candidate (real bug in shipped versions), but
I'll leave that call to whoever reviews this.

Regards,
Mostafa

I think our fix is fine, we also need to remove vacuum_delay_point
from hashbucketcleanup function, since it is ineffective if called was
Interrupt holdoff. This will follow existing coding practice, see also
how GIN vacuum works with buffer lock/vacuum_delay_point

Also, we can actually test this deterministically using injection
points, but I dont think this test is worth cpu cycles in buildfarm.
Too much for this.

--
Best regards,
Kirill Reshke

--
Mostafa Nabil Software Engineer

--
Mostafa Nabil Software Engineer

Attachments:

t253470_4
v2-0001-Check-for-interrupts-between-hash-index-vacuum-bu.patchtext/x-patch; charset=US-ASCII; name=v2-0001-Check-for-interrupts-between-hash-index-vacuum-bu.patchDownload+6-3
#5mostafa nabil
mostafa.nabil.nafie@gmail.com
In reply to: mostafa nabil (#4)
Re: BUG #19628: Uninterruptible vacuum during hash index processing

Hi Kirill,

Thanks again for the review. Did you get a chance to look at v2? It
removes the vacuum_delay_point() call in hashbucketcleanup() as you
suggested, and keeps the check at the top of hashbulkdelete()'s
per-bucket loop.

cfbot is green on all platforms:
https://commitfest.postgresql.org/patch/7216/

The entry has been moved to the November commitfest (PG20-3). If v2
looks good to you, would you mind adding yourself as a reviewer there
and marking it Ready for Committer?

Regards,
Mostafa Nabil

On Fri, Sep 4, 2026 at 12:47 PM mostafa nabil <mostafa.nabil.nafie@gmail.com>
wrote:

Hi Kirill,

Thanks for the review.

v2 attached, i removed the vacuum_delay_point() call inside
hashbucketcleanup()'s per-page loop as you suggested. Good catch because
it's dead code.

Regards,
Mostafa

On Fri, Sep 4, 2026 at 12:42 PM mostafa nabil <
mostafa.nabil.nafie@gmail.com> wrote:

Hi Kirill,

Thanks for the review.

v2 attached, i removed the vacuum_delay_point() call inside
hashbucketcleanup()'s per-page loop as you suggested. Good catch
because it's dead code.

Regards,
Mostafa

On Thu, Sep 3, 2026 at 9:48 AM Kirill Reshke <reshkekirill@gmail.com>
wrote:

On Sat, 22 Aug 2026 at 19:54, mostafa nabil
<mostafa.nabil.nafie@gmail.com> wrote:

Hi Sergei,

Thanks for the report and root cause analysis.

Attached is a patch adding a vacuum_delay_point() call at the top of
hashbulkdelete()'s per-bucket loop, before any buffer lock is taken.
The function currently has no interrupt check outside the one inside
hashbucketcleanup(), which is ineffective for the reason you already
found (InterruptHoldoffCount stays > 0 for the whole bucket once
LockBufferForCleanup() is called). This adds a check at the one spot
where nothing is locked yet, so a pending shutdown or cancel is
noticed at the next bucket boundary instead of only after the whole
index scan finishes.

No automated test: both patched and unpatched code eventually honor
the cancel, so a TAP test would need a hardcoded time threshold,
which risks flaking on slower CI hosts. Verified manually instead
(script attached): on an 8M-row table with ~90% dead tuples, cancelling
a VACUUM during the "vacuuming indexes" phase took ~2.08s on unpatched
master vs ~0.014s with the patch.

Not addressed: a single bucket with a very long overflow chain.
hashbucketcleanup() uses lock chaining (locks the next overflow page
before releasing the current one) to prevent a race with concurrent
scans overtaking a partially vacuumed bucket, per the hash AM README.
So interrupts are never truly clear during one bucket's own cleanup,
and this patch can't help there without changing the locking scheme.
I'd treat that as a separate, riskier follow-up.

Likely a backpatch candidate (real bug in shipped versions), but
I'll leave that call to whoever reviews this.

Regards,
Mostafa

I think our fix is fine, we also need to remove vacuum_delay_point
from hashbucketcleanup function, since it is ineffective if called was
Interrupt holdoff. This will follow existing coding practice, see also
how GIN vacuum works with buffer lock/vacuum_delay_point

Also, we can actually test this deterministically using injection
points, but I dont think this test is worth cpu cycles in buildfarm.
Too much for this.

--
Best regards,
Kirill Reshke

--
Mostafa Nabil Software Engineer

--
Mostafa Nabil Software Engineer

--
Mostafa Nabil Software Engineer

#6Heikki Linnakangas
hlinnaka@iki.fi
In reply to: mostafa nabil (#5)
Re: BUG #19628: Uninterruptible vacuum during hash index processing

On 02/10/2026 12:21, mostafa nabil wrote:

Hi Kirill,

Thanks again for the review. Did you get a chance to look at v2? It
removes the vacuum_delay_point() call in hashbucketcleanup() as you
suggested, and keeps the check at the top of hashbulkdelete()'s
per-bucket loop.

I had a look at this. Some further fixes:

Do we have more places where we call vacuum_delay_point() while holding
locks? It seems like a bad idea to ever do that -- you don't want to
sleep while holding locks. I added an
"Assert(INTERRUPTS_CAN_BE_PROCESSED())" into vacuum_cost_delay() and ran
the regression tests, and that indeed revealed a few more places that
did that. The attached patch removes or moves those vacuum_delay_point()
calls too. I didn't include the assertion in the patch, because I'm
afraid there might be more places that we've missed, including in
extensions.

After fixing those, if you ever do call vacuum_cost_delay_point() while
holding a lock, e.g. from an extension or if we missed a caller, I think
you don't really want to sleep. And you definitely don't want to call
ProcessConfigFile() while in a critical section. So I added a quick exit
to vacuum_delay_point() if it's called with !INTERRUPTS_CAN_BE_PROCESSED().

What do you think?

- Heikki

Attachments:

t253470_6
v3-0001-Don-t-call-vacuum_delay_point-while-holding-locks.patchtext/x-patch; charset=UTF-8; name=v3-0001-Don-t-call-vacuum_delay_point-while-holding-locks.patchDownload+15-8
#7shihao zhong
zhong950419@gmail.com
In reply to: Heikki Linnakangas (#6)
Re: BUG #19628: Uninterruptible vacuum during hash index processing

Hi Heikki,

On 02/10/2026, Heikki Linnakangas wrote:

Do we have more places where we call vacuum_delay_point() while holding
locks?

I tried to run tests under assertion you had and I did not see any new
failures.

I also asked Fable to list 28 use cases and manually check the code path
and did not find any vacuum_delay_point with LWLock holding.

But, there is a thread on hackers with the same three fixes, in case you
have not seen it. Kevin Rocker's v8 is here:

/messages/by-id/d46104e6-19e0-4dfe-bb64-8ff21d53b1dd@app.fastmail.com

Tom suggested the Assert there, and v8-0003 adds it.

Thanks,
Shihao

#8Heikki Linnakangas
hlinnaka@iki.fi
In reply to: shihao zhong (#7)
Re: BUG #19628: Uninterruptible vacuum during hash index processing

On 03/10/2026 07:05, shihao zhong wrote:

But, there is a thread on hackers with the same three fixes, in case you
have not seen it. Kevin Rocker's v8 is here:

/messages/by-id/d46104e6-19e0-4dfe-
bb64-8ff21d53b1dd@app.fastmail.com <https://postgr.es/m/
d46104e6-19e0-4dfe-bb64-8ff21d53b1dd@app.fastmail.com>

Tom suggested the Assert there, and v8-0003 adds it.

Ah, thanks for the pointer! I thought issue sounded familiar but didn't
remember that other thread.

I committed the fix, taking the discussion from that other thread into
account.

- Heikki