Episode Transcript
[00:00:00] All right, Postgres18 is here. And what's interesting about this release is I think it will actually help people more who have not fully optimized their system yet because of the number of features that we'll be talking about. But if your system's already fully optimized, you have a lot of index scans or index only scans, you're probably not going to see as big of an improvement, but I hope you, your friends, family and co workers continue to do well. Our first piece of content is PostgreSQL 18 is released. This is from PostgreSQL.org and of course the main feature released in this edition of Postgres is asynchronous I O. So basically Postgres will now be able to issue multiple I O requests concurrently instead of waiting for each one to finish. Now, Postgres was already taking advantage of read ahead with things like the Linux disk subsystem, but this just adds to the performance, being able to do it asynchronously and not having a process just wait around for the disk to get back the data. Now this most helps sequential scans, bitmap heap scans and vacuum operations. So not so much index scans. Which is going back to what I said. If you've already fully optimized your systems, you're probably not going to see as much of the benefit with this release.
[00:01:19] The next step is faster upgrades and better post upgrade performance because they are now transferring the statistics when you're doing a PG upgrade. And it also accelerates the upgrade performance when you're dealing with a lot of database objects like tables. And it can process checks in parallel using the jobs flag. And it also offers a swap flag that swaps directories instead of even just linking files. So it's another option to speed things up. So a lot of benefits to PG upgrade in terms of querying general performance. There are a skip scan capability is now available on multi column B tree indexes. So that means you don't always have to use the leading column in your query. You could use a secondary or a tertiary column as well, potentially. So again, if you haven't fully optimized your index layout, if you had a multi column index out there and the system wasn't using it efficiently, say a secondary column. This feature may actually give you better performance without you having to do anything. And they say you can also optimize queries that use or conditions in the where clause, so you could realize a performance benefit from that as well. There are a couple of enhancements to Hash joins, merge joins to use incremental sorts as well as parallel builds for gen indexes to help speed them up. In terms of the developer experience, they're talking about adding the new virtual generated columns so they don't have to be stored any longer. You could make them virtual. The returning clause now allows you to choose old or new values that have changed. And we'll see some blog posts about this. And I think this is a really big one for people if you're using UUIDs as primary keys. Is the introduction of UUIDv7 actually in Postgres. And there's also an alias for the pre existing function gen random uuid. It now becomes uuid version 4, which is what it is.
[00:03:13] And they've also added temporal constraints as well for constraints over ranges. There's a few text processing improvements and you can check this out more, but I think the big one to pay attention to is that full text search now uses the default collation provider instead of always using libc. So they're saying quote, you know, you may want to reindex all of your full text search indexes and PG trigram indexes after you do an upgrade. In terms of security, OAuth authentication is now available in Postgres 18 as well as they are now deprecating MD5 password authentication. So you should definitely start using scram authentication if you haven't yet. And of course there are a host of other features, you know, improvements to replication. We'll actually look at a logical replication improvement in a blog post a little bit later, as well as adjustments to vacuum and explain and particularly explain now includes buffers by default, which is great, but check this out if you want all the detail.
[00:04:16] Next piece of content. PostgreSQL 18 better I O performance with AIO. This is from Cyberdeck. PostgreSQL.com and he shows a little bit of asynchronous I O in action. So basically with synchronous I O, which was what we have today in say, Postgres 17 and earlier, the process sends a request to the disk and it basically has to wait until that request is complete before it submits another request of the I O system. So there's significant weights involved with doing that. Whereas with the asynchronous I O, basically you can queue up multiple requests all at once to the disk system and then process those results as they are returned. So basically you don't have to do as much waiting. And with asynchronous I O, you now have three options that is set by the I O method configuration variable.
[00:05:07] The default will be worker. With three asynchronous workers, there is the IOU ring option which is available for Linux kernels I think 5.1 and higher, which for example, if you're using ubuntu, I think 22.04 and 24.04 do have version of the kernel that supports this. And then sync is basically the legacy synchronous I O. So for some reason the system isn't behaving as you expect. You could set it to sync. And he created a 1 TB database using PGBench. So you can see the table size here. Like the accounts table is now 938 gigabytes. And he went ahead and set it for IOU ring for the IO method and he did an explain query and then he actually ran the query and it ran in 4 minutes 48 seconds. And looking at the disk output, it was relatively busy. Not many CPU wait events for example. And he says there are some functions you can do to examine the Asyncio subsystems like PG getaios and it returns information that looks like this to show the different state of the I O requests. And in this example he turned the I O method on to sync. So basically like PG Postgres 17 and before, and as opposed to taking 4 minutes and 48 seconds or so, it took 6 minutes and 8 seconds. So in previous blog posts we've seen a 3 times performance improvement under some workloads using IO uring. But in this example it's definitely much less. I mean maybe it's 20, 25%, but if you look at the sync system, you can see the wait events are significantly higher in the CPU wait state. So basically this results in the CPUs being more busy. Why? Because they're just waiting. So a lot of this Asyncio system, maybe this will actually help reduce CPU utilization of your system if you do a lot of disk access. And again he shows the disk throughput comparison sync was about 2.6 gigabytes per second versus 3.4 gigabytes per second for iOurink. So again, maybe 25%, 33% or so better performance. So that's great. But check this out if you want to learn more.
[00:07:21] Next piece of content tuning AIO in PostgreSQL 18. This is from Vonder Me and what's great about this post, he has a lot of the previous benchmarking posts about Asyncio. So he has this blog from PGAnalyze this blog from DBI Services, this one from PGEdge and Neon.com and the recent CyberTech one also has a talk about AIO that was done at pgconf.dev. so as he was examining this, he talks a little bit about the system and how it works. And he mentioned, you know, some of the posts were showing again the IOU ring having three times greater performance in some cases. But his performance test that he did, he was actually getting better performance from the worker and not the IO uring.
[00:08:10] So maybe it just depends on your load. And he says some of the reason why this may be different is that iouring everything happens within the same postgres backend process, whereas with the workers you're essentially using independent processes. So maybe there's some efficiency that way on certain workloads. But if we look at this chart here, you can see this is a sequential scan, relatively uniform data distribution.
[00:08:36] And the Y axis is timing. And you can see at 3 workers and 12 workers, which is the yellow and the orange dots gives the best performance with IOU ring following up from there and then all the other sequential scans doing 18 setting it to sync or 17 is pretty much identical. You do see some improvements in bitmap scans in this chart. And again, at high selectivities, the workers offer better performance, followed by IOU ring and then PG18 sync. But you'll see this outlier of three workers actually gives worse performance.
[00:09:14] You know, that's the thing about this new I O subsystem. We'll see how it reacts in the real world, but they may be adjustments that are needed, or maybe there are issues that might be resolved in some subsequent releases to deal with potential issues like this. But I thought this was interesting from the fact that some of the previous blog posts had said IOU ring are the way to go, whereas he's actually advocating for actually using the worker processes and setting the number of workers equal to 25% of your current CPU cores, or maybe even as high as 100%. Anyway, this is a great blog post to get more detail with regard to the AIO subsystem. And again his conclusion, which I mentioned, he thinks the workers probably the way to go. So he suggests worker, which is already the default, and increase the number of IO workers to something like 25% of your cores or 100% in extreme cases. But check this out if you want to learn more. Next piece of content is actually an episode of the talking Postgres podcast. This one is what went wrong and what went right with AIO with Andres Freund. So if you want to dive even deeper into Asyncio, you can definitely check out this podcast. Next piece of content, PostgreSQL update returning made even better. This is from cyberduck postgresql.com and this is a case where you can both get the old row and the new row when using returning in Postgres 18.
[00:10:43] So in this example here, he just creates one row in a table and he does an update of that row, and he returns all the columns using asterisks, and you get all the columns that were updated. So it's showing you basically all the new data that exists after this Update. But in Postgres 18, you can also use the new keyword. So if you say new, you basically get all the columns returned from the new state of that row. But it also offers old as well. So now you can actually get what the original columns were.
[00:11:15] And of course, you may be thinking, can you get both? Well, yes, you can. You can say, give me all the old columns old, new, and it will give you both the old and the new columns together.
[00:11:28] So now you can do some interesting thing and say, does what was old? Return what was new? And you can determine whether something was changed or not, whether it's true or false. So that's definitely a great addition. Next post related to I.e. postgres 18 old and new rows in the returning clause. This is from CrunchyData.com and they're highlighting the same functionality, but he had a different use case where he was using an insert into an on conflict do an update. Whereas he wanted to determine, okay, was the row inserted or was it updated? And you used to have to use this trick by checking for whether XMax was zero, then it essentially was an insert. Well, now with this new capability, you can do the same returning statement, but then do a test. Old is null, and if it is, then basically it's a new row. So basically, check both of these posts if you want to learn more about this new feature.
[00:12:21] Next piece of content, Parallel apply of large transactions. This is from amitkapila16.blogspot.com and he's talking about when you're doing logical replication and when you set up your subscription for a while since Postgres 14, you could stream large transactions. You didn't have to wait till the commit. So this is if a transaction is going on on the publisher, it used to have to wait till it was committed before that data was transferred. Well, now you can stream those large transactions. Well in 16 they took it further by offering a parallel apply option so it can actually stream those transactions in parallel. Well now that's the New default in Postgres18. And this blog post goes into depth about the parallel apply worker and how it works postgres18 and even this performance chart and how much faster streaming in parallel can be compared to just having streaming on or off. And it is a significant improvement of about double the performance improvement when you're talking about say 10 million rows. So check this out if you want to learn more. Next piece of content changes to not null in Postgres18.
[00:13:30] This is from enterprisedb.com and now not nulls are considered more of a valid constraint. So actually when you do a D for a particular table, it shows the not null constraints and they also have names. So this is table name, column name, not null. Now what the cool part about this is is that you can actually create not nulls as not valid now. So for example, you can alter person add not null to the last name column and make it not valid.
[00:14:03] So for new rows it will start keeping this constraint up to date, but it doesn't get applied to existing rows. And then over time you can validate that constraint. So alt a person validate constraint and you specify the one that you created, basically applying the not null to the table. And they also have some enhancements that were done for inherit and no inherit as well. So check this blog post if you want to learn more about that next piece of content. UUIDv7 comes to PostgreSQL 18. This is from thenil.dev. and of course we're talking about the new capability to create UUIDv7s as opposed to just using a fully random UUID. So just to remind you, this is basically a timestamp ordered UUID which can be great to use for primary keys to avoid excessive random IO for indexes, for example. And this blog post talks about that and how to use them.
[00:14:59] And the last piece of content there was an episode of postgres FM last week. This one was on Gadget's use of postgres. Here Nick and Michael were joined by Harry Brundage from Gadget, and they're basically talking about the zero downtime major version upgrade they did that we discussed last week in scaling Postgres where they had a seven terabyte database, they sharded the user data to separate independent databases and then they did a zero downtime upgrade of their database system itself, their control plane. Essentially they talked about database systems in general and particularly how efficient AlloyDB is, especially in terms of costs. What was interesting is they also talked a little bit about postgres being a queue system and that's how a particular person thought about it. But when they actually started using it as a queue they started to run into bloat problems and just a lot of tuple turnover. And because of that I immediately thought of that's where we need oriodb. I know I keep on mentioning that, but it would be great to have a storage system for these type of queue like workloads and eventually in the episode they did specifically mention oriodb in that episode as well. They also talked a little bit about the gadget platform and how basically developers created application on their platform and they can set up the data any way they want. So basically they control the schema and if any query against it has a column and a where clause, essentially they create an index on it. So they're creating many indexes per table for the applications their developers are creating.
[00:16:36] So there was some discussion about is that a good practice or not? And they also just talked about database management in general. So if you're interested in learning more, you can listen to the episode here or watch the YouTube video down here.
[00:16:49] 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 and I'll see you next week.
