Why Search Is Such a Hard Thing to Talk About
Search sounds like one feature, but it is really a bundle of product decisions, customer expectations, matching strategies, ranking tradeoffs, and business context.
Search sounds like one feature.
Add a search box. Let people type something. Show the right results.
Easy.
Except it is rarely easy, because “search” is not one thing. It is a whole pile of different behaviours that users expect to feel obvious, even when those behaviours are technically very different.
One person types a full name and expects an exact match. Another types the first three letters of a last name and expects autocomplete. Someone else misspells the name and still expects the system to know what they meant. A sales team wants the most commercially important customer at the top. Support wants the most recent ticket. Operations wants every possible match, even if the order looks messy.
All of those are reasonable requests.
They are also different search systems.
That is why search is such a hard thing to talk about. The conversation usually starts with technology, but the real problem is expectation management.
Search Is a Product Decision First
Before choosing a tool, it helps to ask a boring question:
What is the user actually trying to do?
That sounds obvious, but it changes everything.
If a user is looking for a known customer, exact search might be enough. If they are exploring a large list, partial matching matters. If the data contains typos, fuzzy matching matters. If the content is long-form, full-text search matters. If some results are more valuable to the business than others, ranking matters.
The trap is treating all of that as one universal search box.
It usually is not.
A customer lookup field, a document search page, a product catalogue, a CRM contact finder, and an internal admin table can all have “search” on the screen. But they should not always behave the same way.
The Simple Version: Exact Search
The simplest search is when the user knows exactly what they are looking for.
For example, maybe your table has people in it:
select *
from people
where last_name = 'Lawton';This is fast, clear, and predictable.
If the last name is exactly Lawton, it matches. If it is Lawten, Laughton, or Lawton-Smith, it does not.
That is not a bug. That is the contract.
Exact matching is great when the user has a precise identifier:
- an email address
- an order number
- a customer ID
- a SKU
- a known account name
The upside is confidence. When the result appears, everyone understands why.
The downside is brittleness. Humans do not always know the exact spelling, exact casing, exact punctuation, or exact format.
The Common Version: Partial Search
Most users do not search like databases search.
They type fragments.
Maybe they know the start of a last name:
select *
from people
where last_name ilike 'law%';That finds names starting with law, such as Lawton, Lawrence, or Laws.
This is useful for autocomplete and directory-style search. The user starts typing and the list narrows.
Sometimes they want the fragment to appear anywhere:
select *
from people
where last_name ilike '%ton%';That might find Lawton, Ashton, Norton, or Templeton.
This feels more flexible, but it gets messy quickly. The more permissive the match is, the more irrelevant results you can pull in.
From a business point of view, this is the first major tradeoff:
- stricter search gives fewer but cleaner results
- looser search gives more results but creates more noise
Neither is universally better. It depends on the job.
If a support agent is trying to find one customer while on the phone, noise is expensive. If an analyst is exploring a dataset, missing possible matches might be more expensive.
The “I Know Part of It” Problem
Names are a good example because people search for them in different ways.
One person starts with the last name because that is what they remember:
select *
from people
where last_name ilike 'smith%';Another person starts with the first name and a rough idea of the last name:
select *
from people
where first_name ilike 'john%'
and last_name ilike 's%';Another person types the whole thing into one box:
select *
from people
where first_name || ' ' || last_name ilike '%john smith%';Each of those can be valid.
But they do not mean the same thing.
This is where teams start to discover that “just add search” is not specific enough. Are we searching first name? Last name? Full name? Email? Company? All of them? Should John S match John Smith, Johnny Stone, or Sarah Johnson?
The technical query matters, but the product decision matters first.
The Typo Problem
Then comes the next layer: people make mistakes.
They type Jon instead of John. They type Smyth instead of Smith. They hear a name on the phone and guess the spelling. They copy data from a spreadsheet that already had mistakes in it.
Postgres can help here with trigram similarity using the pg_trgm extension.
select *
from people
where similarity(last_name, 'smtih') > 0.3
order by similarity(last_name, 'smtih') desc;That kind of fuzzy search can catch spelling mistakes that exact or partial search would miss.
It can be very useful.
It can also be dangerous.
Fuzzy matching works by saying “this is close enough.” But close enough is a business decision. A fuzzy match in a movie search box is harmless. A fuzzy match in a medical record system, legal workflow, or financial approval process needs much more care.
The upside is forgiveness. Users do not need to spell everything perfectly.
The downside is ambiguity. The system may return a result that looks plausible but is not the right one.
That is why fuzzy search often works best when the UI makes the uncertainty visible. Show more context. Show multiple possible matches. Avoid pretending that a fuzzy match is the same as an exact match.
The Content Problem
Searching names and IDs is one thing. Searching long text is another.
If users are searching articles, tickets, notes, product descriptions, or documents, plain LIKE queries start to feel weak.
Postgres has full-text search for this kind of work:
select *
from support_tickets
where to_tsvector('english', title || ' ' || body)
@@ plainto_tsquery('english', 'late delivery weekend stock');This is no longer just looking for a raw string. It is turning the text into searchable terms and matching a query against them.
That helps when people search for ideas, not exact phrases.
You can also rank the results:
select
id,
title,
ts_rank(
to_tsvector('english', title || ' ' || body),
plainto_tsquery('english', 'late delivery weekend stock')
) as rank
from support_tickets
where to_tsvector('english', title || ' ' || body)
@@ plainto_tsquery('english', 'late delivery weekend stock')
order by rank desc;This is where search starts feeling more like a real product experience. The user does not just need matching results. They need the best matching results first.
But ranking introduces another product question:
Best according to whom?
Ranking Is Where Business Logic Sneaks In
Search ranking is not neutral.
If two results match, which one goes first?
The most recent? The most popular? The highest value account? The closest spelling match? The result the user has permission to act on? The one with the most complete data? The one the business wants to promote?
You can add a simple ranking layer on top of search:
select
id,
name,
case
when name ilike 'john smith' then 100
when name ilike 'john%' then 80
when name ilike '%john%' then 50
else 10
end
+ case when status = 'active' then 20 else 0 end
+ case when last_contacted_at > now() - interval '30 days' then 10 else 0 end
as score
from customers
where name ilike '%john%'
order by score desc;That query is simple, but it shows the point.
The search result is no longer only about matching text. It is about matching text and applying business priority.
For a CRM, active customers may matter more. For a help desk, open urgent tickets may matter more. For ecommerce, in-stock products may matter more. For an internal admin tool, the newest records may matter more.
This is why search becomes hard to generalise.
The matching strategy and the ranking strategy are both part of the user experience.
When Postgres Is Enough
For many products, Postgres search is a perfectly good place to start.
LIKE, ILIKE, trigram search, full-text search, indexes, and a small ranking layer can take you a long way.
That is especially true when:
- the dataset is moderate
- the search behaviour is well understood
- the data already lives in Postgres
- the UI only needs a few search modes
- the business can tolerate simple ranking rules
The advantage is operational simplicity.
You already have the database. You already know how to deploy it. You do not need another moving part just because someone said the word “search.”
But there are limits.
If you need advanced relevance tuning, typo tolerance, faceting, analytics, synonyms, distributed indexing, or very large scale document search, Postgres may stop being the right centre of gravity.
When You Reach for OpenSearch
Tools like OpenSearch exist because search can become its own serious system.
OpenSearch can help when you need things like:
- dedicated search indexes
- better relevance tuning
- fuzzy matching at scale
- faceted filtering
- autocomplete and suggestions
- document search across large volumes of text
- search analytics
- separation between transactional data and search workloads
That can be the right move.
It also adds complexity.
Now you have to think about indexing pipelines, data freshness, mappings, cluster operations, failure modes, and keeping the search index in sync with the source database.
This is the second major business tradeoff:
- Postgres search is simpler to operate but may be less powerful
- OpenSearch is more capable but adds infrastructure and maintenance cost
Again, neither answer wins by default.
The right answer depends on what the search experience is worth to the business.
If search is a side feature in an internal admin screen, keep it simple. If search is the main way users discover products, documents, customers, or decisions, it deserves more investment.
Different Users Need Different Search
The hardest part is that users often need different search behaviours inside the same product.
A support agent wants speed and certainty.
An account manager wants relationship context.
A warehouse operator wants exact SKUs and low ambiguity.
A customer wants forgiving search that handles bad spelling.
An executive wants the most commercially relevant result.
A developer wants predictable APIs.
All of those users may be searching the same underlying data.
That does not mean they should all get the same search logic.
One screen might need exact matching first. Another might need fuzzy matching. Another might need full-text search. Another might need strict filters before any ranking happens.
This is why universal search is such a tempting idea and such a difficult product to deliver.
The Real Work Is Choosing the Route
The hard part of search is not writing the first query.
The hard part is deciding what kind of search problem you actually have.
Are users trying to find one known thing? Browse a set of possible things? Recover from bad spelling? Search long-form content? Discover the best result? Audit every matching record? Move quickly with low risk?
Each answer points to a different route.
That route might be a simple ILIKE. It might be trigram similarity. It might be Postgres full-text search. It might be OpenSearch. It might be a custom ranking layer. It might be several of those at once, depending on the screen.
That is why search is part technology, part customer expectation, part business priority, and part product design.
The code matters.
But the code is only useful after you decide what “good results” actually means.
There Is Always a Tradeoff
Good search is not universal.
It is contextual.
The more forgiving you make it, the more noise you introduce. The stricter you make it, the more good results you might miss. The more business logic you add to ranking, the less neutral the search becomes. The more infrastructure you add, the more operational responsibility you take on.
That is not a reason to avoid investing in search.
It is a reason to be honest about it.
Search is one of those features that looks small on a roadmap and huge in practice. It sits right between engineering, product, data quality, customer behaviour, and business strategy.
So when someone says “we just need search,” the useful response is not “sure, we can add a search box.”
The useful response is:
What kind of search are we talking about?