PostgreSQL Hybrid Search Course – Keyword (BM25) + Vector Retrieval

Author: freeCodeCamp.org · Watch on YouTube

Video authored by the channel shown, not by IANIA; all rights belong to the creator. Transcript and summary generated automatically; the original-language content is preserved.

Original language: English

Original video: PostgreSQL Hybrid Search Course – Keyword (BM25) + Vector Retrieval · Channel: freeCodeCamp.org · 1:00:01 · Watch on YouTube

Topics: Enterprise AI applications, Data, databases and embeddings, RAG, search and information retrieval

Summary

Beau Carnes builds a Wikipedia search app using PostgreSQL, PG text search for BM25 keyword retrieval, and pgvector/pgvector scale for semantic search. The course covers loading and indexing Wikipedia articles, comparing PostgreSQL’s built-in text search with BM25, and generating embeddings for a subset of the collection. It then combines keyword and vector results with reciprocal rank fusion (RRF) and connects the search modes to a FastAPI web interface. The course emphasizes evaluating result quality and performance on the same queries, while noting that semantic search covers fewer articles in this project.

Key points

The project keeps articles and their search indexes in PostgreSQL, so article updates and indexes can be managed together.
Matching determines which documents qualify, while ranking determines their order; these are separate parts of search.
PostgreSQL’s built-in text search supports Boolean and phrase queries, while PG text search adds BM25 ranking and an index for ranked retrieval.
BM25 uses term frequency, document length, and collection-wide document frequencies; its scores are specific to the query and index.
The keyword index covers about 242,000 simple English Wikipedia articles, while semantic search uses embeddings for approximately 25,000.
Semantic search can find related concepts expressed with different words, but approximate retrieval can miss matches and similar meaning does not guarantee a relevant answer.
Reciprocal rank fusion combines keyword and semantic results using their positions rather than adding incomparable BM25 scores and vector distances.
The app uses FastAPI, parameterized SQL, a reusable database connection pool, and previews with highlighted keyword matches.
The course recommends comparing result quality and timings with repeatable queries and changing one setting at a time.
Possible next steps include embedding more articles, splitting long articles into passages, and trying a re-ranker.

Chapters

Start	Chapter	Summary
00:00	Course overview and the search problem	The course introduces a Wikipedia search app and demonstrates how keyword, semantic, and hybrid search can return different results.
04:06	Search architecture and BM25 concepts	The instructor explains why the project keeps indexes in PostgreSQL and introduces matching, ranking, and BM25’s corpus-aware scoring.
11:37	Set up Tiger Cloud and the project	The walkthrough creates a database service, configures the project environment, and checks the required PostgreSQL extensions.
15:22	Load the Wikipedia dataset	The course describes the dataset and loader, then imports articles and checks that the records landed correctly.
20:34	Try PostgreSQL built-in text search	The instructor creates a GIN index, tests all-terms and any-term queries, and inspects the execution plan.
27:23	Build and query a BM25 index	The course creates a BM25 index, runs ranked queries, and compares its execution plan with the built-in search plan.
32:06	Prepare keyword search for the app	The instructor uses parameterized SQL, searches titles and bodies together, and adds highlighted result previews.
36:13	Boolean queries and semantic search	The course demonstrates Boolean and phrase matching before introducing embeddings as a way to find conceptually related articles.
38:17	Generate and index article embeddings	The app embeds a selected subset of articles, stores their vectors, and builds an index for approximate semantic retrieval.
41:56	Run semantic searches and compare results	The instructor queries the vector index with example searches and discusses its coverage and relevance limitations.
43:29	Combine rankings with reciprocal rank fusion	The course explains RRF and walks through how the SQL combines keyword and vector candidate lists into a final ranking.
49:11	Evaluate search modes and tune their balance	The instructor compares keyword, semantic, and hybrid results, adjusts their relative weights, and notes the uneven collection coverage.
51:44	Connect search to the web app	The course reviews the FastAPI endpoint, model reuse, database connection pool, and the working search interface.
54:24	Test the app, insert an article, and consider next steps	The instructor tests weighted search and article insertion, then discusses performance context, future improvements, and project resources.

Quotes

That gives us one transaction boundary for the data and its index — 04:38

BM25 isn’t simply counting how many query words it finds. — 07:19

Similar meaning doesn’t always mean the right answer. — 37:46

A BM25 score and a vector distance aren’t directly comparable — 43:29

You now have a working search app to experiment with. — 1:00:01

Concepts and entities

PostgreSQL, PG text search, BM25, PG vector, PG vector scale, Reciprocal Rank Fusion, TigerData, Tiger Cloud, FastAPI, Beau Carnes

Full transcript (original language)

00:00 In this video, you’ll learn about adding search to apps by building a Wikipedia search app with PostgreSQL, PG text search, and Tiger data. Watch what happens when I search for animals that make their own light. That describes an idea, but it might not use the words in the article I need. Here are the keyword results. Now, let’s switch to semantic search, which compares the meaning of the query with the article embeddings. And here’s hybrid search, which combines both

00:31 lists. Our articles, keyword index, and vectors all live in one PostgreSQL database. We’re using an extension called PG text search for BM25 ranking and PG vector with PG vector scale for semantic search. I’m Beau Carnes, and in this course, we’ll build this application together. We’ll load real Wikipedia articles, inspect the SQL, and connect everything to a small web interface. You’ll learn what each search

