PostgreSQL search for SaaS products: ILIKE, trigrams or full text?

Choose PostgreSQL search from the user's matching task: ILIKE for an explicit substring, trigram similarity for tolerant name matching, or full text for language-aware document queries and ranking. These mechanisms answer different questions. Build a small relevance fixture before adding an index, and measure execution separately from whether users receive the results they intended.

Write expected matches before SQL

Create a fictional catalogue with short names, descriptions and identifiers. Include an exact fragment, a misspelled name and a multiword description query. Ask a product owner which results should appear and in what order. A substring test can be correct for a code lookup but frustrating for a document search; a stemmed linguistic search can be useful for prose but surprising for an exact identifier.

PostgreSQL full text search uses configurations, lexemes and text queries rather than plain substring matching. pg_trgm supplies similarity operators and index support for tolerant string matching. The selected text-search configuration, language and normalization affect results, so keep them in the API contract rather than relying on an unspecified database default.

PostgreSQL: full text search concepts and configuration

PostgreSQL 18: trigram similarity and index support

Three synthetic query shapes

The following fragments illustrate different tasks against a disposable table. Parameter values must be bound by the client. The full text example states an English configuration; a multilingual product needs a deliberate language design. No query or timing comparison was executed for this article, and the snippets do not establish that a particular index will be selected.

-- Literal fragment task; escape wildcard characters when literal meaning is intended.
WHERE display_name ILIKE '%' || $1 || '%'
-- Tolerant name task; choose and test the similarity threshold.
WHERE display_name % $1
-- Linguistic description task with an explicit configuration.
WHERE to_tsvector('english', description)
   @@ websearch_to_tsquery('english', $1)

An input containing percent or underscore changes LIKE semantics unless the interface deliberately treats it as a wildcard query or escapes it. Parameterization prevents values from becoming SQL syntax, but does not decide that matching policy. Trigram thresholds also need relevance testing: a short name can behave differently from a long phrase. Full text stemming and stop words can remove terms that a user thought were exact.

Match the index to the expression

For frequent substring or similarity searches, evaluate the appropriate pg_trgm GIN or GiST strategy under your operators and ordering. For document search, evaluate a stored or expression tsvector with an appropriate index. Ensure the query uses the same configuration and expression the index supports. A generic B-tree on the raw text is not a universal accelerator for every leading-wildcard or linguistic query.

Bound rows and response bytes and define a deterministic tie-breaker after relevance rank. Keep tenant filtering and authorization in the query path, including autocomplete and counts. Search suggestions can reveal records even when the detail endpoint is protected. A result cache must include account scope, query normalization and relevant filter inputs rather than only the user's raw phrase.

Evaluate relevance and operating cost separately

For each fixture query, record expected relevant IDs, observed results and ordering. Then capture execution plans and timings under representative data. A faster query that misses the intended record is not a successful search improvement. Also record index size, write maintenance and how updated descriptions become searchable; these are operational costs that a single read benchmark misses.

Start with the smallest mechanism that satisfies the known task. It is reasonable to combine an exact identifier path with tolerant name search and a separate document query, provided the interface explains the behavior. Avoid silently falling back through every mechanism until something appears; that makes relevance and cost unpredictable. Bring the expectation set and one query plan to a scoped review so the implementation can be judged against observable user intent.

Firestore or PostgreSQL? Start with the product’s data.

Building UltraFit360: the app, coach portal and shared backend

Bring one API or data workflow and a sanitized failure example for a bounded implementation assessment.

AptixLabs saas development