FTS vs Elasticsearch: when PostgreSQL is enough and when it isn't

Capsule description

Your Blog API has Spanish FTS + ranking + snippets + fuzzy + autocomplete, all in PostgreSQL. It works in <50ms for 100k posts. The tech lead asks in standup: "shouldn't we migrate to Elasticsearch to scale, so the team doesn't have to learn FTS?". If your only answer is "PostgreSQL is simpler", you lost the discussion. If your answer is "for our corpus of 800k docs with keyword + filter queries, FTS is at 35ms p99, and adding ES adds operational cost, a double-write, and oncall with no measurable benefit", you won.

This capsule gives you the concrete criteria for making and defending that decision. You're going to learn what PostgreSQL FTS wins at (transactionality, no additional operations, joins with relational data), what Elasticsearch wins at (complex faceted search, aggregations, horizontal scale, near-real-time analytics), and the points where the decision is clear vs the points where "it depends." You're going to see a decision matrix with numeric criteria and an example of a real case (dev.to, 50M rows, 4s → 12ms) that documents the pattern in production.

By the end you'll be able to walk into a technical meeting with a decision justified by data, defend the position against a tech lead who assumes "ES is the standard", and recognize the few cases where Elasticsearch really does win.

Mental model: two engines with different philosophies

PostgreSQL FTS and Elasticsearch solve the same problem (full-text search) but from opposite starting points.

┌─────────────────────────────────────────────────────────────────────┐
│                                                                     │
│  PostgreSQL FTS                                                     │
│  ──────────────                                                     │
│  Philosophy: "PostgreSQL is the database. Everything lives there."  │
│                                                                     │
│  Pros:                                                              │
│   - Transactional (ACID): matches consistent with your data.        │
│   - No double-write: no sync between DB and search engine.          │
│   - Trivial joins: filter by category + search + author             │
│     in a single query.                                              │
│   - No additional operations: your DBA already knows PostgreSQL.    │
│   - No new service: one less dependency in the stack.               │
│   - Cost: $0 marginal.                                              │
│                                                                     │
│  Cons:                                                              │
│   - Less sophisticated for complex faceted search.                  │
│   - No native horizontal scaling (short of Citus or sharding).      │
│   - More limited complex aggregations.                              │
│   - Less search-specific observability tooling.                     │
│                                                                     │
│ ─────────────────────────────────────────────────────────────────── │
│                                                                     │
│  Elasticsearch                                                      │
│  ─────────────                                                      │
│  Philosophy: "Search is a specialized domain. A dedicated service." │
│                                                                     │
│  Pros:                                                              │
│   - Native faceted search with aggregations.                        │
│   - Trivial horizontal scaling.                                     │
│   - Complex near-real-time analytics.                               │
│   - Rich tooling (Kibana, dashboards, alerts).                      │
│   - Very complex queries (phrase boost, function score, etc.).      │
│                                                                     │
│  Cons:                                                              │
│   - Double-write: your DB and ES are always out of sync a moment.   │
│   - Operations: cluster, indexes, shards, monitoring, oncall.       │
│   - Cost: a minimum cluster is $$$ per month.                       │
│   - Learning curve: the team must know ES on top of SQL.            │
│   - Eventual consistency: the result may not reflect what was       │
│     just inserted (indexing delays).                                │
│                                                                     │
└─────────────────────────────────────────────────────────────────────┘

The right decision isn't "PostgreSQL always" or "Elasticsearch always." It's defaulting to PostgreSQL and migrating to Elasticsearch when a concrete case shows up that PostgreSQL doesn't solve well. That philosophy minimizes dependencies and maximizes the reusability of the team's skills.

The decision matrix with concrete criteria

How to read the matrix:

• If your case lands mostly in the left column, PostgreSQL FTS is enough. No discussion.
• If it lands mostly in the right column, Elasticsearch (or Meilisearch, OpenSearch, Typesense) makes sense.
• If it's mixed, default to PostgreSQL and reevaluate when you see a concrete pain that only ES solves.

A real case: dev.to, 50M rows, 4s → 12ms with pure PostgreSQL

dev.to publicly documented how they scaled search with PostgreSQL FTS instead of migrating to Elasticsearch. The post is a few years old but the pattern still holds.

Context:

• A posts table with ~50M rows.
• Full-text search with filters (by tag, by user, by language).
• Volume: high, comparable to a popular tech site.

Before:

• A query with to_tsvector(body) @@ to_tsquery(...) without a generated index.
• Latency: 4 seconds.
• The team evaluated migrating to Elasticsearch.

