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
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
Code: https://github.com/beaucarnes/wikisearch
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