Full Text Search, the Next Generation

This talk covers full text search in PostgreSQL, with a focus on its challenges and advancements. The speaker, Kristoff Pettis, introduces key concepts such as documents, corpora, and the importance of tokenization in search. He discusses various full text search systems available in PostgreSQL, including the built-in TS query engine and advanced methods such as BM25 and PG Vector for semantic searching. The speaker emphasizes the benefits and limitations of each method, particularly the speed, relevance, and the need for proper indexing. He also highlights the significance of hybrid searching, combining lexical and semantic approaches to enhance search accuracy and performance.

My name is Kristoff Pettis and we're going to talk about full text search in Postgress. Postgress should have been on the slide. Well, I'm used to speaking at Postgress conferences where I don't have to say that. Um, I'm the CEO and lead consultant at PGX Incorporated. We're a uh consult a pure consultancy. We're based up in Alama, California. And I've been doing post I keep I really

don't have a good memory of when I first started using Postgress but I know it was Postgress without the Q I I'm one of the people who actually learned how to write queries in Quell and immediately had to throw that knowledge away because it added SQL. So I'm going to take a moment if anyone wants to take a picture of this that's the presentation is there as

a PDF and I apologize that some of these slides are a little dense and there's an old folk there's an old folk song called little teeny eyes which goes little teeny eyes for reading little teeny print and some of these are like that so um follow along if you like so what's the problem we have full text search See, problem solved. It searches all the text, right?

Well, not quite. So, it only finds documents that contain exactly that string. Well, except for capitalization. And it return So, it returns a lot of documents you just didn't want that you know were irrelevant to what you were looking for. And there's no way of sorting by relevance because they're all just as relevant. And there are a lot of possible ways of querying a big blob of

text that it doesn't do like proximity, like fuzzy searching, like semantic. And I hope you like sequential scans because there's no way for an index to speed that up. Okay, so we're going to define some terms just so that we're all talking about the same thing. Um, a document in this lingo is sort of the is the unit of search. It's you can it's I'm going to

say it's kind of it's the row. It's the singleton that gets returned, you know, or um or even a a particular cell. Um it can be it can be any blob. It can be text. It can be word processor documents, PDFs, JSON, XML, anything that that the part that it can that the code can take apart and get get at the text. Um corpus is basically a

big collection of documents. Traditionally they're or they're um or um organized around a single you know have some kind of thematic thing like they're a corpus of legal documents or something like that but this is a useful thing because it gives you an idea of kind of everything that could be searched and um so words everyone knows what a word is right I can't pronounce that I'm

trying I'm learning but I can't pronounce that. Yeah, that that that word means you're wanting to make fun of us. So, it's a noun phrase like in your wanting to make fun of us makes us unhappy, you know, that kind of thing. And it's a single word uh linguistically because it's built up of like a zillion aixes. Um and it's actually local kind of local to this

area. Um that's that's from what um chum chumashian is the um English term for it. And it's a kind of like call polyynthetic and they have some special challenges there. But okay fine, you know what is a word? Um so the reason words are important is that we need to for advanced searching we need to break the document up into its component words. And it's very language

and domain specific like okay up to date is that one or two words one word raise your hand. two words. No one's willing to and okay, no one's going out on a limb here. Honestly, there's no there's you can't fail this test. Um, but so people different people have different intuitions over whether that's a single word or not. And different full text search um approach this differently.

Sometimes you'll see the word lexim float along because somebody doesn't like that word. Um, so we're going to call, but what we're going to call for this purpose is something with whites space on both sides and at least some non- symbolic characters. That is to say alphabetical or p, you know, picto something that's not a punctuation mark or a math symbol or something like that. And don't

at me about that definition. It'll work for our purposes. Okay. So, we need the problem is word words vary a lot. Um, like rain and raining are different words, right? So, but if we're searching for hard rain, we'd probably like the documents to say that with raining hard in them. Um, and so there's a piece of software called the tokenizer um which is usually usually a pipeline

of different parts and it turns um words into somewhat normalized tokens. It like it removes the aixes to create a base token like in this case it would remove the ing from raining. If it's a plural, it'll remove the plural and um and it comes up with a what what is often called a stem for it. The this will also usually reject very common words like 'the'

