WEBVTT

1
00:00:00.000 --> 00:00:47.000
Well, we've got a regular battle royale going on between different text search solutions for Postgres. On one hand you got paradedb that keeps optimizing and pushing performance, and then you have PlanetScales 10, which is also trying to push performance as much as possible. But the good news for us as Postgres users is we are the winners in this race because both solutions seemingly keep getting faster. But I hope you, your friends, family and co workers continue to do well. Our first piece of content Making contents and the rest of search really fast, part 2 this is from paradedb.com and they posted last week how they believe they closed the performance gap with 10.

2
00:00:47.000 --> 00:00:54.000
And in this one it shows them where they pull ahead as much as 21 times faster.

3
00:00:54.000 --> 00:01:01.000
But of course you always have to be wary of benchmarks. But this analysis is based upon count.

4
00:01:01.000 --> 00:01:17.000
So they looked at a Wikipedia count and from what they were looking at, they got about twice as fast depending upon the type of query, whether it was a mixed conjunction, disjunction or phrase, although disjunction was not quite as fast.

5
00:01:17.000 --> 00:01:20.000
Now how did they improve this count performance?

6
00:01:20.000 --> 00:02:21.000
The first optimization they did is segment partitioning, because what they realized when doing profiling is that query visibility checking accounted for 80% of the page accesses. So basically how can we reduce these visibility checks? And with their LSM tree implementation, data gets partitioned into immutable segments that are merged together at a particular size threshold and that this merging is relatively naive. It's oblivious to the data contained within a segment, so each segment is basically random data. But with their new implementation they're actually splitting up segments based upon the values of the columns, so there's some correlation between them and they are partitioning merging strategy based upon the spooky LSM compaction paper and they said they can partition by ctid, which gives each segment a range of Postgres page numbers, so they can check that range against the visibility map up front.

7
00:02:21.000 --> 00:02:47.000
And this enhancement brought visibility related page accesses down from 1382 to just 30. So that's definitely an improvement. The next thing they did is they inverted their doc ID map. This allows them to do more efficient visibility checks when they need to do that. So basically it's a compressed column that maps Postgres page numbers to the first doc ID found on that page.

8
00:02:47.000 --> 00:03:46.000
And by doing this it reduced buffer accesses from over 7,500 to just under 400, which is a 95% reduction. And then the third thing they did is used bitmaps for common terms so for the indexes posting list it uses BP 128 encoding, and that's fine for most terms, but very common terms had super dense posting lists and that intersecting and unioning large B128 arrays incurred significant overhead and they were able to get efficiencies by representing them as bitmaps instead. And that basically halved the P99 latency in the Wikipedia benchmark count. And what they said in their comparison to 10 is that 10 is optimized for top K by BM25 and for counts, whereas PARDB is trying to be an Elasticsearch alternative where you're combining sorts and scoring along with the term that you're searching for.

9
00:03:46.000 --> 00:04:09.000
So in this example, looking at a count of hacker news items for the term database with a score greater than 10, when doing a filtered count like this, parade db was 10 times faster than 10. And then doing a filtered top k by BM25 relevance where you're adding in the ordering PreyDB was 11 times faster than 10.

10
00:04:09.000 --> 00:05:05.000
And then looking at the top case search again ordered by time was 21.7 times faster than 10. Now this also depends on what version is being used, because you'll see in the next blog post that PlanetScale has actually upped their game as well. But these are the results with the versions that they're using right now, and they're making the argument that when you're combining filtering and ordering that in These use cases paradedb might be superior because everything's in one index, whereas the 10 solution relies on B tree indexes for filtering and perhaps sorting. And then 10 just takes care of the text queries. And they show an example of that here where you're just creating one index for Parade DB, whereas you have to apply your 10 index and then two other indexes to be able to order by time or do scoring.

11
00:05:05.000 --> 00:05:33.000
But if you want to learn more, definitely check this out. Next blog post 10.1.0.62 to 3 times faster with support for stemming. This is from Planetscale.com so basically they've added some stemming support to basically be able to search by root words. But they also mentioned one competitor, optimized their full text index and identified one read only use case where their index was briefly faster than 10.

