Use PostgreSQL search before adding a search engine

A substring query is easy to ship. It is also a poor model of what readers mean by search. It does not know that “forms” and “semantic” can be separated by other words; it does not stem common word forms; and ILIKE '%term%' is not, by itself, a ranking strategy. Searching title, summary, and body with three OR conditions gives you a match set, but no principled order. The problem here is not scale. It is that the product has not defined a useful result.

Write down that last line before touching SQL. Search quality is partly retrieval and partly product policy: a draft must stay hidden; a title hit should usually outrank an incidental body hit; the result page should preserve the reader's query so they can revise it. A more powerful engine cannot supply those decisions for you.

There is another subtlety in this example: the published article says “form labels,” while the reader types “forms.” English text search can reduce those related word forms to a common lexeme. That makes this a better test than merely splitting a phrase across columns. It also explains why the result might differ if you switch to the simple configuration or index content in a language whose stemming rules you have not chosen. Write these expectations into a test set before changing configurations.

PostgreSQL full-text search works with a tsvector, a normalized document of searchable terms, and a tsquery, the interpreted request. We will store the document as a generated column so updates to title, summary, or body update the search data in the same transaction. Title gets weight A, summary B, body D. Those letters are ranking weights, not access permissions or relevance percentages. We still filter publication status in the query.

CREATE TABLE articles (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  summary text NOT NULL DEFAULT '',
  body text NOT NULL DEFAULT '',
  status text NOT NULL CHECK (status IN ('draft', 'published')),
  published_at timestamptz,
  search_document tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', title), 'A') ||
    setweight(to_tsvector('english', summary), 'B') ||
    setweight(to_tsvector('english', body), 'D')
  ) STORED
);

CREATE INDEX articles_search_document_gin
  ON articles USING GIN (search_document);

INSERT INTO articles (title, summary, body, status, published_at)
VALUES
  ('Accessible form labels in semantic HTML',
   'Connect labels and inputs without guesswork.',
   'A form is easier to use when every control has a label.',
   'published', now()),
  ('A field guide to page landmarks',
   'Structure a page for navigation.',
   'Semantic HTML also helps readers move around forms.',
   'published', now()),
  ('Unreleased notes on semantic forms',
   'Editorial draft.', 'Not ready for readers.', 'draft', NULL);

The generated column avoids an application-side “update the search index too” call that can be forgotten on one edit path. It does not make an article eligible for search: the draft row has a perfectly valid search_document, but the public query must still exclude it. If your content arrives in multiple languages, do not silently stem every document as English. Store language explicitly and design the indexing and query configuration around your actual content. PostgreSQL's table-search guide describes both generated columns and expression indexes.

websearch_to_tsquery accepts search-box text, including familiar quoted phrases, OR, and minus terms. It does not replace SQL parameterization: pass the reader's string as $1, never splice it into query text. @@ asks whether the document matches. ts_rank_cd ranks matches using the weighted document and positional information. A tie-break by publication time and ID makes the order stable between requests.

WITH input AS (
  SELECT websearch_to_tsquery('english', $1) AS query
)
SELECT a.id, a.title, a.summary,
       ts_rank_cd(a.search_document, input.query) AS rank
FROM articles AS a
CROSS JOIN input
WHERE a.status = 'published'
  AND a.search_document @@ input.query
ORDER BY rank DESC, a.published_at DESC, a.id DESC
LIMIT 20;

On the three sample rows, the first article should be the prominent result: its title includes a form-related term and semantic HTML. The landmarks article may also match because its body mentions both ideas. The draft must not appear. Do not assert a fixed decimal rank from this illustration; ranking depends on the full indexed text and weighting. Ask whether the order serves the reader, not whether the score looks large.

It is tempting to add published_at directly into the relevance score, or a popularity counter with a convenient multiplier. That quickly creates a mystery number that can bury an exact title match. Keep recency as a tie-break until your query set demonstrates a real need for more. PostgreSQL's text-search controls document the query parser and rank functions; they do not define your product's relevance policy.

A result also needs a stable identity. Return the article ID for linking and use the same publication rule on the destination page. Search filtering alone is not access control: if a draft URL is directly reachable, hiding it from results does not protect it. Keep permissions in the page or API that serves the article, and treat search as a second surface that must honor the same policy.