01:02 method does well, where it struggles, and how to decide whether combining them actually improves your results. Our project is called Wiki search. Keyword search will cover about 242,000 simple English Wikipedia articles. To keep the embedding job manageable, semantic search will cover approximately 25,000 of those articles. The interface reports both counts because that difference matters when we compare results.

01:32 You’ll need basic SQL, enough Python to run a script, and PostgreSQL with the extensions we’re using. I’ll use Tiger Cloud, and the repository also explains the local option. All the code is included. You can pause at a chapter, run its SQL file, and inspect what changed. We’ll spend our time understanding the decisions that make the application work. Our project is called Wiki Search. Keyword search will cover about 242,000

02:02 simple English Wikipedia articles. To keep the embedding job manageable, semantic search will cover approximately 25,000 of those articles. The interface reports both counts because that difference matters when we compare results. You can use the code link in the description to follow along. You’ll need basic SQL, enough Python to run a script, and PostgreSQL with the extensions we’re using. I’ll use Tiger Cloud, and the repository also explains the local option. All the code is included, so you can

02:33 pause at a chapter, run its SQL file, and inspect what changed. We’ll spend our time understanding the decisions that make our application work. Okay, here’s our URL and then {slash} health. And you can see it’s going to give us some information here. We’re targeting PG Text Search version 1.5.1. We’ll verify the installed version before starting, so the Boolean and phrase examples later in the course match the database you’re actually running.

03:03 Now, this course is made possible with a grant from TigerData, which is the company behind TimescaleDB and PG Text Search. So, thanks to TigerData for supporting freeCodeCamp. And they’re offering $1,000 in Tiger Cloud credit for 30 days through the uh link in the description. We’ll use Tiger Cloud for the walk-through because the database extensions are available there, and the service handles the server configuration. We’ll still enable the extensions in our own database, which we’ll do together

03:36 shortly. But, all this can be done locally as well, and not through the not through Tiger Cloud. PG Text Search is open-source under the PostgreSQL license, so you can also run it on your own compatible PostgreSQL server. The search code you’re learning is ordinary SQL plus extension operators. You can open up the sample link in the description, and then later we’ll create the service, check the version, and connect our project.

04:06 Let’s start with the problem we’re solving. Imagine an application that already stores its articles in Postgres. At first, the search box might use a simple text filter. Eventually, users want better ranking, and the team adds a separate search service. An article changes in Postgres, a job or change stream copies it to the search system. The application then reads search results from that second system. Now we have to decide what happens when the copy is delayed, when an event

04:38 fails, or when we rebuild the search index. A user might save an edit and still see the old version in search. For this project, we’ll keep the search indexes alongside the articles. That gives us one transaction boundary for the data and its index, and lets us combine search with the relational queries we already understand. There are two different jobs inside a search request. Matching decides which documents are eligible. Ranking decides

05:09 which eligible documents should come first. Suppose I do a search for volcano eruption ash cloud aviation. An all terms filter asks whether every surviving query term appears in the document. A relevance ranking asks how useful the document looks for this query. A page about disrupting flights could be useful even if it never uses the word aviation. PostgreSQL’s built-in text search supports boolean queries and phrases.

05:43 We’ll use it as our baseline and see what BM25 adds. PG text search adds corpus aware BM25 scoring and an index designed for efficient ranked retrieval. Later, we’ll combine that ranking with explicit explicit matching rules when our application needs them. The built-in TS rank function considers matching terms within a document. It doesn’t have the collection-wide document frequencies that BM25 uses.

06:16 Those statistics statistics help distinguish a term that appears almost everywhere from one that identifies a much narrower topic. PostgreSQL offers length normalization options and its ranking function already handles repeated terms with diminishing effects. BM25 gives us a tunable model that combines term frequency with average document length and collection-wide document frequencies. That combined model is what we’re adding here.

06:47 A gin index can find matching documents, but it doesn’t provide the BM25 score ordering we’re about to use. Ranking many matches may require calculating scores and sorting them before returning 10 rows. We’ll inspect our actual plan instead of assigning a universal runtime to native search. The number of matches, document sizes, memory, and hardware all affect that measurement. BM25 is a ranking function.

07:19 First, a query term is more information when it appears in fewer documents. Second, seeing a term repeatedly helps, but each additional mention should contribute less. Third, document length matters because a very long article has more opportunities to mention almost any subject. Those ideas work together. BM25 isn’t simply counting how many query words it finds. It uses collection statistics and a controlled contribution from each

07:52 matching term. You’ll encounter BM25 in search systems such as Lucene and Elasticsearch. Sharing a ranking family doesn’t make two search products identical, though. Tokenization, features, configuration, and implementation still matter. In PG text search, we choose a text search configuration and build a BM25 index. The index supplies the statistics needed for scoring. Let’s unpack the three

08:22 ideas with examples, then we’ll run them against real articles. Imagine a collection of a thousand documents. 900 mention database, but only 10 mention pooling. For the query database pooling, pooling narrows down the collection much more. These counts are illustrative, so we can see the idea clearly. Inverse document frequency, or IDF, captures that difference. A term that appears in fewer documents contributes more evidence of relevance.

08:54 BM25 combines that evidence with term frequency and document length. Rarity helps, but it isn’t the whole score. The collection matters, too. In this In this subset, pooling appears in eight of 20 specialist documents. It was rare in the full collection, but here it’s common. Partial indexes and partitions can have different statistics. A BM25 score belongs to its query and index, so compare rankings within that