12
00:05:33.000 --> 00:07:08.000
So they got to work and the result is 1.0.4 released last week and version 1.0.6 which is releasing they say next week, which should be live already because this was written on October 8. So they talk about the stemming and I don't know if it's just ironic, but they put Competition is good, which was my title from last week's episode, and they discussed the benchmarks with paradedB and what they do say here is that 10 remain faster at counting queries, which paradedB just released an improvement to their account queries, and dramatically faster at any workload that involves concurrent writes. So that may be where the 10 solution has advantages on writes, but maybe parade DB's is the fact you only have one index and when you're combining terms, maybe it will be faster that way. We'll just have to see how this sorts itself out. But they did another set of benchmarks and based upon their improvements they're now showing themselves four times faster on conjunction queries. Again, not quite sure which PARADB version this was, it was probably the one that had the recent enhancements, although not the one I just spoke about in the previous blog post. Again, about 4 times faster, maybe 3 times faster with disjunction queries phrase queries. Again about maybe 4 times faster, 4 to 5 times faster and then looking at top 10 ranked about 4 times faster as well. But anyway, if you would like to learn more, definitely encourage you to check out both these blog posts.

13
00:07:08.000 --> 00:07:30.000
Next piece of content announcing PGvector search scalable open source vector search in PostgreSQL this is from tigerdata.com and they have released an open source extension that makes vector search dramatically faster and more cost efficient because it's using a new index type called Prism, not HNSW.

14
00:07:30.000 --> 00:07:50.000
And in terms of performance at 100 million vectors, Prism achieved 6 millisecond query latency on a machine with 4 times less memory than an HNSW index. And the index builds are also 20 times faster. So PRISM is an acronym for Partitioned Routing Index for Similarity Matching.

15
00:07:50.000 --> 00:08:18.000
And I should mention this particular project is an alpha. But you can get the open source library and start using it if you're interested. So Prism uses a partition based approach to vector search. So quote Instead of connecting every vector to its neighbors in a graph, Prism groups similar vectors into partition and stores them as a list in a compressed format that is densely packed into database pages. So the index will be smaller without the graph connections.

16
00:08:18.000 --> 00:08:45.000
And usually it can do sequential data reads as opposed to jumping different locations on disk if you're doing a graph search. So if we take a look at the chart here at a million vectors, they looked at a 2 gigabit memory machine, the index didn't even build in HNSW and it built in three minutes on Prism on a four gigabyte machine it did build, but it took 21 hours compared to three minutes for Prism. That's insane.

17
00:08:45.000 --> 00:09:10.000
And if you look at the hundred million vectors, a 120 gigabyte memory instance, HNSW won't even build. It takes an hour and 40 minutes to build on Prism and you need a half a terabyte of memory in order to get the HNSW index to build it takes 42 hours versus 48 minutes for Prism, again, which is insane. And then these are the query results.

18
00:09:10.000 --> 00:09:13.000
And this is for 100 million vectors.

19
00:09:13.000 --> 00:09:43.000
And at 95% recall, it's 4 milliseconds for PRISM and 8 milliseconds for HNSW. So a little bit faster. But if you use Prism on a smaller machine at a quarter of the cost, you can get it in 6 milliseconds. So again, that's pretty amazing. And they said here most teams don't have 100 million vectors. So basically they tested a million vectors and they could get excellent performance on the order of 15 milliseconds or less on two gigabyte memory machines.

20
00:09:43.000 --> 00:09:57.000
Now what's interesting is that Tiger Data also released PGvector scale in 2024 that used a graph based algorithm inspired by Microsoft Disk Approximate nearest neighbor research.

21
00:09:57.000 --> 00:10:53.000
But as they started putting this in the wild and using it, they said they encountered a troubling problem. Portions of the graph would become unreachable, causing recall to degrade and they couldn't find a good solution for it. So that's when they decided to pivot to this PGvector search. And they say quote, you know, scanning partitions, which is what PGvector search is based on. Beats walking a graph on modern CPUs. So graph search hops from node to node looking for things and that just doesn't use the CPUs prefetch. Whereas the partitioned index splits the vector space into clusters with k means and each cluster source compressed vector codes and row IDs back to back. So it's just much easier for CPU and mid loops to be able to pull back the data. Now again, this is in the alpha state, so they list what works today.

