PostgreSQL Hybrid Search Course – Keyword (BM25) + Vector Retrieval
Learn how to build a complete Wikipedia search engine entirely within PostgreSQL using BM25 keyword matching, vector embeddings, and reciprocal rank fusion. This step-by-step course covers setting up `pg_textsearch` and `pgvector-scale`, analyzing query plans, tuning hybrid weights, and serving results through a FastAPI backend. By keeping your data, keyword indexes, and vectors inside a single database, you eliminate complex synchronization pipelines and unlock fast, transactional search. Code: https://github.com/beaucarnes/wikisearch Course created by @beau Try Tiger Data Cloud: https://tsdb.co/fcc-postgres-search Chapters - 00:00 Introduction & Project Overview - 04:06 Matching vs. Ranking & PostgreSQL Text Search - 07:20 Understanding BM25 (IDF, Term Frequency, & Document Length) - 11:39 Setting Up Tiger Data Cloud & PostgreSQL - 14:06 Environment Setup & Enabling Database Extensions - 15:43 Loading Wikipedia Dataset & Schema Setup - 20:30 Native PostgreSQL Full-Text Search Baseline - 24:38 Analyzing Query Plans with EXPLAIN ANALYZE - 27:40 Implementing BM25 Keyword Search with pg_textsearch - 32:32 Building the Keyword Search Query & Highlighting (TS_HEADLINE) - 36:18 Boolean Conditions & Phrase Search - 37:32 Semantic Search Fundamentals & Generating Embeddings - 41:33 Vector Indexing with pgvector-scale - 43:28 Reciprocal Rank Fusion (RRF) for Hybrid Search - 46:58 Query Plan Analysis for Hybrid Search - 49:12 Comparing Keyword, Semantic, and Hybrid Results - 52:13 Connecting the FastAPI Backend & Connection Pooling - 54:06 Testing the Web Interface & Weight Tuning - 55:23 Adding New Records via Transactional Writes - 57:24 Scale Benchmarks & Next Steps for Optimization 🎉 Thanks to our Champion and Sponsor supporters: 👾 @omerhattapoglu1158 👾 @goddardtan 👾 @akihayashi6629 👾 @kikilogsin 👾 @anthonycampbell2148 👾 @tobymiller7790 👾 @rajibdassharma497 👾 @CloudVirtualizationEnthusiast 👾 @adilsoncarlosvianacarlos 👾 @martinmacchia1564 👾 @ulisesmoralez4160 👾 @_Oscar_ 👾 @jedi-or-sith2728 👾 @justinhual1290 -- Learn to code for free and get a developer job: https://www.freecodecamp.org Read hundreds of articles on programming: https://freecodecamp.org/news
