Re: Make pg_prewarm, autoprewarm yield for waiting DDL

Hi,

On Tue, Jul 14, 2026 at 8:21 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Wed, Mar 25, 2026 at 5:32 PM SATYANARAYANA NARLAPURAM
> <satyanarlapuram(at)gmail(dot)com> wrote:
> > Both pg_prewarm() and the autoprewarm background worker hold AccessShareLock on the target relation for the entire duration of prewarming. On large tables this can take a long time, which means
> > that any DDL that needs a stronger lock (TRUNCATE, DROP TABLE, ALTER TABLE, etc.) is blocked for the full duration.

Thanks Satya for the off-list discussion, and thanks Robert for the review.

> This patch goes to quite a bit of trouble to restart prewarming of a
> relation after releasing and reacquiring the lock. I feel like that's
> adding a lot of complexity of questionable value. I think I'd be
> inclined not to change the foreground path at all, just like a
> foreground VACUUM doesn't do anything special to deprioritize itself,
> and make the autoprewarm give up on the relation entirely if someone
> else wants the lock, just like what autovacuum does.

Agreed on keeping the behavior in sync with vacuum. Rather than the
autovacuum's cancellation via PROC_IS_AUTOVACUUM, I used the vacuum's
truncation approach of calling LockHasWaitersRelation() to detect
waiters, checking every 32 blocks and at most every 20ms. Please let
me know if those intervals need to be larger, or if there's a better
idea here.

With this approach, autoprewarm may leave already-loaded blocks of the
relation in the buffer pool after giving it up. We could evict them,
even after releasing the lock so the waiter isn't delayed, but that
feels like overkill IMO, and vacuum leaves blocks behind in the same
way anyway.

> If we do it like
> this, I think we need a really good argument for handling this case
> differently from autovacuum. If somebody takes AccessExclusiveLock on
> a relation, there's a good chance that the block numbers we have are
> not even relevant any more afterwards.

IMHO this behavior is simple to reason about, and it avoids the
problems that a concurrent rewrite can cause.

> On a purely mechanical note, this patch results in a block of code in
> autoprewarm_database_main() that currently looks very simple looking
> extremely complicated instead. The purpose of that code is not so
> obvious any more, and there's a lot of extra indentation that impacts
> readability. If you want to pursue this, I suggest thinking about how
> you could introduce subroutines or otherwise refactor so that a future
> human reader will be able to understand this nearly as easily as they
> can understand the current code.

I moved that logic into a separate function to keep
autoprewarm_database_main() readable.

Please find the attached v2 patches. 0002 is a TAP test that I don't
intend to get this committed, as it relies on a very large table that
doesn't fit well with the overall test timing.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com