OOmnistu

Blog / Engineering Playbooks

Engineering Playbooks

How search works in an app: full-text search, ranking and what "matching" really means

Understand PostgreSQL full-text search, query parsing and ranking without confusing text relevance with a complete matching system.

OOmnistu
Reviewed 2026-11-16 · 3 min read

Job boards, marketplaces and catalogues all need a search box that returns useful results. This article explains the building blocks using the PostgreSQL 18 documentation on full text search (https://www.postgresql.org/docs/18/textsearch-intro.html). Other search systems exist and we did not research them. We also do not describe how any particular product matches candidates to jobs.

Why LIKE is not enough

The PostgreSQL docs say the simple text operators (LIKE, ILIKE and regular expressions) lack properties modern search needs: no linguistic support, so searching for "satisfy" can miss "satisfies"; no ranking, which makes them ineffective when thousands of documents match; and these operators do not themselves provide the full linguistic and relevance behavior of a full-text search system. Do not read the introductory page as a universal claim that LIKE or ILIKE cannot use an index: PostgreSQL's pg_trgm documentation explicitly supports trigram-based LIKE, ILIKE and regex searches (https://www.postgresql.org/docs/18/pgtrgm.html). The suitable index depends on the query.

What full text search does

Per the docs, full text search identifies natural-language documents that satisfy a query and can sort them by relevance. Documents are preprocessed:

  1. Parsed into tokens (words, numbers, email addresses and so on).
  2. Converted into lexemes, normalized forms: for example folding to lower case and often removing suffixes, and removing stop words (very common words).
  3. Stored in an optimized form, with optional position information so documents where query words are close together can rank higher.

Dictionaries give fine control: stop words, synonyms, thesaurus phrases and stemming rules. A "document" is the unit of search, and in a database it may be a combination of fields, such as title, author and body, not stored anywhere as one piece. The docs advise coalesce so a single NULL field does not make a concatenated document NULL. An empty string is not the same as NULL.

Weights: title matters more than body

The docs describe labelling parts of a document with weights A to D, typically to mark title versus body, so a match in the title can rank above a match in the body. Their example gives the title weight A, keywords B, abstract C and body D. Choosing those weights is a product decision, not a technical default.

Turning what users type into a query

PostgreSQL provides several functions to convert query text. The docs say to_tsquery offers more features but is less forgiving of input, while websearch_to_tsquery is a simplified version designed for search-box style input. The docs also note that plainto_tsquery joins surviving words with AND and does not recognize operators.

What this means for a founder

Limits

We read two sections of one database's docs. We did not read about index types for text search, ranking function details or other engines such as Elasticsearch.

By Omnistu.

Sources

Previous
REST API security: a checklist for app teams