in English or ah so that the um because those can swamp the um the the indexes or whatever it's building and make the search more difficult. Okay. So terminology done. What is a full text search system? There's no real like super great definition, but for the for these purposes, we're going to say it can take relatively free form queries and it returns a set of documents that

match those queries against specified rules. Um, it can rank how good a match it is so that better matches come out at the top and it can handle lexical variations on words like rain and raining. And it has it has some indexibility so you don't have to scan the entire corpus every time you do a query. So what are some examples? Well, we're going to talk about

three. Um, first is the post Postgress in incore full text search system. Postgress terminology that's quote full text search unquote which is not very useful in a talk about full text search different full text search edges. So we're going to call it the TS query engine. Um, postgriskll implementations of the BM25 algorithm. Uh, the one we're going to be using as an example of that is PG

search. Um and there are actually a bunch of PG searches. This is the one from parade DB and using embeddings and vectors and with um you with the PG uh vector extension to do that kind of searching and we'll call that PG vector. There is another one which is PG um tur which is for triagrams. It's it's marginal whether this is full text search or not. It's

kind of an intermediate thing between I like and full text search. um it takes text and mo and um creates triagrams, threeletter combinations. What it's really good for is you have a drop-own list that has that is searching over, you know, 25,000 items and you want it to be responsive to each keystroke. It's really good for that kind of stuff, but I wouldn't call it a full

text search system, but I wanted to make sure no one understood why we were passing it. Everyone understood why we're passing it over. Okay, so we're going to talk about TSQuery. Now, this is not the next generation of search. However, it it's available in every single Postgress installations that could possibly be running today or should be. Um, and it's really good as sort of a balance a

baseline for what the terminology means. Um, it used to be contrib module now got moved into core. It's a first class feature. People are still maintaining it. You know, if it breaks, someone will fix it. And it uses the same concept as all the engines. So, you know, we'll start here. So at a very high level what it does is it takes the document you know which

is usually a text field you know literally txt cx if it's type and it parses it and tokenizes it into a new type called ts vector. This is a it's actually not that opaque but it's not intended for human consumption. Um it's it's a specialized uh it's a specialized um array to use for searching. Um it's basically a list of tokens. They're positioned in the original document.

um based um based on order and the same it applies the same process to the query string. So now it has some tokens and some tokens and the algorithm goes through and says, "Huh, how many of these in what, you know, what order or whatever appear in it and generates a ranking, a rating or a score, a score." Uh, this is one of these little teeny eyes

things. Um, this is example of a sentence and we feed it into the 2TS vector um, function with saying it's an we're using the English configuration. Um and there it comes up with you know some offsets and weights and things like that. And so documents are searched using the TS query type. Um ultimately before it can do a lookup it you have to give it a TS

query which is not just a text string. Um and it's it's represented as a series of tokens with possible operators. Um so you can build one if you like if you like the internal language you can build one directly. uh if you just want to dump in a text string and say please look for something like this there's a function called plain um plain to tsquery and

there's a a semi-nuance postcris 11 I think um that you can use what has become the google syntax you know quotes to make it to see find this exact phrase that kind of thing and then it'll generate a tsquery out of that so here's an example um which is not very which is only semi-interesting um you pass in walked to end formality um and walk in formal

that should be plain tsquare I think but anyway you get the idea it stems it you know pulls off the ity and the ed and that's an and the amperand means what the amperand means in everywhere in computing which is it you're looking for documents that have both of them but anywhere no there doesn't have to be any spatial relationship between them um and there are a

bunch of operators match documents that contain them both of them um one that contain them as a phrase you know the walk formal which um they um or operations and you can use parenthesis to build expressions pretty cool um and you know that's there's an example of how it looks so you turn the document into a TS in in this query the first thing we do is

turn the document into a TS vector then we turn the um the string that we want to use to a TS vector and we use the double um the double at sign operator which is contains and it will return true if that query matches that document and it's a binary operation. All you get back from it is yes. Yes, it matches. No, it doesn't. And it does

not evaluate how good a match it is. And you know what good means is kind of a nebulous term. But um Postgress has an answer for this which is a function called tsc rank. Um here's an example. It takes the vector and the query just like the double amperand did and it produces a floatingoint number that say that is in that has no units but it's it

