[00:00:00] With the release of Postgres 18, we've seen a number of benchmarks that are looking at the Async IO changes. But there's one performance improvement that I haven't really seen that many benchmarks about, and that is the Skip Scan. Because this could be a bigger performance gain for some people compared to Asyncio, for example, because historically, to use an index efficiently, you always had to have the leading column of that index in, say, your where clause. Whereas with Skip Scan, that's no longer the case. You can actually skip over, say, that first column, maybe a second column too, in order to still use that index to efficiently run your query.
[00:00:42] But we'll take a look at one example of that in the first blog post today. Also, be sure to stay till the end where in my consulting corner. I'm going to talk a little bit more about my upcoming sale of my course, this blog, Black Friday, but I hope you, your friends, family and co workers continue to do well. Our first piece of content is PostgreSQL 18 more performance with index skip scans. This is from Cyberpeck. Postgresql.com and he's on Postgres18, created a table and created two columns. One that has very common values that he's calling frequent. So it's frequently the same value. You can think of a status column with say, frequency, three possibilities.
[00:01:26] And the second column he's calling rare is actually many more different values. So a high cardinality, because this should demonstrate the Skip scan where it's able to skip over the very common column and just look at the second one and he goes a little bit over indexes and how they're structured in case you want to learn more about that.
[00:01:48] And then he goes into the performance differences. So first he starts with Postgres 17 and running a query where he's just looking at that rare column, that high cardinality column, which is the second column in the index. He did turn off Enable sequential scan, so he is only getting an index scan and that ran in 66 milliseconds. And how skip Scan works, as he explains it here, it's going to find the first entry it can, and then within a loop, look for the values in the second column that match, jump over the ones that don't match, and then move on to the next frequent entry. Now, when you ran this on Postgres18, that has the Skip Scan enabled, of course, that same Query ran in 0.6 milliseconds, so essentially 100 times faster than Postgres 17.
[00:02:39] So that's pretty interesting. I wonder how high the performance gains could get when you have larger data and different types of data distribution. But check this out if you want to learn more. Next piece of content. This is another part of the postgres ultravast replica creation with PG Backrest. This is from Postgres AI. And when you're wanting to create a replica, you essentially have to copy all the data from the primary database to set up that new replica. And the accepted way to do it is to run PG backup. But the problem is it's sync single threaded and it typically delivers only 300-500 Mbps of a transfer rate. And he said this is in spite of having NVME storage and a 75 gigabits per second network capacity. However, on Postgres 18 and using the asynchronous I O with the default of IO method being the worker and three workers, he actually witnessed a 1 gigabyte per second. So basically a double to triple the speed need of Postgres 17 with copying over a one terabyte database. So there's another reason to upgrade to Postgres 18 when you can, but there's an alternative way to do it, and that is using PG Backrest. Now that's typically a backup tool of course, but he's only using it for the purpose of transferring in parallel the primary's data files to a replica to be able to set up replication.
[00:04:02] So he shows how he set everything up here and has the code for doing it. And important to mention is that after the replica is created, PG backrest is no longer needed. It's only a means to get a copy of the primary databases files over to the replica. And he did a performance benchmark with different processes being used and it looks like the best performance was using 32 processes. And this is a 128 virtual CPU instance they're using. But when set to 32 processes, it transferred at 10 gigabits per second. So almost 10 times faster than just using PGBASE backup. So if you want to transfer a database system or set up a replica quickly, this definitely seems to be the way to do it. So definitely check out this blog post if you're interested in all the detail with regard to it. Next piece of content. Why keep your index set? Ling this is from Postgres AI and this is basically a list of why you should keep your number of indexes lean and mean. Essentially the first problem that can happen when you have too many indexes is you have write amplification because every insert or non hot heap only tuple update must modify all the indexes so the more indexes the more updates you have to do. Second extra indexes slow down selects now definitely speeds up execution time but the planning time is where that performance hit comes and he shows details with regard to that.
[00:05:31] Third is basically you're wasting disk space if you're not really using these indexes they're taking up space on your disk. 4 is cache pollution if you have more data on disk that could find its way into shared buffer pool and push out other more frequent data. So if you're not using these indexes it is best to go ahead and remove them.
[00:05:51] 5 is autovacuum overhead every new index adds more work for vacuum to do.
[00:05:57] Six is wall generation because every index change generates more wall so it definitely makes your system run more efficiently when you have just the right number of indexes.
[00:06:07] Next piece of content Postgres internals hiding in plain sight this is from CrunchyData.com and this is a great reference post of different commands you can run either using PSQL or some of postgres system views to understand the current state of your postgres cluster. So to start up they have a list of various PSQL commands you can use to find out data about your tables, about your indexes, about your databases, roles, functions, etc. Next they go into different catalog views like PGStatActivity that shows you the active processes and what queries are running on your system.
[00:06:47] PGStatStatements if you're using the PGStatStatements extension to track the performance of statements that are running on your system. The pglocks view to show what's locking statistics about your database or tables or indexes the various settings that are available within postgres with PG settings the PGrolles to see what roles that are available and she goes over a few other system views as well. So I definitely think this is a blog post to keep handy if you're new to postgres as a way to familiarize yourself with commands and to understand how postgres is running.
[00:07:23] Next piece of content waiting for PostgreSQL 19 sequence synchronization and logical replication so this is a big deal for me. I'm super happy that this looks like it may get into Postgres 19 because whenever I'm doing a logical replication upgrade, those sequence transfers are something that have to be done.
[00:07:43] Now I've got it down to a one liner. I just have to paste into the command line to transfer those sequences. But having postgres add support for it is a lot better. And it has detail about how this works. And essentially you get normal logical replication up and running. You set up a publisher, you have a subscriber, and the sequences can be considered separate. So you can create a publication for all tables and all sequences, or you could create a separate publication for those sequences if you want. There's no real difference in the subscription that you would need to be set up, although if you create two publications, you need to create the two subscriptions. But once you have everything set up and you check the sequence values, they're not in sync. And even if you add new data to the publisher, you see the data on the subscriber, but the sequence still hasn't changed. However, to synchronize the sequences, you do need to alter the subscription and refresh the sequences.
[00:08:41] So the sequence synchronization is not real time. You have to trigger when you want that synchronization to happen, which kind of makes sense because sequences can be burned through pretty quickly. Like imagine you're trying to insert data running into errors. You could reuse a bunch of sequences and you probably don't want to transfer all of that over to the replica. So right now it is not real time and you do have to trigger a manual synchronization on your own. But, but still, I think that's much better than rolling your own script or command to do it. Next post is related to that PostgreSQL 19 logical replication of
[email protected] and essentially they explore the same thing and talk about the problem. Similarly, Next piece of content, there was a new version of pgbouncer that has been released and the main highlight is support for LDAP authentication.
[00:09:34] So if you use that, this could be beneficial to you. They also have added support for client side direct TSL communications, and I think this is supposed to be faster in Postgres 17.
[00:09:46] So basically, if you have an upgraded client library in your application and you're connected to pgbouncer, you should get faster TLS connections with this new version of pgbouncer. Although from the notes that I read about this, it's not as fast connecting to the database yet. But still, it's the client side that's going to have a lot of connect and disconnects very quickly, whereas the database side tends to remain static. I mean, that's the point of the pooler. Next is that they've altered the reporting for idle clients. Now list as idle instead of active. So that's beneficial and also greatly improving performance of scram authentication, which is good as well if that could be sped up for clients connecting to the pooler. But you can look at the full details in the changelog here.
[00:10:33] Next piece of Content did you know tables in PostgreSQL are limited to 1600 columns? This is from Databean IO and this blog post goes over the column limitations in postgres for an individual table and he does all sorts of different testing with regard to it. This is similar to a blog post that I think Cybertech did a month or two ago where they were testing this and the reality is that 1600 limit does not get decremented if you delete columns. So that 1600 number is the total columns that you can ever add to that table. So if you add 800 columns and then you remove 800 columns, you don't still have 1600 left. You have 800 left because you've already used 800 columns and you can't use vacuum to essentially clear them out and reset that counter. You have to actually create a new table and they go through a couple of different ways that could be done, and particularly if you have foreign keys related to that Next piece of content AWS Performance Insights has been deprecated what to know about CloudWatch database insights so at the end of this November, AWS is deprecating the Performance Insights tool for RDS postgres as well as Aurora, and users are expected to migrate to the Database insights functionality in CloudWatch by June 2026.
[00:11:55] And this blog post from pganalyze kind of covers hey, here are some of the differences in terms of how things have changed and how now the Database Insights gives you a Fleet Health dashboard. So if you manage multiple databases you can see that your entire fleet and how you get access to different data. But there can also be a cost difference with this move. And they have a table here that shows the different features that are available at the standard level of Database Insights and Advanced and also the pricing table when you're using the Advanced mode of Database Insights. So if you want to learn more about that, definitely check out this blog post.
[00:12:34] Next piece of content postgres Kafka and Event Queues this is from kmople GitHub IO and he has thrown his hat into the ring talking more about postgres. Should it be used for an event queue or should it be used for Pub sub? And a little bit about Kafka. So if you want to to learn more about that, you can check out this blog post Next Piece of Content There was another episode of the Talking Postgres podcast. This one was on Building a Dev Experience for Postgres in Vs Code with Rob Emanuelle. So if you're interested in that, you can check out this episode.
[00:13:08] And the last piece of content is actually just something a little fun. The blog post title is the Bastard DBA From Hell and This is from cyber.kyvanpostgresql.com and this is how an evil DBA can get back at users, causing problems. And you have to know a little bit about Postgres to know why certain things happen. Like if someone keeps complaining about deadlocks, he says, I'll solve your deadlock problem for you. I'll change the lock timeout to 500 milliseconds. So you kind of have to know why that would work. Or what about he's encountering someone with a diskful message? What would an evil DBA do about that? But go ahead and check this out if you want to learn more.
[00:13:51] And now for my consulting corner. So I'm just going to talk a little bit about the course changes that are coming up and the sale that's coming this Black Friday or maybe a little bit prior. So for this sale I am substantially dropping what the current price is of the course and it will be close to the level where people got the video only option when the course was released. So I'm only making the 200 gigabyte option of database available for download, not the 2 terabyte version and the QAs are being removed. So it's basically a video only version of the course in exchange for a substantial discount. But again, my plan is to rerecord the course on Postgres 18 and when that happens, that version will be free for anyone who ever bought the course.
[00:14:40] In terms of some of my consulting, I am still seeing problems with order by limit come up, suddenly hitting customers with issues.
[00:14:49] Now the most recent issue I had to deal with some of the ways the data was distributed. I couldn't find a sensible default statistics target that allowed all the queries to run successfully. So unfortunately in that case the best path forward was to basically recreate indexes, adding the order by column as the last column in the index. But of course as a reminder, you could also just make the order by a function. So if it's a timestamp, add a 0 second interval, or if it's an integer, add a 0 to it. Or if it's a text string add an empty string to it, but most customers don't really like that hack. Other than that, I hope your fall is going well.
[00:15:34] I hope you enjoyed this episode. Be sure to check out scalingpostgres.com where you can find links to all the content mentioned, 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.
