Hi there,

We have been working hard on VACUUM Advisor, and we're excited to finally share it with you! If you are familiar with pganalyze Index Advisor, the layout and overview page of the VACUUM Advisor might feel familiar to you.

With VACUUM Advisor, we give you a high-level overview of performance improvement opportunities around 3 important sub-categories, as well as server-wide and per-table recommendations:

- Bloat - Reduce unnecessary empty space (bloat) in tables

- Freezing - Understand how Postgres prevents TXID and MXID wraparound

- Performance - Tune how fast, efficient, and often autovacuum is running

You can find out more about VACUUM advisor in the launch blog post, as well as the pganalyze documentation. You can also find the direct link to the recording of our launch webinar below.

In this newsletter you will also find other product updates, our 5mins of Postgres episodes as well as a recent podcast interview.

Note: If you're considering whether to become a new pganalyze customer, be aware that we will be changing our prices for new subscribers from September 1, 2023 going forward to support our continued product improvements. We will also be introducing a new reduced pricing for replicas.

All the best,

Lukas

VACUUM Advisor Launch: Webinar recording

Over 150 people attended our live session where we discussed advanced autovacuum tuning, how to reason about it, and walked through the main features and views of the pganalyze VACUUM Advisor. You can watch the recording for free here:

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

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. Other important metrics tracked in the new pganalyze VACUUM Advisor

Podcasts with pganalyze

TestGuild DevOps Toolchain: Postgres Performance at Any Scale→

Lukas joined host Joe Colantonio to talk about tuning Postgres databases for optimal performance. They talked about the evolving role of the traditional DBA, the impact of AI on decision-making processes, and the latest trends in database indexing.

5mins of Postgres

E74: Storing and querying vector data in Postgres with pgvector→

We talk about vectors in Postgres. We are having a look at the claim that vectors are the new JSON in Postgres, talk about Andrew Kane’s pgvector, and what work is needed in the Postgres core to support vectors and ML and AI work in Postgres better.

E75: Postgres performance with IN vs ANY→

We talk about a surprising case Kaarel Moppel ran into, where using "IN" is faster than "ANY". We investigate this edge case in detail and are also looking at Tom Lane's response, who is one of the main authors of a lot of Postgres planner logic, to Kaarel's case.

E76: How to optimize correlated subqueries in Postgres→

Laurenz Able explains how to optimize subqueries in Postgres by understanding the Postgres planner better. We look at correlated vs. uncorrelated subqueries, as well as scalar subqueries vs. tabular subqueries.

E77: How Table Access Methods in Postgres may offer a future without Bloat→

We look at VACUUM and a potential future without bloat in your database thanks to table access methods. In detail, we are looking at Alexander Korotkov's OrioleDB.

E78: Partitioning in Postgres and the risk of high partition counts→

We walk through blog posts by Hans-Jürgen Schönig and George Petropoulos and talk about partitioning in Postgres and how it can either kill performance or make it a lot better.

Subscribe on YouTube

We publish weekly videos on Postgres, our conference talks, tutorials, product walk-throughs, and more.

Product updates

Review us on G2

Please leave your feedback (good or bad!) in this survey - we appreciate it!