On Sun, Sep 13, 2026 at 10:14 PM shihao zhong <[email protected]> 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