09:27 context. >> [snorts] >> Now imagine two articles that both discuss pooling. One mentions it once. Another mentions it 10 times. Repetition can indicate that the topic is central, so it makes sense for it to help. But the 50th mention shouldn’t have the same effect as the first. BM25’s term frequency component levels off. The parameter called K1 controls that saturation. The default in PG text search is 1.2.

10:01 Think of this as controlling how quickly additional mentions stop making much difference. A larger value generally allows frequency to keep contributing over a wider range. We also need to account for document length. That’s the job of B, whose default is 0.75. It adjusts the frequency contribution using the document’s length relative to the collection’s average. We’ll keep the defaults until we have evidence that changing them helps our queries.

10:33 With B at zero, that length adjustment is disabled. Increasing B strengthens it. This doesn’t mean short documents always win. Matching terms with their frequency still matter, and a long document can absolutely be the best result. Now, how do we avoid scoring everything just to show 10 articles? PG text search uses an inverted index, which connects terms with the documents containing them. It’s block max wand optimization can

11:05 skip posting blocks that cannot improve the current top results. That’s why the combination of an ordered score and a limit is important. We’re asking the index for a small set of strong candidates. The extension stores its index in PostgreSQL pages and participates in PostgreSQL storage and recovery machinery. Internally, it manages new postings and disk segments. We do not need to check that our query plan uses the intended access path. So, let’s create the

11:37 database. So, go to this URL. We have the URL in the description, and then you can sign up. And this will give you $1,000 in cloud credits for 30 days. Now, we can go through and choose our service after getting logged in. So, there’s always a free version, but since we have a bunch of credits, I’m going to choose a bigger one. I’m going to just choose the one GPU version. We’re getting 4 GB of RAM, and then we

12:08 can choose our region. Just Usually, you just want to choose the region that’s closest to you. And then click continue. Now, we’re going to just choose that we’re using our development environment and continue. And then you can name your service or just use the default name. So, what you’re going to want to do is copy the connection string. And then you want to create a copy of the .env.example file, and then add your connection string

12:40 right in the database URL. And then you can have a random token, and the model device is CPU. And you remember this is should be called the This is should be called .env. Now, make sure to open up the code file the code from the repository link. And we And the readme, it’s going to show everything we need to do to get this set up. There’s going to be quick start commands, and we’ll use the console to establish the database, then run the Python code locally. Now, what

13:12 I’m going to do is go back to the dashboard here. I’m going to go into my project, and then we have our SQL editor at the bottom. So, this is where we can kind of test out the database. I’m just going to do select current date date and then run. Okay, we got the date here. So, this is all set up correctly. Now, I’m just going to put in some other code here. And this will just check the the version of everything we have. Okay, now I’ve ran this code in the SQL

13:44 editor and this inspects available PG text search versions. We’re targeting 1.5.1 which we see is set as true. And this is should be available on TigerGraph. Now, we can keep running commands in the SQL editor, but there’s also an easier method. Let me show you. Basically, we just want to follow the quick start guide here. So, first you want to run all these commands in your terminal to create a virtual environment and then install the

14:16 requirements.txt. And maybe you’ve already done this, create your .env file. And then we’re going then you’re basically going to run every one of these commands here. And you can run them right in your terminal. Now, I’ve already run them. It takes a little bit. Some of them can take a little while to run, but let me just go over them so you can understand how this works. These are the commands are going to set up your database. So, first it’s the extensions file. This enables PG text search and vector

14:49 scale. The cascade option also installs the vector dependency if it isn’t already enabled. The version query should show PG text search, vector, and vector scale. The script also checks that PG text search is new enough for this course. If an existing service is on an older release, follow Tiger’s service update instructions. You may need the service maintenance update before the newer extension version becomes available. Then update the extension in the database as the readme describes.

15:22 Don’t keep going past the failed version check. Fixing that now is much easier than diagnosing a a phrase query example against an older installation. Now, we’ve already discussed these about creating a Python virtual environment and activating it, installing the requirements. But, just so you know, the embedding libraries can take a while to install. For this project, we’re using simple English Wikipedia from Wikimedia’s dataset on Hugging Face. The sha- The snapshot is named

15:52 2023-11-01.simple. It contains 241,787 articles. The downloaded parquet file is about 157 MB. That is the compressed download size, not the final size of our database and indexes. Each record includes an ID, URL, title, and article text. So, if I do a search here, I can click one of these links, and it’s going to open on simple English Wikipedia.

16:23 And the database card The dataset card lists Creative Commons Attribution-ShareAlike 3.0 and the GNU Free Documentation License. Our readme and interface include attribution and license links and identify that we shorten and highlight excerpts. So, now I’m going to open this load.py, which we are going to be running soon to load the database. The loader pans a specific dataset revision and checks the downloaded file’s checksum.

16:56 That helps keep a future run of this course tied to the same source material instead of whatever a moving branch contains. We’re also going to create a schema with this uh schema.sql SQL file. The table has five columns to start. ID, URL, title, body, and word count. The Wikipedia ID becomes our primary key. The body stores our little text and word count gives us a simple field for filters and choosing an embedding subset.

17:27 So, back on load.py, we can see it downloads the parquet file through hugging face, reads in batches, and sends rows to PostgreSQL with copy. We copy into a temporary staging table, then insert into articles. If an ID is already present, the loader leaves that row alone. That makes re-running the initial load safe without accidentally replacing work we’ve added later. Word count here is a white space based estimate. It isn’t the same as the token count used by the text search

