Hi, On Sun, Jul 12, 2026 at 5:59 PM Alena Rybakina <[email protected]> wrote: > > I have noticed that patches need to be rebased, so I have rebased them.
Thanks for the latest patches. I reviewed mainly the design and the set of stats added by the v40 series. I read only parts of the discussion upthread, so please correct me if I missed something. The way I look at this is along these lines. How are these stats used in practice for monitoring vacuum performance and making tuning decisions. How hard is it to get or derive them from what already exists. Why does core need to make them available at runtime rather than via hooks. And what are the minimal missing pieces we could start with. As I understand it, one of the purposes of these stats is to help tune vacuum parameters. If yes, did we agree which parameters (cost limits, max_workers, parallelism, failsafe age, work_mem) could be autotuned from these stats? A small test extension demonstrating how these stats are used to autotune vacuum would, in my opinion, set the purpose correctly. In this specific case, where the work is really an external module's job (collecting and storing stats, historical analysis, monitoring, autotuning), I think core would do better to provide hooks with structs carrying the needed information and leave the rest to the module, along the lines of pg_stat_statements. Most of what the patch adds is already computed by the instrumentation vacuum has at the end of each run [1]. The rest can be combined with pg_stat_*, or reconstructed via pg_walinspect and pageinspect. Did we agree why a hook providing these already-computed stats, with an external module storing them via custom statistics as in the v28 approach, is not enough? I looked at what the patches add and whether they are already available in the instrumentation or elsewhere. 0001 (number of times all-visible and all-frozen VM bits cleared). This one is not in the instrumentation but the intent is good for understanding update/delete patterns. It may be more useful to know how many heap pages get frequent updates over time, and how that relates to a write spike or buffer pool lock contention (if only a few pages are hot, the contention and the I/O pattern). But is an accumulated counter enough, and how does it help tune vacuum? I would suggest a separate thread unless we can tie it to vacuum. Can some of this be derived using pageinspect's get_raw_page on the VM fork? 0002 (heap/tuple metrics, per-index metrics). The heap side is already in the instrumentation. pages: 111110 removed, 135804 remain, 246914 scanned (100.00% of total), 0 eagerly scanned tuples: 17999919 removed, 2000081 remain, 0 are dead but not yet removable Per-index page counts. index "bd_id_idx": pages: 54840 in total, 49347 newly deleted, 49347 currently deleted, 0 reusable Per-index tuples_deleted is the one piece not already there. If we need it, why not add it to the existing instrumentation rather than new views and functions? 0003 (recently_dead / missed dead). Already in the instrumentation. tuples: 17999919 removed, 2000081 remain, 0 are dead but not yet removable tuples missed: 81 dead from 1 pages not removed due to cleanup lock contention 0004 (WAL records, FPIs, bytes). Already in the instrumentation. Per-relation WAL can also be reconstructed from pg_walinspect over an LSN range if this is not enough. WAL usage: 3717897 records, 528460 full page images, 4243614245 bytes, 3831091900 full page image bytes, 15221 buffers full 0005 (wraparound_failsafe, interrupts_count). Whether the vacuum ran in normal, aggressive, or failsafe mode is already captured by the instrumentation. I do not have a strong opinion on interrupts_count. Errors are reported in the logs anyway, and since an errored vacuum never reaches the end of heap_vacuum_rel, the instrumentation cannot capture this one. If we do want it as a counter, I would not mind adding it to pg_stat_all_tables, similar to total_vacuum_time and friends. 0006 (VM pages newly set). Already in the instrumentation. visibility map: 246913 pages set all-visible, 222221 pages set all-frozen (0 were all-visible) 0007 (buffer pool access). Already in the instrumentation. buffer usage: 1698168 hits, 6295992 reads, 3135601 dirtied 0008 (per-relation buffer access). The combined count is already in the instrumentation as shown above. Relation-level buffer I/O also already exists in pg_statio_all_tables and pg_statio_all_indexes (though not scoped to vacuum), and block I/O at the database level is in pg_stat_database. Does a vacuum-scoped per-relation breakdown change a tuning decision often enough to justify the new instrumentation? 0009 (timing counters). Already in the instrumentation. Note that total_vacuum_time and total_autovacuum_time already exist in pg_stat_all_tables. I/O timings: read: 287.357 ms, write: 3580.593 ms system usage: CPU: user: 9.89 s, system: 5.74 s, elapsed: 69.45 s Thanks again for all the effort on this. [1] INFO: finished vacuuming "postgres.public.bloat_demo": index scans: 18 pages: 111110 removed, 135804 remain, 246914 scanned (100.00% of total), 0 eagerly scanned tuples: 17999919 removed, 2000081 remain, 0 are dead but not yet removable tuples missed: 81 dead from 1 pages not removed due to cleanup lock contention removable cutoff: 711, which was 1 XIDs old when operation ended new relfrozenxid: 704, which is 1 XIDs ahead of previous value frozen: 0 pages from table (0.00% of total) had 0 tuples frozen visibility map: 246913 pages set all-visible, 222221 pages set all-frozen (0 were all-visible) index scan needed: 222222 pages from table (90.00% of total) had 17999919 dead item identifiers removed parallel workers: index vacuum: 36 planned, 36 launched in total index "bd_id_idx": pages: 54840 in total, 49347 newly deleted, 49347 currently deleted, 0 reusable index "bd_a_idx": pages: 54840 in total, 49347 newly deleted, 49347 currently deleted, 0 reusable index "bd_b_idx": pages: 54840 in total, 49347 newly deleted, 49347 currently deleted, 0 reusable index "bd_c_idx": pages: 144171 in total, 1 newly deleted, 1 currently deleted, 0 reusable index "bd_d_idx": pages: 16920 in total, 15219 newly deleted, 15219 currently deleted, 0 reusable I/O timings: read: 287.357 ms, write: 3580.593 ms avg read rate: 708.185 MB/s, avg write rate: 352.698 MB/s buffer usage: 1698168 hits, 6295992 reads, 3135601 dirtied WAL usage: 3717897 records, 528460 full page images, 4243614245 bytes, 3831091900 full page image bytes, 15221 buffers full memory usage: dead item storage 22.25 MB accumulated across 18 resets (limit 1.00 MB each) system usage: CPU: user: 9.89 s, system: 5.74 s, elapsed: 69.45 s -- Bharath Rupireddy Amazon Web Services: https://aws.amazon.com