is roughly it is how good a match it is. So it's unfortunate that it uses the term rank because rank really generally means the ordinal position in a list you know one two three four. So what everything else in the entire world calls it is a score. So we'll call it a score. And um that's so it looks like that and one is you pass me the

same string. What's about that? And zero is there is no correlation between these whatsoever. So here's an example. We select everything from the corpus, you know, every field from the corpus um ordered by the descending score. you know higher is better and only the things that match that particular um uh that particular thing in the wear clause I'm using document that's actually not technically true but I

haven't introduced the other concept that we have to put there um so it it goes through each document converts it to a TS vector matches the the TS query and if it matches converts the document to a TS vector again and ranks it against the TS query unless the optimizer happens to notice um um that it and optimize that and it doesn't always do that. That sounds

pretty bad, doesn't it? And it does a sequential scan. So, it does that with every row. That doesn't sound good at all. So, fortunately, we have indexes. Um the the Jin index type was introduced specifically to um support full text search, although it's good for other stuff, too. It also tr um um uh kicked off a whole series of of index names like rum and vodka. um

it indexes TS vectors not documents. So it does you need to process it in the document into a TS vector before the G a gen index can work on it. So you know how do we get how would we do that? Well generally this mean you know the the traditional way was you had a a separate column of typet ts vector it's a first class type you

know you can have columns of it um and you maintain it with a trigger. So every time the the the document updates it processes and updates the um excuse me it processes it and updates that column. Um you can create an expression index directly on the um 2TS vector and effectively at that point you're um you're you're storing the TS vector but you're storing it in the

index. The downside of this one is you're counting on the optimizer noticing that that's the same as the one you're using in a query. Sometimes it does, sometimes it doesn't. And when it doesn't, that can be an a nasty surprise. The query will work just in a lot of take a lot of time. Now, in version 18, you have another kind of fun option. Um, you can

create the you can create the um column TS vector. Um, and it's a generated virtual column. So it only recalculates the uh it only calculates the the 2TS vector when it has to um that can be expensive because it is going to be recalculating on every query. It doesn't cache it anywhere. So and then um so but then you can create an index on that and that

does cache it. So and this is if you're on version 18 or higher this is probably the way to go. And so you do do this and you and you get this error message and you say Kristoff it doesn't work. And the reason is you forgot to put the English there because without the um show of hands difference between a stable and immutable function in Postgress. Okay,

quick explanation. Immutable is a pure function. It only depends on its input and Postgress takes that really seriously. For example, anything that accepts a um time stamp is not going to is going to be not going to be immutable because the underlying time zone file may change, right? And because and you can only build an index off of an immutable function because if something changes about it,

that corrupts the index. Stable means the qu the the function will return the same value for the lifetime of the query. So that's a much um looser thing. So TS vector without English without the quote English is is stable not immutable because someone could swap in a new config and corrupt the index. So there's that. Um so you so always put in an explicit configuration you know

of what the the the configurations contain a lot of stuff but they roughly match to the general rules for that particular language. So gen indexes um they're pretty slow to build although there are things that are slower and they're really slow to update. Um in fact they're so slow there's an optimization which is when you update a a um gin index field it doesn't immediately run off

and and update the gin index. It builds a thing called the posting list and puts all the changes there and queries consult the posting list as well as the actual index. Um it and if you have a very high up rate table and update rate table and you're changing that field a lot, this can be a really big bottleneck. Um you have some dials to turn. You

can um set how big the pending list can get. So you so if you lower it, it's going to flush the pending list down to down into the index more often. And you can just turn it off. Uh so um updates will be slower, but they will be predictable now if you So that's that's TS vector. Postgress is built in a cert there's some there are some

advantages. It's straight out of the box. You don't have to install um an extension. You don't have to do anything. Um the everything that it uses is a is a mainstream post postgris data structure. If it breaks, someone will almost certainly fix it. Um it does have an ability which I don't talk about much here, but you can index multiple parts of the document with different weights.

So for example, when you're doing a search, the title counts more than the abstract, which counts more than the body. And that's a very useful feature. So, but not the fastest. And there's no way around the similarity ranking that has to require requires an entire sort of the entire result set. That's not great. Um, jin index updating, you know, it's an issue. Um, you don't have a