17:57 configuration or the embedding tokenizer. It’s useful for a coarse filter, but we shouldn’t confuse those different measurements. So, basically, we’re now going to run this command in your terminal. load.py The first run downloads the data set, checks it, and copies the records. Your time will depend on your network, database resources, and connection latency. The loader reports how many records it read, how many it inserted, and the total rows now in the table. If you rerun it, the inserted count

18:29 should be zero for records already present. But, we haven’t created the search indexes yet. So, for the initial import, loading the data first and building the indexes afterward avoids maintaining those indexes one insertion at a time. Before we go into the next command, we can actually check. I’m going to open this a record this recording/17 counts file, and that’ll give me the the command to try running. So, this time I’m actually going to show you running the other ones I’d already run

19:00 beforehand. So, I’m just going to run python db.py, and then I’m going to pass in the SQL command. And then we can see how many articles So, So, on a fresh full load, we expect the data site sets 241,787 rows. The source only count stays the same when we add original demo articles with negative IDs. Now, I’m going to run this as another test. So,

19:32 basically, we’re sorting by work count descending and we’re looking at the 10 largest articles and then we’re opening a few record we open a few records and check that titles bodies and URLs landed in the expected columns. If the body’s empty or the import only loads a sample, basically, an index won’t repair that. So, this is just checking that things are being imported correctly. One thing interesting is that the the load.py runs analyze

20:04 after the import. That refreshes the planner’s table statistics. Those planner statistics are distinct from the corpus statistics inside the BM25 index. We’ll also check whether our planned demonstration titles exist in the snapshot. The finished snapshot should show actual results from this data, not a result invented for the introduction. So, here’s the text queries.txt. So, we’re going to do some test queries.

20:34 So, here’s some different items that we’re going to be testing and we’re going to use the same queries throughout the course. Apollo 13 asked for a specific subject while animals that make their own light describes an idea without naming it. As we try each search method, we’ll check whether the results answer the question and how long the search takes. For now, we’re just choosing our test searches. So, let’s start with the volcano query. So, we’re going to run

21:06 the this SQL here and we’re going to use this query, which is a volcano eruption as cloud aviation. So, this is before adding PG text search. We’re going to try the search tools already built into PostgreSQL. This gives us a starting point to compare against. We’ll search the article bodies first and add the titles later. So, before we run that query, we’re going to run this here. Basically, this run ran this command. Uh

21:38 and it’s going to create an index to help PostgreSQL find articles containing our search terms. An index is a structure an extra structure the database maintains so it can find data more efficiently. The function uh TS vector or says two TS vector, the function two TS vector converts article text into a searchable representation. The English setting applies language rules such as reducing related word

22:08 forms and ignoring common words like the. Using gin, the using gin here, it selects an index type that can look up those searchable terms. So, we’ve run the command and it’s created the index. So, now we’re going to finally run this query here. We’ll read it in the order that matters for understanding the search. Plain two TS query, which is this section right here, it converts our search text into

22:39 matching rules. The where line uses the double at operator to test whether an article matches. For the matching articles, TS rank calculates a relevance score. Order by rank descending puts higher scores first and limit 10 returns the first 10 results. Okay, so I’ll run this right now. We can see that this search term returned no matches. That’s because it requires every search term to appear in the article body. Let’s see how that matching rule works, then loosen it to allow any of the

23:11 terms. So, now we’re going to try this uh matching every word or any word. And when I run this command, we can see the ampersands mean every term is required. Our example sentence doesn’t contain all of them, so the match is false. The vertical bars change the rule to to accept any term. Basically, this sentence doesn’t contain those terms. So, it fails the all terms test. Now, let’s try that or rule on our articles.

23:44 So, notice the vertical lines between the search terms. That’s going to mean or. And we’ll run the we’ll run the these SQL commands right now. Okay, so now we’re finally getting some results because we’re doing the or here. So, we have loosened the matching requirement. More articles can qualify and TS rank still decides their order. This demonstrates a choice we can make with PostgreSQL’s built-in search.

24:16 So, keep these two questions separate. Which articles are allowed into the result and which of those articles appear first? And and or change the first question. The ranking function handles the second. We’ll return to the original and query for next check. Later, BM25 will change how we search and rank, so different result list won’t be due to scoring alone. So, now we have some new SQL I’m going to run here. And this is showing us the plan, the query plan. This is basically showing how SQL found

24:47 the results. The the explain here shows the database’s execution plan, the the it chose to answer a query. And adding analyze actually runs the query and reports measurements. Buffers here adds information about the data pages that accessed. You don’t need to understand every line here. We’ll just focus on the work that leads to our 10 results. So first we have the limit 10.

25:19 That’s what post equal caps the output at 10 rows. So basically here in the plan, we can follow the indentation to the steps feeding it. We can look at for the steps that find matching articles and if present a sort step that orders them. The actual row rows values describe what happened during this run. Post equal may examine and rank many matching articles before returning 10. Limit 10 controls the output size. It doesn’t automatically limit all the work to 10 articles.

25:49 I’m going to run another command. Basically I have this T here. T shows the output in the terminal and also writes it to a text file. We can also find the execution time near the bottom. The second run may be faster because well, let’s see if it’s faster. Yeah, 0.323 milliseconds. But I guess it’s not faster. So never mind. But sometimes it can be faster because post equal already has some data memory. So now we’re going to try another search

