PGSQL Phriday #012 Contribution Summary

For the September 2023 edition of PGSQL Phriday I asked folks to write about what they were most excited about with PostgreSQL 16, possibly due to release within the next 10 days or so. Over the last few years as I’ve gotten more involved with the PostgreSQL community, the (essentially but not guaranteed) yearly release cycle always produces a lot of excitement and commentary. I was hoping to capitalize on some of that. 😉

I think due to a number of people attending a PostgreSQL conference during the week blogs would have been written, and the ongoing runup to a pending release, participation this month was lower than normal. But the blog posts (and audio podcast) that we did receive were top-notch and I’m genuinely thrilled to see people make the effort. Keep an eye on these blogs for other content, because the quality of their work is excellent and you’ll surely learn new things with anything new they produce!

PostgreSQL 16 improves infinity: Ryan Lambert

Every blog post that Ryan publishes is thoughtful and clear. I’ve learned a ton from his work over the last few years!

In his PGSQL Phriday post, Ryan discussed the seemingly minor change that now allows +infinity to be used as a timestamp value. The change here is that we can now use the + symbol to specifically reference “forever into the future” and - to reference “forever into the past.”

It matters to Ryan (and probably a lot of others) because other values, like numerics, already allowed the +infinity notation, so not supporting it with timestamps was just an inconsistency that caused additional cognitive load. (my interpretation).

Ryan also threw in a bonus section to encourage people to use the Docker containers of the PostgreSQL 16 beta to do testing. In days past, the best way to work with each beta and release candidate was to install from source, which can be challenging for many people. But the global development group has done a great job making Docker images available for beta testing. I appreciate the encouragement from Ryan.

PostgreSQL 16 has better number literals: Stephan Rutz

I believe this is the first time that Stephan contributed a post to PGSQL Phriday. So first, let me say a big “THANK YOU!!!” Every single month for the last year, someone new has joined the conversation and I get excited every single time. 🎉

I’ve been surprised by the number of people that have mentioned this new formatting option for number literals. To be honest, it’s just not something I think that much about, but as I keep reading about the excitement for this enhancement, it makes more sense and I see the value of helping to visually identify numbers more easily in SQL.

What I also appreciated about Stephan’s post was how he investigated the code changes that were necessary to make this possible across the whole of PostgreSQL 16. In doing so, he also found that this changes goes beyond the ability to place separators in the number literal because values can also be specified as binary, octal, and hexdigits.

Bidirectional Logical Replication in PostgreSQL 16: Aakash M

Another new contributor, Aakash tackled a slightly different feature of PostgreSQL 16 logical replication: bidirectional replication. What Aakash did really well in this post is describe why this kind of replication architecture hasn’t worked before PostgreSQL 16, showing the “infinite loop” problem.

With changes in PostgreSQL 16, users can now specify which data is applied as part of a subscription using a new parameter called origin. By default, this parameter is set to any which will work the same was as before, causing the infinite loop problem. However, the parameter can also be set to None which will instruct the subscription to only request changes that were applied directly from the publishing server directly, not data that was sent from the subscribing node as part of another subscription.

Aakash goes into more detail about some of the nuances in the initial setup of bidirectional logical replication at the end of the post. In many ways this is a really interesting development for logical replication, and one of a few big changes for this important class of data mobility in PostgreSQL.

Our Favorite PostgreSQL 16 Features: Postgres.FM

Michael and Nikolay always have a great discussion on this weekly podcast about all things PostgreSQL. I can’t believe they’re 62 episodes into this venture and still have years of content to dive into. 😉

It should be no surprise to anyone that has listened to the show that both Nickolay and Michael are ardent PostgreSQL fans, and particularly the only and original open-source version. That was Nickolay’s #1 favorite feature. 😊

Once the conversation really gets going, however, both hosts shared a few of their favorite things in the upcoming release. And surprisingly, few of them are “big ticket” items.

• Allowing \watch to run for a specified number of cycles
• Utilizing the op_bytes column in the new pg_stat_io view to more easily convert io counters into sizes for analytics and monitoring
• auto_explain now includes the query_id for parameterized queries which completes the ability to join logs with pg_stat_statements and other places that the query_id is visible. This is a huge improvement, and one I wish could be backported to previous versions, honestly.

Give the episode a listen for even more detail and commentary by these two PostgreSQL community heroes! 🐘

Conclusion

And… that’s a wrap on the last installment for our first year of blogging together! I encourage you to visit each of these posts and consider contributing to the conversation in the future. As PostgreSQL 16 is released in the coming weeks, don’t hesitate to write your own posts showing folks why you love a specific feature or enhancement. If you happen to be available in London, New York or Prague in the coming months, there’s also no better time to come connect with the PostgreSQL community and learn everything you can to excel in your craft. We need each other.

See you next month!!