In this video, you will learn how to add search to your applications by creating a Wikipedia search engine using PostgreSQL, PG text search, and Tiger data. Look what happens when I look for animals that emit light themselves. This describes the idea, but may not use the words that are in the article I need. Here are the results by keyword. Now let's move on to semantic search, which compares query values to article vectors. And here is a hybrid search that combines both lists. Our articles, keyword index, and vectors are all stored in a single PostgreSQL database. We use the PG text search extension for BM25 ranking and PG vector with PG vector scale for semantic search. I'm Bo Karns, and in this course we're going to build this app together. We'll load real Wikipedia articles, learn SQL, and plug everything into a small web interface. You'll learn what each search method is effective at, where it has difficulties, and how to decide whether combining methods improves your results. Our project is called Wiki search. The keyword search will cover about 242,000 articles in the English Wikipedia. To make the vectorization task manageable, semantic search will cover approximately 25,000 of these articles. The interface shows both values because this difference is important when comparing results. You will need basic SQL knowledge, enough Python to run the script, and PostgreSQL with the extensions we use. I will be using Tiger Cloud, and the repository also explains the local option. All code is included. You can pause on a section, run the corresponding SQL file, and see what has changed. We will spend time understanding the solutions that make the app work. Our project is called Wiki Search. The keyword search will cover about 242,000 articles in the English Wikipedia. To make the vectorization task manageable, semantic search will cover approximately 25,000 of these articles. The interface shows both values because this difference is important when comparing results. You can use the code link in the description to follow along with me. You will need basic SQL knowledge, enough Python to run the script, and PostgreSQL with the extensions we use. I will be using Tiger Cloud, and the repository also explains the local option. All the code is included, so you can pause on a section, run the SQL file, and see what has changed. We will dedicate time to understanding the solutions that make our app work. Okay, here's our URL, followed by {slash} health. And as you can see, this will give us some information. We are focusing on PG Text Search version 1.5.1. Before we begin, we will check the installed version to ensure that the logical and phrase examples from the course match the database you are running. This course was created thanks to a grant from Tiger Data, the company behind TimescaleDB and PG Text Search. So, thank you Tiger Data for supporting freeCodeCamp. They are providing a $1,000 credit on Tiger Cloud for 30 days via the link in the description. We will use Tiger Cloud for our review because it has database extensions available and the service takes care of server setup . We will still activate the extension in our own database, which we will do together soon. But all this can be done locally, not just through Tiger Cloud. PG Text Search is open source software with a PostgreSQL license, so you can run it on your own compatible PostgreSQL server. The search code you are learning is regular SQL plus expansion operators . You can open the link to the examples in the description, and later we will create the service, check the version, and connect our project. Let's start with the problem we are solving. Imagine an application that already stores its articles in Postgres. Initially, the search box can use a simple text filter. Over time, users need better rankings, and the team adds a separate search service. When an article changes in Postgres, a task or change stream copies it to the search engine. The application then reads the search results from this second system. Now we have to decide what to do when the copy is delayed, when an error occurs, or when we rebuild the search index. The user can save the changes but still see the old version in search. In this project, we will keep search indexes along with articles. This gives us a single transaction boundary for the data and its index, and also allows us to combine search with relational queries that we already know. There are two different tasks in a search query. Eligibility determines which documents are eligible. Ranking determines which of the eligible documents should be first. Let's say I'm looking for: volcanic eruption, ash cloud, aviation. The All Terms filter checks whether each of the requested terms is present in the document. Relevance ranking determines how useful a document appears to be for that query. A flight cancellation page can be useful even if it doesn't contain the word " aviation." PostgreSQL's built-in full-text search supports logical queries and phrases. We will use it as a baseline and see what BM25 adds. PG Text Search adds corpus-aware BM25 scoring and an index designed for efficient ranked search. Later, we will combine this ranking with explicit matching rules when our application needs them. The built-in TS rank function takes into account relevant terms within a single document. It does not use the frequency of the document across the entire collection as BM25 does. These statistics help distinguish a term that appears almost everywhere from one that indicates a much narrower topic. PostgreSQL offers length normalization options , and its ranking function already takes repeated terms into account with diminishing effect. BM25 gives us a flexible model that combines term frequency with average document length and document frequency across the entire collection. It is this combined model that we are adding here. The GIN index can find relevant documents, but it does not provide the BM25 scoring ordering that we are going to use. Ranking many matches may require calculating scores and sorting them before returning 10 rows. We will check our actual plan instead of assigning a universal execution time to our own search. The number of matches, document sizes, memory, and hardware all affect this indicator. BM25 is a ranking function. First, a query term contains more information when it appears in fewer documents. Second, revisiting the term helps, but each additional mention should contribute less. Third, the length of the document matters, as a very long article has more opportunities to mention almost any topic. These ideas work together. BM25 doesn't just count how many query words it finds. It uses collection statistics and the controlled contribution of each relevant term . You will encounter BM25 in search engines such as Lucene and Elasticsearch. Using a common ranking family does not make two search products identical . Tokenization, features, configuration, and implementation still matter. In PG text search, we select the text search configuration and create the BM25 index. The index provides the statistics needed for evaluation. Let's break down these three ideas with examples and then apply them to real articles. Imagine a collection of thousands of documents. 900 mention "database ", but only 10 mention "pooling". For the query "database pooling", " pooling" narrows the sample much more. These calculations are visual so that we can see the idea clearly. The inverse document frequency, or IDF, captures this difference. A term that appears in fewer documents provides more evidence of relevance. BM25 combines these indications with term frequency and document length. Rarity helps, but it's not the whole assessment. The collection also matters. In this subset, "pooling" appears in eight of the 20 specialized documents. It was rare in the full collection, but it is common here. Partial indexes and partitions can have different statistics. The BM25 score belongs to its own query and index, so compare rankings within that context. Now imagine two articles, both discussing pooling. One mentions him once. Another mentions him 10 times. Repetition can indicate that the topic is central , so it makes sense that it helps. But the 50th mention shouldn't have the same effect as the first. The term frequency component in BM25 is flattened. A parameter called K1 controls this saturation. The default value in PG text search is 1.2. Think of this as a gauge of how quickly additional mentions stop having a significant impact on the outcome. A larger value usually allows the frequency to continue to affect a wider range. We also need to consider the length of the document. This is a parameter B assignment, the default value of which is 0.75. It adjusts for frequency contribution using the document length relative to the average in the collection. We will leave the default values until we see that changing them improves our queries. If B is zero, length adjustment is disabled . Increasing B enhances this effect. This doesn't mean that short documents always win. Coincidences of terms and their frequency still matter, and a long document may well be the best result. Now, how do we avoid being judged just for showing 10 articles? PG text search uses an inverted index that links terms to documents containing them. Block max wand optimization allows you to skip blocks that cannot improve the current best results. This is why the combination of ordered ranking and limit is important. We query the index for only a small set of strong candidates. The extension stores its index on PostgreSQL pages and interacts with PostgreSQL's storage and recovery mechanisms. Internally, it manages new writes and disk segments. We don't need to check whether our query plan uses the planned access path. So, let's create a database. So, follow this link. The link is in the description and you can register. This will give you $1,000 in cloud credits for 30 days. Now, after logging in, we can select our service. There is a free version, but since we have a lot of credits , I will choose the larger one. I'll just choose the single GPU version. We get 4 GB of RAM, after which we can choose the region. It is usually best to choose the region closest to you. And then click " continue". Now we'll just indicate that we're using a development environment and continue. You can give your service a name or leave the default name . So, you need to copy the connection string. Then create a copy of the .env.example file and add your connection string directly to the database URL. Next, you can add a random token and the model device is the CPU. And remember that the file must be named .env. Now make sure you open the code file from the repository link. And the readme file shows everything we need to do to configure it. There will be quick start commands there, and we will use the console to create a database and then run the Python code locally. Now I'll go back to the control panel here. I'll go to my project, and then we have the SQL editor at the bottom. This is where we can test the database. I'll just do "select current date" and hit "run". Okay, we got the date. So, everything is set up correctly. Now I'm just going to paste some more code here. And it will just check the versions of everything we have . Okay, now I've run this code in the SQL editor. It checks available versions of PG text search. We are targeting 1.5.1, which we see is set to true. And it should be available in TigerGraph. We could continue to execute commands in the SQL editor, but there is an easier method. Let me show you. Essentially, we just need to follow the quick start guide. So first run all these commands in your terminal to create a virtual environment and then install requirements.txt. You may have already done this—created an .env file. And then you just need to execute each of these commands. And you can run them right in your terminal. I've already launched them. This takes a little time. Some of them may take longer to execute, but let me briefly walk you through them so you understand how it works. These are the commands that will configure your database. So, first of all, this is an extension file. This enables PG text search and vector scale. The cascade option also sets the vector dependency if it is not already enabled. The version query should show PG text search, vector, and vector scale. The script also checks if PG text search is new enough for this course . If your existing service is using an older version, follow Tiger's instructions for updating the service. You may need a maintenance update before a newer version of the extension becomes available. Then update the extension in the database as described in the readme. Do not continue if the version check fails. Fixing this now is much easier than diagnosing a text query example in an old installation. So, we have already discussed creating a Python virtual environment, activating it, and setting the requirements. But just so you know, installing the embedding libraries can take some time. For this project, we use the simplified English Wikipedia from the Wikimedia dataset on Hugging Face. The data snapshot is named 20231101.simple. It contains 241,787 articles. The downloaded parquet file weighs approximately 157 MB. This is the size of the downloaded compressed file, not the final size of our database and indexes. Each entry contains an ID, URL, title, and article text. So if I do a search here, I can click on one of these links and it will open in the simplified English Wikipedia. The dataset card is licensed under the Creative Commons Attribution-ShareAlike 3.0 and GNU Free Documentation Licenses. Our readme file and interface include links to attribution and licenses, and indicate that we abbreviate and highlight excerpts. So, now I'm going to open the load.py file, which we'll run shortly to load the database. The uploader uses a specific version of the dataset and verifies the checksum of the uploaded file. This helps keep future runs of this course tied to the same source material, rather than the current state of the branch. We will also create a schema using this SQL file schema.sql. Initially, the table has five columns. ID, URL, title, text, and word count. The ID from Wikipedia becomes our primary key. The “body” column stores the text of the article, and the “word count” gives us a simple field for filtering and selecting a subset for embeddings. So, going back to load.py, we see that it loads the parquet file via Hugging Face, reads it in chunks, and sends the rows to PostgreSQL using the copy command. We copy the data into a temporary staging table and then insert it into the main articles table. If the ID already exists, the loader does not touch this line. This makes it safe to rerun the initial upload without the risk of accidentally overwriting work added later. The word count here is approximate , based on spaces. This is not the same as the number of tokens used by text search settings or the embed tokenizer. This is useful for coarse filtering, but we should not confuse these different measurements. So, actually, now we're going to run this command in your terminal. load.py: On first run, it loads the dataset, validates it, and copies the records. The time taken will depend on your network, database resources, and connection latency. The loader reports how many records it read, how many it inserted, and how many total rows are now in the table. If you run it again, the number of records inserted should be zero for already existing data. But we haven't created search indexes yet. Therefore, for the initial import, it is better to load the data first and then build the indexes so that you do not have to update them on each insert. Before we move on to the next command, we can check something. I'll open this file record/17 counters and it will give me a command to run. So, this time I will show you the execution of those commands that I have already run before. So , I'll just run python db.py and then pass in the SQL command. And then we can see the number of articles. So, at full load, we expect 241,787 rows of data. The source-only count remains the same when we add the initial demo articles with negative IDs. Now I'll run this as another test. So, essentially, we sort by word count in descending order, look at the top 10 articles, and then open a few entries to check if the titles, texts, and URLs fall into the appropriate columns. If the text is empty or the import only loads a sample, the index will not fix this. So this is just a check that the data is imported correctly. Interestingly, load.py runs analyze after importing. This updates the table statistics for the scheduler. These planner statistics are different from the corpus statistics inside the BM25 index. We will also check if the demo headers we planned exist in the data snapshot. The finished abstract should contain real results from this data, not fictional ones for the introduction. So, here is the text queries.txt file. We are going to run a few test queries. Here are a few different points that we will be testing, and we will be using these same queries throughout the course. " Apollo 13" refers to a specific object , while " self-luminous animals" describes an idea without naming it. As we test each search method, we will check whether the results answer the question and how long the search takes. For now, we're just selecting our test objects. So, let's start with a query about a volcano. We'll run this SQL here and use the query: volcanic eruption as an aviation hazard. This is before the addition of PG full-text search. We will try the search tools already built into PostgreSQL . This will give us a starting point for comparison. We will search the text of the articles first, and add the titles later. So, before we run this query, we're going to do this. Basically, just run this command. And it will create an index that will help PostgreSQL find articles containing our search terms. An index is a structure, an additional structure, that a database maintains to help you find data more efficiently. The function , er, TS vector, or more precisely to_tsvector, converts the text of an article into a searchable representation. The English setting applies language rules, such as collating similar word forms and ignoring common words like "the." Using GIN, here "using gin", selects the type of index that can search for these terms. So, we ran the command and the index was created. Now we will finally execute this request. We will read it in the order important for understanding the search. Plainto_tsquery, this section, converts our search text into matching rules. The "where" line uses the double " at" operator to check if an article matches. For matching articles, the ts_rank function calculates a relevance score. "Order by rank descending" puts the highest scores at the top, and "limit 10" returns the first 10 results. Okay, I'll launch it right now. We see that this search query did not return any results. This is because it requires that every search term be present in the text of the article. Let's see how this matching rule works , and then relax it to allow any of the terms . So now we're going to try this, uh, matching every word or any word. And when I run this command, we see that the ampersand means that each term is required. Our example sentence does not contain all of them, so the match is false. Vertical bars change the rule to accept any term. In fact, this sentence does not contain these terms. Therefore, it does not pass the check for the presence of all terms. Now let's try this "or" rule for our articles. Note the vertical lines between search terms. This would mean "or". And we'll run these SQL commands right now. Okay, now we finally get the results because we use " or". So, we have relaxed the compliance requirements. More articles can be selected, and ts_rank still determines their order. This demonstrates the selection we can make using PostgreSQL's built-in search. So separate these two questions. Which articles make it into the results, and which of those articles appear first? " And" and "or" modify the first question, and the ranking function decides the second. We will return to the original "and" query for further verification. Later, BM25 will change the way we search and rank, so a different list of results will depend on more than just the rating. So now we have a new SQL that I'm going to run here. And this shows us the plan, the execution plan of the query. This essentially shows how SQL found the results. The explain command here shows the database's execution plan for the query , that is, how exactly it decided to process it. And adding analyze actually runs the query and reports the measurements. The buffers parameter here adds information about the data pages that were accessed. You don't need to understand every line here. We will simply focus on the work that leads to our 10 results. So, first we have limit 10. This is what PostgreSQL does to limit the output to 10 rows. So basically here, in the plan, we can follow the indentations to the steps that fill it. We can look for steps that find matching articles, and if there are any—a sorting step that orders them. The actual string values describe what happened during this execution. PostgreSQL can look at and rank many articles before returning 10. Limit 10 controls the size of the output. It does not automatically limit all work to 10 articles. I'm going to run another command. Basically, I have T here. T displays the output in the terminal and also writes it to a text file. We can also find the execution time at the bottom. The second run might be faster because, well, let's see if it's faster. Yes, 0.323 milliseconds. But I don't think he's faster. So forget it. But sometimes it can be faster because PostgreSQL already has some data in memory. So now we'll try another search using the same method. And we are looking for a jazz trumpeter in New Orleans. Now let's look at the first few titles here. This time we're really getting some headlines and rankings. So these two look useful. I'll just save these two for later. We will essentially compare them later when we change the way we perform searches. And then I'll just do a search for " Apollo 13." SQL uses the numbers one and three, just like our saved query. Pay attention to whether the results are relevant to the mission we are looking for. So, we simply compare what the system outputs. So we can compare this to BM25. Later, we will change the way we search and want to see how BM25 compares to another method. So, for now, we tested the built-in search and saved its query plan. Next, we'll add BM25 to the same column with the article text and see how that changes the search. BM25 uses the word statistics across the entire collection that we discussed earlier, including term rarity. Using the same article text and search terms makes it easier to understand the changes. So, now we will create our BM25 index. That's exactly what this code does. Articles body BM25 is the name we give to the index. On articles defines the table, using BM25 selects the search method, and body indicates the column to search. This configuration text config English applies the rules for processing English text. This is where PG text search prepares existing articles for searching using BM25. The two set commands above the index manage resources for this session. One allows up to four background maintenance processes, the other sets a maintenance memory budget of 256 megabytes. Now let's try some queries using BM25 query calls. The query looks similar to our first search, but the score calculation has changed. Operator is “less than”, “snail” and “greater than”. So, it looks like this. It is located between the body of the article and the BM25 query. You can think of this expression as "rate this article for search." We use this expression in select to display the score, and order by to sort the results. We see the most negative ratings at the top. Therefore, sorting in order by is in ascending order. So, negative numbers are expected. PG text search returns a negative value of the BM25 score, so a more negative number means a stronger match. A score of -20 is listed before -5. That is why the query uses ascending sort. Our previous TS rank query used descending sort . When switching between these methods, you should check the sort direction. Now let's take a look inside the BM25 query. We pass our search text and the title to articles body BM25. The index name tells PG text search whose statistics to use. The rating helps organize articles for that particular search. These are not percentages. A negative value of -20 does not mean that the article is 20% relevant. Evaluate both the returned articles and their order when reviewing the results. So later, our application will exclude keyword candidates with a score of zero. Okay, we got the BM25 results. Now let's check if PostgreSQL actually used the index we created. This is a test of how the query is executed, not a new search feature. So, we run this command to explain the search. We see in the plan articles body BM25. This will show the results extracted in the order of scores. Plan and execution time may vary depending on data volume and server. Okay, here is our native plan.txt file. We essentially followed this plan, created it, and saved it to a text file. Now we can compare it with the BM25 plan. So let's compare these plans. We see a bitmap index scan for articles body gin. Postgres uses our GIN index to find articles containing all five search terms. Ampersands mean "and", meaning each term is required. In this dataset, no article text meets this condition, so we got zero results. We also have a sorting key TS rank. This plan has a separate step to sort matches by relevance, but this particular search found nothing, so there are no articles to rank or sort. Now we see the index scan using articles_body_bm25 and its order by string. Thanks to PG text search, the BM25 index supports the return of articles in order of their relevance . Note that there is no separate sorting step. This query does not require all five terms to appear and returns 10 ranked results. If we see actual, there are 10 rows here, and we also have this execution time. The built-in search completed in about a third of a millisecond, while the BM25 took about 9 milliseconds, but they perform different tasks. One found no articles matching all terms, while the other returned 10 ranked articles. These time metrics don't tell us which approach is faster overall. We have verified that our BM25 query uses the correct index to produce ranked results. Next, we will transform our demo query into the query used in the application. So, I open the SQL directory, the file search_keyword.sql. Our application should accept a search query entered by the user. I'll open his query by keywords to see how we pass this text into SQL. This is just a preview. Next, we will create a full-text search column and an index. The placeholder for Q contains the user's search text. The fixed name article_search_bm25 identifies the index that provides statistics for evaluation. So, now let's move on to one of the actual files in our application, query.py. And we can see this execute call with SQL and then with parameters. We're actually looking at this Python call. It receives SQL and a separate set of parameter values. A parameter is a value provided during query execution. Here, this includes the user's search text. This psycopg2, which is our PostgreSQL driver, securely sends values to the database. We don't want to compile SQL by inserting user text directly into the string. So if we go back to this search keyword, for example, a user might search for a name that contains an apostrophe. This apostrophe must remain part of the search text. Parameter binding treats it as data . The fixed index name has another purpose—selecting the search index. Save this name in the application's query file. The user provides a search phrase and our code provides the SQL structure. So far, we've only searched the text of articles, but the title of the article is also useful information for searching. So let's include both options. This command creates a search text that combines the title and body text. PostgreSQL automatically updates it when any of them change. So , I will now execute this in my terminal below. And, in essence, we changed the table. Now we will build the index, here is the next command. We are going to create a BM25 index for this merged text. It will become the keyword index for our application. So I'm going to run this SQL query. Now our keyword search can use words from both the title and the text. Next, we will help users understand why the article appeared by highlighting matching words in its preview. So, we have the TS headline, and here are our top results. The name alone may not tell us whether the result is useful. Let's add a quick preview with matching words highlighted. In a keyword query, TS headline creates this preview. We first select the best articles and then prepare a preview for only those results. There's no point in preparing thousands of previews when we're only showing 10. Now in safety.py, the database marks the words that need to be highlighted. This Python function separates these words from the rest of the preview. This allows the page to safely display the text of the article, even if it contains something resembling HTML code. Now let's open index.html here. Here, the page inserts the words as text and adds highlighting around the marked fragments. That's all the way. The database selects the excerpt. Python defines the highlighting, and the browser displays it. We will see these previews when we open the finished application. And remember, you can always view the code yourself using the link in the description. Now let's look at this SQL code. Sometimes ranking related articles isn't enough. A user may want to find articles that mention jazz but do not mention rock. This example adds such a requirement. The ampersand means "and" and the exclamation mark means "not." We'll run it and check the headers . This is an additional query option, so it doesn't actually change the search field in our application. We can also require an exact phrase. Here we search for "New Orleans" together , instead of allowing those words to appear anywhere in the article. PostgreSQL applies its own language processing rules, so this is not a case or punctuation check. And you see it right here. And there is a separate sorting by BM25. These queries perform two tasks. The matching condition determines which articles are eligible, and BM25 orders them by relevance. Such a combination may require additional work instead of a single scan of the combined index. Our app will keep ranked search simpler. So, let's help people find an article, even if they don't know what words to type. Imagine you are looking for animals that emit light themselves. You may need an article about bioluminescence, even if you don't know the word. This is where semantic search can help. The model converts text into numbers representing patterns and meanings. Then we can search for articles with similar representations, even if they use different words. A similar meaning does not always mean the correct answer. A search for one space mission may return another mission instead. That's why we'll also leave the keyword search . We'll first build a semantic search, then merge the two result lists and see if that helps. To compare values, we first need to convert text to numbers. This list of numbers is called an embedding or vector. Our local model generates 348 numbers for each input. We will prepare approximately 25,000 articles to make the exercise manageable. Keyword searches will still cover the entire collection. We will provide the model with the title and beginning of each article. The code takes the first 1500 characters of the main text, and the model can truncate them further to meet the input limit. So, this search represents the introductory parts of the articles. He may miss an idea that only appears near the end of a long article. So, now let's give the database a place to store these vectors. This command adds a column with space for the 384 numbers that our model generates. She creates a repository. The next step is filling it in. We will use the same fixed version of the model for articles and search queries so that their vectors can be compared. Now, I've already run this, and just so you know, I've run a lot of different things, but everything you really need to run to get this app working is in the README file. So, essentially, instead of running these files, I ran a broken version to better explain them. But this one can be run directly, the one I was just looking at. And then we will run embed.py. So, let's take a look at it. We look at the embed.py file. So, before you start, pay attention to which article the scripts choose. If we scroll down, we see that it takes the 25,000 longest articles and includes volcano and bioluminescence for our examples. This can give us up to 25,002 articles. It returns the same group if we restart it. Thus, an interrupted run completes the original task instead of starting a new group. So, basically you will need to run this command to run the embedding creation task . This is how I will generate embeds. So, I personally have already launched this. During the first run, the model is loaded, and after that it calculates the vectors on this computer and saves them to the database in parts. If you have already completed this step, the script will inform you that you do not need to do anything else . This step may take some time. If the connection drops and the script stops after trying to reconnect, you can simply run the same command again. The saved parts remain in the database, and the script continues from where it left off. Wait for it to complete before moving on. Now let's talk about vector storage and indexing. So, executing this command, this SQL query, will confirm that the task is complete. And we want to check for missing embeddings. We want to have zero missing vectors in our chosen group. The rest of the collection may still not have vectors, this is expected. We have prepared only some of them for semantic search. Good. Now we will execute this SQL code. We have vectors, but we also need a fast way to search through them. This command creates a vector index using PG vector scale. Think of this as a search structure for our semantic search. Just like the BM25 index supports our keyword search. We need to make sure that we wait for the build to complete and update the statistics. Now I'll look at SQL/ search_semantic.sql. This semantic query compares the search vector with the article vectors. A smaller distance means a closer match , so the closest result goes first. The index uses approximate search to reduce the amount of work, which means it may miss some of the closest matches. And a close match can still be a bad answer. So let's try it and read the results. Okay, let's try some examples of semantic search. So, I'll run query.py with the words " glowing animals" and set the mode to semantic. The script converts these words into a vector and queries PostgreSQL for the nearest article vectors. And look at the names. Which of these helps answer this question? If the article you were expecting is missing, remember that semantic search can only find articles for which we have provided an embed. The first request may also take longer while the model is loading. Now let's try Apollo 13 . This time we are looking for a specific mission. Okay, we can see if the results stayed within the scope of this mission or moved on to other space topics. So, this helps us see where content matching works well and where exact words still matter. So now we have keyword results and semantic results. So let's combine them into one list. How do we combine two searches that use different rating systems? Well, the BM25 score and vector distance are not directly comparable, so adding their " raw" numbers would not give us a useful ranking. Instead, we will use the position at which each article appears in the list : first, second, third, and so on. This method is called reciprocal rank fusion or RRF. An article receives points for its position on each list, with higher positions giving more points. If both searches find the same article, it receives contributions from both. Let's look at this using two articles as an example. Article A is second in keyword search and third in semantic search. The formula here converts each position into points. 1/60 plus position. For A, we add 1 divided by 62 and 1 divided by 63. This gives approximately 0.032. You don't need to memorize arithmetic. Just notice that Article A gets points from both lists. Article B is first in the keyword search , but is missing in the semantic search. She only gets points from one list, totaling about 0.0164. So, in this example, article A comes first. The fact that both methods find article A gives it enough overall points to beat article B. The number 60 in the formula controls how much the differences in positions affect it. If the number is not the number of results. Our application starts by giving equal weight to both methods. He multiplies each contribution by 1/2. This makes the amounts smaller, but keeps A ahead of B. We can change the balance later if one method is more useful to our users. Here's how I would do it in the app. Keyword search gives us 20 possible results , and semantic search gives us 20. We call them candidates. We combine their scores and keep the top 10. This allows us to work with two small lists instead of merging the entire collection. So this hybrid search is essentially a way of combining them. SQL follows the same sequence. These named sections collect keyword candidates and vector candidates. The next two sections give each article its position in the list. You can read this as a sequence of steps. Get two lists, then number their results. The with operator allows us to give names to these steps to make the query easier to understand. An article must be on at least one of these candidate lists to appear in the final results. We start with 20 method candidates. Over time, you can try a longer list to see if they improve results enough to justify the extra work. Here we give each article a score for its position. We merge the two sets of contributions and add points for articles with the same ID. Then we keep the top 10 results , get their titles and previews— that's our hybrid search. The two lists are combined into one rating. So, I'm going to launch a hybrid team. Okay, we explained it, so let me explain it to you. First, we can look at candidates by CTE keywords. We see that semantic search uses the BM25 index to select 20 rows. So, we have 20 candidate articles for Apollo 13. These are the possible results before we combine the two search methods. Then we have vector CTE candidates . Index of vector representations of articles and actually 20 lines. So, semantic search also selects 20 candidates using a vector index. This long list of numbers are embeddings of our search text. We may miss individual numbers. The important thing is that both searches use these indexes. Here we see an append operation, actually 40 lines. And then we have a hash aggregation with 32 rows . This merges the two lists together. 20 + 20 gives us 40 entries, but some of them are related to the same article. After grouping by article ID, we have 32 unique articles. This means that eight articles appeared on both lists. Each article receives points based on its position, and articles found by both methods receive contributions from both. We also see the best performing CTE, followed by a limit of 10 actual rows. We sort these total scores and keep the 10 articles with the highest ratings. The query then retrieves their details and prepares a preview for the results page. And we see the execution time is 71 milliseconds. The database completed this query in, essentially, about 71 milliseconds during this run. This does not include loading a Python model or creating searchable embeddings. The plan confirms that our two searches form one joint ranking. So next, let's look at the headlines themselves to assess whether this ranking is useful. So, I'll run the query again in hybrid mode. And this is the result of combining all of them. Actually, let's run them all one by one. So, keyword mode , semantic mode, and hybrid mode. And we'll see what...I just ran all the different ways, and we see different results. So, here are the results by keyword. Here are the semantic results. But here are the hybrid results. Combining methods can help, but it's worth testing rather than assuming that it improves every query. What if semantic search is more useful for queries that people type? We can give him more influence. A semantic weight of 0.7 gives it 70% weight, and 30% for keyword searches. We still use list positions. We simply vary the contribution of each method. So if I go to the app.py file, we can look at semantic weight. This setting in the API controls this balance. Zero uses only keyword searches. The unit uses only semantic search. And the values between them combine these two methods. You can look at this code to see all the different URL endpoints and how they work. To assess whether this helps, you can use the same saved queries each time. You can change the weight and then see what has shifted up or down. If you leave the query unchanged, it will be easier to see the impact of the setting. Let's go back to our three lists from the search for " Apollo 13". So, did hybrid search move a useful article closer to the top? Did he downvote a useful article? Actually, it depends on us. But if we look, we see that "Apollo 13" is higher in the hybrid search list than in the semantic search. And " Apollo 13" comes out on top for keywords, but then we lose some other important things. So I would say that the hybrid result is the best when considering these search terms. However, there is one limitation in our comparison. Keyword search covers the entire collection, while semantic search covers only the articles we have embedded. An article without a vector cannot receive semantic points. Therefore, to compare methods on equal coverage, we need to use the same set of articles or embed the rest of the collection. When improving your app, only test one change at a time. You can add more candidates or add embeds for more articles. Repeat the same queries, see if the results are more useful , and see how long the search takes. Now our three search methods are ready. So let's put them on the web page. Actually, that's what we did in the app.py file we already looked at. A web page needs to somehow query search results in Python, and that's the job of our API, built on FastAPI. So, the search endpoint here receives the search terms, the selected mode, and the number of results. It validates this input before performing the search. And here we see the query display. This mapping selects an SQL file for each mode. Keyword mode searches directly, while semantic and hybrid modes first convert the user's words into a vector. And C will use this for encoding. The application reuses the loaded model, so it doesn't need to load it again for every request. Then we have con. execute. And after that we get the answer. The application sends the SQL and parameter values to PostgreSQL, and then returns our articles and time spent on page. This time includes creating a query vector when necessary. This is a broader metric than the database execution time we saw earlier, so distinguish between the two metrics when comparing performance. So, let's take a look at this configuration and the lifetime here. Each search requires a connection to the database. So, instead of opening a new one every time, the app maintains a small pool that can be reused. Here the pool allows up to four connections. Each connection is ready to handle vectors and text search in English. The timer also prevents operations from executing endlessly in the database. So, next we'll run the application. Basically, we're going to use this command from the readme file, and I'm just going to use it to run the program. It's already up and running, and we can check it again . So, we're going to test it. It actually has everything we've been working on. Now he has this example with the glowing animals, which we'll just insert and use now, because we've been working with it all this time, and we can switch between hybrid, keyword, and semantic search. These are the modes we worked on. So, hybrid, and we can switch to keywords. We can choose to switch to semantic. Highlighted words help explain keyword matches . You can follow the source links to read the original articles. But let's see what else we can do. I'll open a new terminal and run this curl command. So, we're actually testing the weight settings. Okay, let's go back . We see this team. We got the results. There are many results here. Essentially, this query gives semantic search 70% weight and keyword search 30%. And then the response shows the returned articles. So, we can compare the titles to the equally weighted hybrid search results and see if the changed query balance helps. Our app also has a way to add articles. So if I go to app.py, I can look at "add article" here. I'm going to open this demo insert.py, which will use this to add an article. Our application can search for existing articles, and this helper script sends a sample title, text, and URL to the API. Adding content requires an administrative token. The helper script reads this token from our environment file, so we don't need to type it or display it on the screen. So, here is the sequence of writing the article. We want the article to be ready for both search methods. First, the application creates its vector. It then stores the text and vector together in a database transaction . A transaction allows us to save a change as a single entity or roll it back if it fails. If vector generation fails, the article is not inserted. The API reports success after the database commits the record, including index updates. So I'm going to run a command, the demo insert command. So, it was inserted. Now I'm going to find this in our browser. Okay, I just did a search for this and it came up. So, for semantic search, this is number two. If I do a keyword search, it will appear as the first result. In hybrid mode, it will appear as the first result. So it has both text and vector, so all three modes can use it. We did not export the article to a separate search engine. The text and search indexes are in PostgreSQL. Okay, I can look for other things. I can look for plants that eat insects, and we see that the first one is an insectivorous plant. So, the app works. As the collection grows, we will also need to monitor how the database handles searches and updates. For the initial import, we downloaded the articles before creating the indexes. This avoided updating each index for each incoming row. Creating an index still requires resources, so plan this work for a busy service. So how does this compare on a much larger scale? TigerGraph published a test using about 138 million snippets, comparing PG text search 1.0 with Parade DB 0.21.6. In this test, conducted by the vendor with already cached data, PG text search responded faster to short queries and handled more parallel queries. Parade DB built its index about twice as fast. These results describe that particular experiment. This is a measurement of our app or version 1.5.1. So use them as reference information and test your own data and queries. Test the quality of results, search duration, and system behavior when multiple people search for information while articles are being updated . The performance testing methodology is available via the link in the resources. Now we have keyword, semantic, and hybrid search. What you add next should depend on the difficulties users are facing. If they make mistakes in words, you will need an additional approach to correct the handwriting. If there are no vectors for relevant articles, a broader coverage of embeddings will help . The Readme contains information about additional filtering and settings for further work. For this project, storing articles and search queries in PostgreSQL allows you to work with SQL and save changes through database transactions. A specialized search platform may be better suited for other requirements. Now we have a working application that we can test to determine our needs. And we can do much more with our database directly in Tiger Data Cloud. So, we can go to the explorer if we want to explore different articles. So, this is the schema of our database. We can see what indexes we have. We can look at foreign keys. We can view logs of what happened to our database. There are even opportunities for increased security and many other features that we get by using Tiger Data Cloud. The description includes links to project files, implementation documentation, and datasets. You can use them to replicate these examples or build your own article search. And the readme file will provide much more information about what you can do with this application. You can continue working with this. For your next experiment, you might want to choose one thing you want to improve. Add more embeds to expand your reach, or break up long articles into smaller chunks so search can look beyond their introductions. You can also try a reranker, a model that takes the first results and analyzes them more closely to change the order. Repeat the same test queries and see if the better results are worth the extra time. You now have a working search application for experiments. Try your own queries and review the results. Thanks for learning with me, and thanks for watching freeCodeCamp. See you next course.
Generated algorithmically for Search Engine
Indexing.