Webinar recording: Advanced Autovacuum Tuning and working with the new pganalyze VACUUM Advisor

Find the presentation slide deck and all other shared material below! On July 26th, 2023, we hosted our webinar advanced autovacuum tuning and how you can work with the new pganalyze VACUUM Advisor that got launched on July 25th, 2023. In detail, we talked about 1. How to identify ineffective VACUUMs that run but don’t actually produce any results 2. Tracking freezing stats to prevent TXID wraparound, and how to find problem tables 3. The VACUUM xmin horizon, and how it causes “Dead Tuples Not Yet Removable”, and how pganalyze alerts you of this situation 4. How pganalyze calculates the age of transaction IDs using calendar time (not just counting TXIDs) 5. How pganalyze estimates bloat, and when the estimates are more vs less accurate 6. The new logic for scale factor and threshold tuning recommendations, and how it works behind the scenes 7. How VACUUMs that have multiple index phases can use a lot of resources, and how pganalyze recommends changing autovacuum_work_mem to avoid the problem 8. The new pganalyze VACUUM Advisor launching at the end of July We are glad that you liked the presentation. Come join us live for our next webinar! Articles and material we talked about in the webinar (in chronological order): Presentation slides: https://resources.pganalyze.com/pgana... pg_stat_progress_vacuum: https://www.postgresql.org/docs/curre... Autovacuum, dead tuples not yet removable, and the Postgres xmin horizon: https://pganalyze.com/blog/5mins-post... Launch blog post for the pganalyze VACUUM Advisor: https://pganalyze.com/blog/introducin... Getting started docs for pganalyze VACUUM Advisor: https://pganalyze.com/docs/vacuum-adv... Table bloat in Postgres: https://pganalyze.com/docs/vacuum-adv... pganalyze column stats helper: https://pganalyze.com/docs/install/tr... pganalyze docs around bloat: https://pganalyze.com/docs/vacuum-adv... “Vacuuming ‘dead tuples not yet removable’ in PostgreSQL and other tales" blog post:   / vacuuming-dead-tuples-not-yet-removable-in...   pganalyze check for xmin horizon: https://pganalyze.com/docs/checks/vac... pganalyze docs for freezing: https://pganalyze.com/docs/vacuum-adv... Webinar recording: How to tune Postgres autovacuum to improve performance and reduce bloat:    • Webinar recording: How to tune Postgres au...   VACUUM performance metrics in pganalyze: https://pganalyze.com/docs/vacuum-adv... Inefficient index phase check in pganalyze: https://pganalyze.com/docs/checks/vac... Free pganalyze ebooks and webinar: https://pganalyze.com/resources pganalyze newsletter: https://pganalyze.com/newsletter - - - - - - - - - - - - - Following, we are sharing some of the responses to the questions asked in our live Q&A session with everyone as they might prove to be useful to all of you, not just the original questioner: - - "Do we need to upgrade our Collector for the VACUUM stats to appear in our pganalyze cloud?" - by Krishna K. S. Answer at 44:04 - - - - "How do you determine a stale replication slot?" - by Shane B. Answer at 44:33 - - - - "Once auto_vacuum doesn't clean up a dead tuple in a table because it's still being used in a transaction, if it tries again and again, won't it clean it up when the running transaction has ended?" - by Sixtus A. Answer at 45:30 - - - - "Does pgAnalyze treat RDS replicas differently than RDS master? Or would the Vacuum stats be specific to each instance as physically they are different databases?" - by Krishna K. S. Answer at 46:26 - - - - "if I want to update 1 Billion of data. what is the best strategy to avoid autovacuum run continuously?" - by Daniel S. Answer at 47:21 - - - - "Can this potentially substitute full vacuum completely in a 24x7 production world?" - by Viral S. Answer at 48:42 - - - - "Should autovacuum average read/write speeds be consistent ? and if they are not what does that mean?" - by Ahmed I. Answer at 49:40 - -