huge variety of search operators. you you have some you know they're reasonable but they're not great they're not enormous. Um if you're using this again on user user input and that user input for some reason could be hostile. There is a thing called stuffing where you just paste one word into a million times into a document and it will always push itself to the head of any

query that uses that word. Um and the TS vector implementation has no guard against that. It just does it. and you might have to add a column to TS vector which you know you pay for space and all that. so this was the situation for a long time and then suddenly there was this huge burst of activity. I don't know everyone at the same time woke up

and decided I'm going to do new full text search things for Postgress. It's kind of odd. Um so we're going to talk about uh the next one is an algorithm called BM25. It's actually quite old. The first papers about it were written in the 70s. Um, so it's an algorithm, not a specific implementation. Just to be clear, it's a piece of paper that says here that here's

the math. Um, it is the underlying algorithm for lucine, which is the underlying tech um module for elastic search, which I'm sure most people have heard of. So it's it's a it works. It's a very good algorithm. Um, and suddenly everybody decided to run off and build Postgress extensions. I guess everyone find, you know, it took them eight, you know, 12 years to figure out how to

write an index and um which is and I'm impressed they got it that fast. Um so there were two that kind of are pushing their head. One is uh PG_TX search which is from time scale and there's PG search from parade DB. Parade DB is a Postgress fork that's intended to as a that you don't have to use elastic search. It's fine for that kind of stuff.

Um, but PG search is a is packaged as a separate extension if you want to use it. Um, pg text search has big red letters saying do not use this in production yet. So, I kind of didn't um use that as uh as an example. Um, there's a library called Tantivi which is actually um is um used by a lot of different search things and PG search

is basically a very fancy interface to that library. That's fine. And so we're going to use PG search without parade DB as the example for a BM25 algorithm. And just to be clear, a lot of stuff in here is going to be specific to PG search. Um, PGE search works differently. Every implementation you see will have different knobs and dials on the front. Um, so here are

the installation instructions. Shared pre-ro library. it needs to go in shared uh preload libraries and you have to create the extension in the um um um in your database. Now everyone who who's on RDS running a postcrist install on RDS, I have some bad news for you. PG search is not on the list. So you you need to send a a a support thing to email threatening

to move to GCP if you they don't install So the one nice thing about this is you don't have to mess with this TS vector type. It just you you pass in the raw document and it does its thing and it builds an index and that's f and it's done. In fact, there's no way of using this of using it successfully without having an index which is

so that's fine. Um so you create the index. It has a few options it requires um like you have to pass in which is the primary key. I don't know why it in doesn't inspect the table for it, but whatever. Um, it can be a covering index, which is handy. You can put fields in it that you don't search on, so it can return those directly without

having to go out and rumage around for them. So, that's and that's a nice performance optimization. Um, you can you can configure unlike post in um TS vector there's this thing called a configuration which has a name like English and it is a bundle of everything. It's how the stemer works. It's the it's what the stop words are. It's everything in a single package. Um BM25 and

or at least Parade DB's implementation of it lets you spec um specify uh break it down into individual characteristics like that. So you can say okay I don't you know I don't want you to do stop words. I'm doing a research project where I care about the um and do you do you want the um diioritic stripped off? Stuff like that. So um here's querying pretty normal

you uh the the triple bar operator is or. So this is um looking for things that have either um something like walked or something like formality for it. It does the stemming under the hood for you. And you can you can add this function function call which looks kind of like the one that you that we did for TS vector but unlike that it can use the

index. So and that's where the real speed went comes in. So you add this function call get a you know here it's renamed to score do the um uh and do a sort on it so it gets the top 10. By the way I do have performance numbers um at some point in this one. So it works really well. Um, it's significantly faster than TS vector. Um,

and ranking is directly supported by the index, so you don't have to do that big horrible sort. That's great. Has lots more config options. You don't have to have a separate type uh a separate vector type, which is good. Um, BM25 is intrinsically more resistant to stuffing. It's not perfect, but it but it is definitely better. And it can do faceting. Um when whenever you've gone to

