Show HN: How This Graybeard Built the Fastest and Freest Postgres BM25 Search

Summary

Modern ranked text search for Postgres. Simple syntax: ORDER BY content <@> 'search terms' BM25 ranking with configurable parameters (k1, b) Works with Postgres text search configurations (english, french, german, etc.) Fast top-k queries via Block-Max WAND optimization Parallel index builds for large tables Supports partitioned tables Best in class performance and scalability 🚀 Status: v1.0.0 - Production ready. See ROADMAP.md for upcoming features. The original name of the project was Tapir - Textual Analysis for Postgres Information Retrieval. We still use the tapir as our mascot and the name occurs in various places in the source code. PostgreSQL Version Compatibility pg_textsearch supports PostgreSQL 17 and 18. Download pre-built binaries from the Releases page. Available for Linux and macOS (amd64 and arm64), PostgreSQL 17 and 18. cd /tmp git clone https://github.com/timescale/pg_textsearch cd pg_textsearch make make install # may need sudo pg_textsearch must be loaded via shared_preload_libraries. Add the following to postgresql.conf and restart the server: shared_preload_libraries = 'pg_textsearch' # add to existing list if needed Then enable the extension (once per database): CREATE EXTENSION pg_textsearch; Create a table with text content CREATE TABLE documents (id bigserial PRIMARY KEY, content text); INSERT INTO documents (content) VALUES ('PostgreSQL is a powerful database system'), ('BM25 is an effective ranking function'), ('Full text search with custom scoring'); Create a pg_textsearch index on the text column CREATE INDEX docs_idx ON documents USING bm25(content) WITH (text_config='english'); Get the most relevant documents using the <@> operator SELECT * FROM documents ORDER BY content <@> 'database system' LIMIT 5; Note: <@> returns the negative BM25 score since Postgres only supports ASC order index scans on operators. Lower scores indicate better matches. The index is automatically detected from the column. For explicit index specification: SELE...