26:21 with the same method here. And we’re doing jazz trumpet in New Orleans. Now let’s look at the first few titles here. When this time we’re actually getting some titles and ranks. So these two look useful. I’m just going to save these two for later. We’re basically going to compare these later once we change the the way we’re doing search. And then I’m just going to do the search for the Apollo 13. The sequel uses the digits one and three

26:52 just like our saved query. Notice whether the results uh concern the mission we’re looking for. So, we’re just comparing what it what it comes with here. So, we can basically compare it with with BM 25. So, later we’re going to be changing the way we’re doing searching and we just want to see how the BM25 relates to the other method. So, so far we’ve tested built-in search and saved its query plan. Next we’ll add BM25 to the same article body column,

27:23 and we’re going to see how that changes the search. BM25 uses the collection-wide word statistics we discussed earlier, including how rare a term is. Using the same article text and search words makes it easier to see what changes. So, now we’ll create our BM25 index. That’s That’s what this code is doing. Articles body BM25 is the name we give the index.

27:53 On articles identifies a table using BM25 selects the search method and body identifies the column to search. This text config English applies English text processing rules. This is where PG text search prepares the existing articles for BM25 search. The two set commands at the top above the index scroll, they control resources for the session. One allows up to four maintenance workers, the other sets a maintenance memory budget of 256 megabytes. Now,

28:25 let’s try some queries using the BM25 query calls. The query looks similar to our first search, but the score calculation has changed. The operator is less than at sign greater than. So, looks like here. It sits between the article body and the BM25 query. You can think of that expression as score this article for the search. We use the expression in select to display the score and an order by to sort the results. We can see the most negative

28:57 scores near the top. Then it’s ascending the order by. So, the negative numbers are expected. PG text search returns a negative of the BM25 score, so a more negative number represents a stronger match. A score of -20 sorts before -5. That’s why the query uses ascending order. Our earlier TS rank query used descending order. When switching between these methods, you should check the sort direction.

29:28 Now, let’s look inside the BM25 query. We pass our search text and the name articles body BM25. Naming the index tells PG text search which index’s statistics to use. The score helps order articles for this particular search. It isn’t a percentage. Negative -20 doesn’t mean an article is 20% relative. Judge the returned articles as well as

30:00 their order when we look at the results. So, later our application will exclude keyword candidates with a zero score. Okay, we have our BM25 results. Now, let’s check whether PostgreSQL actually used the index we created. This check This is a check on how the query runs, not a new search feature. So, we run this command to explain the search. So, we see in the plan articles body BM25.

30:32 So, this is going to show the results being retrieved in score order. Now, the plan and timing can differ with the data and service size. Okay, so here is our native plan.txt. We basically ran this plan we created this plan and put it to a text file. So now we can compare it to the BM25 plan. So let’s compare these plans. So we see this bitmap index scan on

31:04 articles body gin. Postgres is using our gin index to look for articles containing all five search terms. The ampersands mean and every term is required. In this data set no article body meets the condition so we got zero results. We also have the sort key TS rank. This plan is a separate step for sorting matches by relevance but this particular search found nothing so there aren’t any matching articles to rank or sort. Now we can see the index scan using

31:36 articles body BM25 and it’s order by line. With PG text search the BM25 index supports returning articles in relevance order. Notice that there’s no separate sort step. This query doesn’t require all five terms to appear and it returns 10 ranked results. If we see the actual and then rows 10 here and then we have these execution times. The native search finished in about a third of a millisecond while BM25 took

32:06 about 9 milliseconds but they’re doing different jobs. One on no article satisfying an all terms condition and the other returned 10 ranked articles. These timings don’t tell us which approach is generally faster. What we verified is that our BM25 query uses the intended index to produce ranked results. Next we’ll turn our demonstration query into the query used by the app. So I’m opening the SQL directory the search keyword.sql file. Our app needs to accept a search typed

32:37 by a user. Oh, open its open its keyword query to see how we’ll pass that text into SQL. This is a preview only. We create its text search column and index next. The placeholder for Q holds the user’s search text. The fixed named article search MBM25 identifies the index that supplies the scoring statistics. So, now we’ll go to one of the actual files in our

33:07 application, query.py. And we can see this kind of execute and with the SQL and then params. We’re basically looking at this Python call. It retrieves the SQL and a separate set of parameter values. A parameter is a value supplied when a query runs. Here that includes the user’s search text. This psycopg2, which is our PostgreSQL driver, sends the value safely to the database. We don’t want to assemble SQL by inserting the user’s text into a

33:38 string. So, if we go back over to this search keyword, for example, a user might search for a name containing an apostrophe. That apostrophe should remain part of the search text. Parameter binding handles it as data. The fixed index name has a different job, choosing the search index. Keep that name in the application’s query file. The user supplies a search phrase and our code supplies the SQL structure.

34:09 So far, we’ve only searched article bodies, but the an article’s title is useful search information, as well. So, let’s include both. This command creates search text, which combines the title and body. PostgreSQL keeps it updated automatically when either one changes. So, I’m I’m to run it in my terminal down here. And we’ve basically altered the table. Now we’ll build, so here’s the next

34:39 command. We’re going to build a B25 index on that combined text. This becomes the keyword search index for our app. So I’ll run this SQL. Our keyword search can now use words from both the title and body. Next, let’s help users understand why an article appeared by highlighting matching words in its previews. So we have the TS headline and we have the top results here. A title alone may not tell us whether a result is useful.

