The future of running a Postgres database, 5mins of Postgres, and more

View in browser

Hi there,

Welcome to the April 2023 edition of the pganalyze newsletter!

Last month the pganalyze team got together in Boulder, Colorado, and one of the questions we asked ourselves: "What does the future of running a Postgres database look like?".

For example, does running a Postgres database mean you have to tune autovacuum constantly? Or re-think your indexes when your workload changes? Or figure out what caused the Postgres planner to change a plan?

What if there was a way that the database could be optimized in a more systematic manner, whilst automating the repetitive tuning tasks?

We believe that pganalyze has a role to play - but instead of looking at this as a machine learning, or generative AI problem, our focus is on a smart, deterministic system that helps you tune your database, that is controlled by data platform engineers and application developers.

Working towards that goal, in the past month we've made substantial progress in two directions:

1. Our new autovacuum tuning recommendations (for example to determine the optimal autovacuum scale factor/threshold setting for each table) which we are starting to test in early access

2. An improved Index Advisor that lets you have more control over how indexes are selected, through constraint programming (see also our talk at pgcon)

In this newsletter you'll also find a few more notes from our team-offsite in Boulder, as well as the latest product updates and "5mins of Postgres" episodes.

All the best,

Lukas

Team offsite in Boulder, Colorado

pganalyze_team_offsite_Boulder_Colorado-1

In late April, we came together in beautiful Boulder, Colorado, to discuss future features of the pganalyze app, ways to engage better with you, our customers and newsletter readers, and how to keep on building the pganalyze products in a high quality, sustainable way for you.

The team had a really good time and it was good to see people from Japan, Austria, Canada, and the US coming together to meet in person.

As noted in the introduction, we spent quite some time on talking and prototyping how pganalyze can help you run your database better - and we look forward to sharing some more outcomes of this with you later in the year.

Have thoughts on what you'd like pganalyze to help with? We're always eager to hear your feedback and ideas. Feel free to reply back to this email with your notes.

Updated Privacy Policy, effective April 28, 2023

To incorporate recent changes in US and European data privacy laws, we have updated our Privacy Policy, as well as the Data Processing Addendum (DPA).

5mins of Postgres

E61: Collations in Postgres→

We show what collations are and specifically look at glibc and ICU collations. We flag problems that can occur with collations when using different OS releases.

E62: PL/Rust 1.0 and its trusted language mode→

We walk through trade-offs and benefits of PL/Rust, how it achieved trusted language mode by disabling specific features to preserve security, and more.

E63: Vacuum Cost Limit & Parallel Aggregate improvements in Postgres 16→

You can now adjust vacuum cost limit whilst VACUUM is running. We also look at array_agg and string_agg now being parallel aggregates.

E64: Waiting for Postgres 16: Buffer cache hit ratio and I/O times in pg_stat_io→

We look at the concept of a "normal" I/O operation and the addition of the read time and write time columns to pg_stat_io.

E65: Waiting for Postgres 16: Running EXPLAIN on any query (even with $1 parameters!)→

In Postgres 16 you will be able to easily get generic plans for a query with the new EXPLAIN (GENERIC_PLAN). We're looking at generic plans vs. custom plans, the benefits of generic plans and how it helps you get a plan from pg_stat_statements.

Subscribe on YouTube

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

Subscribe to pganalyze on YouTube

Product updates

Index Advisor

Improve loading times of Index Advisor overview when many recommendations are present
Prevent errors when recommending indexes on schemas that include existing hash indexes
- Note that this does not yet recommend hash indexes (which is on our roadmap)
Fix "Unexpected rtekind" errors for functions and VALUES
Initial stage of rollout of new Index Advisor model based on constraint programming
- Upcoming feature that will give you a better index selection, and more control over how Index Advisor picks its indexes
- This is not yet available outside of very limited testing - we'll open up early access of these improved recommendations in a few weeks
- Hear more on the background about this work at our upcoming pgcon talk: Automating index selection using constraint programming

Check-up and Alerting

Email alerts: Include pganalyze organization name in email body
- This helps distinguish between which organization sent an alert, when you are a member of many organizations
Improve performance of loading check-up list with many issues

Connection tracing / Wait events

When viewing high resolution data in the wait events view, we now use the following time buckets, for better readability:
- equal or less than 1 hour: 10 seconds bucket (up to 360 data points)
- 1 - 24 hour: 5 mins bucket (up to 288 data points)
Allow zooming into specific time ranges by click and drag to zoom (like on other graphs)

Bugfixes and other improvements

We've removed a character length limit (20,000) that previously applied to query texts in certain conditions
- This limit that may have caused collected queries to not be analyzed by pganalyze, e.g. the Index Advisor would have reported an error
Explain text to JSON: Add support for "Presorted Key" property
Fix support for certain SAML configurations when utilizing the same IdP across multiple organizations
We've begun rolling out an more efficient approach for uploading statistics snapshots
- This change should be transparent with no visible changes, but if you notice anything amiss, please reach out

Review us on G2

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

Fill out the G2 survey