<aside> 📘

Agument Code Series：

How we made code search 40% faster for 100M+ line codebases using quantized vector search - Augment Code

How to build your Agent: 11 prompting techniques for better AI agents - Augment Code

📒 Integrate BM25:

🤖 Combining Embeddings with BM25 for Chunk Indexing

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

📒 ANN

🤖 Implementing ANN-Based Semantic Search with Go and PostgreSQL

Optimize generative AI applications with pgvector indexing: A deep dive into IVFFlat and HNSW techniques | AWS Database Blog

https://github.com/pgvector/pgvector

📒 RAG Related:

https://blog.laisky.com/p/llm-rag/

Contextual Retrieval in AI Systems \ Anthropic

</aside>

PostgreSQL Full-Text Search: Fast When Done Right (Debunking the Slow Myth)

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.

The Benchmark Setup (Recap from Neon's Analysis)

The analysis used a table structure similar to this with 10 million log entries:

CREATE TABLE benchmark_logs (
    id SERIAL PRIMARY KEY,
    message TEXT,
    country VARCHAR(255),
    severity INTEGER,
    timestamp TIMESTAMP,
    metadata JSONB
);

They tested various query types. Many involved searching the message text field, using queries structured like this in their "standard Postgres" examples:

-- Create index on to_tsvector('english', message)
CREATE INDEX message_gin ON benchmark_logs USING gin (to_tsvector('english', message));
-- Example problematic query structure (likely used in Neon's baseline)
SELECT country, COUNT(*)
FROM benchmark_logs
WHERE to_tsvector('english', message) @@ to_tsquery('english', 'research')
GROUP BY country
ORDER BY country;

Even with a GIN index present on message, this query structure and the likely default GIN index settings are where the performance issues for the standard FTS baseline begin.

Our Test Environment Details (Replicating the Baseline Scenario)

To demonstrate the impact of proper optimization on standard FTS, we used the following setup: