Confession: I dragged Elasticsearch into a project six months ago because a stakeholder said the word “search” three times in a planning meeting and I panicked. Two weeks later I ripped it out and shipped the same feature on plain Postgres. The user-facing behavior was indistinguishable, the infra bill dropped by about $60 a month, and I got to delete an entire docker-compose service plus its two backup scripts.
So yeah, I have opinions about when Postgres full-text search is enough, and when it really isn’t. This post is mostly the “enough” cases, because that’s the majority, and it’s the case people skip past to buy the fancier thing.
Short version if you’re scanning: if your corpus fits comfortably in a Postgres instance, your query patterns are exact-ish keyword matches, and you don’t need a query-time relevance model tuned by a search engineer, Postgres full-text search will handle it. Below is what I actually use.
Postgres full-text search in 2026: what actually changed
Not much in the last two years, and I mean that as a compliment. The tsvector/tsquery machinery has been stable since Postgres 9.0 (2010), and every version since has quietly made it faster or added edge cases: parallel index builds, better statistics on GIN indexes, incremental updates. Postgres 17 landed a small but nice tweak: JSON-aware to_tsvector casts feel less like sandpaper.
The real change is external. pg_trgm matured, pgvector exists for hybrid semantic and keyword search, and extensions like paradedb push into “actual full-search-engine inside Postgres” territory for teams that want it. The default install of Postgres already ships everything I need for typical app search: search by title and body, rank by relevance, ignore stop words, handle stems. If you want to see what’s actually in the box, the official Postgres full text search docs are famously dry but complete.
The three-index question I ask myself
Before I write a single query, I answer three questions. Answer these and the index choice picks itself.
Q1. What am I searching, a title, a body, or both? If it’s a title only (short strings), a plain GIN or GiST on to_tsvector('english', title) is fine. If it’s a body, use GIN. Always. GiST loses on large text.
Q2. Do I need fuzzy matches (“laravl” to “laravel”), or just linguistic matches (“running” to “run”)? Linguistic goes to tsvector. Fuzzy goes to pg_trgm. If you want both, keep two indexes and OR the results at query time. Yes, really. Two indexes cost less than one Elasticsearch pod.
Q3. Does the corpus fit in RAM? If the entire indexed column is under, say, 5 GB, you’re fine. Postgres GIN indexes are dense; queries return in single-digit ms once warm. Once you’re pushing 50+ GB of searchable text, revisit.
Here’s the migration I typically ship:
ALTER TABLE posts
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(body, '')), 'B')
) STORED;
CREATE INDEX posts_search_idx
ON posts USING GIN (search_vector);
CREATE INDEX posts_trgm_idx
ON posts USING GIN (title gin_trgm_ops);
Two indexes. A generated column so I don’t have to remember to update the vector on writes. setweight gives title matches four times the rank of body matches. pg_trgm (via the title gin_trgm_ops index) is there for the fuzzy fallback I’ll show below.
Real query patterns I ship
The lookup I use maybe 90% of the time:
SELECT
id,
title,
ts_rank_cd(search_vector, plainto_tsquery('english', $1)) AS rank
FROM posts
WHERE search_vector @@ plainto_tsquery('english', $1)
ORDER BY rank DESC, published_at DESC
LIMIT 20;
plainto_tsquery handles the user typing a raw string. ts_rank_cd uses cover density: it weights matches that appear near each other higher than matches scattered across the doc. Tiebreak by recency so a stale post with the same rank doesn’t win.
For the “user is clearly typing a partial word” case (autocomplete, mostly), I fall back to pg_trgm:
SELECT id, title, similarity(title, $1) AS sim
FROM posts
WHERE title % $1
ORDER BY sim DESC
LIMIT 10;
The % operator uses the trigram index. similarity() returns a float between 0 and 1. I usually set pg_trgm.similarity_threshold around 0.2 in the session; the default of 0.3 was too strict for how people actually type.
Where it gets interesting: mixing both. If FTS returns fewer than N results, run the trigram query and union the leftovers in. Elasticsearch calls this “search-after fallback”. In Postgres it’s a CTE:
WITH fts AS (
SELECT id, ts_rank_cd(search_vector, plainto_tsquery($1)) AS score
FROM posts
WHERE search_vector @@ plainto_tsquery($1)
),
trgm AS (
SELECT id, similarity(title, $1) * 0.5 AS score
FROM posts
WHERE title % $1 AND id NOT IN (SELECT id FROM fts)
)
SELECT id, score FROM fts
UNION ALL
SELECT id, score FROM trgm
ORDER BY score DESC
LIMIT 20;
The * 0.5 is my hack to keep trigram matches ranked below any exact-ish keyword hit. Tune to taste.
Where I’ve been shipping this in real apps
On a Rust backend I set up recently, I wired this directly through sqlx. The pattern’s covered in more detail in my Axum + sqlx backend setup post, but the short version: the generated search_vector column just becomes another sqlx field. No custom query builder. No search service in front of your database. Migrations run through the same pipeline as every other schema change.
If your stack is TypeScript, Drizzle handles tsvector columns cleanly (I get into that in the Drizzle vs Prisma post): the raw SQL escape hatch is fine here, since you’ll write @@ plainto_tsquery(...) inside a tagged-template SQL literal anyway. Prisma is more of a fight; you’ll be dropping into $queryRaw.
When Elasticsearch still wins
I’m not going to pretend Postgres full-text search beats Elasticsearch across the board. Here’s my rough list of when I still reach for Elastic (or now, more often, OpenSearch or Meilisearch):
- You have a full-time search engineer or want one. Elastic’s query DSL is powerful, and if someone is going to spend real hours tuning boosts, analyzers, and synonyms, the ceiling is higher.
- Corpus > 100 GB of active searchable text. Postgres will do it, but you’ll spend more time on GIN maintenance than on the app.
- Cross-language stemming matters. Postgres ships stemmers for a few dozen languages, but Elastic’s per-language analyzers are a step ahead for anything past English, German, French.
- You need distributed search across shards you control. Postgres has partitioning, but if you’re at that scale you probably already know you need Elastic.
- You want geo, aggregations, and search in the same query. PostGIS plus FTS in Postgres works, but Elastic’s aggregations were built for this.
If none of those apply, you’re in the “Postgres is enough” zone. That’s most apps. For a nice practical writeup on the Postgres side, the Supabase FTS guide is more grounded than most vendor docs.
Ranking without gaming the score
The mistake I made for years: treating ts_rank output as a stable score I could compare across queries. It isn’t. The score is meaningful within one query (the top result is more relevant than the second), but the absolute number depends on doc length, term frequency, and cover density. Don’t build alerts on “score > 0.4” thresholds. You’ll be tuning them forever.
What I do instead: rank within the query, then apply business rules. So the SQL sort becomes ORDER BY rank DESC, boost_column DESC, published_at DESC, where boost_column is something concrete like “is this a featured post” or “does the author have verified status”. That’s the same pattern Elastic recommends with function_score, just written in SQL.
Testing search is where most teams give up
Not really a Postgres thing, more of a general take. Search is one of the easiest features to ship a demo of and one of the hardest to test in CI. What I actually keep in the test suite:
- A fixture set of 20-ish “canonical” docs with hand-labeled expected ordering for a handful of real user queries. If any change to the schema or ranking function breaks that ordering, the test fails and I have to look at it.
- A snapshot test for the raw
EXPLAIN ANALYZEplan of the main search query. Not asserting timings; asserting that it still uses the GIN index and hasn’t fallen back to a seq scan. This catches migrations that accidentally drop the index or break the generated column. - A property test that runs the search endpoint against a random string and asserts it doesn’t 500. Sounds dumb; catches so many parser bugs.
I kept most of these habits after reading the classic Postgres vs Elasticsearch HN thread, which has aged pretty well.
What I’d do this week
If you have a Postgres app with a LIKE %term% search endpoint, spend one afternoon and swap it for FTS. The steps: add the generated tsvector column and GIN index, swap the LIKE for @@ plainto_tsquery, keep the exact same API contract. You’ll get a 10 to 100x speedup on any table with more than a few thousand rows, better relevance out of the box, and a foundation you can extend into ranking, autocomplete, and fuzzy matches without spinning up new infra.
If a stakeholder says “search” in a meeting, ask them for three specific queries they want to work and how they’d know it’s good. If they can’t answer, you don’t need Elasticsearch. You need a spec.
I write about this kind of “keep it in Postgres” stuff regularly on my site; most of my recent client work has been picking database patterns that scale without needing a platform team.
