🔗 PostgreSQL BM25 Full-Text Search: Speed Up Performance with These Tips

You might have come across discussions or blog posts suggesting that PostgreSQL's built-in full-text search (FTS) struggles with performance compared to dedicated search engines or specialized extensions. A notable recent example comes from Neon's blog post, "Performance Benchmark: pg_search on Neon" (link).

In their benchmark, Neon compared query performance on their database platform with their pg_search extension (based on Rust's Tantivy library via pgrx) against the Postgres built-in fulltext search setting with tsvector and GIN index. They commendably stated they optimized this standard setup by adding GIN indexes where appropriate (benchmark code available here).

However, while adding GIN indexes is a necessary first step, their results showing significantly slower performance for the "standard" setup suggest crucial additional optimization steps for PostgreSQL FTS were likely missed. The conclusion that standard FTS is inherently much slower than pg_search might be based on an unintentionally handicapped baseline.

Let's dive into how to correctly set up and use standard PostgreSQL FTS for optimal performance, addressing the specific configuration flaws likely present in the baseline used in the Neon benchmark, and demonstrating the true speed of built-in FTS. We'll show concrete numbers demonstrating a ~50x performance increase just by applying these standard optimizations to the baseline configuration.