35:09 Let’s add a short preview with the matching words highlighted. In the keyword query, TS headline creates that preview. We chose the top articles first, then prepare previews only for those results. There’s no reason to prepare thousands of previews when we’re showing 10. Now over in safety.py, the database marks which words to highlight. This Python function separates those words from the rest of the preview. That lets the page display article text

35:41 safely even if the article contains something that looks like HTML code. Now open up the index.html here. Here the page inserts the words as text and highl- and adds highlighting around the marked pieces. That’s the whole path. The database chooses the excer- excerpt, Python identifies the highlights, and the browser displays it. We’ll see those previews when we open the finished app.

36:13 And remember, you can always look at the code yourself using the link in the description. Now let’s look at this SQL code. Recording So sometimes ranking related articles isn’t enough. A user might want articles that mention jazz, but don’t mention rock. This example adds that requirement. The ampersand means and and the exclamation mark means not. We’ll run it and inspect the titles. Now, this is an extra uh query option, so it doesn’t actually

36:45 change our app’s search box. One thing we can also do is require phrasing. Or here we’re asking for New Orleans together rather than letting those words appear anywhere in the article. PostgreSQL applies its language processing rules, so this isn’t a check for identical capitalization or punctuation. And you see this at at here. Um and there’s a separate BM25 order by.

37:15 These queries do two jobs. The matching condition decides which articles qualify. The BM25 puts those articles in relevance order. That combination can require extra work rather than one combined index scan. Our app will keep the simpler ranked search. So, next let’s help find people find an article even when they don’t know the right words to type. Imagine searching for animals that make their own light. You might want an article about bioluminescence without knowing that word.

37:46 That’s where semantic search can help. A model turns text into numbers that represent patterns and meaning. We can then look for articles with similar representations even when they use different words. Similar meaning doesn’t always mean the right answer. A search about one space mission might return another mission instead. That’s why we’ll keep keyword search also. We’ll build the semantic search first, then combine the two result lists and see whether that helps.

38:17 To compare meanings, we first need to turn text into numbers. The that list of numbers is called an embedding or a vector. Our local model produces 348 numbers for each input. We’ll prepare roughly 25,000 articles to keep the exercise manageable. Keyword search will still cover the full collection. We’ll give the model the title and beginning of each article. The code takes the first 1,500 body characters

38:47 and the model can shorten that further to fit its input limit. So, this search represents article introductions. It may miss an idea that only appears near the end of a long article. So, now let’s give the database somewhere to store those vectors. This command adds a column with room for the 384 numbers our model produces. It creates the storage, the next step fills it. We’ll use the same fixed model version for articles and search queries so their vectors can be compared.

39:19 Now, I’ve already run this and just so you know, um I’ve been running a lot different things, but the only thing you really need to run to get this app working are all in the read me. So, basically, instead of running these files, I ran like the the the like the broken down version so I can explain them better. But, this one you can just run directly, the one that I was just looking at. And next we’re going to be running embed.py. So, let’s look at that one. We’re looking at the file embed.py.

39:52 So, before we start, notice which article the scripts chooses. If we scroll down here, we’ll scroll down and then we can see that it takes the longest 25,000 articles and includes volcano and bioluminescence for our examples. That can give us up to 25,002 articles. It returns the same group if we restart it. That way an interrupted run finishes the original job instead of instead of

40:23 starting another group. So, basically you are going to want to run this command to to start the embedding job. That’s how I’ll generate the embeddings. So, I’ve personally already run it. And on the first run the model downloads and after that it computes the vectors on this computer and saves them to the database in batches. If you’ve already completed this step, the script will report that there is nothing left to do. That step can take a while.

40:54 If the connection fails and the script stops after retrying, you can just run the same command again. Saved batches stay in the database and the script picks up the missing work. Wait for a completion before moving on. Now, let’s talk about vector storage and the index. So, this running this command this SQL will make sure the job finished. And we want to look for missing embeddings. We want zero missing vectors in the group we selected.

41:25 The rest of the collection can still have no vectors, that’s expected. We only we only prepared part of it for the semantic search. Okay? Now, we’re going to run this SQL code. We have the vectors, but we also need a fast way to search them. This command builds a vector index using PG vector scale. Think of it as the lookup structure for our semantic search. Just as the BM25 index supports our keyword search. We want to make sure to wait for the build and the statistics

41:56 update to finish. Now, I’m going to look in the SQL/search_semantic.sql file. This semantic query compares the search vector with article vectors. A smaller distance means a closer match, so the closest one comes first. The index uses an approximate search to reduce the work, which means it can miss some nearest matches. And a close match can still be a poor answer. So, let’s try it and read the results.

42:27 Okay, let’s try out some semantic search examples. So, I’m going to run a query query.py animals that make their own light and I’ll put mode semantic. The script turns those words into a vector and asks PostgreSQL for nearby article vectors. And look at the titles. Which one helps answer that question? If an article you expected is missing, remember that semantic search can only find articles

42:58 where we’ve given an embedding. The first query may also take longer while the model loads. Now, let’s try the Apollo 13 next. This time we’re looking for a specific mission. Okay, we can see whether results stayed on that mission or drift towards other space topics. So, this helps us see where matching meaning works well and where exact words still matter. So, we now have keyword results and semantic results. So, let’s combine them into one list.

