Question 1

What's the difference between tsvector and a full-text search extension?

Accepted Answer

tsvector is Postgres' built-in document type for lexical search — you create a tsvector column, build a GIN index, and query with to_tsquery + ts_rank. It works well for small-to-medium corpora and basic ranking. FTS extensions like pg_search add BM25 (a modern, stronger ranking algorithm), highlighting, faceted filtering, multi-field boosting, and faster index builds via Tantivy. For under 100k documents, tsvector is fine. Above that, pg_search delivers Elasticsearch-grade performance without leaving Postgres.

Question 2

Should I use Postgres full-text search or Elasticsearch?

Accepted Answer

Stay on Postgres with pg_search when your search workload is text-only against your primary database — you get one auth model, one backup story, one transactional boundary. Move to Elasticsearch when you need: cross-cluster replication across regions, sub-50ms search at high QPS over tens of millions of docs, real-time indexing of arbitrary JSON schemas, or built-in features like learned-to-rank and percolators. For 90% of B2B SaaS product search, pg_search is enough and significantly simpler to operate.

Question 3

What is BM25 and why does it matter?

Accepted Answer

BM25 (Best Matching 25) is the relevance-ranking algorithm used by Elasticsearch, Lucene, and most modern search engines — it considers term frequency, inverse document frequency, and document length normalization to score how well a query matches each document. It's significantly more relevant than Postgres' default ts_rank for natural-language queries. pg_search and pg_bestmatch implement BM25 inside Postgres, giving you Elasticsearch-quality ranking without leaving the database.

Question 4

How do I install pg_search?

Accepted Answer

On managed services: pg_search is currently available via ParadeDB's managed offering or self-hosted Docker images — AWS RDS, Supabase, and Neon do not yet ship pg_search in their default extension allow-lists (check the latest docs). On self-hosted Postgres: use ParadeDB's Docker image (paradedb/paradedb) for the simplest setup, or build from source against your existing Postgres install. After installation, run CREATE EXTENSION pg_search; and create a BM25 index with CREATE INDEX ... USING bm25.

Question 5

Does pg_search work on managed Postgres (RDS, Supabase, Neon)?

Accepted Answer

Currently no. pg_search requires a shared library and isn't in the default extension allow-lists of major managed providers. ParadeDB offers its own managed service, or you can self-host with their Docker image. AWS RDS, Aurora, Supabase, and Neon support tsvector + GIN out of the box (good enough for most cases), pg_trgm for fuzzy matching, and Aurora/Neon support some additional ranking via pg_bestmatch on certain versions — check the provider's current extension list.

19+ Full-text Search Extensions for PostgreSQL

What is a PostgreSQL Full-Text Search Extension?

When to Add a Full-Text Search Extension

Frequently Asked Questions

Manage PostgreSQL Visually