a uh I don't know who's ever bought a camera from B&H Photo or somebody like that, it'll say I want digital cameras. Okay. I want 35 millimeter format. I want this. I want this. That's faceting is searching within searches. Um BM25 and or Tantivity really and um has support for that. So that's which is very nice. Um so the bad news is it's not a common extension.

I don't believe it is currently installed on any cloud platform except Parade DB's own. So you're you're kind of stuck running your own Postgress install or Parade DB which is clearly what they want because it's a commercial product. Um so and the data structures are open source. Um you can go and look at PG search's code. There's nothing magic about it, but you're probably going to have

to figure out bugs yourself or file a bug and hope it gets fixed. um because these are not community things you know they're not and they're not very common data structures out in the world of okay now we come to the new hotness PG vector you cannot do a I now when I Google postgress skill for any reason I get like the first three are all about

PG vector that's how hot this is um so super quick introduction to um semantic searching so the first thing you have to do to do semantic searching which is what PG search does is you have to train a model um you take a very large corpus much bigger than anyone would have in have in their garage and it produces a set of embeddings um they're basic embeddings

are vectors which are basically long lists of numbers um what these what these things are is like you know a two two um two numbers is a point in space and three number point is a point on a plane and three numbers is a point in space. Well, these are then you go to 384 dimensions and that's why I yeah I can't visualize that either. Um this

requires a ton of compute power and it's not something you do at home usually. Um well maybe with stable diffusion but nothing you know it's it's usually you go out and get a model that someone else has trained already. Um you download it. Um when this is happening it lives in your application memory. It doesn't live in the database. So, and you use that model to turn

your search into the same type of vector. So, you pass in the thing you're searching on and you get another embedding out of it. And then the search, what the search does is, if you can imagine points in space, say, well, okay, this one's here, that one's closer, that one's farther away. It does basically that only in 384 So, and the the the closer it gets to

the query, the more semantically similar it is. That is to say, the more kind of the it that when it built the model, it saw those concepts close together a lot. That sounds really weird and spa, you know, did does this really work? And it really does work very well. Um, this is how every chat, you know, chat GPT cla works. Um, the choice of model is

really important. it kind of has to be sympathetic to your corpus. So, for example, if you're building a legal database, go out and get a model that was trained on legal documents. If you're building a medical database, you know, etc. Um, if you ever wondered what that what people talk about, huggingface.co, that's what it's all about is a place to go and look for models. So, I

don't know why they picked the hugging face emoji, but their site. Um, and you they're both open source and commercial models. There's lots of open source models or if you need a hyper specialized one you can go and buy one. So the first thing you do is you have to go through and create embeddings for every document which kind which reflect kind of what where it is

in the grand scheme of things. This is and you have to do this unlike training a model. You have to do this because it's they're your documents. It's really slow and it requires a lot of um compute power. you really want a GPU for this. Um, and you have to store it as a separate column. They're not very big, fortunately. You know, they're 384 floatingoint numbers. Um,

but you do you do have to put it in your database and pay the storage. And you so you create embedding for each document. And some bad news, you can't do it exclusively inside of PostgreSQL. You have to write an application to do it. And because it uses a ton of Python libraries like pretty much everything in AI does. And of course the first thing somebody's going

to ask is what you can use plython, right? Well, the tooling that when you you do a pip or UV, you know, pip installer UV add to the base to the base package, it pulls in a lot of other packages. Um that and that's not going to run inside of PL Python. No way. So, I don't know how many there are, but it has it has things

like, you know, it has all the the GPU drivers and all this stuff. And it has to talk to the GPU, which I would not be excited about doing from within the database. Okay? And so now you have the now you have this big old list of of everything and everything's stored. So, what do you do? Well, you take your query and you turn it into that

into an embedding as well because it's going to look for similarities and it has to create um so you have to create an index over the embeddings in the document. Oh, by the way, creating creating an embedding requires application code. There's no current way of doing this successfully in the database. So, you know, which is a thing. And if you're not a Python shop, bad news. Um,

so you you you the model. There there are a bunch of index types of PG ve Now now we're back in PG vector land. Um and there's a there are several different index there's two index types and sort of several different in um index ways of building. Um I'm not going to go into full detail about what they are. They're they're the algorithm it uses to calculate