A reader may type “Sematic HTML”. Full-text search will not necessarily repair the misspelled word. PostgreSQL's pg_trgm extension compares three-character sequences and can offer plausible title matches. For this example, use it only after the full-text query returns nothing or too few useful results. Mixing trigram similarity and full-text rank into one uncalibrated score makes the ordering harder to explain and test.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX articles_title_trgm_gin
  ON articles USING GIN (title gin_trgm_ops);

-- Bind 'Sematic HTML' as $1, only on the fallback path.
SELECT id, title, summary, similarity(title, $1) AS closeness
FROM articles
WHERE status = 'published'
  AND title % $1
ORDER BY closeness DESC, id DESC
LIMIT 10;

Keep the UI honest: label these as possible matches, retain the original query in the input, and let the reader edit it. Test short queries separately; trigram indexes are not a magical accelerator for every one- or two-character input. For a catalog where people search partial names inside long titles, word_similarity may be more appropriate than whole-title similarity, but that calls for a separate evaluated query and index plan. The pg_trgm documentation covers the operators, thresholds, and supported index classes.

Search evaluation does not need to begin with a large benchmark. Collect ten real or representative requests and record the top result a reader ought to see. Include one exact title, one two-term query whose words are separated, one phrase, one misspelling, one term that appears only in a draft, and one query that should return nothing. Then run the actual endpoint, not only the SQL console. If your first five results are odd, adding more indexes will not fix the product decision.

Review the list when editors change titles, add content types, or ask for filters. In the UI, show a meaningful title and summary, a link to the article, and the active query. If you later generate highlighted snippets with ts_headline, budget for the extra work and handle the output safely before inserting it into HTML. A good result page is not just a database row rendered in a card.

The GIN index helps find documents matching @@, but it does not make every part of the request free. PostgreSQL still applies visibility filters, calculates rank for candidates, and orders results. If a category filter becomes common, measure the full query with that condition instead of declaring the search solved because an index exists. A partial index or other index design might help a real workload, but the right choice depends on row counts, data distribution, and writes.

ANALYZE articles;

EXPLAIN (ANALYZE, BUFFERS)
WITH input AS (
  SELECT websearch_to_tsquery('english', 'semantic forms') AS query
)
SELECT a.id, a.title,
       ts_rank_cd(a.search_document, input.query) AS rank
FROM articles AS a CROSS JOIN input
WHERE a.status = 'published'
  AND a.search_document @@ input.query
ORDER BY rank DESC, a.published_at DESC, a.id DESC
LIMIT 20;

EXPLAIN (ANALYZE, BUFFERS) runs the query and reports actual timing and buffer activity. Compare plans when the corpus has realistic row counts and term frequencies. Check both a common word that matches many articles and a rare term that matches one. Monitor the search endpoint's tail latency in the application too; a fast database plan does not tell you whether the page, network, or rendering feels fast. The EXPLAIN guide explains why estimates and actual rows can differ.

A dedicated search service is useful when you have a concrete requirement that this implementation handles poorly: rich faceting across several content types, sophisticated autocomplete and spelling suggestions, language-specific analysis, personalized ranking, or latency targets that the measured PostgreSQL workload cannot meet without unacceptable tradeoffs. “We have a few thousand articles” is not, by itself, a migration trigger. Nor is “PostgreSQL can do search” proof that it should own every search feature forever.

If you migrate, keep the acceptance queries. Run them against both implementations and compare the order people see, not just response time. Write down how publication status, edits, and deletions reach the new index, and what readers see when synchronization lags. A second search system introduces a consistency boundary: a draft published in the database may not be searchable yet, or a withdrawn article might linger if updates fail. Those are manageable problems, but they belong in the decision.

Plan the rollback at the same time as the migration. If the new service is unavailable, can the product temporarily return to a simpler PostgreSQL result set? If not, what message does the reader see? Do not promise an invisible failover if relevance, filters, or freshness would change. A deliberate degraded mode—with an honest label and a working path to content—is better than a spinner that never completes.

The useful starting point is smaller: one weighted document, one parameterized full-text query, one explicit publication filter, a narrow typo fallback, and a set of reader queries you can inspect. You can improve relevance from there—or justify a separate engine with evidence instead of instinct.