Postgres Full Text Search vs the rest

Summary

The post compares PostgreSQL Full Text Search with dedicated search engines including MeiliSearch, OpenSearch, SQLite FTS, and Typesense, asking whether a separate system is justified for common application search. It presents a PostgreSQL setup using a generated stored column built with to_tsvector('english', ...) and a GIN index, queried with websearch_to_tsquery. The benchmark loads a movie dataset and focuses on query speed after data ingestion, using comparable scripts and default configurations with limited tuning. PostgreSQL was close in performance to several alternatives; in-memory SQLite was fastest, while Typesense and MeiliSearch handled misspellings correctly and OpenSearch ingested data quickly. The conclusion is that PostgreSQL can provide good-enough search quality and speed for basic cases without adding another system, although dedicated engines offer capabilities PostgreSQL does not.

Takeaways

1. 1

   PostgreSQL Full Text Search can combine fields into a generated tsvector column and use a GIN index for faster searches; queries can be issued with websearch_to_tsquery.

2. 2

   In the reported small movie dataset, in-memory SQLite was fastest and PostgreSQL was not far behind, while PostgreSQL was generally close in performance to several other systems in their default configurations.

3. 3

   Typesense and MeiliSearch handled the misspelled “suprman” query, whereas OpenSearch’s default configuration did not index misspellings; Typesense’s match strictness is configurable.