Episode Transcript
[00:00:00] Good news. This week there are no articles talking about Postgres 19 or the delays in its release date. Instead, the first article shows how competition can be good, particularly when it comes to performance, but I hope you, your friends, family and co workers continue to do well.
[00:00:22] Our first piece of content, PlanetScale released text search and we have a lot to say. Part 1 this is from paradedb.com so paradedb.com if you're not familiar, they have actually embedded tantiv to give Postgres BM25 ranked text search in Postgres and I did cover this article a number of weeks ago and this is what the PlanetScale article looks like. They released 10 which uses CTIDs to help track and do text searching. And you can see the really high performance difference between paradedB and 10, about tenfold, at least with the data set and the queries that they were using. And I particularly like on the graph here you can show balls, which basically shows how much faster TIN is compared to Parade db. And heaven help you if you're using Postgres Gen for doing text searching. But upon seeing this, and they say in this blog post, you know 10 is fast, at least 8 times faster than parade DB 0.25 in every planet scale benchmark. And the quote continues so fast that the only response which made sense was to shut up and put on our performance optimization hats.
[00:01:41] So kudos to them.
[00:01:43] This is why competition is good. And they tried to replicate the benchmarks using the same stack exchange, benchmark, data set, harness and machine types. And with the optimization they've done, they do see in certain cases, like looking at mixed queries and disjunction queries, maybe now it looks like they're three times faster than ten. Now are they truly three times faster? Again, you always have to take benchmarks with a grain of salt. But what I do appreciate is they saw how fast 10 was on planet scale, so they rolled up their sleeves and said all right, how can we make this fast?
[00:02:19] And at least according to these benchmarks, clearly they have. Now, one reason that they say that PlanetScale says tin is faster is it identifies rows by CTID values, whereas Tantive has to map values from docid to CTID values. So basically this map makes things slower compared to just using CTID values as the direct document identifiers. But they question that and some of the changes they made, they're still using the Tantive engine which uses that.
[00:02:53] So they're still mapping the tantive doc IDs to CTIDs and they looked at another hacker news and it appears that ParayDB is twice as fast or more across various different metrics, although not as quite as much with two terms in a top K search.
[00:03:12] So in terms of what they did, they also saw a few cases where it kind of depends on how things were configured. So there are cases where you could configure 10 to be faster than Parade DB, but it's based upon the dense ratio and then once stop words are enabled that made paradedb faster.
[00:03:33] So how you set up the benchmarks does matter, but in terms of what paradedb did, they started writing a denser structure with better data locality for finding documents. Now this makes the index a bit larger, but it makes it faster. They're also using a blockmax algorithm with a higher throughput. And third, a few other small optimizations related to lazy reading of other pieces of data and more detail is up above, but they said most of these changes were happening in the TANT V library, apparently not in their integration code. So these performance improvements are in their 0.26 release candidate two, so those results should be reproducible from that and it will be in the Next stable release 0.26 targeted for next week. So if you want to learn more, definitely encourage you to check this out.
[00:04:26] Next piece of content related to the previous ones is PGX BM25 1.0 B VM25 ranked full text search as a native PostgreSQL index.
[00:04:37] So PGX Incorporated has released version 1.0 of a BM25 index. So it's essentially what TIN is. And the Parade DB solution is and it says the whole index lives in the index relations own pages. So it gets wall log, crash recovery, physical replication, et cetera. So there's no external search engine and no separate runtime to operate there. Now what does the performance look like? Don't know. But this is an open source alternative so you can check this out if you're interested as well.
[00:05:10] Next piece of content, nobody patches the pooler. This is from thebuild.com and it is easy to forget about. Hey, you're upgrading Postgres. Oh, when was the last time we upgraded PgBouncer? Who knows?
[00:05:24] And that is a particular problem now because of the vulnerabilities that that are now in the public domain for versions less than 1.26 because out of the most recent 3 CVEs 2 of them can be triggered by anyone who can open a PC connection to the listener. So if you have PgBouncer running on the public Internet. You need to patch this ASAP.
[00:05:48] And he goes ahead and discusses the different CVEs, but basically patch it as soon as you can.
[00:05:55] Next post Also related to security CVEs is the vector that lied about its dimensions. This is also from thebill.com and there's a PGvector release 0.8.7 which fixes a CVE that can create an IVF flat index that writes out of bounds on the back end, which can lead to arbitrary code execution. Now what's interesting, what he listed here is that this is the third time this year, he says.
[00:06:22] So it's the third time that a memory safety CVE has appeared in PGvector's index build code this year. And he mentions the other CVEs. So again, if you're using PGvector in your database, definitely go ahead and upgrade that package. Next piece of content what is DirectIO and why does Clickhouse managed Postgres use it for backups? This is from Clickhouse.com and DirectIO basically is a flag that tells the kernel to skip the cache for particular reads.
[00:06:51] So that has a benefit because now you're no longer evicting things from the cache when you take a backup. But a disadvantage of it is that these particular reads also lose the kernel's read ahead, which can cause ramifications for actually how fast things can be read. And this particular blog post talks about their managed Postgres solutions, which can use four striped NVMe drives, and one small read can keep the drive busy while the other three sit idle.
[00:07:21] So let's take a look at these charts. Here you can see that the actual backup wall time is pretty much the same whether you're using buffered reads or direct I O, although direct I O with very small reads was slower. Maybe that's because it was only accessing one disk at a time. If you have very small reads, if you make it the size of a stripe, then it does access all four disks. Next we look at warm but idle data that was evicted. And when you're using buffered reads for the backup, in this example here, with a half a terabyte database, they had 40 gigabytes of cache evicted, whereas if you use direct IO, nothing is evicted from the cache. And you also have higher performance from queries, because when using Direct I O it looks like the query P99 is three times faster than when doing buffered reads. And they also said it does use 14% less CPU. But I imagine most of the difference is because you're evicting things from the cache, so it's slower to run those queries. Now, how they're using Direct IO with this is that they do use Wall G, which has a direct I O option. So a normal backup without Direct IO, a read from the backup is similar to a read from the app. It essentially goes through the same pathway and causes the cache eviction that was shown up above. And they show a graphical example of the kernel page cache slowly losing queries when it's coming from memory because all the cache is being taken over by the backup. So they're skipping the cache with Direct IO. But if your direct read is small and you're striping the volumes, you're only going to be pulling from one drive at a time. So you need to, as I say here, size the direct IO read to the stripe. So if you have one drive in RAID 0, you need 1 megabyte read size, and 8, you require 8, etc. And they show an example of that where you have four drives and they're using a 1024 wall G direct IO block count to do the backup, and all volumes are being used when it's taking the backup. And they also talk about some other Wall G configuration that they had to tune as well, as well as more detailed data down here. So if you're interested in this, definitely check out this blog post and the last piece of content. Consolidating databases with Postgres logical replication.
[00:09:50] So this is from TBA o u-dot org and this is a second part of a series where he talks about Postgres logical replication use cases.
[00:10:00] And in this one he's talking about maybe you have multiple OLTP application servers. For example, you have a shop server running, you have your CRM server, you have a billing server, or these are separate applications with, of course, Postgres databases. Well, you can set up logical replication to publish particular tables and even specific rows and columns of those tables into a warehouse or a staging area. And each application or server would have its own schema. So you have the shop schema, the CRM schema, the billing schema, and then as that data is synchronized into the staging area of sorts, you can then do CDC operations out of it, or change data capture to pull that data out to put it into some other analytics solution or platform. Or you could just query it straight from here, if that makes sense for you. And he goes over all the detail and a lot of code in terms of setting this up and some things to be aware of and cautious of as you're setting this up, so if you want to learn more about this, definitely check it out. I hope you enjoyed this episode. Be sure to check out scalingpostgrads.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.
