Postgres Just Killed Elasticsearch

Key Takeaways

If you've ever needed to search for something in a database, you've probably experienced a little bit of pain. Now, if you've used a database like Postgres, you may have used their built-in full-text search and then quickly realized that it didn't quite accomplish the task and switched to something like Elasticsearch, right? An external tool. But now, in the AI era, search is changing and even these external tools like Elasticsearch have a lot of problems. Now, the good news is that in this video, I'm going to show you an insanely good solution that's completely open source that you can start using for free today. Now, what I'm going to show you in this video is something called PG text search and it brings true BM25 relevance ranked keyword search directly into Postgres. Now, what it lets you do fast, high-quality text search without standing up or syncing to an external search system. You can pair it with something like PG vector and get hybrid keyword plus semantic search for your database all inside of Postgres. Now, this sounds confusing, don't worry. I'm going to clear it up and explain exactly what it means and also show you how you can experiment with it today for free courtesy of Tiger Data who actually open sourced this technology and is sponsoring this video. Now, I'm also going to talk about the history of search so you can understand how we're kind of in this new era right now with AI and how searching has actually become extremely important and something that needs to be done a lot differently than it was done in the past. Let's dive in. So, for a long time, search was pretty simple. The first era was human facing search, right? So, searching for products, you know, documentation, blog content, users typed a few keywords and they expected relevant results from a relatively small, mostly static data set. Now, Postgres handled this extremely well with its built-in full-text search and TS vector and also TS query. Now, for thousands or even hundreds of thousands of documents, it was fast, simple, and just good enough. Then, the search workloads changed and we entered a second era where search wasn't just for humans but for systems. Think like logs, metrics, event streaming and we have millions of entries. So, ranking the search results actually mattered less than scale, speed, and aggregation and that's where Elasticsearch search dominated and you've probably heard of it or used it before. Now, it wasn't just about finding documents anymore, it was about filtering, fastening, and querying massive, constantly changing data sets. Now, that workload required a very different architecture. Now, we're in the third era which is the AI native applications era. Search today is increasingly used by LLMs, not just people. We have rag systems, retrieval augmented generation, chatbots, and agents need to search to retrieve the right context and not just get the matching documents. Now, the data isn't streaming like logs, but the result quality is critical. Now, vector search gives semantic understanding, but it's fuzzy, right? And keyword search gives precision, but it's brittle. Now, modern AI systems need both of these and that's the gap that this new generation of Postgres native search tools is trying to fill. Okay. Now, let me talk a little bit about the ranking problem because this is extremely relevant for AI applications and it's going to set the tone for the demo I show you later and why the tool I'm kind of presenting here is worthwhile. So, for AI applications, search quality isn't just a nice to have, it's what directly determines the output quality. In a rag system, the LLM can only reason over the documents that it retrieves. So, if your search returns mediocre matches instead of the best ones, the model produces mediocre answers, no matter how good the underlying LLM is. Now, this is where Postgres native full-text search starts to fall apart, right? Postgres uses something called TS rank, which basically does keyword matching, but it lacks the ranking signals that modern search relies on. It doesn't account for how rare or how meaningful a term is across the entire corpus, so common words like database get the same weight as important terms like pooling. Now, it doesn't prevent keyword repetition from gaming rankings as well. So, long documents that repeat a term over and over again tend to float to the top. And it also doesn't normalize for document length, which unfairly favors longer documents regardless of how relevant they actually are. Now, there's also a brittleness problem. Postgres full-text search relies on Boolean matching, meaning all query terms must be present. If a highly relevant document is missing just one word, it's excluded entirely before the rankings even begin. Now, for human search, that's annoying, but for AI systems, it's very damaging. When the retrieval is weak, the entire pipeline suffers. So, hopefully that gave you a little bit of context about kind of the native built-in full-text search in Postgres. If you're not aware, in the Postgres database, it has this feature, full-text search, which is as I just described. However, there's something called BM25 and this fixes a lot of these problems. Now, that's because modern search engines have solved the problems that we talked about, right? Using BM25, which is now the baseline ranking algorithm across the industry. Now, BM25 improves keyword search in three critical ways. First, inverse document frequency or IDF. This increases the weight of rare, meaningful terms and downplays the common terms. So, pooling matters more than database or the and it, right? And BM25 understands that. Second, term frequency saturation prevents keyword stuffing. So, repeating a term helps to a point, but after that, it adds diminishing returns instead of dominating the rankings. And then third, length normalization ensures shorter, focused documents can compete fairly with longer ones instead of automatically losing due to size. Now, these ideas aren't experimental, they're proven production-grade techniques that power modern search engines. Think Google, YouTube search, right? They all use these techniques we've talked about. Now, PG text search brings this exact ranking quality into Postgres without trying to recreate a full search platform. So, this is the feature I'm going to show you in this video called PG text search and it focuses on what Postgres was missing, high-quality ranked keyword search that pairs naturally with vector search for AI applications. Now, the result is a cleaner architecture, one database, better rankings, and retrieval that actually matches how modern AI systems work. Okay, so that's the overall kind of understanding that you need in order for this video to make sense. Effectively, what I'm going to show you here is a new technique, which is the PG text search, which you can add to a Postgres database, which is open source. I'm going to show you how to add it, how to experiment with it, and why it's better than just doing a normal search like you would have had in Postgres in the past. Hopefully, that gave you a little bit of insight, but as we get into this AI era, search is becoming more and more important and that's why I wanted to make this video and I'm going to continue showing how to set this up. Let's get into it. Okay, so I'm on the computer and there's a great article here that comes from Tiger Data that explains a lot of the stuff in more depth that I just went over in the intro there. So, you can see it talks about the ranking quality gap, right? Talks about how PG text search works. And what I'm going to do is I'm going to run through kind of a modified tutorial that comes from this document and show you how you can set up PG text search directly in your database and how you can do it for free. Now, in order to do that, I'm going to be using Tiger Data, specifically their Tiger Cloud platform to create a free database. You don't need to pay for this, you don't need a credit card, and when you set this up, you can connect it directly to something like Cursor and actually use their MCP server to control the database and run queries on it, so you don't need to manually write them. So, the first step here, if you want to follow along with this video or you can just watch, is go to Tiger Data. I'm going to leave a link to it in the description, make a free account, and simply just spin up a free service. So, when you go into the account, it should prompt you to create a service. You can select the lowest one that's free to use shuts down after a few hours if you don't use it. So, what I'm going to do is go create service here and I'm just going to make this free service in Tiger Data. It's going to take a second for this to be spun up and then what I'm going to be able to do is use the uh connection strings here if I wanted just to connect to this like a normal database, but in my case, I'm actually going to install an MCP server inside of Cursor that can control this database so we can directly run queries from our IDE rather than having to do it directly inside of uh what do you call it here? Like, you know, a SQL editor. Okay, so once the service is ready, you can just skip the uh configuration step. You press this skip this step and you can see that it has something that allows you to connect this to an AI tool. So, I'm just going to press on that and it gives me some instructions that I can use directly inside of something like Cursor or WinSCP or wherever you use an MCP server to directly install it. Now, if you're on Mac, it's a little bit easier than if you're on Windows or if you're on Linux. So, you can just use Homebrew or you can just use the script that you see right here, follow along with these steps, and then just type tiger mcp install. However, if you're on Windows, it's a little bit easier to use it with Go. So, what I'm going to do is follow the Go installation steps, which does mean you have to have Go installed on your computer. I'm going to show you how to kind of get it set up and then we'll start actually working with this new search. Okay, so the first command that it gave me there was this, go install and then just this GitHub repo. So, I'm just inside of Cursor and I'm just going to go ahead and press enter and install the Tiger CLI. Okay, so it just finished installing. Now, let's go back and we can see the next thing it says tiger off login. So, let's copy that and paste it and now it's just going to bring us to the Tiger Cloud website. I'm going to press on authorize and then we should be good to close this page and return to our terminal. And let's see. And validating the API key and it looks like we were logged in successfully. So, now what we need to do is just link this to our project. So, you'll see that we just need to put the uh password here. So, we need to copy this line like this. So, tiger new password, you put the password, and then tiger db save password, and then this is the kind of like the project name. So, I'm going to copy that and paste it. And okay, so inside of PowerShell, I don't think it works cuz I need to like set an environment variable different way. So, I'm just going to ask Cursor, how do I run this in Power Shell? Okay, and then just paste it and let's see if it can tell me the answer there. Okay, so it just gave me one line to run. So, I'm just going to copy this and paste it. Notice I'm just using this like dollar sign ENB. And then run and it says password save successfully for this service. Okay, cool. Now, if you're on Mac or Linux, you could just paste it that in because it would work for you, but in my case on Windows, it's a little bit wonky. And then if I go back here, let's go back connect AI tool. We can see the last command is tiger MCP install. So, I'm just going to go ahead and install this MCP server. You see all the options here. I'm going to go with cursor. And when I do that, it should add this to my cursor installation and you can see if I go into my settings, tools and MCP, I have the tiger MCP server showing up. And now I can start using cursor. Let's open up a new window here and I can ask it to do something like it says right here, you know, list my tiger cloud services. And it can just directly list it out for me. So, let's see if it gives me the answer here. running the tiger MCP server and you see I have the timescale database right here and it can directly execute SQL queries. And this also has the cool feature of being able to actually clone and replicate the database extremely quickly if you're working in like dev versus production environments. Won't get into that right now cuz I talked about it in another video which I'll put which I'll put on screen right now. But, what I'm going to do is I'm going to go to this guide. I'm going to start showing you how we can set up kind of a quick example to actually use this new search feature that I've been hyping up this video. So, you can see that we have this kind of demo front coming from this blog post which I'll link in the description where we're just creating like an articles table and inserting some articles into it and then creating a search index on that. Now, after we create the search index, this index is something that is transactional, which means we don't need to modify it. We don't need to update it. It will just automatically sync with our database and then we can start running search queries on it that will give us a better result than a fuzzy text search like we had in kind of the previous version of Postgres. So, because I kind of want to flex the MCP server here because it's pretty cool, I'm going to copy this. Again, this is linked in the description if you want to directly copy this. I'm going to go to cursor and I'm going to say, using a different example, but the same concept, uh you know, run a query similar to this to make some sample, let's say sample data do 100 rows. Okay, and I'm just going to paste in the example. And now what it should do is it should go to Tiger Data. As you can see, it's DB execute query and it should make 100 sample rows for me. So, let's see the first one. Okay, it just created the table products. Okay, nice. And then this one I'm going to go just run and it should insert 100 sample product rows for me. Okay, cool. And then you can see that it did that and it just gave me like a quick uh kind of sample of some of the data that it inserted into this products table. So, now that we've done that, we want to create the index. So, I'm going to say same thing for this. I'm just going to copy this. You can see the syntax to create the BM25 index. I'm going to go back here and say, okay, now do the same. Modify this to work work with our data. Okay, and I'm just going to paste this here and have it hopefully execute this query as well. Okay, so it actually caught a mistake that I should have caught myself, which is that it needed to enable the BM25 um feature in the database, which is kind of the most important part. So, if we go here, you can say create extension PG text search. You need to run that on the database to actually enable this feature. So, it did that for me automatically, but this is the first step. So, um anyways, create extension PG text search. Then you make the sample data or, you know, you could do it before, it doesn't matter. Then you create the index, which it just said that it did. So, it said that it made the index for me. So, now I can do an example test using a select query like this where I'm going to search through my products and try to find something. And let's see the kind of the quality of the search that we get. So, I'm going to go to cursor and same thing say, now adjust this for search. Right, and just paste that in and give me some search results. Uh you know, show me the query and what we got. Okay, and let's see it execute that on the database and see the quality of the search that we're getting. Cool. So, it just executed the query. This is the query it made. So, it said, you know, description at to BM uh 25 query. I'll show you what these mean in a second. Wireless charging and then product description index as score. And you can see that it gave me a bunch of results where we had wireless mouse pad, wireless charging stand, wireless charger stand, wireless charging mat, wireless charger with all of their different relevant scores popping up and all of their different indexes in the database. And then they did another one for gaming keyboard and you can see we get the results here. And this is significantly better than if you were using the kind of native before built-in, what is it just like full text search that uh Postgres had compared to now this PG text search, which just works significantly better. So, like that's it. It's just in the database. We can now do search. I don't need an external search tool. I don't need Algolia. I don't need uh elastic search. I don't need any of that. I don't need to update a search index all the time. I literally just enabled it, created the index, and now I have a super performant high-end search directly built inside of Postgres, which I'm just messing with with an MCP server, which is kind of crazy that we even have these features here in 2026. Now, just to give you some insight into how this works. If you're looking at the syntax, right, it kind of explains how this works. You can use the eyeball operator, which calculates the BM25 score between the text and the query. So, ID, title, content, and then we're calculating thing, right, as the score. And then we're just ordering this by score. So, effectively what we've done is we said, okay, so for the content, we'll select the ID, title, and content. But then we're going to do this operator with all of the content. So, as we do this search and then when we get the score, we can just the score on the articles and get the top five. So, you're writing a kind of adjusted SQL query using this BM25 query, right, which is the search query, which is pretty cool. Now, if we keep going through here, there's a bunch of other stuff that you can do. So, for example, you can combine this with vector search as well. Now, I'm not going to go through the whole demo cuz the video's already a little bit long. I'll leave this in case you want to mess around with it on your own. But, you can see that in this case, they create the index, which is a vector embeddings index. Now, if you have an LLM, you could use the embedding model to create the embeddings and store it in the database, which they're kind of doing right here with demo. And then you're able to run some more advanced queries, right, where you're using the vector search as well as using the text search. You can combine both together, right, where you're doing first, okay, the vector search, then on the vector search we do the keyword search within that and we can get some pretty cool results, which is what they're calling hybrid search. And again, it goes into more details in this article, which I will link in the description in case you're curious about kind of how to get the best performance out of this. This is super cool. It works extremely well, very easy to set up, free, open source to use, right? I just showed you an example inside of Tiger Data here because that's how I was able to get it up and running very quickly. Massive thank you to them for sponsoring the video and making this technology available to everyone. Let me know what you guys think of this in the comments down below and I will see you in another video.