Welcome to the first 2024 edition of the pganalyze newsletter!
Today, after the first full work week of the new year, I took a moment to sit down, think about Postgres in 2024 and beyond, and how we at pganalyze will help you work better with Postgres in 2024.
When it comes to core Postgres, I keep coming back to asynchronous I/O. Postgres 17 will likely show the first noticeable gains from async I/O, as described by Andres Freund in his talk at PGConf.EU last month. I also found Heikki Linnakangas'talk on Multi-threaded Postgres quite fascinating. The multi-threading work is still exploratory, and may not go anywhere, but its great to see this, and other efforts to evolve Postgres' 27 year old code base to the next level. And, for a more complete picture, Jonathan Katz wrote a great post about Postgres in 2024.
But, what's in store for pganalyze in 2024?
At the high level, we'll continue doing what we do well: Give you the best insights about Postgres, show Postgres metrics not found anywhere else, and be a trustworthy source of information on all things Postgres.
More specifically, in a few weeks you'll see a new version of the pganalyze Index Advisor, with an improved interface, a configurable model based on constraint programming, and more clarity into the cost data that is used to make recommendations. We will also soon, through an optional setting, enable using the Index Advisor for assessing how you could consolidate or remove indexes.
Soon, we'll start tracking extended statistics, so you know when to run CREATE STATISTICS, for example to help Postgres know about a functional dependency. And if you're note quite sure what that means, there's a 5mins of Postgres episode for that.
We're also going back to the drawing board on some of the core user experiences in the pganalyze app:
Over the next months, we aim to revise how you work with query plans in the pganalyze application, and help you detect plan regressions, and row mis-estimates. We have plans to surface wait event data in more places in the app, to make it very clear when your queries are bottle-necked on a particular subsystem of Postgres. And you'll see us offer better ways to work with clustered and distributed Postgres in pganalyze.
And, all that said: We are always open for feedback!
If you have a burning idea on top of your mind on how pganalyze should work - let us know! Reply back to this email, and I promise we'll discuss it, and may set up a conversation to hear more details from you.
---
In the other parts of this newsletter, you can find details about the new pg_query release-as a developer, you can now run our query parsing library on Windows, work with Postgres 16 syntax (such as SQL/JSON), and more. You can also find our usual 5mins of Postgres episodes, as well as the latest product updates.
Here is to making 2024 the best year for Postgres yet!
All the best,
Lukas
Introducing pg_query for Postgres 16
Behind the scenes, one of the most important libraries in pganalyze is pg_query. We created pg_query almost 10 years ago, and it's seen widespread success, as part of pganalyze, and in many community projects.
pg_query enables you to parse Postgres queries, just like Postgres itself does. But it's more than just parsing: pg_query can fingerprint a query to enable grouping, modify a query's parse tree and turn it back to SQL using the deparser, split up a string that contains multiple queries, and more.
With the new release, we've now added support for running pg_query on Windows (a frequently requested feature in the community), updated the parser to Postgres 16, so you can parse the new SQL/JSON syntax, and added support for alternate parse modes to support PL/pgSQL, thanks to a PR by a community member.
Read more about all the details on the new release, with some code samples to try it out yourself:
Lukas Fittl and Rob Treat join Claire Giordano and Pino de Candia on the Path To Citus Con podcast for developers who love Postgres—to discuss their respective journeys into Postgres monitoring.
Multiple revisions to the new constraint programming model, thanks to early access feedback (more on this soon!)
Correctly handle scans involving OR conditions
In some cases Index Advisor incorrectly assumed an expression like "a AND (b OR c)" was not able to use a simple Index Scan that supports "a", even though "a" could be indexed in such cases.
Scan expressions: Improve readability by hiding type casts and unnecessary parenthesis
Query analysis:
Keep additional type information, to reduce data type related errors
Fix errors loading system functions like "make_interval" on PG 14+
Add support for "Tid Scan" node types
Schema Statistics
Columns: Fix distinct value count for some columns
Queries / Scans:
Improve performance of loading scans list
Show correct scan method for queries with set returning functions in the SELECT targetlist
You can now specify the new `db_log_syslog_server_cert_file` and `db_log_syslog_server_key_file` config settings to configure a TLS certificate
The certificate authority both on the server side and client side also can be specified via config settings
Azure Postgres: Correctly fall back to managed identity when workload identity is not available
Citus / Azure CosmosDB for Postgres: Avoid error collecting schema stats on Citus tables with no indexes
Other improvements and bugfixes
Log Insights: Increase precision of visualized log events when viewing short durations
When a timeframe of 3 hours of less is selected (e.g. with the "Last 1 hour" selector in the date picker), the visualization now shows data points in 1 minute intervals to make it easier to debug spikes in events.
Update query parsing logic to Postgres 16 parser
This adds compatibility for queries that use new syntax added in Postgres 16 (for example the new SQL/JSON syntax), when parsed for table references, or syntax highlighting purposes.
Fix table rendering issues causing table content to disappear after scrolling
Previously certain pages, for example the per-table queries list, could have a client-side rendering bug where the data in a table suddenly disappears, and only the background color is visible
This is now resolved by replacing the relevant parts with an improved, modernized rendering logic
Review us on G2
Please leave your feedback (good or bad!) in this survey - we appreciate it!
