Alena Rybakina has submitted a new session! Vacuum Performance Analysis Tools Vacuum is one of the most critical processes in a database system. It prevents issues like table and index bloating and emergency freezes caused by transaction ID wraparound. It also keeps the visibility map up to date. However, improperly tuned autovacuum settings can lead to excessive resource consumption, causing queries across the system to slow down. Database administrators must carefully adjust autovacuum parameters to balance the benefits of vacuuming with its overhead. Today, administrators typically rely on basic statistics, like the number of vacuum passes over tables or real-time data from pg_stat_progress_vacuum. But these metrics are limited — they’re only available during vacuum operations and don’t offer a strategic, long-term view. I’d like to explain our mechanism for collecting detailed information about vacuum resource consumption for both tables and indexes [0]. This data is stored in the Cumulative Statistics System on a per-relation basis — meaning for each table and each index. It can be analyzed using monitoring tools like pg_profile [1]. pg_profile is a PostgreSQL extension that captures query statistics and snapshots them over time, helping to identify and troubleshoot server performance bottlenecks. I’ll demonstrate how these new vacuum counters can give you a clearer, object-level view of vacuum activity in your database and help guide more effective autovacuum configuration. [0] https://www.pgevents.ca/events/pgconfdev2025/callforpapers/374/ [https://www.pgevents.ca/events/pgconfdev2025/callforpapers/374/] [1] https://github.com/zubkov-andrei/pg_profile/tree/master [https://github.com/zubkov-andrei/pg_profile/tree/master] Open session [https://sessionize.com/app/organizer/session/19661/950384] These notifications can be turned off on Edit event [https://sessionize.com/app/organizer/event/edit/19661#_tab-advanced] page. Tip: You've used Classic (confer***@***orysql.org) to login. You're receiving this message because you had joined event 'HOW2025' on Sessionize. Sessionize.com, PO Box 6945, Unit 34564, London W1A 6US, UK