Changes applied (all pure PostgreSQL):

1. A generated column tsv tsvector with to_tsvector('english', ...) instead of computing it per query.
2. A GIN index over the generated column.
3. Partial indexes for frequent filters (e.g., posts with published = true).
4. JSONB for metadata and specific indexes.
5. Partitioning by date for associated log/analytics tables.

After:

• Latency: ~12ms p99.
• Stack: pure PostgreSQL, without adding ES.
• The team saved itself the entire operation of an Elasticsearch cluster.

Generalizable lessons:

1. The 100× factor comes from applying the right techniques, not from changing engines. Without a GIN index and a generated column, FTS is slow. With both, it's production-ready.
2. The additional filters (tag, user) are solved with classic PostgreSQL indexes, they don't need ES.
3. For 50M docs with keyword + filter queries, PostgreSQL is enough. The "Elasticsearch mandatory" threshold is much higher than people think.
4. The decision not to migrate saved the team a permanent source of complexity and risk. ES cluster, sync jobs, monitoring, oncall — all avoided.

Cite this case when someone proposes ES without justifying it with numbers. Ask them to show their corpus, their QPS, and the type of queries before accepting the debate as valid.

Patterns where Elasticsearch clearly wins

So you don't fall into the opposite trap ("PostgreSQL is always better"), recognize the cases where ES is the right tool:

1. Complex faceted search with real-time aggregations

A typical e-commerce site: the user searches "zapatillas" and the UI shows:

• 1245 results.
• Filter by brand: Nike (340), Adidas (290), Puma (180), ...
• Filter by size: 38 (50), 39 (80), ..., 46 (12).
• Filter by price: 0-50 (200), 50-100 (450), ...
• Filter by color: red (150), blue (200), ...

PostgreSQL can do this but it requires multiple GROUP BY queries that get slow at scale. Elasticsearch does it natively with a single request using aggs. If your product lives on faceted search, ES wins.

2. Real-time analytics over events

Massive logging, user events, product metrics. Wanting to do "how many events of type X in the last 5 minutes grouped by country and platform" in milliseconds, over billions of records. That's exactly what ES (with Kibana) was designed for.

PostgreSQL can handle part of this with materialized views (module 5) and partitioning (module 4) but past certain volumes ES wins on total cost.

3. Advanced geospatial search combined with full-text

"Restaurants with the word 'pizza' within 5km of location X, ordered by rating and open hours". PostgreSQL with PostGIS does this but it's verbose. ES with geo plugins simplifies it.

4. A very large corpus with very high QPS

Past 50-100M docs and 5000+ sustained QPS, scaling PostgreSQL requires complex architecture (replicas dedicated to search, Citus, manual sharding). For Algolia/ES scale, ES wins naturally.

5. Your team already has ES in the stack for another reason

If you already have ES running for logs and monitoring, adding a search index isn't a significant additional operation. The "don't add a new service" argument doesn't apply if the service already exists.

Patterns where PostgreSQL clearly wins

1. A mid-sized app with search as a feature, not as the core product

A Blog API, a knowledge base, a ticket system, an e-commerce site up to a certain scale. Search is important but it isn't the main product. PostgreSQL FTS solves it without adding complexity.

2. You need joins with relational data in every query

"Find posts where the author is a follower of user X and the post isn't soft-deleted and the category is Y". In PostgreSQL it's a trivial JOIN. In ES you'd have to denormalize (duplicate data) or do two steps (search in ES, then filter in the DB).

3. Consistency is critical

Financial, healthcare, governance apps. The user inserts something and must be able to search it immediately. ES has eventual consistency (1-5s of typical lag). If your UX doesn't tolerate that lag, PostgreSQL wins.

4. A small team without a dedicated DBA

A startup with 3-5 full-stack devs. Adding ES means someone has to learn ES, monitor it, debug it. PostgreSQL FTS reuses skills the team already has.

5. Simple compliance / data residency

If your data lives in PostgreSQL in a specific region for compliance, adding ES requires replicating the compliance setup. Keeping it all in PostgreSQL simplifies things.

6. The dev.to case: 50M docs with keyword + simple filter queries

Documented. Well-tuned PostgreSQL reaches 12ms p99. There's no excuse for "we need ES because we have 10M docs."

The "double-write" argument (why it matters more than it seems)

If you choose Elasticsearch as your search engine, there's always a double-write: the data lives in PostgreSQL (the source of truth) and gets replicated to ES (the search index). Three ways:

