Re: Opportunistic pruning is lost under direct io, and nothing shows it

On Sun, Sep 13, 2026 at 10:14 PM shihao zhong <zhong950419(at)gmail(dot)com> wrote:
>
> On access pruning gives up without a word when it cannot get a cleanup lock
> on a page. That happens when some other session holds a pin on it. The dead
> tuples stay, vacuum has to deal with them later, and not one counter moves.
> I think we should be able to see this. The attached patch makes it visible.
>
> I started looking because on access pruning now does more than it used to.
> It can set a page all visible and it keeps the free space map up to date. So
> when a scan walks away from a page, we lose more than a few dead tuples.
>
> Here is what I measured. One table of 42 MB, shared_buffers of 8 MB,
> autovacuum off, five clients, four of them counting rows in the table and
> one doing single row updates, twenty seconds per run.

This setup is contrived enough that I do not anticipate seeing
anything like this in real workloads. Though the ratio of table to
shared_buffers may be a real-world scenario, space for 1000 blocks in
shared_buffers is highly unrealistic. The contention this produces is
going to give you all kinds of problems beyond missed on-access
pruning.

> The patch adds four columns to pg_stat_all_tables.
>
> prune_onaccess counts pages that a scan pruned.
>
> prune_onaccess_missed counts prune attempts dropped because the page was
> pinned. This is the number that goes up under direct io.
>
> pages_all_visible_onaccess counts pages that on access pruning marked all
> visible.
>
> vacuum_missed_dead_pages counts pages vacuum could not clean up for the same
> reason. Vacuum already counts these, it just prints them in the log and
> forgets them, so you cannot follow the number over time.

Adding four columns to pg_stat_all_tables is not worth it for this.
There are other vacuum-related stats that might be worth persisting in
a cumulative way outside of the logs, but I don't think this is one of
them. For your scenario, the user's only remediation would be to
increase shared_buffers. And this stat cumulatively won't tell you
much over time. For bursty workloads, what does a sum of missed pages
tell you? The log is much more useful to see at specific times when
vacuuming wasn't able to clean up a table. It's true that on-access
pruning doesn't have stats like this, but I don't feel convinced yet
that this should be added to pg_stat_all_tables.

- Melanie