PostgreSQL hybrid search tutorial with pgvector

Beau Carnes builds a Wikipedia search app that keeps articles, keyword indexes and vectors in PostgreSQL. The course targets pg_textsearch 1.5.1 for BM25 ranking and uses pgvector with pgvectorscale for semantic retrieval. The walkthrough uses Tiger Cloud; Carnes says the repository also documents a local option on a compatible PostgreSQL server. Following along requires basic SQL and enough Python to run scripts.

The tutorial starts with PostgreSQL's built-in text search, separates matching rules from ranking, and checks execution plans before adding BM25. It then covers parameterized queries, highlighted excerpts and locally computed embeddings. A FastAPI backend exposes the search modes and reuses database connections.

Coverage limits matter: keyword search includes 241,787 articles from the 2023-11-01.simple Wikipedia snapshot, while semantic search uses roughly 25,000. Embeddings represent titles and article introductions, so they can miss details later in a document. Approximate vector retrieval can also miss nearby matches.

Hybrid search combines 20 candidates from each method through reciprocal rank fusion and returns the top 10. Carnes demonstrates weight tuning but cautions that combining methods does not improve every query. The course also shows transactional article inserts and distinguishes database query timing from API timing, which includes query embedding work.