Episode Transcript
[00:00:00] Yes, we're going to be talking about pgrust again this week, but I just find the story fascinating and I can't turn down Nick and Michael interviewing Michael Malice. But I hope you, your friends, family and co workers continue to do well.
[00:00:16] Our first piece of content is the most recent episode of the Postgres FN podcast, this one on pgrust. And as I said, Nick and Michael are joined by Michael Malice, who, who's one of the co creators of pgrust, to talk about the Postgres rewrite. And it's interesting. He got into this because he was wanting to help people with their websites, he says, although maybe he was actually talking about web applications and a lot of the pain problems he was hearing about was people having issues with Postgres. So he says, I wonder if we should solve the problem at the source, which is re architecting Postgres. And several times during the interview there was the question about, you know, something new like this is classified as not as reliable or maybe since it was written by AI, it's not as reliable. And I think Michael said an interesting thing. Michael Malice that is where you don't necessarily trust the AI coding so much, but it's the testing infrastructure you build up, you have to trust because the code ultimately has to pass all the tests that you've come up with. And that actually highlights a limitation that most Michael Malice mentioned with Postgres and that passing the Postgres regression suite is actually a kind of a low bar because Postgres, according to him, only has about two thirds test coverage. So there's a fair amount of it that's not covered with tests, presumably.
[00:01:38] So that makes it hard to be 100% identical to Postgres. Of course, now to address this, they have developed numerous code fuzzers to test across both the C code and the Rust code to ensure that the functions are returning the same thing. And they're even finding bugs in Postgres with this process that they are posting back to the project, which is good. And then I was thinking the other day, you know, when would someone migrate to pgrust? And I think Michael Malice had a interesting response to that, is that he sees it as potentially starting as a read replica against your existing cluster.
[00:02:15] At least the most recent version is much faster with analytical queries. So you could spin this up as a read replica and do analytical queries against it. So it's not necessarily committing your OLTP transactions to it yet, but it's kind of getting your feet wet using it. And seeing how it works and then over time that trust will presumably build as I assume this moves forward and the project continues to get more stable. But I thought this was a great show. If you're interested in learning more, you can definitely listen to the podcast here or watch the YouTube video down here.
[00:02:50] Next piece of content pgdog vs. RDS proxy this is from pgdog.dev and so pgdog is the sharding pooler that Lev Kokotov is developing, and he's doing a series of blog posts to compare pgdog to other poolers that exist.
[00:03:09] So RDS proxy is the managed connection pooler created by aws, and the first thing he highlights is connection pinning, because that is something that RDS proxy does if you do a set statement in your application session, you will pin that database connection such that no other application connection can use it. Once you've executed the set statement, you've essentially pinned it to that application connection.
[00:03:35] So you're essentially eliminating the benefits of the pooler to a large degree. This can also happen if you're using temporary tables, certain types of advisory locks, and executing long queries. And by long queries I don't think he's necessarily meaning they run a long time, but the queries themselves, the actual text is large because it does pin connections at a certain size, so 16 kilobytes, but but that's quite a long query. And PGdog doesn't do this. It has a built in Postgres parser and can handle queries up to 1 gigabyte in length. And PGDog automatically handles set commands by transplanting the session state between Postgres connections when it's needed. So that's a technology that RDS proxy does not have currently.
[00:04:23] Next he talks about auto scaling. This is not something that pgdog currently supports, but pgdog is multi threaded and built to run on large CPU machines and generally you would deploy a certain number of replicas if you need them anyway. So it doesn't auto scale like RDS proxy does transparently. But he says the more instances you add, RDS proxy maintains its own connection pool and that pool can't be shared between clients connecting to different instances. So it actually uses more connections on on the database side than pgdog, according to him. And then he talks about performance and he was testing select one queries. So not testing the actual speed of the database but just how fast you can connect and get a response from Postgres. And he tried 1, 10 and 100 connections using PGBench and in all cases PGDOG was faster, but again, take this with a grain of salt. But as he says, PGDog is free and open source so you can try try it anytime you want and they have a helm chart you can use or an AWS ECS terraform module you could try. That's experimental at this time, but if you want to learn more, definitely check out this blog post. Next Piece of content highly available PostgreSQL on Kubernetes in 10 minutes this is from CyberTech PostgreSQL this is their new CyberTech PG operator and they're showing how quickly you can deploy a highly available cluster with it. So if you're interested in potentially using it, you can definitely check this out. Next piece of content Postgres 19 how our advice has changed since we wrote it this is from CrunchyData.com and this post basically compares some of the new features in 19 and how it would revise Some suggestions in previous blog posts from Crunchy Data so the first thing they mention is with the advent of Asyncio, faster scans and vacuum on modern storage, what would they do different? They said they in 2019 they did benchmark a Brin index, a B tree and a parallel sequential scan. They said sometimes Brin won, sometimes the parallel sequential scan won. But with Postgres 18 and now 19 those heap reads can be a lot faster. So there may be more instances where actually scanning the heap doing a sequential scan in parallel is faster than certain indexes. So they talked about some of the configuration options coming in 19. You can adjust in terms of loading data. Copy is still king. It's just with all the different features it's more resilient to failures and how to reject records and whatnot that they discuss here.
[00:06:57] In terms of Toast, it's the same mechanism, but now there's faster default compression because they are introducing LZ4 as a default. Next they mentioned Brin.
[00:07:09] There are some richer ops classes of OP classes available and it's also faster to build. But of course the key if you're going to want to use Brin, which is a block range index, is if your column is correlated with physical order. So you'll definitely want that to get the maximum benefit out of Brin indexes. Next he talks about covering indexes.
[00:07:33] So this is adding the include statement to an index to include additional data payload with the index. So that still helps, but with the advent of Skip Scan, so you can just include the additional column in the index and it can skip over leading left columns that can give you much of the benefit of what an include statement would have done in the past and partitioning still, the reason primarily to do it is for lifecycle, not necessarily for performance, meaning you are managing a data life cycle, there's certain data that needs to expire, that's frequently when you need to reach for partitioning. It's just now smoother to operate with all the different changes that have been introduced in recent versions, along with a few recommendations at the end of things that haven't changed. So if you want to learn more, definitely check out this blog post Next piece of Content Failover Slot synchronization in PostgreSQL this is from PostgreSQL and they're talking about some new features that come to Postgres 19 with regard to replication slot failover so this was a feature introduced in Postgres 17 where a logical replication slot can exist on a standby and automatically synchronize with the primary.
[00:08:49] So in that case, if you have subscribers subscribing from the primary and you lose the primary, the standby can take over as a publisher immediately because it has the slot for logical replication and has been keeping it in sync. So that capability dropped in 17. But coming in Postgres 19 are further enhancements to this process and more insight into the current state of that replication. So they talk about that here, but if you want to learn more, definitely check out this blog post.
[00:09:21] Next piece of content introducing YesQL practical PostgreSQL one concept at a time this is from tapoueh.org and he's released a free set of 24 short PostgreSQL lessons along with a runnable query on real data you can use without having to sign up for anything. So this is definitely free education training about Postgres and SQL.
[00:09:48] So if you're looking for something like that, definitely check out this piece of content and the last piece of content. The curious case of Google's AlloyDB. This is from boringsql.com and here he's analyzing AlloyDB.
[00:10:02] This is Google's Postgres compatible database solution that's 100 times faster for analytical queries.
[00:10:10] I think it's designed similarly to Amazon Aurora where it's the storage system that was significantly rewritten because he's mentioning, you know, the log is essentially the database and wall records are sent to a distributed storage layer built on top of Colossus, which is Google's cluster file system. So there's basically one storage layer and many compute nodes on top, but he goes over in much more detail about what AlloyDB is and how it works as far as we know because it is a proprietary database. But if you want to learn more, definitely check out this blog post. I hope you enjoyed this episode. Be sure to check out scalingpostgrows.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 find an audio version of the show as well as a full transcript. Thanks. I'll see you next week.