the distance. Every you um each model used an algorithm to do it and you just pick the one that matches your model and don't worry about it. Um there are two index types you have it which are um HNSW and IV uh IV flat with two periods. Um you usually want um HNSW because it is it has a lot of very good characteristics but it requires more

memory to build. So on a tightly constrained system that could be an issue and that's the that's the syntax. So you say, "Okay, I want an H NSW um index using the and the the the card on my model said it was built using the using cosiness, so I'm going to use cosiness." Here's a little p piece of Python to that what it looked like for my

test structure to build it. It's not really complicated. It's sort of like fetch it 4k rows at a time, drop those um drop those into the model. code which comes back with 4K embeddings. Um, interestingly enough, it the the database was slower than the than that calculation. So, um, then I use copy because copy is nice and efficient to append it to a table. And after a

while, it's done. Now, you notice I'm appending to an existing table rather than updating a table. That's because I tried it with update first and we it would and I did started this in back in January. It would still be running. So the updates are really expensive especially on this kind of stuff. I think it's because it just crosses the threshold into using the toast table. So

um so what I did is instead I c I did a whole new the the embedding creation script copied the the copied everything um from one table to another added the embedding and then I dropped the old table and renamed the new one and that worked much faster. um there's two so there's two kinds of search here. Um TS vector and BM25 return matches based on lexical

matching. It's the letters, you know, it's it's it doesn't it doesn't know that what rain means. It just knows it's, you know, ra the ask letters ra in a row. PG vector does what's called semantic searching. Now, it does not actually understand anything. It's a computer. you know, when I in the Terminator movie where he says, "I'm a machine." You know, it's like it um but it

gets it what it's doing is saying, "Well, everybody else that I read said these two ideas were close together, so they so I think they're close together as to a first approximation." Um, for example, I I was trying to find a phrase that would not appear in the corpus to test to test the null and I so I searched on exploding seafood and it came back with

a Yelp review saying well um where the they were complaining that their seafood entree was vaporized because clearly it thought vaporized and exploded were similar terms which is not unreasonable. Okay, so the good and the bad about semantic searching, it's very powerful and it can deliver some really great results. It's really fast um as you'll see in a bit. And the good news is if you're on

AWS, you've got PG Vector. PG Vector is very hot and it's supported on all the major cloud platforms. And similar and similarity ranking is inherent to the search. You don't have to do anything special to get it. But it requires this external tooling. It requires an embedding column. Um, semantic search can generate false positives. We're we're I'm using the term false positive positive rather than more the

term that everyone else uses for it, which is a hallucination because that's what a hallucination is is when it thinks things are approximate but they really aren't, you know, to us the humans. Um, building those initial embedding if you if you start out with a giant database of documents, which most people have already, it's going to take a long time to build those first round of embeddings.

So just so I would do them on a separate machine with a big old honk and GPU and there are some cloud hosting platforms I think the big three all have it but that um are slow to catch up in which case all you can do is bug them to put it in. Okay I so I was tossing around things like it's faster it's this or this.

So so let's look at some numbers. Um so I ran tests on all three algorithms. It's on a a virtual machine that I got from Lenode. um 32 gigabytes of main memory, eight processors. It does not have a GPU or if it does, it's not worth useful for this. And the test database was a million random Yelp reviews, which Yelp provides their entire review database as a

downloadable file. It is pretty cool. And I picked um uh four words, which one would be common, one would be kind of like moderately common, one that would appear in the corpus but was rare, and a word I just made up that would not appear in the corpus at all. And I did top 10 ranking. So TS vector first. It took a minute and 48 seconds to

create those TS vectors. That's not too bad, you know, especially for a million. And the gen index took 24 seconds. So maybe it's not that hard to build after all. Um the it was the relation was 1.8 gigabytes and the index by itself though is only.13 um 135 gigabytes which is kind of interesting. Um this kind of kind of belies this well gin indexes are really expensive

and horrible. So the query time was um for for missing rare and moderate they were all basically you know within within a very small standard dev fraction of a standard deviation of each other and common was horrible. And the reason for that is that's where that sortation step kills you because a lot of entries came back and it had to do it had to assign a rank

