Episode Transcript
[00:00:00] I think last quarter when we had Postgres releases I was surprised at 11 security vulnerabilities.
[00:00:10] Well hold on to your seats because we got a lot more this quarter but I hope you, your friends, family and co workers continue to do well. Our first piece of content is PostgreSQL 18.6, 17.11, 16.15, 15.19, 14.24 and 19 beta 3 are released. This is from PostgreSQL and these releases fix 28 security vulnerabilities and over 110 bugs over the past several months. And you'll notice for version 18 there was a gap because 18.5 was not shipped due to a regression. So basically we went from 18.4 to 18.6.
[00:00:54] Now with this update you you will have to address some index issues.
[00:00:59] So there's some additional steps you need to take. If you have done parallel gen index builds, if you use BTRI GIST extension or ltree and basically this is index related so maybe you have to re index but the full guidance on what you should do is at the bottom here under the updating section. So definitely read it if you use any any of these features in your database. And reminder Postgres 14 end of life is coming up November 12th and here are the 28 security issues which is more than double the last quarter I think if I'm remembering correct there were 11.
[00:01:40] So at this point if you are leaving your database out and accessible to the Internet, I would do what you can to reverse that practice as soon as you can. Now there might not be any of these that are remote code execution, but still to my mind it's just got to be a matter of time. But irrespective you should probably update your database as soon as you can. And then of course they list the different bugs that were addressed as well. If you want to check that out.
[00:02:09] Next piece of content. There was another episode of Postgres FM last week. This one talks about MVCC and Nick and Michael will join by Rad and Merrick to discuss his article on mvcc which was MVCC is bad. I think they were saying this article pretty much blew up. I know that my episode scaling Postgres where I had that as the lead got a lot of views as well and I saw a lot of parallels with what they were discussing and kind of what I mentioned because of course we're talking about the blog post of course, which here's the direct link to it right here. But basically the premise is it's all about trade offs. Postgres has chosen certain trade offs with storing row versions in the heap, and other database vendors have chosen different trade offs where they use an undo or redo log to store those old row versions. And there are benefits and downsides either direction you go. But they did follow up on what would be great is to give users a choice. So if Postgres had another engine that supported an undo log, that way you could put, say, update heavy workloads on it, that would be advantageous. And they did talk about it, and they talked about Z Heap briefly and oreldb and what its status is. But Radham mentioned an interesting thing where he tried using the Table Access method API because that's how you could theoretically add more storage engines. But he ran into limitations quite quickly. So is this something that needs to be beefed up and improved in Postgres to be able to support someone adding an engine more easily? But if you want to learn about all the topics they covered, feel free to listen to the episode up here or watch the YouTube video down here.
[00:03:54] Next piece of content Multi tenant Byok encryption in PostgreSQL with PGCrypto so BYOK is bring your own key. So this blog post talks about the case where you have a multi tenant database and each tenant will want to bring their own key to be able to encrypt their data using PGCrypta. So this is showing a solution to do that and each tenant would have their keys living in AWS kms and they show a process of how to use that key to be able to encrypt their own data in their own database. So if you're interested in that, you can check this out.
[00:04:31] Next piece of content does anyone run Postgres without PgBouncer? This is from brainer.org and he was saying to his mind everybody should be running some sort of a pooler and it seems like all the different Postgres providers they're using PgBouncer and arguably RDS proxy is a fork of that, although it looks like they're using Supavisor at Supabase of course, but most of these others are using PgBouncer or variants thereof. And he's basically saying since this is essentially a required component of of a standard operating Postgres, shouldn't it be included in the core? Which it does make a lot of sense, but he does call out the reintegration might be a bit of a challenge because most of the Postgres architecture is centered on processes whereas PgBouncer is more about threads as far as I understand. But I could see how it would make sense to be able to merge these projects together or at least bundle PgBouncer as a default that you could choose to not use. But if you want to learn more, definitely check out this blog post Next piece of content Introducing the CyberTech PGOperator this is from CyberTech PostgreSQL and they have created a new PostgreSQL operator. The reason is for multi site Postgres along with some other capabilities. So basically running more multi site clusters. Maybe you have database cluster in multiple countries in Europe for example, they wanted an operator that could support cross site replication, automated failover, things of that nature. So if you have that use case, maybe you're interested in checking this out.
[00:06:15] Next piece of content Modeling state transitions in Postgres this is from thoughtbot.com and they're talking about a scenario where maybe you have a users table with a status column.
[00:06:25] The problem with this is you are not tracking state changes. So if a new status comes in and someone becomes active, for example, you don't know that historically they were in review or they were pending or they were denied. So they are proposing to model it differently and actually have a user statuses table. So it links to the user with the particular status and when it was created is essentially when that status change happened and they wanted to be able to query this efficiently to be able to get a given user status. So they created an index on user status on the user ID, the CreatedAt descending and ID descending and include the status as well as a separate payload. They first tried a correlated subquery to pull the current status for a list of users. Then they tried a window function variation. They also tried distincton, which is definitely, I would say, the shorter way to write the query. Then they tried a lateral join and looked at the performance.
[00:07:27] So pulling a single user it pretty much doesn't matter. But Distinct on was the slowest by about tenfold compared to the next fastest time and they checked out 15 users and again Distinct on got significantly worse. The others weren't too bad and then they tried 100,000 users. Distinct dong didn't get too much worse, but it was still the worst of the bunch. But the fastest was the lateral join method followed by a correlated subquery, so those would be the fastest way to retrieve this data. They also showed a couple of other different queries for how you can pull different data, although they didn't measure the performance of those, but check this out if you're interested.
[00:08:10] Next Piece of content Postgres and Production Special Series Diagnosing High Cardinality Workloads in PGSTAT statements part 6 this is from pganalyze.com and they're looking at high cardinality workloads and basically this is where you have a lot of variation, say in your where clause. You're going to be tracking a lot of distinct statements in PGStatStatements, which can be harder to track.
[00:08:33] So one thing they're mentioning here, if you have all of these unique queries, it may be coming from an orm, for example, because there are certain ways you can ask for data where actually run separate queries within lists, and some of them are quite large, so those could be all considered distinct queries. Some things you can do to address it Is upgrade to Postgres 18 because they did have an enhancement to do endless normalization, so that can help. In his example, 671 unique statements went down to 120 on Postgres 18. In addition, Postgres 19 does this even further where it converts it to an array, I believe in terms of what gets stored in PGstats statements. So PG19 when it's released would be even better in theory. And of course you may need to increase the number of max statements that you're tracking with pgstatstatements also if you're needing to collect more data to be able to track queries. But check this out if you're interested. And the last piece of content There was another episode of the Talking Postgres podcast last week. This one is on how AI is changing software development with Simon Wilson.
[00:09:44] I haven't had a chance to listen to this one yet. I don't know how much they talk about Postgres, but if you're interested, you can definitely listen to the episode here or watch the YouTube video down here.
[00:09:57] I hope you enjoyed this episode. Be sure to check out scalingpostgres.com where you can find links to all the content discussed, as well as sign it to receive weekly notifications of each episode. There you can also find an audio version of the show as well as a full transcript. Thanks. I'll see you next week.