1. Direct sync in the code: after every INSERT/UPDATE in PostgreSQL, you call the ES API to index. Problem: if the ES call fails, you have drift between the DB and ES.
2. The outbox pattern: you insert into an outbox table alongside the change, and an async worker reads from the outbox and sends to ES. More complex but more reliable.
3. CDC (Change Data Capture): Debezium or similar reads PostgreSQL's WAL and propagates to ES. The most correct option, but significant infrastructure.

Any of the three adds permanent complexity to the system. Bug surfaces:

• Race conditions: two changes in PostgreSQL at the same instant — in what order do they arrive at ES?
• Recovery: if ES goes down, how do you reconcile when it comes back?
• Backfill: if you want to reindex everything, how long does it take? Does it block writes?
• Schema evolution: if you change the mapping in ES, how do you migrate the existing docs?

With PostgreSQL FTS, none of this exists. The tsvector is a column in the same table. Inserts and updates are reflected immediately. Backups, replicas, transactions — everything works like it does with any other column.

It's a technical argument that many teams underestimate when evaluating ES. Operationally, it's enormous.

Worked example: how to defend the decision in a meeting

Scenario: you're in standup. The tech lead says "we need search for the blog. Let's set up Elasticsearch this sprint."

Bad: a defensive answer ("PostgreSQL is simpler")

"I prefer PostgreSQL FTS. It's simpler."

Why it's bad: opinion vs opinion. The tech lead probably believes ES is the standard. Without data, you lose the discussion.

Good: a data-based answer

"Before we commit to ES, I'd like to show numbers from a POC with PostgreSQL FTS. I have the setup on my branch. Metrics I measured yesterday:

• Current corpus: 80k posts, projected to 800k in 2 years.
• Queries: keyword + filter by category/author. No faceted search.
• Projected QPS: 100 max (based on current analytics × 5×).
• PostgreSQL FTS latency with GIN index + ranking + snippets: p50 = 18ms, p99 = 47ms.
• Latency with the fuzzy fallback (pg_trgm): +20ms when it kicks in (rare).

With those numbers, ES would give us:

• Cost: ~$300/month for a minimum cluster (vs $0 with PostgreSQL).
• Operations: additional oncall, sync jobs, double-write.
• Benefit for our case: hard to identify — we don't need faceted search, we don't need complex analytics, the corpus is inside PostgreSQL's comfortable range.

I propose: we ship PostgreSQL FTS this sprint. If in six months a case shows up (>5M docs, faceted search, unacceptable latency) that PostgreSQL doesn't solve, we evaluate ES with that concrete data. And in the meantime the team doesn't learn a new service we probably don't need.

What do you think?"

Why it's good:

• It acknowledges the tech lead's point (it doesn't dismiss ES outright).
• It brings concrete data from the POC.
• It compares real costs (financial + operational).
• It proposes a path with criteria for revisiting the decision.
• It ends with an open question, inviting feedback.

That conversation, repeated across many teams, is what separates senior devs from technically-junior devs. Technical ability matters, but the ability to defend an architectural decision with criteria matters more.

Why does this matter in real work?

1. It's a decision that gets discussed in most backend projects with search. Showing up prepared with criteria and concrete data sets you apart. Devs who show up with "X is better" without justification lose the technical discussion over and over.

2. It saves you (or helps you avoid) an enormous operational pain. Every extra service in production is: monitoring + oncall + double-write + a learning curve for the team + financial cost. Justifying when it's worth it is senior work.

3. It works symmetrically: defending PostgreSQL when someone wants ES, and defending ES when someone wants PostgreSQL but the case calls for it. It isn't ideology, it's judgment.

4. The industry trend is "more PostgreSQL, fewer services." AWS, Supabase, Neon, Crunchy Data, the whole cloud-native ecosystem is pushing in this direction. Joining the trend with judgment positions you professionally.

Traps and common mistakes

Mistake 1 (conceptual): "Elasticsearch is the standard for search"

Symptom: someone argues that ES is the default option without evaluating the concrete case.

Why it's confusing: ES has strong marketing and many courses present it as "the search engine." For certain profiles (analytics, observability) it is standard, but for application search it's just one more option.

How to tell: ask concretely: "what specific problem does ES solve in our case that PostgreSQL doesn't?". If the answer is vague ("it's more complete"), there's no case.

Mistake 2 (conceptual): "PostgreSQL doesn't scale for search"

Symptom: someone has a corpus of 200k rows and says "we need ES because PostgreSQL doesn't scale."

Why it's confusing: "doesn't scale" is being confused with "doesn't scale infinitely." PostgreSQL scales enough for most apps. The dev.to case demonstrates up to 50M with the right techniques.