43:29 How do we combine two searches that use different scoring systems? Well, a BM25 score and a vector distance aren’t directly comparable, so adding their raw numbers wouldn’t give us a useful ranking. Instead, we’ll use where each article appears in each list, first, second, third, and so on. This method is called reciprocal rank fusion or RRF. An article earns points for its position in each list, and higher positions earn more points. If both

44:00 searches find the same article, it gets a contribution from both. Let’s see that with two example articles. Article A is second in keyword search and third in semantic search. The formula here converts each position into points. 1 / 60 plus the position. For A, we add 1 over 62 and 1 over 63. That gives about 0.032.

44:31 You don’t need to memorize arithmetic. Just notice that the A gets points from both lists. Article B is the is first in keyword search, but missing from semantic search. It earns points from only one list for a total of about 0.0164. So, A finishes ahead in this example. Both message Both methods finding A gives it enough combined points to beat B. The 60 in the formula controls how strongly positions differ.

45:02 If the num If it isn’t the number of results. Our app starts by giving the two methods equal weight. It multiplies each contribution by 1/2. That makes these totals smaller, but keeps A ahead of B. Later, we can adjust the balance if one method is more useful for our users. Here’s how I’ll do that in the app. Keyword search gives us 20 possible results and semantic search gives us 20. We call those candidates. We combine their points and keep the best 10. This lets us work with two small lists

45:34 instead of combining the entire collection. So, this search hybrid is basically how they get combined. The SQL follows that same sequence. These named sections collect the keyword candidates and the vector candidates. The next The next two sections give each article its position in its list. You can read it as a series of steps. Get two lists, then number their results.

46:05 The width clause lets us name those steps, so the query is easier to follow. An article has to reach at least one of these candidate lists to appear in the final results. We start with 20 candidates per method. Later, you could try a larger list to see whether they improve the results enough to justify the extra work. Here we give um each article points for its positions. We bring the two sets of contributions together and add the points for matching articles ID. Then we keep the 10 highest

46:36 totals and fetch their titles and previews, and that’s our hybrid search. Two lists combined into one ranking. So, I’ll run the hybrid command. Okay, we run we explained it, so let me explain this explained to you. So, we first we can look at the CTE keyword candidates. We can see semantic search We can see the semantic search uses the BM25 exit uh

47:07 index to select it says 20 rows. So, 20 candidate articles for Apollo 13. They’re possible results before These are possible results before we combine the two search methods. Then we have the CTE vector candidates. Article embeddings IDX and the actual rows 20. So, semantic search also selects 20 candidates using the vector index. That long list of numbers is the

47:37 embeddings of our search text. We can skip the individual numbers. The important part is that both searches are using these indexes. Here we can see append actual rows 40. And then we have the hash aggregate rows 32. So, this brings the two lists together. 20 + 20 give us gives us 40 entries, but some refer to the same article. After grouping by article ID, we have 32 distinct articles. That tells us eight

48:09 articles appeared in both lists. Each article receives points based on its position, and articles found by both methods receive contributions from both. We can also see CTE top results then limit actual rows 10. We sort those combined scores, and we keep the 10 highest-ranked articles. Then the query fetches their details and prepares the previews for the results page. And then we can see the execution time

48:40 of 71 milliseconds. The database completed this query uh basically in about 71 milliseconds on this run. That doesn’t include loading the Python model or creating the search embeddings. The plan confirms that our two searches feed into one combined ranking. So, next let’s look at the actual titles to judge whether that ranking is useful. So, I’m going to run the query again with mode hybrid. And this is the results the result of combining all all of those. And

49:11 actually, let’s just run all of them one at a time. So, basically mode keyword, mode mode semantic, and mode hybrid. And we’re going to see what they So, I basically ran it all the different ways, and we’re seeing the different results. So, here are the keyword results. Here the semantic results. And here are the hybrid results. So, combining methods can help, but we need to check rather than assume it improves

49:43 every query. What if semantic search is more useful for the queries people type? We can give it more influence. A semantic weight of 0.7 gives it 70% of the weight with 30% for a keyword search. We’re still using list positions. We’re just changing how much each method method contributes. So, if I go to the app.py file here, we can go down and look at the semantic weight. This setting in the API controls that

50:13 balance. Zero uses keyword search alone. One uses semantic search alone. And and the values between them combine the two. So, you can look through this code to see all the different endpoints, the different URL endpoints, and how they work. To judge whether that helps, you can use the same save queries each time. You can change the weight, then look to what look at what moves up and or down. And then if you keep the query the same, it makes it easier to see the effect of

50:43 the setting. Let’s look back at our three lists from the Apollo 13 search. So, did hybrid search move a useful article closer to the top? Did it push a useful one down? Basically, it kind of is kind of up to us. But if we look, we can see that Apollo 13 appears higher on the list in the hybrid than in semantic. And

51:14 Apollo 13 appears goes at the top for keyword, but then we miss some other things that are also important. So, I would say that the hybrid result is the best when we look at these search terms. But there is one limitation in our comparison. Keyword search covers the whole collection, but semantic search covers the articles we embedded. An article without a vector can’t earn semantic points. So, to compare the methods on equal coverage, we need to do the same

51:44 set of we you need to use the same set of articles for both our both or embed the rest of the collection. As you improve the app, test one change at a time. You could give it more candidates or add embeddings for more articles. Repeat the same queries, check whether the results are more useful, and see how long the searches take. For now, our three search methods are ready. So, let’s put them behind a web page. Well, that’s what we’ve done in this app.py that we’ve already looked at.

