Video by freeCodeCamp.org · streamed from YouTube. All rights remain with the creator.
Lesson 2: Hybrid Search in PostgreSQL
About this lesson
Keyword search and vector search each miss things the other finds, so serious search systems use both. This lesson shows how to build that combination inside a single PostgreSQL database, without running a separate search engine alongside it. The project is a full search application over roughly 240,000 articles from Simple English Wikipedia. The course starts with the keyword side, setting up an extension that brings BM25 ranking to PostgreSQL, a method that scores matches using statistics from the whole collection. It then adds the meaning side, storing a 384-number sentence embedding for every article using vector extensions and indexing them for fast nearest-neighbour lookups. With both searches working, you combine their results using Reciprocal Rank Fusion, written directly in SQL, so that articles favoured by both methods rise to the top. The lesson then turns to performance, reading query plans with the database's own analysis command to see which indexes are used and how much data each step touches. The remaining sections build the application around the queries: a Python back end using FastAPI and the Psycopg driver, highlighting of the matched words in results, and writing new documents inside a single transaction so the text and its indexes never fall out of step. You should be comfortable with basic SQL and Python before starting. Afterwards, read Chapter 2 of the course book, which explains the BM25 formula with a graph and works through a rank fusion example number by number.