Episode Transcript
[00:00:00] If you thought there were a lot of security fixes last quarter, you ain't seen nothing yet.
[00:00:07] As I'm recording this, it is November 12th and they just released all new versions of Postgres, fixing 52 security vulnerabilities and 140 bugs. But I hope you, your friends, family and co workers continue to do well. Our first piece of content, PostgreSQL 18.7, 17.12, 16.6, 15.20, 14.25 and 19 release candidate 2 have been released. This is from PostgreSQL and I thought we had a lot of security vulnerabilities fixed last quarter, but Today there are 52 fixes and so many of them are 8.8, primarily because it looks like it executes our arbitrary code, because that seems to be what a lot of the 8.8s indicate.
[00:00:57] So I know I mentioned this last quarter. I'll mention it again.
[00:01:01] If you are running Postgres on the wide open Internet, I would do what I can to stop that practice. Use some sort of VPN to connect to it and put it in a private address space, access it through a bastion, etc. But other than the security issues that were fixed, this set of releases is notable because it is 19 release candidate two, and I think that may mean we may get the 19 release by next week.
[00:01:30] Yes, the planned date for the general availability of Postgres 19 is November 19, so that's one week from today, which is November 12 for me. The other reason it's notable is that this is the final release of PostgreSQL 14.
[00:01:45] So if you're still on 14, I would upgrade as soon as you can. But there were also a lot of bugs fixed and this is one of the largest updating guidances I've seen. Now a lot of it is you just need to re index certain indexes like gin indexes, some gist indexes. If you're using GIST trigramops, there could be some issues with partition tables and BPCAR columns, so I would definitely read this through to make sure that you're doing the necessary steps when you do your upgrade. But if you want to learn more, definitely check out this blog post. Next piece of content Stop using AI to report PostgreSQL bugs this is from thebill.com and he says that the PostgreSQL bug report mailing list has been receiving a large number of AI generated bug reports, often coming in flurries of 10 to 15 bugs each from the same email address and I think this is an example of one such flurry. He said one bug was working as designed, so there's nothing really to do. Two were already being discussed on the hackers news list. One was a duplicate of a previous report by the same person. Three have already been patched in version 19.
[00:03:02] One case where the diagnosis was incorrect or there was a correct diagnosis but the patch offered wouldn't fix the bug and one or two that were maybe not bugs, but basically he's lamenting the facts. It takes him a lot of time to go through these and actually determine if it's an actual bug or not, whereas it probably took them very little time to submit these. Just basically letting the AI do it. And he says please don't do that. Relying totally on the LLM. But basically he said he's not against AI, he's using it himself. But you do need to do your due diligence to make sure that it is indeed a novel bug and quote, be prepared to explain it, justify it and stand behind it. And looking at this list, I wonder if when they submit bugs, because I personally never done it, should there be guidance given to the individual like you know, you know, first check the hackers list to make sure this hasn't already been submitted, make sure it hasn't already been patched. I wonder if the bug submission process, there could be more controls added for that. But if you want to learn more, definitely check out this blog post. Next piece of content what LLMs do to PostgreSQL and DBAs this is from depeche.com and this post mirrors exactly what the previous one was where he's seen an increase in mails to the psql bugs mailing list.
[00:04:23] Whereas there are fewer emails to PostgreSQL General and he actually charted it out in terms of average emails, the volume hasn't changed significantly, at least in terms of PSQL hackers. However, the PSQL bugs emails have definitely increased as you can see here.
[00:04:42] And this is a shorter time horizon for 2026 and you could see an almost two to threefold increase in the number of bugs being submitted for Postgres. Basically exactly mirroring what the previous blog post was talking about. In contrast, questions to the PG SQL General list have actually fallen off a cliff. Almost like this is crazy, it may be a tenfold drop down to I would say a low average of 50 to a low average of maybe 5 now. So it's basically people are getting all their answers from the LLMs and he anecdotally has said that he sees less slack in discord discussions to the point where they're almost gone. He says he does see some traffic on IRC though, but even his website explain.depeche.com that helps analyze queries his traffic to this site has, quote, dropped significantly. So it's definitely an interesting time that we live in and you can check this out if you want to learn more. Now, next piece of content fixing PostgreSQL biggest mistakes this is from Malaspur Me and this is the creator of pgrust and he's talking about a blog post he did back in May called the Four Horsemen behind thousands of Postgres outages. And in that blog post he mentioned these as the biggest problems. I wouldn't necessarily agree with him, but these were his 1 vacuum and transactional ID wraparound issues, 2 connection limits and query parallelism, 3 bad query plans and 4 missing JSON B statistics. And now that he's further into the development of PGRUST and he's hit a 100% pass rate on Postgres upstream regression suite, he's examining where he is in terms of resolving these particular issues. So in terms of vacuum and transaction ID wraparound, he's basically wanting to move towards a storage system using an undo log and a lock free buffer pool, which is the same idea that OrientalDB is using that we've discussed, but apparently also Umbra, LeanStore and Hyper and NoDB, which is from MySQL so it basically does updates in place and it stores older versions compacted onto deltas for active snapshots and rollbacks. So basically this avoids bloat. Now this is not the current storage engine that pgrust is using. They're still using heat based storage. But he says, quote right now we're wiring this new engine into pgrust as a table access method and testing it on real SQL workloads. So it's ironic, the thing that I wanted is basically just another engine to store update heavy workloads on. Is pgrust going to offer this earlier than some of the other projects?
[00:07:30] The second issue he highlights is connection limits and query parallelism, and this is basically going against the process model of Postgres. But how they've designed PGRUST is as a single process database using threads. Now they can do this safely because pgrust is written in rust and you get memory safety at compile time without paying for OS process isolation. But they actually went further and are currently prototyping lightweight fibers in user space as opposed to using threads. So basically if there's some IO waiting, the fiber can suspend itself and let something else run on a CPU core. And with fibers, context switches run orders of magnitude faster than OS thread switches. So in theory you can hold tens of thousands of connections directly against PGRUST without PgBouncer in front. So again, that's something they're still working on, but that's the general direction they're going.
[00:08:26] Third is bad query plans, and for this I know they talked about their gravity query engine and I mentioned it, I think either last week or two weeks ago in scaling Postgres. But in addition to that they're talking about an adaptive planner that uses a feedback loop. So it actually runs the query and if the estimates are different from what was really reported, it can actually give that feedback back to the planner to choose a different plan next time. But that's not without its problems because in some cases it made a query run 10 times slower with the feedback on.
[00:09:00] So that still looks like it's a work in progress. And the fourth one missing JSON B statistics. So basically collecting statistics throughout the JSONB keys and values, that's something that was shipped in the most recent release of pgrust. So that's already in there. Now in terms of what he discussed, he says the native JSON B statistics and the thread based execution model are already in PGRust 4 beta. I'm assuming the fibers he was mentioning, that's still something, that's a work in progress. So they're still wiring in the new storage engine and user space fibers and adaptive query planner. So that's the work moving forward. But if you want to learn more, definitely check out this blog post.
[00:09:44] Next piece of content. There was another episode of Postgres FM last week. This one they talked about extensions on demand.
[00:09:51] And here Nick and Kirk were joined by Alvaro Hernandez to basically discuss containers in Postgres. So they did talk a lot about what's the difference between containers and a virtual machine and why you would want to use containers. And especially why would you want to run Postgres on containers, because typically they're considered immutable and how would you typically manage this? And it's basically using kubernetes. And if you're using kubernetes for Postgres, if you don't want to make your life terrible, you're going to want to use a Postgres operator. And that essentially is what statgres offers. They offer an operator to manage Postgres on kubernetes and this is similar to Cloud native PG for example, which I've mentioned in the past on different scaling Postgres shows. But statres is another one and Alvaro goes into it in this podcast and specifically they're also talking about how to handle extensions when you're working with containers, because do you want to make a fat container with tons of extensions, or do you want to dynamically load in extensions based upon what you want? And that's basically the technology that they're describing in Stackgrass, and I think Cloud native PG does something similar with that, or they've recently introduced that, but they have a particular technology that they're using that they discussed here. So if you want to learn more, definitely listen to the episode here or watch the YouTube video down here. The advantage of it is they do have different graphs and charts that they present during the show.
[00:11:25] Next piece of content an index scan is only half the story. This is from richyan.com and he said they had a same query, same index, but on a different machine. It was giving different performance. I shouldn't say a different machine, but it's basically they were moving to Azure and they had fundamentally different performance.
[00:11:44] So this query was using an index scan and they noticed it was slow when things were not cached, so it looked to be a disk I o bottleneck somehow. So they wondered if bloating or fragmentation was the cause. So they tried vacuum full and it still didn't improve things.
[00:12:02] So they started analyzing what was wrong. They took a look at PGSTAT, tuple, PGSTATindex to see if there was anything wrong with the index in terms of leaf density, fragmentation, anything like that, and they really didn't see that. But as I thought about it, you know when you'd have an index, that index just has pointers to where to find the data on the heap. And it's entirely possible. You have an index with 12 entries in this example here, and those 12 entries only point to two heap pages, so there's actually not many heap fetches you need to do to pull back the data.
[00:12:36] However, if your data is scattered across the heap in an unordered manner, those 12 index entries actually need to read from 12 heap pages to deliver the data. And this is actually something they found when they were doing Explain Analyze buffers, it was doing a lot more buffer reads. So one thing they looked at is correlation. So they looked at PGStats and the correlation for this column for this particular table, if it's a one or a negative one, that Means there's a strong correlation between the column and the table's ordering, whereas if you have a zero, it's little relationship. So on the older environment they were at a correlation of 0.57, whereas on the new table they had 0.037. So almost no correlation. Whereas they had, I would say, pretty good correlation on the old environment. So the question is what's going on with this? So they actually looked at the CTID and how scattered the heap pages were and they saw in the old environment querying 200 rows, all the heap pages were clustered. So it's very easy to pull that out of the disk. Whereas on the new environment, as opposed to being in five heap pages, the data was spread across 101 heap pages all across the disk. So it had to do random IO from many more heap pages to get the data to satisfy the query. So the next obvious question is, well, how did the rows get scattered? Well, it's because when they were transitioning to Azure, they loaded the data into the environment in parallel. So that helped throughput, but it caused the data to be entirely laid out in this randomized manner because multiple processes were inserting data whenever it wanted to as opposed to doing it in a linear fashion. Now vacuum full didn't solve this, and it won't because it doesn't actually try to reorder the rows. For that you need to do cluster which can write table in an index order. The problem with it, it does take an access exclusive lock. So the customer ended up using pgrepack with the order by option. But if you want to learn more, definitely check out this blog post.
[00:14:46] Next piece of content be tree pages sorted and sparse this is from boringsql.com and here they're talking about internals related to Postgres, heaps and especially the indexes. Is what this covers and how the heap can more easily reuse free space. So you can insert data in there without too much consideration for order, whereas with the index it is an ordered structure. So it's much harder to use all available free space in the index.
[00:15:15] So you actually end up with quite a lot of bloat with indexes. And he goes through and demonstrates a lot of this. So that's why it's important periodically if you know you have a bloated index to go ahead and re index concurrently your indexes to rebuild them and make them more compact.
[00:15:32] Next piece of content Is PostgreSQL really winning Part two the Evidence beyond the Rankings this is from stoker PostgreSQL and this is a follow on to his post last week where he talked about the DB Engine rankings and he actually adds a few more reasons why Postgres might be winning. Number one is Follow the money so Databricks acquired Neon, Snowflake acquired Crunchy Data, and then there has been significant investors in Supabase and apparently Supabase was valued at 2 billion in April 2025 and 5 billion six months later. Which is crazy, the fact that different vendors with different databases all have a Postgres version they offer and he makes an interesting note here. You know the protocol may be winning bigger than the database because so many other databases now talk Postgres, essentially. So CockroachDB, YugabyteDB, Google AlloyDB, Postgres Interface and Google Spanner, Aurora DSQL, et cetera, et cetera. And apparently he says also AI agents have already made their choice. They're choosing Postgres to deploy so many different databases. But check this out if you want to learn more.
[00:16:46] And the last piece of content there was another episode of the Talking Postgres podcast. This one was how I got started in Postgres with Magnus Hagonder. So if you want to learn more about Magnus and all his contributions to Postgres and the work he does, definitely check out this show. You can listen here or watch the YouTube video down here as well.
[00:17:07] 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 up 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.