Why Search Is Such a Hard Thing to Talk About

Search sounds like one feature. Add a search box, let people type something, show the right results. Easy.

Except it’s rarely easy, because “search” isn’t one thing. It’s 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, and operations wants every possible match even if the order looks messy.

All of those are reasonable requests. They’re also different search systems.

That’s 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’re 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. And 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, and it usually isn’t. 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 shouldn’t always behave the same way.

Exact Search Has a Clear Contract

The simplest search is when the user knows exactly what they’re looking for. Say 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’s Lawten, Laughton, or Lawton-Smith, it doesn’t. That’s not a bug, that’s the contract.

Exact matching works well 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, because humans don’t always know the exact spelling, casing, punctuation, or format.

Partial Matching Trades Precision for Coverage

Most users don’t 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.

Users Search From Memory, Not from Data

Names are a good example, because people search for them in different ways. One person starts with the last name because that’s 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 don’t mean the same thing.

This is where teams start to discover that “just add search” isn’t 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.

Fuzzy Matching Is Forgiveness with a Price

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, and 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 don’t need to spell everything perfectly. The downside is ambiguity, because the system may return a result that looks plausible but isn’t the right one.

That’s why fuzzy search often works best when the UI makes the uncertainty visible. Show more context, show multiple possible matches, and avoid pretending that a fuzzy match is the same as an exact match.

Text Search Is a Different Game

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’s turning the text into searchable terms and matching a query against them, which 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 doesn’t 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 isn’t 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’s about matching text and applying business priority.

For a CRM, active customers may matter more. For a help desk, open urgent tickets. For ecommerce, in-stock products. For an internal admin tool, the newest records. This is why search becomes hard to generalise: the matching strategy and the ranking strategy are both part of the user experience.

Postgres Gets You Further Than You Think

For many products, Postgres search is the right starting point, and sometimes the permanent one.

LIKE, ILIKE, trigram search, full-text search, indexes, and a small ranking layer can take you a long way.

That’s 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

One thing to plan for as search grows: adding trigram and full-text indexes to busy tables needs care. Building them safely without blocking live traffic is its own concern, separate from the search design itself.

The advantage is operational simplicity. You already have the database, you already know how to deploy it, and you don’t 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 tool.

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, but 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, but that doesn’t mean they should all get the same search logic. One screen might need exact matching first, another fuzzy matching, another full-text search, another 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 isn’t writing the first query. It’s 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, or trigram similarity, or Postgres full-text search, or OpenSearch, or a custom ranking layer, or several of those at once depending on the screen.

The code matters, but it’s only useful after you decide what “good results” actually means.

There Is Always a Tradeoff

Good search isn’t universal, it’s 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 isn’t a reason to avoid investing in search. It’s a reason to be honest about it.

Search is one of those features that looks small on a roadmap and huge in practice. It lives at the intersection of engineering decisions, product expectations, and data that’s never quite as clean as the schema suggests.

So when someone says “we just need search,” the useful response isn’t “sure, we can add a search box.” It’s a question: what kind of search are we talking about?