Welcome to the November 2023 edition of the pganalyze newsletter!
Today we're happy to announce the VACUUM Simulator - a new feature in pganalyze, as well as open-source project, that lets you experiment how different autovaucum scheduling settings affect when VACUUM gets triggered in Postgres.
We've developed the simulator as part of our work on the recently launched pganalyze VACUUM Advisor, and it shares some of its logic with the Insufficient VACUUM Frequency insight that helps you tune the autovacuum threshold and scale factor, to reduce table bloat.
To explore the simulator on your own tables in pganalyze, simply navigate to Schema Statistics, select a specific table, and adjust the settings in the VACUUM Simulator tab.
Last month we've also hosted a webinar about the next generation of pganalyze Index Advisor, enabling better recommendations and more control over what gets prioritized, using our new constraint programming model for index selection. Missed the webinar? Watch the webinar recording.
As always, you will also find our recent podcast interviews and "5mins of Postgres" episodes in this newsletter.
All the best,
Lukas
Introducing the VACUUM Simulator
You might remember the launch of our pganalyze VACUUM Advisor back in July. For many of us, it's not easy to understand how VACUUM works in Postgres and when autovacuum triggers. As you start digging into it, you’ll discover more and more questions. And when you have many tables, it’s hard to keep track of it all. During our work on the VACUUM Advisor, Keiko on our team figured it would be great to be able to visualize specific autovacuum settings and their impact.
Today we're excited to announce a new project that helps you understand how autovacuum scheduling works for your tables, and how different Postgres settings influence it: The VACUUM Simulator.
The VACUUM Simulator is available as a standalone open-source project, as well as bundled into pganalyze for each of your tables based on statistics we already collect. Learn more in our blog post!
Webinar Recording: Automating Postgres Index Selection Using Constraint Programming
On November 15th, 2023, we hosted our webinar on index selection with constraint programming. We walked through our constraint programming optimization model that finds the mathematically optimal solution, showed how to guide a constraint programming solver to find the right indexes for a table, and how pganalyze extracts the relevant data from a Postgres instance.
In this episode, Lukas talks with Tobias Macey and shares some hard-won wisdom about the causes and solution of many performance bottlenecks and the work we're doing at pganalyze to shine some light on PostgreSQL to make it easier to understand how to keep it running smoothly.
In this episode of the SaaS Podcast, Lukas went into a lot of the details around how pganalyze got bootstrapped into the business it is today. He shares lessons learned along the way over the years.
We talk about pg_stat_checkpointer and how it changed pg_stat_bgwriter statistics. We also look at its impact on shared buffers, the Postgres WAL, and pg_stat_io.
We look at how GitLab investigated an issue with LWLock lock_manager contention. We explain heavyweight locks and lightweight locks, when a fast path happens, and more.
We talk about creating functional dependency statistics to improve plan cost estimates in Postgres. We also walk through the different types of statistics Postgres collects.
VACUUM Simulator lets you tweak autovacuum settings to learn the relation between settings and autovacuum patterns. It also helps you finding better autovacuum settings for the table.
This feature is available in the individual table statistic page in the Schema Statistics page.
Columns: Show estimated distinct values for each column (n_distinct from pg_stats)
Constraints: Show unique indexes correctly in the UI, even if they miss a dedicated pg_constraint entry
