Postgres Full-Text Search in Production: How to Load-Test the Index and Pin Down Relevance

Adding full-text search to PostgreSQL is simple, yet ensuring it works smoothly in production can be challenging. The GIN index that accelerates searches also increases write amplification and runs background cleanup, which can be overwhelmed by heavy insert rates. Relevance results that worked on a developer's machine might change when a dictionary is altered. To validate Postgres search capabilities, you need actual load testing against real write rates and a way to measure relevance, not just counts.

This post focuses on the operational aspects of using GIN indexes in production. The GIN index maps lexemes (words) to row lists, requiring each row write to update many index entries. Fastupdate defers index updates until autovacuum or a pending list threshold is exceeded, but this still adds write amplification. If the pending list grows faster than autovacuum can clean it, searches will scan an unsorted list, slowing down query performance.

Tuning options include reducing the pending list size or turning off fastupdate, but both require load testing to determine if autovacuum can handle the peak write rate.

Beyond performance, relevance is crucial. Changes to search configuration can silently degrade results without triggering any alerts. A relevance contract quantifies these changes by explicitly defining the configuration per field and language, building a small set of judged queries with expected results, and monitoring metrics like zero-result rate and precision@10.

By testing under real write conditions and measuring both transactional performance and relevance, you can confidently evaluate whether PostgreSQL's full-text search meets your needs.