to all them and then do a I think it did a a four-way parallel sort to do it. So that's that was um interesting. Um so PG search it took 13 seconds to build the index. That's you know not that's nothing. Um the the index and thus the relation were smaller. Um the relation was smaller for two reasons. Um this is total relation size which includes the

indexes. Um both because the index itself was smaller and we didn't have to store any TS vector column or anything to get it to work. And the query times were all basically the same and they were really good um regardless of the um regardless of the frequency of the word. So um I would say for things that are not particularly for moderately or less common in the

database um uh TS vector and um PG search basically the same and on common words no comparison. PG search is much Um you can also do hybrid searching. Um let's see I think I got my slides out of No. Huh. And I have to apologize because somehow um so I'm going to do this from memory. The um so the the and I apologize for this. The PG

vector results every search the um the time to build the initial embeddings was about three and a half hours. So it's significant. Um the the time to build the index itself was dimminimous. It was about a minute. All of the searches were, excuse me, um were 10 milliseconds or less though. It was really fast. And the similarities were okay. The only one I thought was that I

got was weird was the exploding seafood one. Also, when I passed in that word sparastic, sparastic, which is made up, it decided I meant spas and started giving me all these these Yelp reviews of spas. So be aware that some that that sometimes it'll generate these things and they don't have anything else to do with each other, which is why we now have hybrid searching. That was

a good save. So um they use both. Uh hybrid search is you use both. Um because lexical is great for exact matches and semantic search is good for paraphrase and they kind of have each other. They kind of balance out each other's weaknesses. like semantic search will return false positives and lexical can miss if the um if it's not exact or you're looking for a fuzzy thing

you know like rain versus not just rain versus raining but like um one of the projects I've worked on is a legal database that use this and murder and homicide you know as far as lexical search is concerned they have nothing to do with each other but semantic searching obvious relation the problem is how do you do ranking on this because it's coming back with two different

sets of numbers And they both produce these numbers but they have absolutely no relation to each other. You know you can't say this was in in PG search this was a point8 in semantic search it was a 0.5 in lexical search five therefore the semantic one should rate higher. That's not how they work. Um fortunately there's this thing called the reciprocal rank fusion algorithm which sounds like

the dumbest hack in the entire world but amazingly it works. um which is you rank both on both kinds of search. You take a constant they 60 60 seems to work. You can already tell this is be a little fuzzy and you take the reciprocal and you sum it across the all um all the engines and that's the new score to rank on. Um so you could

here's an example. So if it, you know, if it um if you're it's the eighth place on PG search and the 12th on PG vector, we do we um do the division, add it up, and it's so we have a new score of of 0.2 um 86. So it's um there is not a lot of like mathematical proof behind why this works, but it seems to work

very well. And here's a big nasty query that does does hybrid searching. Um it the top one does does the search on TS vector. The bottom one does the search on um on using PG search on embeddings and it calculates them in the calculates the RRF score sorts on it and it works. So it's and you can you can throw this on the um just throw this

at the uh the excuse me um and it and it'll run and it doesn't take that long. you know, it's it's not worse than the sum of the components. Okay, almost done. Um, so what do I recommend? If really you don't you're, you know, this is not a super duper full text search problem. It's like, you know, cat a catalog on an e-commer a small e-commerce site

or things like that, just use TZ Vector. you don't have to worry about um about the build time or whether or not your extensions installed or all that stuff. Um and if you can't install um extensions and PG Vector isn't around, you're stuck with TS Vector. So now there you are. If you can install new um new extensions and you need and you need something sophisticated or

faster like um PG search is definitely a superior thing. You do have to weigh that against the fact it is it is an open source project but it's open source project with basic with one company behind it and that can mean things. Um, and if you want to do semantic searching and you're okay with the resource requirements to build the embeddings and things like that, um, PG

vector does work great. It's very, it's very powerful. And if you do a bulk embedding operation, do it as a copy instead of an update. So it'll have finish before the heat death of the universe. And and then you can use more than one to do a hybrid search. I would gen I unless you were specifically doing um only an embedding search for you know PG vector

type search for some reason I would always go with a um with a hybrid search because the um the benefit of getting rid of things like false positives is very high and that's the presentation questions.