Episode Transcript
[00:00:00] I usually record scaling postgres episodes on Wednesday, but I actually saw the addition of Postgres Weekly that came out last week that said, hey, there may be a delay in the issue next week because we're going to be holding it for the postgres 17 release, and I thought that's a good idea. So I'm actually recording this a day later now that Postgres is released, but I hope you, your friends, family and coworkers continue to do well. Our first piece of content is PostgresQl 17 is released, and this is from postgresql.org dot and we'll just mention some of the highlights that of course we've mentioned in previous episodes over the past months. First is that vacuum has a new internal memory structure that consumes 20 times less memory and it helps it improve its speed as well as reduce the use of shared resources. There's an up to two x better write throughput improvement for write ahead log wall processing, and a new streaming I O interface speeds up sequential scans as well as analyze in terms of query execution. The performance of queries with n clauses using b tree indexes has been improved. Brent indexes support parallel builds and some improvements with not null constraints as well as CTE queries. In terms of JSON JSON table is now available as well as different SQL JSON constructors such as JSON, JSON Scalar and JSON serialize as well as more query functions like JSON exists, JSON Query and JSON value. So merge also gets the returning clause and the ability to update views. There's up to a two x performance improvement when exporting larger rows with a copy command, as well as getting the onerror capability so that rows can simply be skipped over as opposed to having to stop the whole copy operation. You can now use identity columns and exclusion constraints on partition tables, and the postgres foreign data wrapper allows pushdown of exists and end subqueries and I think this is a very big feature right here is that a built in platform independent immutable collation provider that works similar to C but with UTF eight encoding is included in this release. So that's awesome with regard to logical replication. Now replication slots will persist. Doing a PG upgrade process, you can also have logical replication slots fail over to a replica as well. I wasn't fully aware of this feature, but I looked in the release notes and there's a new capability where you can define whether you want your logical replication slot to fail over to one of the replicas so that's awesome. And then of course pgcreate subscriber which lets you create a logical replication connection from an existing physical streaming replication connection. There's an SSL negotiation option when doing connections as well as a PG maintain role in postgres 17 for performing maintenance operations as well as PGBase backup, getting incremental backups and lastly, explain now allows you to show the time spent on local I o blocked reads and writes, and has two additional options. One is serialize. That helps show you the amount of time spent serializing data before sending it for network transmission, and then memory to see how much memory is used. And we can now see the progress of vacuuming indexes. So a lot of great features in this release. So if you want to learn more, I definitely encourage you to check out this article as well as look at the detailed release notes here. Next piece of content real world performance gains with postgres 17 b tree bulk scans this is from crunchydata.com and they are talking about one of the features I just mentioned, where in queries get more performant for B tree indexes, and specifically it's due to an enhancement where they're doing scalar array operations execution and before or multivalue lookups like this would actually be done serially or essentially one at a time for each of the values. So this was transparent to the user. They didn't really see this, but internally, apparently that's how it was done. But with the new method they actually, quote consider all of those inputs as it's traversing the b tree, so basically making these searches more efficient. Now their real world example I don't think is actually a real world example, I think it's fabricated. But they did take some effort to try and create a little bit of a more sophisticated schema with four or five tables, and the query pattern reaches out to several of these different tables, some with n or any queries, and then they did some benchmarking between 16 and 17, and what they saw was a 30% improvement in throughput. So essentially the request per second went from around 1200 to 1500, as well as a 20% drop in the average request time from about eight milliseconds on postgres 16 to about 6.3 milliseconds on postgres 17. So that's great. And I know personally when I look at the logs of some of my clients, there are times where I see huge we're talking thousands of ids of inquiries at times.
[00:05:26] And if this enhancement can improve the performance of those queries. That would be great. And I think this is due to the ORM that is being used, because I know, for example with Ruby on rails, the active record orm, there's a way you can do queries that it gives you an actual join to another table. But there's another way you can query it where it queries one table and then it will queries the second table using the ids it got from the first table. And there you can literally have hundreds if not thousands of ids in your n clause. But this is definitely a welcome performance improvement. Next piece of content online upgrading logical and physical replication nodes this is from amitkapila 16 dot blogspot.com. and he had a hand in a lot of the logical replication improvements, and he shows very concretely how this works now. So imagine you have a publisher, you create a table and you create a publication on it. On a subscriber, you create a table and then a subscription on it. Subscribing to this publication, you insert some values into the publisher and then you query the subscriber. You can see the rows that were inserted. So basically we have a logical replication relationship between the publisher and the subscriber. And now he's going to upgrade the subscriber. So the first thing you need to do is you need to stop the subscriber server. Then you run Pgupgrade on the subscriber. So he's showing the exact command that he used here. Now during this time, you know, the publisher may receive rights, so he does an insert again while the subscriber is being upgraded or is down. Now when the subscriber is restarted. So they start the subscriber server and he queries the table. He actually sees the original values as well as the new values that were inserted. So that subscription persisted through the upgrade of the subscriber node. But now he wants to upgrade the publisher. So the first thing you do is you want to actually disable the subscription on the subscriber first. Otherwise he says it may try to fetch changes during the upgrade process.
[00:07:33] So after the subscription is disabled, you want to stop the publisher. Then you upgrade the publisher again using PG upgrade, and he has the statement here. Then you start that publisher server. As soon as the publisher is up, you re enable subscriptions. And if you happen to query the publisher, it still flows through to the subscriber. So again the logical replication persisted across these major database upgrades. Now, we talked a little bit how it works and some things to consider under the hood, so you can check that out if you're interested. But next I want to look at the case of upgrading a streaming or a physical replication setup. Now how he does this is, is he leverages PGcreatesubscriber, which creates a logical replica from an existing physical replica. So the first thing you do is you take one of the standbys, he's calling it node B, and you stop that server. Then on that server you run PGcreatesubscriber. So basically you're taking a physical replica and you're converting it into a logical replica. And while it's in that shutdown state, because I think it has to be that way, to run PGcreate subscriber, you then upgrade that node, so you run PG upgrade on it to update it to the latest postgres version. Then you start it. So at this point it should be logically replicating from the primary, which presumably is node a. So now you create a new physical replica from node b to a new node he's calling node c. So both node b and C will be on newer versions of postgres. You then do the transition of all writes from node a to node b, and then you can decommission node a. Now the thing to keep in mind here is that at this point of transition, to keep these databases identical, it's using logical replication. And a caveat today is that you can't do ddls don't get transferred as well as sequence changes don't get transferred as well. So at this transition point you definitely want to do sequence changes and also some other steps like analyzing the database thoroughly as well.
[00:09:40] But these are awesome features that are great to have. So check it out if you want to learn more.
[00:09:45] Next piece of content PostgresQL Performance tuning Optimize your database server this is from enterprisedb.com dot. This is a very comprehensive resource of all the different levels at which you can optimize your postgres database system. So as you can see, he's starting considerations from the bare metal. What you should be thinking about, you know, the cpu, the ram disks, and then do you want to run on virtual machines? And if you do, you may want to tweak certain configuration parameters like the wall recycle parameter or the wallet zero parameter. Then he goes into some of the os level tuning. This happens to be on red Hat Enterprise Linux. Some adjustments for the file system. I think the main one here is basically you don't have to have access times enabled on your volumes, then on how to configure huge pages for your system and then getting into the configuration values of postgres. So things like max connections and shared buffers and work memory and effective cache size. And he has specific recommendations for some of these, although some of the recommendations aren't quite what I'm used to hearing. Like for example, in the shared buffers, he basically says the smallest of 50% of the ram or 10gb of, and frequently I've heard using 25% of the ram, or maybe I've heard a range of 25% to 50% of the ram. Same thing with the effective cache size. Typically I've heard the range of 50% to 75%, whereas he's going, you should use 75%. So it's a little more than I've typically heard. But he also has additional calculations here too. And then after covering, I assume all the most important configuration changes he recommends, he goes into talking about query analysis in terms of findings, little queries and whatnot. So I think this is a pretty great resource, at least for understanding areas of your system you may want to consider changing. So definitely encourage you to check out this blog post next piece of content there was another episode of postgres FM last week. This one was on planning time. And basically planning time is the amount of time the planner in postgres takes to decide which path it's going to take to satisfy a given query. So whenever you ask postgres to execute a query, the first thing it does is it plans what it's going to do and then it executes on that plan. And some of the areas that can affect planning time is the number of indexes on the table. There's more decisions that have to be made. How many joins are there to other tables? More joins in your query, define more plans that it has to figure out the most optimum path for. And then if you're talking about partition tables, then that increases the planning time as well, because it has to consider which partition tables should be used to satisfy the result of the query. So the more joins, the more partition tables you have, the more indexes, the longer planning time is going to take. Now, one big defense against planning time is using prepared statements. So if you, for example, are using an Orm that has that built in, it allows you to plan that query once and then execute that same plan multiple times if it sees that same query again. So that's definitely advantageous. But of course, the problem is if you're using a transaction pooler that does not support prepared statements like PG bouncer does today, but there are a number of others that don't, or even some service providers that offer poolers that don't support prepared statements. Well, you're going to see an increase in planning time if you're not using prepared statements, because with super sophisticated queries on multiple partitions, multiple joins, multiple indexes, the planning times can easily exceed the execution time in some cases. And I've actually seen some cases where partition exclusion wasn't working because the partition key wasn't included in the query and the planning time was, I think maybe ten times more than the actual execution time of the query. Now, Nikola and Michael did go into some configuration parameters you can change to impact this planning time, and they mentioned a number of warnings with regard to planning time. For example, if you have a lot of indexes or joins, there's excess shared locks that have to be acquired on each of those. So you could run into lock manager contention again. The more partitions you have joins, you have indexes you have on the tables that you're joining. So this was another interesting episode. You can feel free to listen to it here or watch the YouTube video down here.
[00:14:19] Next piece of content building an image search engine on postgres this is from tempo IO and we've seen so many AI based text solutions, but this is the first one I think I've seen that is using images. So they're using an OpenAI model clip vit based patch 32 mode that predicts the most relevant text given an image. So I think they actually built this solution in Python. Yep, and they have the repository of this example here. So basically they're accessing an Amazon product data set from Kaggle that has URL's to photos. So they take that image path and they generate embeddings based upon the Python code they're using. So then to do a query you can either submit it an image, you find the embeddings for that image and then you compare it to your stored embeddings and it shows you similar images. So in this case you have an image of share here that is the prompt, and then from the data set it finds these two images. But you can also query with raw text to find images as well. So in this example they get the text embeddings for telephones and then they're able to pull up these two images. So I thought this was pretty cool. I actually haven't seen image comparison searches or finding images based upon text, but if you want to learn more about it, definitely check out Timbo IO here as well as the next post. Building an AI image gallery advanced rag with PG Vector and Claude Sonnet 3.5. This is from timescale.com and they're essentially building the same thing, just using a different model, but still allowing image searching as well as by text. So you can check out this example if you're interested as well. Next piece of content a few secure random bytes without pg crypto this is from brandier.org dot and he says the pg crypto extension has a function called genrandom bytes, which is great to give you relatively secure random bytes. So it's not really pseudo random, but he was actually trying to build Postgres 17, he says last week, and he had issues building postgres against OpenSSL, which is a requirement for pg crypto. And he says he's not generally a fan of extensions, but he says okay, where in the code are we actually using Pg crypto? And it was only for this gen random bytes function, and it was only used in this one place to get ten random bytes. So he says, well, is there anything else built into postgres? And as of Postgres 13, there's the gen random uuid, and this generates a version, four uuid, and it has random data with the exception of six variant or version bits that are in the middle. So he basically used this function here and grabbed five bytes from the beginning, five bytes from the end to get the ten bytes that he needed. So that let him get a secure random ten bytes without having to use pgcryptae. So check this out if you want to learn more. And last piece of content cloud native PG playground a new learning environment for postgres in kubernetes. So this is a lightweight environment that's designed to run within Docker and lets you run kubernetes. So basically they're using Docker and kind, which is Kubernetes and Docker to set up two different Kubernetes clusters along with two different object stores using minio. So this enables you to simulate a multi Kubernetes cluster environment with failovers between them because you'll also need these object stores in order to do that. So if you potentially want to play around with this, you can definitely click the link here to find out more how to do that.
[00:18:06] I hope you enjoyed this episode. Be sure to check out scalingpostgroves.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 the audio version of the show, as well as a full transcript thanks, and I'll see you next week.
