PostgreSQL Full-Text Search: Test the Search You Actually Need

A product search box needs more than a query that returns rows. Does “green tea” mean two words anywhere, or an adjacent phrase? Should a free guide appear when the maximum price is zero? Does a renamed product disappear from searches for its old name immediately?

Those questions make a useful starting point for deciding whether PostgreSQL search is enough. A record-count cutoff does not. Document length, language, filters, write volume, acceptable latency and ranking requirements all change the decision.

This experiment runs those questions against four rows in a real, disposable PostgreSQL database. It demonstrates behavior, not a PostgreSQL-versus-Elasticsearch benchmark.

Give the database a search document

A tsvector contains normalized lexemes and, normally, their positions. An English configuration can reduce “running” to a stem shared with “run”. A tsquery describes which lexemes should match. Use the same intended configuration for both sides; relying on a connection's default makes behavior harder to reproduce.

The fixture gives names weight A and descriptions weight B. A generated column keeps that representation attached to the row:

 search_vector tsvector GENERATED ALWAYS AS (
  setweight(to_tsvector('english', name), 'A') ||
  setweight(to_tsvector('english', coalesce(description, '')), 'B')
 ) STORED

coalesce matters because a null description should not erase a perfectly searchable name. The schema also creates a GIN index over this column. PostgreSQL's table and index documentation describes this generated-column approach and why an explicit configuration matters.

The four products deliberately differ: “Green tea”, a free “Tea guide” whose description includes “green tea”, “Tea green sampler” with no description, and “Running shoes”. These are small enough to inspect without trusting a score you cannot explain.

There is one boundary to understand before copying the schema. Concatenating vectors combines their positional space. If phrases must stay inside one field, test the fields separately; the combined representation is not a field-boundary rule. Product names and descriptions also need a language policy when your catalog is multilingual.

Turn a search box into a query

For this fixture, websearch_to_tsquery is a useful input language. Ordinary words imply AND, quotes express a phrase, OR allows alternatives, and a minus sign excludes a term. It tolerates malformed punctuation instead of treating the text as strict tsquery syntax. That does not make string interpolation into SQL safe: application values still belong in bound parameters. These operators are documented in query parsing.

Here is the query inside the tested SQL function:

 SELECT p.id, ts_rank(p.search_vector, query) AS score
 FROM products p CROSS JOIN websearch_to_tsquery('english', q) query
 WHERE p.search_vector @@ query
   AND (maximum IS NULL OR p.price <= maximum)
 ORDER BY score DESC, p.id

The optional price limit uses NULL to mean “no limit”. Zero is a real limit, so searching for tea with a maximum of zero returns only the free guide. In application code, preserve that distinction: Python's if max_price: would accidentally discard zero. Test the value's absence, not its truthiness.

The result order includes id after score so ties are stable in this fixture. A cursor-based production search needs an explicit pagination design too; adding a limit alone does not solve changing result sets.

Matching and ranking answer different questions

Searching for tea matches three rows. Quoting “green tea” matches two: the product name and the guide's description. The reversed name does not satisfy that phrase. tea -guide returns the two remaining tea products. An input containing only English stop words returns no rows here; decide whether your interface should explain that outcome.

Weights influence ranking rather than creating an unconditional “name always wins” rule. The test compares one identical term in an A-weight vector with the same term in a B-weight vector. The A score is higher. A longer real document can have different frequencies and combinations, so inspect actual top results before choosing weights.

ts_rank does not apply document-length normalization by default. Its optional normalization argument changes the calculation. It also does not make scores into probabilities or portable relevance percentages. PostgreSQL explains the available rules in its ranking reference.

Avoid rendering ts_headline output as trusted HTML from arbitrary product text. Highlighting marks matches; it is not an HTML sanitizer. The highlighting documentation explicitly calls out this boundary. This experiment returns identifiers and scores, leaving safe presentation to the application.

Run the fixture, then replace its assumptions

Download the experiment files. With Python 3 and PostgreSQL server tools installed, set PGBIN to their directory and run python3 run.py.

The runner initializes its own temporary cluster, disables TCP, connects through a private Unix socket, executes the SQL, then stops the server and removes its data. It never connects to your application's database. The recorded run used PostgreSQL 14.13 and passed twelve SQL assertions, including an update that removes the old search term and introduces the new one within the same transaction. Use a maintained PostgreSQL installation for your own environment; this version records the local experiment rather than a deployment recommendation.

The four-row query plan chose a sequential scan even though the GIN index existed. That is an observed plan for this tiny fixture, not an index failure. For a representative catalog, collect EXPLAIN (ANALYZE, BUFFERS), latency distributions, write costs and relevance judgments under realistic filters and load.

Stay with PostgreSQL when that measured search meets your requirements and keeping search beside transactional data simplifies the system. Consider a separate engine when a concrete unmet requirement justifies synchronization, deployment and recovery work. The useful result is a tested search contract, not a universal number of rows.