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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
Not much in the last two years, and I mean that as a compliment. The The real change is external. Before I write a single query, I answer three questions. Answer these and the index choice picks itself.<\/p>\n Q1. What am I searching, a title, a body, or both?<\/strong> If it’s a title only (short strings), a plain GIN or GiST on Q2. Do I need fuzzy matches (“laravl” to “laravel”), or just linguistic matches (“running” to “run”)?<\/strong> Linguistic goes to Q3. Does the corpus fit in RAM?<\/strong> 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.<\/p>\n Here’s the migration I typically ship:<\/p>\n Two indexes. A generated column so I don’t have to remember to update the vector on writes. The lookup I use maybe 90% of the time:<\/p>\n For the “user is clearly typing a partial word” case (autocomplete, mostly), I fall back to The 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:<\/p>\n The 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<\/a>, but the short version: the generated If your stack is TypeScript, Drizzle handles tsvector columns cleanly (I get into that in the Drizzle vs Prisma post<\/a>): the raw SQL escape hatch is fine here, since you’ll write 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):<\/p>\n 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<\/a> is more grounded than most vendor docs.<\/p>\n The mistake I made for years: treating What I do instead: rank within the query, then apply business rules. So the SQL sort becomes 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:<\/p>\ntsvector<\/code>\/tsquery<\/code> 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<\/code> casts feel less like sandpaper.<\/p>\npg_trgm<\/code> matured, pgvector<\/code> exists for hybrid semantic and keyword search, and extensions like paradedb<\/code> 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<\/a> are famously dry but complete.<\/p>\nThe three-index question I ask myself<\/h2>\n
to_tsvector('english', title)<\/code> is fine. If it’s a body, use GIN. Always. GiST loses on large text.<\/p>\ntsvector<\/code>. Fuzzy goes to pg_trgm<\/code>. If you want both, keep two indexes and OR the results at query time. Yes, really. Two indexes cost less than one Elasticsearch pod.<\/p>\nALTER TABLE posts\n ADD COLUMN search_vector tsvector\n GENERATED ALWAYS AS (\n setweight(to_tsvector('english', coalesce(title, '')), 'A') ||\n setweight(to_tsvector('english', coalesce(body, '')), 'B')\n ) STORED;\n\nCREATE INDEX posts_search_idx\n ON posts USING GIN (search_vector);\n\nCREATE INDEX posts_trgm_idx\n ON posts USING GIN (title gin_trgm_ops);\n<\/code><\/pre>\nsetweight<\/code> gives title matches four times the rank of body matches. pg_trgm<\/code> (via the title gin_trgm_ops<\/code> index) is there for the fuzzy fallback I’ll show below.<\/p>\nReal query patterns I ship<\/h2>\n
SELECT\n id,\n title,\n ts_rank_cd(search_vector, plainto_tsquery('english', $1)) AS rank\nFROM posts\nWHERE search_vector @@ plainto_tsquery('english', $1)\nORDER BY rank DESC, published_at DESC\nLIMIT 20;\n<\/code><\/pre>\nplainto_tsquery<\/code> handles the user typing a raw string. ts_rank_cd<\/code> 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.<\/p>\npg_trgm<\/code>:<\/p>\nSELECT id, title, similarity(title, $1) AS sim\nFROM posts\nWHERE title % $1\nORDER BY sim DESC\nLIMIT 10;\n<\/code><\/pre>\n%<\/code> operator uses the trigram index. similarity()<\/code> returns a float between 0 and 1. I usually set pg_trgm.similarity_threshold<\/code> around 0.2 in the session; the default of 0.3 was too strict for how people actually type.<\/p>\nWITH fts AS (\n SELECT id, ts_rank_cd(search_vector, plainto_tsquery($1)) AS score\n FROM posts\n WHERE search_vector @@ plainto_tsquery($1)\n),\ntrgm AS (\n SELECT id, similarity(title, $1) * 0.5 AS score\n FROM posts\n WHERE title % $1 AND id NOT IN (SELECT id FROM fts)\n)\nSELECT id, score FROM fts\nUNION ALL\nSELECT id, score FROM trgm\nORDER BY score DESC\nLIMIT 20;\n<\/code><\/pre>\n* 0.5<\/code> is my hack to keep trigram matches ranked below any exact-ish keyword hit. Tune to taste.<\/p>\nWhere I’ve been shipping this in real apps<\/h2>\n
search_vector<\/code> 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.<\/p>\n@@ plainto_tsquery(...)<\/code> inside a tagged-template SQL literal anyway. Prisma is more of a fight; you’ll be dropping into $queryRaw<\/code>.<\/p>\nWhen Elasticsearch still wins<\/h2>\n
\n
Ranking without gaming the score<\/h2>\n
ts_rank<\/code> 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.<\/p>\nORDER BY rank DESC, boost_column DESC, published_at DESC<\/code>, where boost_column<\/code> 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<\/code>, just written in SQL.<\/p>\nTesting search is where most teams give up<\/h2>\n
\n
EXPLAIN ANALYZE<\/code> plan 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.<\/li>\n