How to tell: ask for the current corpus and the 2-year projection. If it's under 5M, there's no scale debate. If it's between 5M and 50M, it's debatable. If it's over 50M with complex queries, ES has a point.

Mistake 3 (practical): running the POC without the right techniques

Symptom: someone does a PostgreSQL FTS POC without a GIN index or a generated column, measures 1.5s latency, and concludes "PostgreSQL doesn't work, let's go to ES."

Why it happens: the difference between naive FTS and optimized FTS is 100×. Comparing PostgreSQL's worst case against ES's best case is cheating.

Fix: make sure the POC uses:

• A generated tsv column with setweight.
• A GIN index.
• websearch_to_tsquery (not to_tsquery).
• ts_rank_cd for ranking.
• pg_trgm for fuzzy.

If those are all right and the latency is still high, then it does justify considering ES. If they aren't, the POC isn't valid.

Mistake 4 (conceptual): underestimating the operational cost of a new service

Symptom: "let's add ES, it's not a big deal."

Why it's confusing: from the point of view of "we're adding one more API," ES looks simple. From the operations point of view, it's: cluster monitoring, alerts, oncall (who gets up at 3 AM if ES is down?), backups, schema migrations, double-write, sync recovery, debugging inconsistencies.

How to tell: ask whoever's proposing it to list all the operational components the team is going to have to maintain. If the list is short, they didn't understand what it entails.

Mistake 5 (conceptual): assuming "the code is simpler" with ES

Symptom: "with ES the search code is simpler because the query DSL is expressive."

Why it's confusing: ES's query DSL is more expressive but it requires learning it. PostgreSQL FTS from SQLAlchemy is Python code the team already understands.

A realistic comparison:

# PostgreSQL FTS (what you learned in this module)
stmt = (
    select(Post)
    .where(Post.tsv.bool_op("@@")(func.websearch_to_tsquery("spanish_unaccent", q)))
    .order_by(desc(func.ts_rank_cd(Post.tsv, ...)))
    .limit(20)
)

# Elasticsearch (with elasticsearch-py)
result = await es.search(
    index="posts",
    body={
        "query": {
            "multi_match": {
                "query": q,
                "fields": ["title^2", "body"],
                "fuzziness": "AUTO",
            }
        },
        "size": 20,
    },
)

A similar number of lines. PostgreSQL reuses the existing ORM. ES introduces a new API + client + syntax to learn. "Simple" depends on your baseline.

Mistake 6 (practical): choosing ES "for the future" with no present need

Symptom: "we don't need it now but better to start with ES in case we grow."

Why it's confusing: over-engineering. You pay the operational cost starting today for a hypothetical benefit. If the case shows up in two years, migrating from PostgreSQL FTS to ES is one or two sprints of work, not six months.

Fix: YAGNI (You Aren't Gonna Need It). Start with the simple thing, evolve when the case requires it.

Exercises

Exercise 1: apply the decision matrix to a real case

For each scenario, decide PostgreSQL FTS or Elasticsearch and justify it with concrete criteria.

a) A corporate blog. 50k posts. ~500 searches/day. Queries: keyword. No faceted search. b) An e-commerce site with a catalog of 2M products. 50k searches/day. Queries with complex faceted search (brand, size, price, color, store, rating). Real-time aggregations. c) An internal knowledge base. 200k docs. ~10k searches/day. Queries: keyword + filter by department + author. d) A logs and metrics platform. 1B events/day. Interactive analysis with dashboards. Filters by hour, source, severity. e) A personal notes app. 5k notes per user max. Fast local search with typos.

Exercise 2: put together the proposal for your team

Imagine: you're in a standup. The tech lead says "we're going to add Elasticsearch for the Blog API's search." Your corpus is 100k posts with a projection to 1M in 18 months. Queries: keyword + filter by category. No faceted search.

Write a Slack message of no more than 200 words proposing PostgreSQL FTS, with data and a review path.

Hi — before we commit to ES, I'd like to share numbers from a PostgreSQL FTS
POC I put together last sprint. Metrics measured on a branch:

- Corpus: 100k posts (projected 1M in 18 months).
- Queries: keyword + filter by category.
- FTS latency with GIN index + ranking: p50 = 22ms, p99 = 58ms.
- With the `pg_trgm` fuzzy fallback: +25ms when it kicks in (rare).
- Setup: one generated column and two indexes. No new services.

