Welcome to the February 2023 edition of the pganalyze newsletter!
Today, we start by sharing all the details about an upcoming Postgres 16 feature: The new "pg_stat_io" view, which our team helped review during the Postgres development cycle, got committed recently.
We're also excited to announce our new pganalyze Enterprise Server release, v2023.03.0, which brings Lock Monitoring and other features to the self-hosted edition of pganalyze.
And later this year, at the end of May, you can meet us at pgcon in Ottawa where we've just heard back that our talk on automating index selection using constraint programming got accepted!
As always, you can also find our 5mins of Postgres episodes in the newsletter, where we've talked about topics such as tracing locks, pg_repack, benefits and downfalls of unlogged tables, and more.
All the best,
Lukas
Cumulative I/O statistics with pg_stat_io in Postgres 16
One of the most common questions we get from people running Postgres databases at scale is: How do we optimize the I/O operations of our database?
In this article, we talk about querying system wide I/O statistics with Postgres 16's newly added pg_stat_io feature, how it enables measuring information that wasn't visible before (such as bulk reads, bulk writes and shared buffer evictions) and talk about the future of I/O observability in Postgres.
In our upcoming webinar you will get an understanding of how the Postgres query planner chooses different plans for the same query, and how to debug bad plans when they occur.
In detail, we'll discuss how to use auto_explain, how to identify a bad plan, how to switch plans to improve Postgres performance, and more.
We've been working on a new approach to enable developers and data engineers to automate Postgres index selection using constraint programming. This work is still in active development, but the good news is:
Our talk at pgcon got accepted! Expect lots of in-depth information about how to use a solver for optimizing index selection, and more. See y'all in Ottawa at the end of May!
We talk about why you shouldn't use VACUUM FULL and how to use the alternative, pg_repack, on Amazon RDS and Aurora. We walk through pg_repack’s benefits, how to install it, and how it helped making one of our tables over 10x smaller.
We talk about performance cliffs encountered with MultiXact transaction IDs. We also look at foreign key relationships and the Postgres VACUUM and autovacuum process.
We talk about unlogged tables, reasons to use them for increased write performance, reduced vacuum impact and less total WAL, and reasons to not use them, because data is lost when Postgres crashes.
Subscribe on YouTube
We publish weekly videos on Postgres, our conference talks, tutorials, product walk-throughs, and more.
Update parser to be fully compatible with Postgres 15
Besides supporting newer syntax like the MERGE statement, this parser update also drops support for "?" replacement characters found in pg_stat_statements output before Postgres 10
When upgrading you may notice that some queries will get new query fingerprints assigned, due to this query parser update
Postgres 10 is now the minimum version for monitored Postgres servers
Enforce maximum time for each snapshot collection
This is especially helpful if you have one slow server on the same collector as many other servers
Support Ctrl+C (SIGINT) for stopping a collector test right away
Multiple improvements for Log Insights & more
Index Advisor
New "Status" page that shows last time the Indexing Engine processed a table, as well as any errors that were encountered
Correctly handle table/index names with leading digits (e.g. "123test")
Consistently retry failed queries when collector is upgraded, or query analysis logic changes
Fix query analysis for operations involving two parameter references (e.g. "$1 * $2")
Unused indexes:
Allow configuring look back interval for unused indexes, and default to 35 days (instead of 14 days)
You can change the interval for when an index is considered "unused" by going to the Check-Up page
Handle table hierarchies (partitioned tables) so that unused indexes are only reported if all children have the index marked as unused
Report never used indexes as unused (if seen for sufficient time)
VACUUM monitoring & tuning
As announced last month, we're starting a new early access program for improved autovacuum metrics (available for testing shortly), and per-table autovacuum tuning recommendations (coming soon)
Previously query tags were detected using a regular-expression based logic, that excluded some special comment syntax - we now use the Postgres scanner (via pg_query's scan method) to find comments in query strings, to improve accuracy for edge cases
Increase entropy of API keys and prefix with "pga"
This doubles the random bits used in API keys for additional security, and adds a prefix to help identify pganalyze API keys
If you want to rotate a legacy API key, go to the "API Keys" page and create a new key, switch over, and then revoke the old key
Fixed incorrect averaging of replication lag for alerts
We've added a rate limit on query samples for the same query within the same log snapshot (we now pick the slowest example for a given query)
Improved warning in case payment for your subscription has failed
Fixed issue where a limited number of invoices were shown in-app
Review us on G2
Please leave your feedback (good or bad!) in this survey - we appreciate it!