52:18 The web page needs a way to ask Python for search results, and that’s the job of our API, which is built with FastAPI. So, the search endpoint right here receives the words to search for, the chosen mode, and the number of results. It checks those inputs before doing the search. And here we can see the the queries mapping. The mapping chooses the SQL file for each mode. Keyword mode can search directly, semantic and hybrid mode first

52:49 turn the user’s words into a vector. And see, it’s going to be using this to encode. The app reuses the loaded model, so it doesn’t have to load it again for every request. Then later, we have the con. execute. And then we get a response. The app sends the SQL and its parameter values to PostgreSQL, then returns our articles and an elapsed time to the page. That time includes creating the the query vector when needed.

53:22 It’s a broader measurement than the database execution time we saw earlier, so keep those two timings separate when comparing performance. So, let’s look at this configure and the lifespan here. Every search needs a database connection. So, instead of opening a new one each time, the app keeps a small pool it can reuse. Here the pool allows up to four connections. Each connection is prepared to handle vectors and English text search. A timer also

53:53 prevents a database statement from running indefinitely. So next we’re going to start the app. Basically, we’re going to use this command right here from the readme and I’ll just use that to start the app. It’s already started and we can actually check it out again. So we’re going to test it. It basically has everything we’ve been building toward. Now it has this example animals that make their own light, which we’re going to actually just paste in and use that example cuz we’ve been using it this whole time and we can change between hybrid,

54:24 keyword, and semantic. These ones that we’ve been building toward. So hybrid and we can switch to keyword. We can choose switch to semantic. The highlighted words help explain the keyword matches. You can follow the source links to read the original articles. But let’s see what else we can do. I’m going to start a new terminal and I’m going to run this curl command. So we’re basically trying the weight setting. Okay, let’s go back. We see this

54:54 command. We got the results. A lot of results here. Basically, we are this request gives semantic search 70% of the weight and keyword search 30% and then the response shows the returned articles. So we could compare the titles with the equal weight hybrid results and see whether they the the changed balance helps the query. Our app also has a way to insert articles. So if I go to app.py

55:25 I can look at add article here. I’m going to open this demo insert.py, which is going to use that to add an article. Our app can search existing articles and this so this help helper sends a sample title, body, and URL to the API. Uh adding content requires an admin token. The helper reads that token from our environment file, so we don’t have to type or show it on screen. So here’s the article write sequence. We want the article to arrive ready for

55:55 both search methods. First, the app creates its vector. Then it saves the text and vector together in a database transaction. A transaction lets us save the the change as a unit or undo it if it fails. If vector generation fails first, no article’s inserted. The API reports success after the database commits the write, including the index updates. So I’m going to run the command the demo insert command. So it’s been inserted.

56:25 Now I’m going to search for that on our uh browser. Okay, I just searched for this and it appears. So uh for semantic it’s number two. If I do a keyword, it will appear as the type top one. In hybrid, it will appear as the top one. So it has both vector and it has both text and a vector, so all three modes can use it. We haven’t exported the article to a separate search service.

56:55 Uh the text and search indexes are in PostgreSQL. Okay, I can search for other things. I can search for plants that eat bugs, and we can see that top one is carnivorous plant. So the app works. As the collection grows, we’ll also need to watch how the database handles the searches and updates. For the initial import, we loaded the articles before building the indexes. That avoided updating each index for every incoming row. Building an index still takes resources, so plan for that work on a busy service. So how does this compare at a much

57:26 larger scale? TigerGraph published a benchmark using about 138 million passages comparing PG text search 1.0 with ParadeDB 0.21.6. In that vendor run test with data already cached, PG text search answered short queries faster and handled more concurrent queries. ParadeDB built its index about twice as fast. Those results describe that particular experiment.

57:56 These are measurements of our app or of version 1.5.1. So, use them as background and test your own data and queries. Check result quality, how long searches take, and what happens when several people search while articles are being updated. The benchmark methodology is linked in the resources. We now have keyword, semantic, and hybrid search. What you add next should depend on what users struggle with. If they misspell words, you’ll need an additional approach for typo tolerance. If relevant articles have no vectors, wider embedding covers may help. The

58:28 readme includes optional filtering and and tuning for further work. For this project, keeping articles and searches in PostgreSQL lets us work with SQL and save changes through database transactions. A dedicated search platform may suit other requirements. We now have a working app we can test to decide what we need. And there’s so much more we can do in our database right within TigerGraph Cloud. So, we can go to the explorer if we want

58:58 to explore the different articles. So, this is our database schema. We can look at what indexes we have. We can look at the foreign keys. We can see logs from what’s been happening with our database. And there’s even stuff we can do to add security and just a lot of other features that we get when we’re using TigerGraph Cloud. And the description links to project files, execution documentation, and and data set. You You can use them to repeat

59:30 these examples or build a search over your own articles. And the read me will just give a lot more information of what you can do with this. And you can continue with this. For your next experiment, maybe you want to pick one thing you want to improve. Embed more articles to expand coverage or split long articles into smaller passages so a search can reach beyond their introductions. You could also try a re-ranker, a model that takes the first result and examines them more closely to reorder them. Repeat the same

1:00:01 test queries and check whether better results are worth the extra time. You now have a working search app to experiment with. Try your own queries and read the results. Thanks for learning with me and thanks for watching freeCodeCamp. I’ll see you in the next course.