Compared with ES in our case:
- Minimum cluster cost: ~$300/month (vs $0 with PostgreSQL).
- Operations: double-write, sync recovery, oncall.
- Benefit for our use case (keyword + simple filter): I don't see a
  material difference.

I propose: we ship PostgreSQL FTS this sprint. Metrics we'll monitor as
triggers for revisiting the decision:
1. If p99 latency goes over 200ms with the current corpus → tune or evaluate ES.
2. If we need complex faceted search → ES has a case.
3. If the corpus passes 5M with more complex queries → reevaluate.

What do you think? I can put together a 15-min demo to show the POC working
if you want to see real numbers before deciding.

PostgreSQL FTS is better for this, we don't need ES.

Exercise 3: identify when the "PostgreSQL is enough" argument stops applying

Your team has PostgreSQL FTS in production. After 18 months, someone proposes migrating to ES. For each situation, decide whether the proposal has merit or whether it's premature.

a) p50 latency went from 25ms to 180ms in 6 months. p99 is at 850ms. The corpus went from 200k to 4M docs. b) The product pivoted to a marketplace and needs faceted search by category/brand/price/rating with real-time aggregations. c) "Other teams are using ES and our code looks old." d) The user reports that the first result is sometimes "weird." The product team asked for better relevance. e) A need came up for search across comments + tickets + docs in a single unified interface. f) PostgreSQL's logs show a lot of Sort in the search query plans.

Exercise 4: total cost analysis

Your team is evaluating migrating from PostgreSQL FTS to Elasticsearch. List all the costs (financial, operational, time) that show up in a real migration. Don't limit yourself to "the cluster costs X dollars."

Summary and next step

In this capsule you learned to defend architectural decisions with judgment:

• Default to PostgreSQL FTS. Migrate to Elasticsearch when a concrete case shows up that PostgreSQL doesn't solve well after reasonable tuning.
• PostgreSQL wins when: a moderate corpus (<5M, up to 50M with tuning), keyword + simple filter queries, joins with relational data, transactionality, a small team without a DBA.
• Elasticsearch wins when: complex faceted search with real-time aggregations, real-time analytics over events, advanced geospatial, a very large corpus (>50M-100M) with high QPS, a team that already has ES for another reason.
• The dev.to case (50M docs, 4s → 12ms) demonstrates that the "ES mandatory" threshold is higher than many people think.
• The double-write is a permanent operational cost that many teams underestimate when evaluating ES.
• Defend decisions with data, not with opinion: corpus, QPS, query type, latency measured in a POC.
• YAGNI: don't add ES "for the future." The cost is present, the benefit is hypothetical. Migrating later if needed isn't six months of work.

Before moving on you should be able to:

• List 5 concrete criteria where PostgreSQL wins and 5 where ES wins.
• Defend the decision "PostgreSQL FTS is enough for our case" with data from a POC.
• Recognize when "PostgreSQL is enough" stops applying (complex faceted search, 50M+ scale, etc.).
• Calculate the total operational cost of adding ES, not just the cluster's cost.
• Apply the decision matrix to a new scenario at work.

Next capsule — The module project: the blog's Spanish-language search. It's time to apply everything from the module in an integrative mini-project. You're going to build a complete /search?q=... endpoint: Spanish FTS with unaccent, ranking with ts_rank_cd, snippets with ts_headline, a fuzzy fallback with pg_trgm, autocomplete with prefix + fuzzy, and Alembic migrations for all of it. It's the exact component that gets integrated into the Blog API refactor in module 8 (the guide's final project). You're going to come out with portfolio-worthy code and a clear evaluation rubric.

Resources

1. dev.to — "Postgres Full-Text Search Is Better Than Part 1" — the real case (50M rows, 4s → 12ms) referenced in the capsule.
2. Supabase Docs — Full-Text Search vs Elasticsearch — a modern perspective on when PostgreSQL is enough.
3. Crunchy Data — "When to use PostgreSQL Full-Text Search" — an analysis of when PostgreSQL is enough.
4. AWS Database Blog — "Implementing full-text search in PostgreSQL on Amazon RDS" — a cloud-vendor perspective with benchmarks.
5. Hubert "depesz" Lubaczewski — "FTS performance over time" — FTS benchmarks on large tables with tuning techniques.
6. Elasticsearch — Documentation — so you know what ES offers beyond search (analytics, aggregations, geo).
7. Hussein Nasser — YouTube channel — videos comparing search solutions and databases with architectural analysis.

Module 3 — Advanced PostgreSQL for Backend Guide

Next capsule: The module project — the blog's Spanish-language search, end-to-end.