Episode Transcript
[00:00:00] Last week, the announcement that wait4 is coming to PostgreSQL where you can read your own rights from a replica. That was almost going to be the headline announcement for the show, but I chose to talk about UUID version 7 and the blog post about wait 4 got pushed back a few positions in the show. Well, it gets moved to the front this week because someone actually put wait4 through its page paces and shows you what it can do. Although this is in a Postgres 19 beta, so we'll have to see what it ultimately looks like when it gets released. But so far it looks pretty cool. But I hope you, your friends, family and co workers continue to do well. Our first piece of content is read your own rights off the primary. This is from boringsql.com and he starts off talking about the issue of where you've written something to the database, but yet maybe your front end is reading off the replica. And a lot of times you could see a latency from data appearing there. Essentially those are stale reads, the data's been updated, it's just not reflected yet in the application. And people try to get around this issue if they encounter it by pinning reads to the primary, which means you're using your replicas less, or they add sleep delays. Or maybe you set flags in redis to determine what should be displayed. But Postgres 19 looks like it's going to be releasing this Wait for command, wait for a particular LSN before you read from it. Now he shows the blog post that we covered last week. Then he talks about in general, what makes a replica lag. Basically, you have the network transport the writing to the disk.
[00:01:46] But one of the biggest problems is replay lag. This is actually you have the wall on the replica, but you now need to replay it into the database files. And this is a single startup process, replaying these records one at a time.
[00:02:02] So unfortunately it's just going to take time and you can have things like maybe a bulk import or index maintenance going on that can slow things down. And a final factor in the lag is how much activity is actually on the replicas, because he looked at a case where load was applied to a replica and when you had no load, the replay lag was 5 milliseconds. I'm assuming this is a primary and a replica on the same machine that he's measuring. But the replay lag was 5 milliseconds. When you applied PGBenge with eight clients, the replay lag increased to 25 milliseconds. And then when he added 48 clients, it increased to over 1 second, which is definitely significant.
[00:02:42] And then this is a very interesting chart where he ran through its paces. He's using two containers on one host, a primary and an asynchronous streaming replica. So he had eight concurrent workers and did a process of writes and reads a thousand times.
[00:02:59] When you read from the primary, of course you're going to have zero stale reads. So you've written to it and then you read from it. Everything will be up to date. And then these are the latencies he reported, usually around 2 milliseconds, as high as 4.3 milliseconds for the P99. He then said, all right, let's write to the primary and read from the replica immediately. So write and then a read, presumably. And he had 992 out of 1,000 stale reads. So more than 99% of the time, what was just written was not read, it was a stale read.
[00:03:35] Now the latencies are a little bit lower because presumably there's less work going on with that replica, but the P99 is higher. Then he said, let's just apply a 50 millisecond sleep and then read the replica. And with that he had zero stale reads. But of course your latencies have been destroyed because you have a minimum latency of 50 milliseconds. Then he said, okay, let's use wait four, so use waitfor. And then read from the replica. He had zero stale reads and the latencies were just slightly increased from reading from the primary. So it was on average 1 millisecond slower, up to 2 milliseconds slower with the P99. So that's awesome. And it shows that this is working. Now, he says you could in theory, use synchronous standbys to try to keep things in sync, but if you just add synchronous standby names, he says, you're still going to get stale reads from time to time, because the default state of synchronous commit is on, which only waits it to be flush to the wall, not actually applied on the database. So you actually need to set remote apply so that data change gets applied to the replica. And that definitely increases the latency, because you have to not only wait for a write to be finished and applied to the primary, but also on the replica, potentially more than one replica as well. And essentially the problem is setting this globally, because what if you have a batch job that needs to run that's going to slow it down? You have migrations basically every Writer is not going to read from some replicas, so only use it when you need it, using the waitfor command. Then he talked about the timeouts, of course, because when you use waitfor, it is holding a connection open. So you should define what the timeout is going to be. Now, he introduced a 50 millisecond delayed replica. Then he tried timeouts of 5 milliseconds, 20 milliseconds, 500 milliseconds, and as you can see, all the reads failed. Now, presumably you're going to want to fall back to the primary if that happens, but essentially every read fell back to the primary if you have too narrow a timeout. So you definitely need to adjust this carefully because as he mentioned, what you don't want to have happen is that suddenly you have eight to ten readers that you're sending read traffic, and suddenly all of them get redirected to the primary because of increased latency. So now you're destroying your primary's performance with read traffic. And because these also hold connections open that could have impact, you know, with max connections or PgBouncer connections. And then he goes over the implementation. It's basically after you've committed a write, go ahead and get the current while flush lsn, store that within the application before the read. You use waitfor lsn and give it the LSN with your particular mode timeout and no throw if you so choose. So if everything works, you should get a success or you may get a timeout output or not in recovery, which basically means a replica is starting up and and it's not in recovery yet. And he goes ahead and covers a sample implementation. So this is very cool. And if you want to learn more, I definitely encourage you to check out this blog post.
[00:06:54] Next piece of content poset talk recap. Postgres isn't slow, your storage is. This is from Clickhouse.com and this of course was a talk at Poset and about Once your database reaches hundreds of gigabytes or terabytes, you start to see slightly slow ingestion performance, inconsistent read latency, vacuum falling behind, slower checkpoints, logical replication lag. Now, there's some things you can do to address each of these, but he says one thing you can do that could be a huge improvement is moving to local NVME storage. So he set up a half a terabyte PGBench accounts heap containing 3.3 billion rows and did a bunch of updates to them. They had two different clusters, one using NVMe instance storage, one using GP3 EBS provisioned at 3000 IOPS, so that's like the bottom of the barrel pretty much. So you should see significant performance differences with this.
[00:07:54] And of course the local NVME clusters achieve 16,000 transactions per second, whereas the EBS volume only achieved 1,700 transactions per second. And the median transaction latency fell from 36.9 milliseconds to 4 milliseconds. So they're showing the performance differences. So there's 19 milliseconds of latency with the page reads on EBS versus 0.3 latency on NVMe storage though, while Fstinc was 11 milliseconds on EBS versus 1.5 on NVMe.
[00:08:29] CPU is pretty much the same, but even lock and schedule operations it was 0.2 on NVME versus 5 on EBS. But again you wonder if they had used a higher performance EBS volume, how close could you get to NVMe? And then what are the costs for that? And then they show a per function CPU profile graph here if you're interested. And then of course they say, well, what are the risks with running NVME backed Postgres on production? Because essentially you're using local NVME drives on ephemeral instances at any time they could be destroyed. And what are you going to do to safeguard your data?
[00:09:04] And his recommendation is basically you have a primary with at least two standbys and furthermore you should use quorum based synchronous replication for those two standbys and ideally those nodes are placed across different availability zones to protect against an availability zone failure. But of course you're introducing even more latency with this and reducing the benefits of NVMe, although it probably still wins out in the end. And then of course what you're going to want to do is to send the wall as soon as it's produced to object storage for higher durability. So place it within S3 for example. And he suggests you could use Wall G, but there are other tools to do that as well. And of course taking your backups as you should always be doing. But if you want to learn more, then definitely check out this blog post.
[00:09:54] Next piece of content New system views in PostgreSQL 19 this is from Clickhouse.com as well. So we're getting some new system views. One is PGStatlock. So it's giving you statistics about the locks that are going on in your system.
[00:10:08] So these are cumulative statistics with one row per lock type and by lock type they Mean whether it's a relation, transaction, ID page object, etc. What type of object is being locked. And it basically shows the number of weight, so it's a cumulative counter and then what the total wait time was spent doing. And it also tells you if the fast path was exceeded. So this could be important if you have a lot of indexes or you have partitioned tables, knowing if you're not able to get a fast path lock would be important to know. So she shows an example of locking one table, one session, locking it in a second, and then querying the PGSTAT lock table for type of relation. And you can see there was one wait for a particular amount of time. Now this looks pretty coarse in its output. So for example, I just see the fact that a relation was locked. It doesn't tell me what relation, but perhaps that may come in a future version. This is just the initial version. She also looked at PGSTAT recovery. So these are statistics with regard to your replicas and how the replay is going.
[00:11:17] So it shows the last replayed read lsn last replayed end lsn current records you are replaying. So basically gives you status of the current recovery state of the system, which is Great. And then PGStack autovacuum scores. So there are five new autovacuum weights being added to Postgres to be able to use a scoring system to determine what is the next table to be vacuumed as opposed to just going in the order found in PG class.
[00:11:47] Now it's actually using table statistics and these weights to determine which table to vacuum next. So it adds a little bit more intelligence to it. And this essentially allows you to output those scores to check the state of the tables and to see whether it's due for a vacuum and analyze a wraparound vacuum, etc. And she also mentions a PGDSM registry allocation. So basically it gives a view into memory that maybe extensions use. But if you want to learn more, definitely check out this blog post.
[00:12:20] Next piece of content. Postgres calculations and the ambiguity of null. This is from crunchy data.com and basically null means unknown. We have no idea what it is. And as a result there are all these I would classify confusing calculations you can do with null. And this blog post goes through all those different calculations and the results you can see because of it. And a lot of it is where you can get into trouble. He talks about Boolean logic with nulls, arithmetic operation with nulls, you know, a not in null trap which I think was discussed by boring SQL in a previous episode of Scaling Postgres aggregate store null and Windows functions with nulls as well as string concatenation.
[00:13:07] So I personally try to avoid nulls as much as I can. Every boolean I create, every text field I create, I create not null frequently integers I set as not null. Timestamps are the only one right now that I still allow nulls. But if you have a lot of nulls in your database, definitely review this blog post to avoid potential issues you can get into Next piece of content LinkedIn Live all right, so you inherited a bad database. This is from virus.org so this is a LinkedIn live of I think he said probably chapter 11 of his book PostgreSQL mistakes and how to Avoid Them. Maybe you've added a lot of notes to your database and he goes over and talks about how you handle that and deal with that. So check this content out if you're interested. There's a YouTube video here as well as the PDF of the slides here.
[00:13:58] Next piece of content PGCatalog Almanac this is from richyan.com and the PGCatalog in Postgres gives us the metadata that describes all the objects in the database, tables, columns, indexes, constraints, etc. And he actually devised an almanac of all the changes through all the different versions that you can then peruse at this link here. And by doing this he's noticed a few things. One Pisker's not 19 has the most new relations in the data set ever.
[00:14:28] Two is only one entire relations ever been removed. PGPL Template and Collation Metadata shows an abstraction evolving, basically values changing from name to text, presumably because text permits customized ICU locale names longer than the 63 byte identifier limit. But if you want to learn more, definitely check out this blog post and the last piece of content Getting ready for PostgreSQL19 this is from Tapou eh.org and this is his opinion about what he thinks is worth knowing about before you upgrade to Postgres 19.
[00:15:08] So this is a pretty long blog post. He has a table of contents up here. First thing he covers is compatibility breaks. Basically JIT is now off by default.
[00:15:18] Standard conforming strings are now always on and you may run into issues. If you're doing dumps and restores from different versions of Postgres. You want to use the most recent version of pgdump that comes with 19 as you're working with things. Next is Radius Authentication is removed. Next is MD5 password authentication now issues a warning. Next, the default OP class for INET and CDR data types. Gist indexing is changing for the ones that B tree GIST extension applies, so you have to be using B tree gist and apply it on essentially IP address data types. So presumably you're going to want to re index those.
[00:15:59] Next, carriage return and line feed characters are disallowed in database role and table space names, so that would be corrected.
[00:16:08] Max locks per transaction gets doubled from 64 to 128 and essentially settings must now be doubled to match the capacity they had in previous releases. So if this has been tuned, it sounds like you're going to have to double it when you upgrade to Postgres 19.
[00:16:24] And lastly the default compression changes from PGLZ to LZ4 and then it goes through the sea of different changes for Postgres 19 that you can read through in order to prepare for it. So definitely check this out if you're interested.
[00:16:40] 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. I'll see you next week.
