timescale/pg_textsearch

pg_textsearch is a PostgreSQL extension that adds modern full-text search ranking to Postgres databases. It implements BM25, a well-established algorithm for scoring search results by how relevant each document is to a given query. Standard Postgres text search can tell you whether a document matches, but BM25 lets you order results by how closely they match, so the most relevant ones come first rather than in arbitrary order. Using the extension follows normal SQL patterns. You create an index on a text column, then write a query with an ORDER BY clause using a special operator. For example: SELECT * FROM documents ORDER BY content <@> 'database system' LIMIT 5. The extension handles the scoring math. Two tunable parameters, k1 and b, let you adjust how the algorithm weighs term frequency versus document length if the defaults do not suit your data. The extension supports multiple languages because it builds on Postgres's existing text search configurations. English, French, German, and other language configurations all work. You can also create separate indexes for different languages on the same table using partial indexes, each covering only rows tagged with a particular language. Beyond plain text columns, pg_textsearch supports expression indexes. This means you can index fields extracted from JSON data, combinations of multiple columns concatenated together, or text that has been transformed before indexing. Partial indexes cover only a subset of rows based on a condition, which keeps the index smaller and queries faster when searches always target a specific category or status. Performance tuning is built in. The extension uses an optimization called Block-Max WAND internally, which avoids scoring documents that cannot possibly rank in the top results of a given query. Parallel index builds are supported for large tables. Partitioned tables are also supported. pg_textsearch is developed by Timescale, released under the PostgreSQL open-source license, and works with PostgreSQL versions 17 and 18.