Full Text Search in PostgreSQL: Practical Patterns

What you actually need to know

• Postgres full text search handles most SaaS search needs.
• Store the tsvector. Index with GIN. Query with tsquery.
• Combine with pg_trgm for typo tolerance.
• Use ts_rank for relevance ordering.
• Move to Elasticsearch only when you hit specific limits.

The core argument

Postgres full text search is good enough for most SaaS. The setup is small. The performance is solid. The features are sufficient. Most teams reach for Elasticsearch or Algolia preemptively when Postgres would have served them for years.

The pattern is consistent. The team thinks search is hard. The team reads about Elasticsearch. The team adds a new database to the stack. The team has to keep Postgres and Elasticsearch in sync. The complexity grows. The actual search needs of the application could have been served by Postgres.

The mental shift is to recognize that full text search in Postgres is not the same as the LIKE pattern matching that most engineers learn first. Postgres full text uses linguistic processing. Stop words are removed. Stems are matched. The query for write matches the document containing writes and writing. The matching is closer to what users expect.

The setup is mechanical. Add a tsvector column to the table, maintained by a generated column or trigger. Create a GIN index. Query with the right operators. The whole setup is roughly an afternoon for a senior engineer.

The performance is solid into millions of rows. A B2B SaaS with ten million documents can run Postgres full text search with sub hundred millisecond latency on most queries. The teams that move to Elasticsearch at smaller scale usually did not need to.

The case for moving exists. Very high query volume. Tens of millions of documents. Advanced faceting or semantic similarity. Each is a real reason. Below these, Postgres usually serves better than the alternatives because it stays in the existing database without the sync complexity.

The patterns that work

How much does this cost

The cost is engineering time. An afternoon for the basic setup. A day or two for the more advanced patterns like ranking, typo tolerance, and highlighting. The infrastructure cost is zero because it lives in your existing Postgres. The savings versus moving to Elasticsearch is the operational complexity that does not happen.

Features the search setup must have

• Stored tsvector with GIN index.
• Ranking with ts_rank.
• Highlighting for the result preview.
• Typo tolerance with pg_trgm where helpful.
• Language configuration matching the content.
• A way to update the tsvector when documents change.
• Monitoring on query latency.
• A path to move to Elasticsearch if you outgrow Postgres.

Expert opinion

Most B2B SaaS that adopted Elasticsearch could have stayed on Postgres full text search for years. The operational complexity of Elasticsearch is real. The Postgres setup is mechanical. The performance is sufficient into the millions of rows. The teams that moved early are usually paying for capability they do not use. The teams that stayed on Postgres until they hit real limits saved meaningful engineering time.

>

Yashveer Singh, founder of Yashveer Labs

How this played out on a real project

A client SaaS was about to add Elasticsearch for search. The team had read about it and assumed it was necessary. The documents were in the low hundreds of thousands. The query volume was modest.

We built the search on Postgres full text instead. Generated tsvector column. GIN index. Ranked queries. Highlight. Typo tolerance with pg_trgm. The setup took two days. The performance was sub fifty milliseconds on every query.

The team avoided adding Elasticsearch to the stack. The operational complexity did not happen. The features were sufficient for what the customers actually needed. Two years later the team is still on Postgres for search and has not hit a limit that would justify moving.

For more on the related work, see the search problem why adding it late always hurts and Algolia vs Typesense vs Meilisearch vs Postgres full text.

Common mistakes teams make

1. Adopting Elasticsearch preemptively.
2. Computing tsvector on query instead of storing it.
3. No GIN index.
4. No ranking. Results in arbitrary order.
5. No typo tolerance.
6. Wrong language config.
7. No monitoring on search latency.
8. Treating LIKE pattern matching as full text search.

A two day setup plan

1. Day one. Add the tsvector column. Create the GIN index. Build the basic query.
2. Day two. Add ranking, highlighting, and typo tolerance. Test against realistic data volume.

For more on the related work, read Algolia vs Typesense vs Meilisearch vs Postgres full text and building an AI powered search that actually works. On the broader search side, SaaS search at scale Postgres full text vs Algolia vs Typesense is the natural next read.