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.
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:
- Parsed into tokens (words, numbers, email addresses and so on).
- Converted into lexemes, normalized forms: for example folding to lower case and often removing suffixes, and removing stop words (very common words).
- 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
- Decide what a "document" is for your product (a job post, a listing, a profile) and which fields count.
- Decide which fields deserve higher weight. This shapes how results feel.
- Filters (location, salary range, skills) are separate from text relevance. The PostgreSQL pages we read cover text relevance only.
- "Matching" candidates to roles may involve more than text search. We did not research it, and we make no claim about accuracy or fairness. If a system ranks people, test it for unfair results and take advice on any rules that apply.
- Search quality needs testing with real queries. Ask your developer for a list of test searches and expected results.
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.