22
00:10:53.000 --> 00:11:03.000
So delete operations work, but they don't reuse space yet. Inserts and updates work, but there's heavy churn that can cause recall drift without manual intervention.

23
00:11:03.000 --> 00:11:08.000
But you can definitely try it and see how it works for you if you're interested.

24
00:11:08.000 --> 00:11:26.000
Next piece of content Scaling Citus without limits Announcing query routers in Stack Res this is from stackrez IO and stackgrs is a kubernetes solution to operate Postgres on. And they already work with Citus.

25
00:11:26.000 --> 00:11:40.000
But apparently they have seen limitations with only having a single coordinator node, because Citus has all queries that go through a single coordinator node and then it gets distributed to worker nodes that have the sharded data.

26
00:11:40.000 --> 00:12:56.000
So basically this is scale out solution. But the disadvantage is you only have one coordinator node. So what they decided to do is introduce a service in front of it as well as a pool of query routers that can then talk to the workers. Because they developed an enhancement in Citus where not everything has to go through a coordinator, you can speak directly to a worker. So what they decided to do is develop a query router solution and what happens is your clients or your app goes through a service that can spread load across the query routers and then each query router can route to the particular worker it needs to to get the sharded data. And essentially the coordinator node no longer has to do query traffic necessarily. Now they discussed the implementation here and how easy it is to add to the statres operator. You just add how many query router clusters you want here and then they showed the benchmarks. So when you have a coordinator alone at 400 clients, you max out at about half a million transactions per second. But with two query routers, essentially you double your throughput to over a million transactions per second.

27
00:12:56.000 --> 00:14:36.000
And if you add three query routers, it looks like the client connections hit a bottleneck. And if you add up to 600 clients connecting now you have 1.4 million transactions per second. So it's not linearly scalable, but it's definitely getting pretty close. And then they show the latencies as well here, and then they show the CPU utilization here. When you only have a single coordinator row at that half a million transactions per Second, it's at 91%. When you have two query routers, the coordinator node is basically at 0% CPU, but each query router is at 90% and your workers are about 54% busy. Whereas if you had a third query router now all the three query routers are almost at 90% CPU. The workers themselves are at 81% CPU coordinator is still at 0. So it basically allows you to get close to linearly scale your Citus deployment. So definitely check this out if you want to learn more. Next piece of content. PostgreSQL 19 release candidate 1 is released. Now they are planning a second release candidate on November 12th and then the planned general availability of Postgres 19 is November 19th, so definitely looking forward to that. There are just a few changes fixes from beta 4 so that looks good. Go ahead and check this out if you want to learn more. Next piece of content 0 downtime Postgres upgrades with Logical Replication this is from Tap o u e h.org and this is his part three series about Postgres logical replication use cases.

28
00:14:36.000 --> 00:15:09.000
This one is of course doing zero downtime upgrades with Postgres which this is the only type of upgrade I like to do now is using logical replication to do the upgrades. I really don't use PG upgrade anymore, but if you want to have all sorts of detail about how to do it, definitely check out this blog post. Next Piece of Content PG Friday against the Wall this is from pgedge.com and they're talking all about PGWall what it is, what the purpose of the wall is and it's important. So if you want to learn more about that you can definitely check this out.

29
00:15:09.000 --> 00:15:59.000
Next Piece of Content How Table Sample Picks Rows this is from Boring SQL.com if you want to learn more about how Table Sample takes samples of rows from tables to give you an insight into the data that a table stores, you can definitely check this out and the last piece of content. Apologies for delaying Postgres 19 this is from percona.community and he says outside committers I'm the only contributor responsible for causing a last minute revert of a major feature and he like others said it's not necessarily AI causing this but it's basically the vulnpucalypse. So all the vulnerabilities that have happened and you can look at this chart across the years and how dramatically higher the number of security fixes were done in 2026 compared to the years prior.

30
00:15:59.000 --> 00:16:21.000
So if you want to learn more, definitely check out this blog post. I hope you enjoyed this episode. Be sure to check out scalingpostgres.com where you can find links to all the content mentioned 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.