Episode Transcript
[00:00:00] Speaker A: In this episode of Scaling Postgres, we talk about postgres 15.1, a postgres Ten retrospective reducing replication, lag and listen and notify. I'm Kristen Jameson and this is scaling postgres episode 241.
Alright, I hope you, your friends, family and coworkers continue to do well. Our first piece of content is PostgreSQL 15.114 point 613.912, point 1311.18 and ten point 23 is released. This is from Postgresql.org and this is a pretty quick set of releases after postgres 15 was released not too long ago in my opinion, but includes over 25 bug fixes that were done over the last several months that primarily affect postgres 15. They also updated some time zone data as well as this is the last official release of PostgreSQL Ten, so it is now considered end of life. So if you're still on Ten, you should definitely upgrade to one of the more recent versions.
Next piece of content, a tribute to PostgreSQL Ten. This is from JCats five and he's talking about now that postgres Ten is end of life, we should actually look back and reflect on the significant release that it was. Some of the things he mentions here is they actually started doing a new versioning scheme so that it was much more clear what a major version is versus a minor version, et cetera. So they went from the major versions being 9.59.6 to 1011 1213. The other significant thing is adding more parallelism to different areas. So much so that on a particular OLTP workload he had when he upgraded from postgres 9.4 to Ten, he saw a 2.5 x speed up for the entire workload. Now of course there were some changes in 9.5, no six that he skipped over because they introduced some parallel things too, but definitely impressive seeing a 2.5 speed improvement upgrading a couple of versions. The other feature it introduced is declarative partitioning, making it a lot easier to do. Also it had introduced logical replication with publishers and subscribers to be able to send data logically as opposed to just copying a whole instance. He mentioned when you're setting up Replicas, you could now set a quorum for synchronous replication, so not everything had to be in synchrony, but you could specify a particular quorum level. It also made passwords more secure, supporting scram password authentication instead of MD five. And then a few other things like enhancements to the postgres foreign data wrapper, doing push down as well as identity columns as opposed to serial as well as supporting the Am check extension. So this is a pretty interesting look back at what Postgres Ten offered and its significance.
Next piece of content, postgres version 15 a billion transactions later. This is from Kmapo GitHub IO and he's talking about a test he did comparing postgres Ten and postgres 15 using PG bench with 1 billion transactions. Now, out of all this testing that he did, there were some variations in runtimes and things of that nature, but the most significant thing that I noticed was the size difference. So if you look the data size growth there was a difference of 46% as well as the index's size was a savings of 60%. So this just goes to show you all the index improvements that have happened over the most recent versions of Postgres, how much that can really save you disk space. So if you are on an older version, if you want to save some disk space and just move up to the most current version to get all those benefits. But if you want to learn more, you can check out this blog post next piece of content reducing replication lag with I o concurrency in postgres 15. This is from Citusdata.com and if there had not been a new release of postgres along with the Postgres Ten retrospectives, this would have been my primary post that I covered this week. I highly encourage you to read this. I found it very informative and interesting what they're working on and kind of where they're going, the next steps. So basically the area that they're trying to resolve is issues with replication lag. And specifically my understanding is the stage at which the Replica is trying to apply the changes to the database itself. So presumably it's received the wall over the wire and is now applying those changes to the database files on the Replica and that this can cause slowdowns because that's a lot of random I O. And they've actually done some changes to Postgres 15 to address this. And they set up this test here where they set up a PG bench scale of 1032 clients. They set Maintenance I O concurrency to zero because that essentially turns off this new feature that they added and full page writes to off to minimize checkpointing in the process. So they started up PG bench and you can see this is the lag in seconds and how it dramatically increases at a pretty steep slope. And again, they say this is the Replay lag. So I'm interpreting this as the wall trying to be written to all the database files on the Replica. Now, what they did is they then changed maintenance I O concurrency to Ten, which is its default value. And at that setting you see that it starts to not slope as highly. So then they set it up to, I believe it's a 32 and you can see it dramatically stopped the lag and then precipitously dropped down. So basically this setting is reducing replication lag. And then they stepped it down in a stepwise fashion and they found around 16 for this setting. Again, the default was Ten was sufficient to keep up with the load that was happening and not have essentially any replication delay. Now they're using Maintenance. I o concurrency. And this has been in Postgres since, I think, version 13. But apparently with postgres 15 they introduced some changes so that it impacts the apply of wall on a replica. And I do want to read this quote that they put here. If trying this experiment at home, note that the current advice based scheme so this is an I O advice based scheme is only known to work on Linux with Ext Four and XFS. So like I have some clients using ZFS, it won't work with that but they're saying it is expected to work in the future. It doesn't yet work with other operating systems. So non Linux and the true Asynchronous I O proposal being worked on for the next generation will work everywhere via various kernel APIs. So basically this is an introduction to this feature in Postgres 15. It works as long as you're using Linux and Ext Four or XFS. But the future you should be able to use different file systems and different operating systems as well. Now, the reason this works is they're using a particular API called Posexf Advise but it says there are many drawbacks to it and that's kind of why you have a reduced set of operating systems and file systems it works on. But they're looking to move towards Linux's. IO uring. So basically they want to get to full Asynchronous I O if they can. But again, this post has a lot of detail. I highly encourage you to check it out and examine these features. If you're thinking of upgrading to postgres 15 and you've had some replication lag, maybe changing the setting could improve it for you.
Next piece of content listening to Postgres how Listen and Notify syntax promote high availability at the application layer. This is from Enterprisedb.com and they're coming up with a scenario where you want to build a social network that has members. Those members have posts and you have followers of particular members. And if a member posts who has a lot of followers you want to send out notifications to each member, essentially and they're asking how can you handle this? So they inserted about a million followers of a particular character here and then they started with just doing a trigger that inserts into Notifying members into the table. But the problem is doing a single post for one user took 10 seconds because this trigger has to basically insert a million rows along with the post. So that's not a viable option. The next option they explored is using Listen Notify. So basically you set up a Python application that set up a listener and then he modified the trigger such that it does a notify using PG, notify when a particular notification needs to be sent out to followers. Then the application takes it and actually does the inserts essentially in an asynchronous fashion. But he says a disadvantage of this is that if your application goes down you essentially can lose notifications if you have an insert into posts. And another alternative is basically doing polling. So on the notification table they put an Is sent flag and they're doing update skip lock to be able to know what notifications need to be sent out, and their application was rewritten essentially just to do polling to say hey, is there any new notifications that need to be sent? And then go ahead and do that. So this blog post walks through different implementations of how to do these types of features using listen, notify or even polling, along with update skip locked. So if you're interested in that, you can check out this blog post next piece of content logical Replication Improvements in PostgreSQL 15 this is from Amiccapilla 16 blogspot.com, and this is a review of all the changes that have been rolled into postgres 15 for logical replication. He talks about allow replication of prepared transactions. So this was already being done with logical decoding, and now the standard logical replication between two different database servers can now use the prepared transactions. Basically, that means it can start sending data before transactions are complete. Essentially it uses a two phase process to do this. The next one is Allow Replication of all tables in a schema. So now you can just specify a schema to be replicated, as opposed to having to state each table so that feature is available. The next one is allowing row filters for logical replication of tables, as well as column lists for logical replication. So basically this reduces the amount of data within a table that you can replicate over, also allowing logical replication to run as the owner of a subscription, as well as improvements to conflict resolution. So now you can actually specify that a subscription should be disabled if it runs into an error, as opposed to keep producing an error, and then different ways that you can skip over the errors and resolve the replication conflicts, as well as improving communication between publisher and subscriber to be more efficient, as well as having a PG Stats subscription Stats view. So if you want to learn about all these changes in detail, definitely check out this blog post, as well as check out five minutes of postgres episode 43, Logical Replication in Postgres 15 and Why the Replica Identity Matters. This is from Pganalyze.com and the previous post is what Lucas covers this week, as well as emphasizing the importance of Replica Identity when you're setting up this logical replication, because the column lists and the row filters rely upon the Replica Identity and need to be included if you're going to be making update and delete changes. But definitely check out his episode if you want to learn more about that last post. Related to logical replication is performance impact of Row filters and column lists in logical replication. This is from PostgreSQL Fastware.com and they are doing some performance tests against postgres 15 to see what the changes have been. Being able to filter down the number of columns or number of rows that are sent over. And as you can see with initial synchronization so you have 2 million rows and you need to do the initial sync to a new replica. It looks like based upon the filters, you have a pretty linear performance change. So if you're going to do all of them, it's essentially the same as without a filter. And if you're only sending 25% of the rows, it's about 25% of the performance. Same thing with actually sending data over. It's not as dramatic as this, but you definitely get performance improvements when sending over synchronization and you do get transaction per second improvements as well, the less data that you're sending over in rows. And this also holds true for using PG receive logical if you are consuming those logical changes in an application. They also checked column list changes as well. It wasn't as dramatic but definitely led to some improvements. So if you want to learn more.
[00:12:52] Speaker B: About that, definitely check out this blog.
[00:12:54] Speaker A: Post next Piece of Content read Before You Upgrade best Practices for choosing your PostgreSQL Version this is from Timescale.com and they're talking about the decision on when you should upgrade. They mention a few myths like you should upgrade as fast as possible every time, or upgrade when you have to, or upgrade for every minor version. But then they talk about being more thoughtful about it and he has a specific plan that he tends to follow and he also mentions his personal procedure, typically how he does it. First, he upgrades major versions when they reach the minor version zero two. So in other words, 15.1 was just released. He probably would not advocate upgrading to that yet. He'd probably wait for zero two. Next upgrade minor versions as they are available. So I'm assuming once the major version has been upgraded, go ahead and upgrade the minor version as is because normally these essentially binary changes are very minimal risk to do the upgrade next upgrade immediately when you are more than two major versions behind. Now, I don't necessarily agree with this, I think you have a little bit more leeway. I was confused by something he stated here because he said, quote, they, meaning the postgres community, are committed to two versions a year, which for as long as I remember, there's a major release every year. So I don't know why he says two versions a year and he says only five major versions are supported, so your installation will be supported in approximately three years. But my interpretation, it's five years. So postgres ten was released five years ago. So I'm not quite sure what he means by this, but this is one of his recommendations. Next upgrade when the security team tells you to definitely need to do that and upgrade because you need the functionality. So there's something in the release that you could use that would make your life easier, essentially. But if you want to learn more.
[00:14:39] Speaker B: Definitely check out this blog post.
[00:14:41] Speaker A: Next piece of content rewrite or to union in PostgreSQL queries. This is from Cybertechposql.com and he's talking about an older post he wrote where he was saying how when you join to another table and use an or looking at comms between, the performance gets pretty bad. One potential solution to it is using a union to do the comparison instead. And someone said, well, the postgres planner should basically use the more efficient process as opposed to you having to decide the best way to do it. But he actually examined some cases where this doesn't always hold true. So there's certain scenarios where it's not possible to get the same answer converting an or when you're joining two tables to when you're doing a union between them. So he goes and covers that here and explains right, exactly what he says here, why the optimizer doesn't automatically rewrite or to union. So if you want to learn more.
[00:15:33] Speaker B: Definitely check out this blog post.
[00:15:35] Speaker A: Next piece of content. Using PostgreSQL JSON functions to navigate reviews of restaurants in India this is from Dev Two by Francesco. Now, this post talks a little bit about Avian IO, but essentially you can just look at the postgres related information and it's basically a post explaining how to manipulate and work with JSON data. So he imports a set of JSON data into a database and he shows you all the different JSON B functions you can use to manipulate it and pull out data in different ways. So if you're looking for a JSON refresher of different functions and different ways to pull out and manipulate JSON, definitely.
[00:16:12] Speaker B: Check out this blog post.
[00:16:14] Speaker A: Next piece of content securing Petroni Rest API endpoints Part Two using SSL certificates. This is from Procona.com. This is a follow on post from last week. And in this post they're covering client certificate verification. This is where you're setting your clients.
[00:16:29] Speaker B: Up to trust the servers so they.
[00:16:31] Speaker A: Show you how to do that as well as handle Ha proxy with certificates.
[00:16:35] Speaker B: So check out this blog post if.
[00:16:37] Speaker A: You want to learn more.
Next piece of content. There was another episode of Postgres FM this week. This one was on database branching. So my interpretation of this you have data at a certain state and you want to branch it off and take a copy of it to do testing on or experimentation on. You can listen to it here, or you can actually look at the YouTube video here.
Next piece of content. The PostgreSQL Person of the Week is Wilfried Rose. If you're interested in learning more about Wilfred and his contributions to Postgres, definitely check out this blog post and the last piece of content. We did have another episode of the Rubber Duck Dev show this past Wednesday evening. This one was talking about Callbacks and specifically Active Record Callbacks in Ruby on Rails and whether they are good or whether they are evil. So if you're interested in the long form developer discussion about that we definitely welcome you to check out our show.
That does it. For this episode of Scaling Postgres, you can get links to all the content mentioned in the show notes. Be sure to head over to Scalingpostgres.com, where you can sign up to receive weekly notifications of each episode. Or you can subscribe via YouTube or itunes. Thanks.
