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.

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:

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:

• Instance: AWS EC2 i7ie.xlarge with local NVMe SSD (minimizing I/O impact).

• CPU: 4 vCPUs.

• PostgreSQL Configuration: PostgreSQL 16 via Docker, configured for the hardware:

    docker run -d \
      --name my-postgres \
      --network=host \
      -v /mnt/localssd/pgdata:/var/lib/postgresql/data \
      -e POSTGRES_PASSWORD=mysecretpassword \
      postgres:latest \
      -c shared_buffers=8GB \
      -c maintenance_work_mem=8GB \
      -c max_parallel_workers=4 \
      -c max_worker_processes=4
  

• Parallelism Note: This setup provides max_parallel_workers_per_gather = 2. The Neon post mentioned their test environment used 8 parallel workers due to larger instances, implying potentially higher parallelism (max_parallel_workers_per_gather up to 8) than our setup. Our optimized results for standard FTS were achieved with less query parallelism.

Mistake #1: Calculating tsvector On-the-Fly (Major issue)

The sample queries shown in the Neon blog (and common in basic FTS examples) calculate the tsvector within the WHERE clause:

WHERE to_tsvector('english', message) @@ to_tsquery('english', 'research')

This forces PostgreSQL to:

1. Perform Expensive Computation: Run to_tsvector() (parsing, stemming, etc.) repeatedly for many rows during query execution.

2. Limit Index Efficiency: Prevent the most direct and efficient use of the GIN index, even if one exists on the base message column.

The Fix (For a Proper Standard FTS Baseline): Pre-calculate and store the tsvector.

1. Add a tsvector column:

    ALTER TABLE benchmark_logs ADD COLUMN message_tsvector tsvector;
   

2. Populate the column:

    UPDATE benchmark_logs SET message_tsvector = to_tsvector('english', message);
   

3. Index the tsvector column (with fastupdate=off): (As shown in Fix #1)

4. Rewrite queries:

    
    SELECT country, COUNT(*)
    FROM benchmark_logs
    WHERE message_tsvector @@ to_tsquery('english', 'research') 
    GROUP BY country
    ORDER BY country;
   

Mistake #2 : Ignoring GIN Index fastupdate (Minor issue)

While Neon’s benchmark correctly identified GIN as the index type for standard FTS, it likely used the default setting: fastupdate=on.

• fastupdate=on (Default): Prioritizes index update speed by using pending lists. This significantly slows down searches over time, especially on large, static datasets used for benchmarks, as both the main index and growing pending lists must be scanned. It also contributes to index bloat and less efficient page structure.

• fastupdate=off: Prioritizes search speed. Index updates are slower, but the resulting index is more compact and significantly faster to query as there are no pending lists to scan. Essential for read-heavy workloads or benchmarking search performance.

For a fair comparison focused on search speed, the standard FTS baseline should have used fastupdate=off.

The Fix (For a Proper Standard FTS Baseline):

CREATE INDEX idx_gin_logs_message_tsvector
ON benchmark_logs USING GIN (message_tsvector) 
WITH (fastupdate = off);

The Performance Impact: A ~50x Speedup for Standard FTS

What happens when we apply these standard optimizations to the standard PostgreSQL FTS setup? We tested a query in Neon’s examples on the 10-million-row dataset:

This demonstrates a ~50x speed improvement for standard PostgreSQL FTS achieved simply by applying well-established best practices. This transforms the baseline performance dramatically, suggesting the comparison point used in the Neon benchmark may not have represented the true potential of built-in FTS. This speedup was achieved even with potentially less query parallelism available than in Neon’s tests.

The Grain of Truth: Ranking (ts_rank) Performance

Neon’s analysis did likely highlight a valid point regarding ranking speed. Functions like ts_rank or ts_rank_cd can be relatively slow in standard PostgreSQL compared to just finding matches. This is because ranking requires fetching and processing data for all matching rows before limiting, which can be I/O and CPU intensive.

Beyond Standard FTS: Advanced Ranking with VectorChord-BM25

While standard FTS excels at finding matches quickly, if sophisticated, high-performance ranking is critical, specialized tools are often required. Instead of relying solely on built-in functions, consider VectorChord-BM25.

VectorChord-BM25 is a PostgreSQL extension specifically designed for fast, relevance-based ranking using the well-regarded BM25 algorithm. BM25 goes beyond simple term matching, estimating relevance based on term frequency within a document and inverse document frequency across the entire collection.

Key advantages reported for VectorChord-BM25 include:

• High Performance: Designed for speed, reportedly outperforming other solutions like Elasticsearch (up to 3x faster) and potentially pg_search for ranking tasks.

• BM25 Algorithm: Implements a standard, effective relevance ranking model.

• Specialized Indexing: Uses a dedicated bm25 index type (leveraging optimizations like Block WeakAnd) which is crucial for both performance and calculating the global statistics needed for BM25 scoring.

• Dedicated Data Type: Introduces a bm25vector type to store tokenized representations suitable for BM25.

If your application heavily relies on the quality and speed of relevance ranking, and standard ts_rank proves insufficient, VectorChord-BM25 presents a powerful, integrated alternative within PostgreSQL.

Conclusion: Standard FTS is Faster Than You Might Think

Standard PostgreSQL FTS is a highly capable and performant feature for finding documents when correctly optimized using tsvector columns and properly configured GIN indexes (with fastupdate=off for reads). Benchmarks suggesting otherwise may be comparing against unoptimized baselines.

For advanced relevance ranking where standard ts_rank falls short, specialized extensions like VectorChord-BM25 offer significant performance improvements by implementing algorithms like BM25 with dedicated data types and optimized indexing strategies.

Choose the right tool for the job: optimize standard FTS first, and if high-performance relevance ranking is paramount, explore dedicated extensions built for that purpose.

Keep your files stored safely and securely with the SanDisk 2TB Extreme Portable SSD. With over 69,505 ratings and an impressive 4.6 out of 5 stars, this product has been purchased over 8K+ times in the past month. At only $129.99, this Amazon’s Choice product is a must-have for secure file storage.

Help keep private content private with the included password protection featuring 256-bit AES hardware encryption. Order now for just $129.99 on Amazon!

Help Power Techcratic’s Future – Scan To Support

If Techcratic’s content and insights have helped you, consider giving back by supporting the platform with crypto. Every contribution makes a difference, whether it’s for high-quality content, server maintenance, or future updates. Techcratic is constantly evolving, and your support helps drive that progress.

As a solo operator who wears all the hats, creating content, managing the tech, and running the site, your support allows me to stay focused on delivering valuable resources. Your support keeps everything running smoothly and enables me to continue creating the content you love. I’m deeply grateful for your support, it truly means the world to me! Thank you!