PostgreSQL Search Optimization: How We Fixed Real-World Search on 1.8M Records Without Elasticsearch

How we solved real-world search problems on 1.8M records using only PostgreSQL (no Elasticsearch).

When we first built search on top of PostgreSQL, everything looked fine.

Exact owner name? works
Exact address? works
Exact parcel ID? works

Then real users showed up.

They typed:

• 1701 w 13
• smith john
• john smth
• intown homes
• eric ortiz cline

…and PostgreSQL did exactly what we asked it to do:

return nothing.

This article is not about how to enable full-text search.

It’s about why search breaks in real systems, and how we fixed each failure using only PostgreSQL — no Elasticsearch, no external search engine.

If you’ve ever thought “Postgres search is too limited”, this is for you.

The Baseline: One Table, Millions of Rows

Everything in this system depends on a single table with ~1.8M rows:

This looks over-engineered.

It wasn’t — every column exists because something broke without it.

Let’s walk through those failures.

Challenge 1: “Why Do I Need the Full Address for Results?”

Problem #1: Partial Addresses Don’t Work

PostgreSQL full-text search performs token-exact matching. Partial addresses like ‘1701 w 13’ fail because tokens do not align.

The fix: normalize addresses before indexing

We stopped indexing raw addresses and introduced generated columns:

Indexes that actually matched how users search:

Query:

Takeaway

Full-text search is not address search.
If you don’t normalize addresses, PostgreSQL will punish you for it.

Challenge 2: “First Name Last Name Works — Last Name First Name Doesn’t”

Problem #2: Word Order Matters (Too Much)

The problem

• PostgreSQL full-text search respects word order. Searching ‘smith john’ fails if the stored order is ‘JOHN SMITH’

Why this happens

PostgreSQL full-text search respects word order.

Your data says:

SMITH JOHN A

The user says:

john smith

Same words.
Different positions.
Different documents.

The fix: pre-compute name permutations

We generate all safe permutations once, at write time:

SMITH JOHN A
JOHN SMITH A
JOHN A SMITH
A JOHN SMITH

Then convert them into a single tsvector:

owner_permuted_tsv = 
to_tsvector ('english', 'SMITH JOHN A JOHN SMITH A JOHN A SMITH..')

And index it:

Now this works:

Takeaway

If word order shouldn’t matter, fix it at write time, not in queries.

Challenge 3: “intown homes Doesn’t Match INTOWNHOMES LLC”

Problem #3: “intown homes” ≠ “INTOWNHOMES LLC”

The problem

• intownhomes → Does not work
• intown homes → Does not work

Why this happens

Two separate issues:

1. Entity suffixes (LLC, INC) pollute tokens
2. Spacing differences break matching

The fix: strip entities + trigram similarity

Entity stripping:

Takeaway

Business suffixes destroy relevance.
Remove them before PostgreSQL ever sees the text.

Challenge 4: Owner + Address Queries Didn’t Work at All

Problem #4: Mixed Queries (Owner + Address)

The problem

Users mix owner and address terms in a single query. This requires token-level OR logic across columns.

The fix: token-level OR across columns

Each token must match somewhere:

With guardrails:

• at least one token matches owner
• at least one token matches address

Takeaway

Combined search is not a feature.
It’s a different query model.

Challenge 5: Autocomplete Worked — But Ranked the Wrong Results

Problem #5: Autocomplete Ranked Wrong Results

The Problem

1701 w 13 returned results, but:

• wrong streets ranked higher
• exact matches buried

Why this happens

Trigram similarity measures closeness, not intent. Exact prefixes must be ranked first.

The fix: prefix bias in ranking

Takeaway

Similarity is a signal — not ranking logic.

Challenge 6: Typos Broke Everything (Until They Didn’t)

The problem

• jhon smth → nothing

The fix: trigram + local threshold

Mistake we made

Row query used one threshold.
COUNT query used another.

Pagination broke.

Final rule

Always set pg_trgm.similarity_threshold locally and consistently.

Indexes Decided Everything

Every index exists because something broke in production.

We didn’t “optimize queries”.

We designed indexes around user mistakes.

If an index didn’t map to a real failure, it didn’t exist.

Final Takeaway

PostgreSQL search is powerful — but only if you stop assuming users behave logically.

Users:

• reorder words
• mix concepts
• type half addresses
• misspell names

PostgreSQL can handle all of this.

Not by magic —
but by modeling for failure instead of correctness.

One Thing to Remember

You don’t need Elasticsearch for most search problems.

You need to understand:

• how PostgreSQL actually works
• and how users actually behave

That’s where real search begins.

In an era where information is easily searchable and tools like AI can generate quick answers, what truly matters is understanding concepts deeply and applying them correctly.

At Mass Tech Bytes, we believe learning cloud technology should be clear, secure, and practical, and this series is built with that philosophy in mind.

Contributors

This article was written by Aditi Pal (Senior Developer)

Stay Connected with Massoftind

Let’s Build the Future Together!

For your Generative AI solutions and project development needs and ongoing updates, technical articles, and insights from our team get in touch with MSS — we’re here to help bring your ideas to life.

👉 Follow us on LinkedIn and Medium for weekly tech insights and practical tips to supercharge your development journey!