Users search, get nothing useful, and go back to scrolling. Analytics show a high rate of searches followed by no click. Almost always the implementation is a substring match against one or two columns, which cannot handle word order, misspellings, plurals or any notion of which result matters most.
01Substring matching is not search
A LIKE query with wildcards on both sides matches literal character sequences. It fails on a transposed letter, on plural forms, on word order differences and on partial phrases. It also cannot use a standard index, so it degrades badly as the table grows.
Critically, it has no concept of ranking. Every matching row is equally relevant, so results come back in whatever order the database produces, which is generally insertion order. The best match may be on the fourth page.
| Capability | SQL LIKE | Postgres full text | Search engine |
|---|---|---|---|
| Word order independence | No | Yes | Yes |
| Stemming (plurals, tenses) | No | Yes | Yes |
| Relevance ranking | No | Basic | Configurable |
| Typo tolerance | No | With trigram support | Built in |
| Synonyms | No | Manual | Built in |
| Faceting and filters | Manual | Manual | Built in |
| Operational cost | None | Low | Separate service |
02Postgres full text is usually enough
For most products, a generated tsvector column with a GIN index and ranking handles the common cases well without introducing a separate system to operate, secure and keep synchronised. Adding trigram support alongside it provides reasonable tolerance for misspellings.
Reserve a dedicated search engine for cases that genuinely need it: very large corpora, complex faceting, or relevance tuning as an ongoing product concern. Introducing one early adds an entire synchronisation and consistency problem for benefits the product may not require.
03Ranking is what users perceive as quality
Users judge search by the first three results. Matching correctly but ordering poorly reads as broken. Ranking should weight fields differently - a match in the title matters far more than one in a long description - and incorporate signals like recency or popularity where they are meaningful.
Include the user's context. In a multi-tenant product, results from their own organisation should outrank generic matches, and items they interacted with recently are usually more relevant than older ones.
| Metric | Indicates |
|---|---|
| Zero-result rate | Vocabulary or tolerance gaps |
| Searches with no click | Poor ranking |
| Position of the clicked result | How well ordering matches intent |
| Immediate re-search | First attempt failed |
| Most common queries | What to optimise first |
04Read the queries that return nothing
The log of zero-result searches is the most useful artefact available. It shows the words users actually use, which routinely differ from your internal terminology - they search for the industry term while your data holds the product name.
Address that with a synonym list rather than by expecting users to learn your vocabulary. A short list covering the fifty most common mismatches typically produces a larger improvement than any change to the ranking algorithm.
05Keep the index synchronised
The moment search lives outside the primary database, the index can drift from the data. Records updated without reindexing produce results that show stale titles or reference deleted items, which destroys trust in search faster than poor ranking does.
Drive indexing from the same transaction or from a change stream, and run a periodic reconciliation that detects divergence. A generated column in Postgres avoids this class of problem entirely, which is a substantial argument for staying within the database while it remains sufficient.
Topics
Priya Iyer
Staff Engineer · SyncTrix
Writes about the engineering decisions behind production systems - architecture, delivery and the trade-offs that only show up at scale.
Building something like this?
SyncTrix engineers AI, SaaS, platform and cloud systems for enterprises and high-growth teams. Tell us what you're shipping and we'll scope it with you.
Talk to an engineer