Hi there,

Welcome to the February 2022 issue of the pganalyze newsletter.

As the Founder of pganalyze, I'd first like to take a moment to condemn the situation that has been caused by Putin's aggression in Ukraine. As someone who has spent time in Ukraine in the past, and remembers it well, it is difficult to come to terms with what has happened in the last week. I can only feel privileged in being able to write you this newsletter in safety from my home office in San Francisco. I wish that you as a reader are safe as well, and that the crisis will find its end soon.

But now, back to Postgres and pganalyze:

Today, we are giving a preview of our upcoming Index Advisor dashboard and share more episodes of our weekly video series "5 Minutes of Postgres". We publish episodes every Thursday, so if you're active on YouTube feel free to subscribe to our channel. All "5mins" videos have English subtitles available and also get published on our blog.

New Index Advisor Dashboard - get early access:

(early screenshot, subject to change)

Back in our September 2021 newsletter issue we officially launched our Index Advisor feature. We're happy it's been received very well and want to share further updates. The pganalyze team has been working on making the Index Advisor a key part of pganalyze, and we're exploring multiple ways to present Index Advisor features (see the screenshot above). Of course, listening to and applying your feedback will reap the best results building out these features - that's why we want to invite you to join our feedback sessions and early access to this feature.

We're about to be ready to have you test drive the new Index Advisor features! Sign up for your chance to be invited to early access to the new Index Advisor features. If selected, we’ll email you details about how you get the new features and let you schedule a 30 minute research interview. You need to have a pganalyze account enabled for at least one server as we will let you test Index Advisor with your own collection of metrics.

Sign up to participate and get early access →

5mins of Postgres - episodes from February:

E3: Postgres performance cliffs with large JSONB values and TOAST→

We talk about JSONB and how JSONB performance can behave badly when the JSONB value exceeds the two kilobyte limit where Postgres starts storing data in TOAST (share this episode here).

E4: Writing your own custom Postgres aggregates with Rust or raw SQL→

In this episode, we look at using pgx to write a Rust extension for Postgres that runs custom aggregate code and how to use raw SQL to implement harmonic and geometric mean in Postgres (share this episode here).

E5: Finding the root cause of high CPU utilization in Postgres with EXPLAIN and the Linux perf command→

We look at a hairy Postgres incident that caused 100% CPU utilization and how to use Postgres EXPLAIN and the Linux perf command to debug the problem (share this episode here).

E6: Optimizing Postgres Text Search with Trigrams and GiST indexes→

In episode 6, we walk through optimizing Postgres text search with trigrams, explain the siglen parameter, and take a closer look at the benefits of GiST indexes (share this episode here).

As mentioned previously, we publish a new episode every Thursday with the goal to give you bite sized information and context around the most interesting things happening in the Postgres community from the last week. We hope you enjoy it!

Subscribe to the pganalyze YouTube channel here→

- - -

Product changes in February:

• Significant effort on the next iteration of the Index Advisor, to be released soon
• Updated setup documentation for the pganalyze.get_column_stats() helper
  • This helper is used by the Index Advisor to more accurately assess the selectivity of filter clauses based on table statistics
  • Read more on how to add the helper, for example on a self-managed server
• Introduction of new fair use quota for log volume included with Log Insights
  • In order to improve stability of the service, we have introduced a new fair use log quota of 1 GB of log text / day for the cloud-hosted version of pganalyze
  • If you are subject to this quota, you will see a message in the Log Insights interface informing you of this - Log Insights will drop a certain % of messages throughout the day to avoid you going over quota
  • In case this affects you, we recommend that you review your logging configuration to reduce noisy log messages, or reach out to us to discuss options to increase the quota
• pganalyze-collector v0.42.2
  • This resolves an issue with the v0.42.1 release that caused temporary files used by Log Insights to not be deleted correctly
  • If you have previously upgraded to v0.42.1, we recommend following these steps to upgrade to this patch release:
    • Shut down the collector
    • Install the new package
    • Delete any files owned by the user running the collector (pganalyze by default) in the temp directory (usually /tmp)
    • Start the collector
• Behind the scenes, we have started rolling out our new Rust backend for statistics processing (more on that another time!)
• Multiple bugfixes and performance improvements

- - - 

As always, I'm happy to hear from you and am personally reading all of your responses. Looking forward to your thoughts!

Have a nice day!

Lukas & the pganalyze team

PS: If you're interested in giving pganalyze a try you can learn more about it here. Of course, I'd be happy to walk you through a demo on a video call. You can book a 30 minute meeting with me here. Or, if you have general requests, feel free to get in touch with us here.