Search in .NET: Postgres FTS, Elasticsearch, OpenSearch

The first time WHERE name LIKE '%' || @q || '%' takes 3 seconds on a 5M-row table, you have hit the search wall. This chapter shows where to land - Postgres FTS for most cases, Elasticsearch for the ones where Postgres runs out - and how to keep the index in sync with Postgres without losing a Saturday.

When does in-app search outgrow LIKE queries?

Three signals.

Latency. LIKE '%term%' cannot use a B-tree index, so Postgres sequentially scans the table. At 100K rows it is fine; at 5M rows it is multi-second. Trigram indexes (pg_trgm) help, but only for short prefixes.

Relevance. LIKE returns rows in primary-key order, not by match quality. Users expect "iPhone" to rank higher than "iPhone case wallet vintage style". You need scoring, and LIKE has none.

Multi-field weighting. Search "C# tutorial" should weight title matches higher than body matches and rank recent posts above old ones. Hand-rolling that in SQL is possible; maintaining it as features grow is a quarterly project.

If none of these bite, do not add search infrastructure. A LIKE query plus a small GIN index on tsvector covers the long tail.

What numbers should I budget for the search tier?

Backend                Index size    Query latency p99    Ops complexity
Postgres FTS           up to ~10M    ~50-200 ms           free (same DB)
Elasticsearch (1 node) up to ~10M    ~10-50 ms            one cluster
Elasticsearch (cluster) ~100M+       ~10-50 ms            sharding, JVM
OpenSearch (managed)   up to ~100M   ~20-100 ms           cloud bill
Algolia / Typesense    no limit      ~5-20 ms             SaaS lock-in

Postgres FTS is free if you already run Postgres. Elasticsearch costs an additional cluster, plus the overhead of keeping it in sync. Algolia costs money but no ops. The math from chapter 2 decides where you land.

What does the minimal architecture look like?

Two shapes. Postgres-only:

flowchart LR
    App[ASP.NET Core] --> PG[(Postgres<br/>tsvector + GIN index)]

Postgres + Elasticsearch with outbox:

flowchart LR
    App[ASP.NET Core] --> PG[(Postgres)]
    PG --> Outbox[(Outbox table)]
    Worker[Indexer worker] --> Outbox
    Worker --> ES[(Elasticsearch)]
    App -. read .-> ES

Writes go to Postgres, the same transaction inserts an outbox row, a worker drains the outbox and updates Elasticsearch. Reads go to Elasticsearch. The outbox pattern is covered in chapter 10; reuse it here.

What is the .NET 10 wiring for Postgres FTS?

Add a tsvector column and a GIN index, then use EF Core raw SQL for the search:

// EF Core migration:
migrationBuilder.Sql("""
    ALTER TABLE products ADD COLUMN search_vector tsvector
        GENERATED ALWAYS AS (
            setweight(to_tsvector('english', coalesce(name,'')), 'A') ||
            setweight(to_tsvector('english', coalesce(description,'')), 'B')
        ) STORED;
    CREATE INDEX ix_products_search_vector ON products USING GIN (search_vector);
""");

// Query:
public async Task<List<Product>> SearchAsync(string query, CancellationToken ct)
{
    return await db.Products
        .FromSqlInterpolated($"""
            SELECT *, ts_rank(search_vector, plainto_tsquery('english', {query})) AS rank
            FROM products
            WHERE search_vector @@ plainto_tsquery('english', {query})
            ORDER BY rank DESC
            LIMIT 50
        """)
        .ToListAsync(ct);
}

Three details. The STORED generated column means the vector is maintained automatically. Weight A (title) ranks higher than B (description), giving you basic relevance for free. plainto_tsquery parses user input safely - never concatenate raw input into the query string.

What is the .NET 10 wiring for Elasticsearch?

Two parts: the client and the indexer worker.

// Program.cs - register the typed client
builder.Services.AddSingleton<ElasticsearchClient>(_ =>
{
    var settings = new ElasticsearchClientSettings(
        new Uri(builder.Configuration["Elastic:Url"]!));
    return new ElasticsearchClient(settings);
});

// Indexer consumer (MassTransit + outbox event from chapter 10)
public class ProductChangedConsumer(ElasticsearchClient es)
    : IConsumer<ProductChanged>
{
    public async Task Consume(ConsumeContext<ProductChanged> ctx)
    {
        var doc = ctx.Message.ToProductDocument();
        await es.IndexAsync(doc, x => x
            .Index("products")
            .Id(doc.Id), ctx.CancellationToken);
    }
}

// Search endpoint
public async Task<List<ProductDocument>> SearchAsync(string q, CancellationToken ct)
{
    var resp = await es.SearchAsync<ProductDocument>(s => s
        .Index("products")
        .Query(qd => qd.MultiMatch(m => m
            .Query(q)
            .Fields(new[] { "name^3", "description" })
            .Fuzziness(new Fuzziness("AUTO"))
        ))
        .Size(50), ct);
    return resp.Documents.ToList();
}

Three details. name^3 boosts the name field three times over description (the equivalent of setweight in Postgres). Fuzziness catches typos. The indexer must be idempotent - the consumer in chapter 6 explains why.

What failure modes does adding search introduce?

• Index drift - the search index disagrees with Postgres. Cause: outbox failure, dropped event, manual data fix. Detection: a reconciliation job that compares row counts hourly. Fix: replay the outbox or full reindex.
• Hot query - one heavy query (regex, deep aggregation) starves the cluster. Mitigation: query timeouts, slow-query log, killing long queries.
• Mapping changes - changing a field's type requires a full reindex. Mitigation: alias indices (products → products_v2), reindex into v2, then swap the alias atomically.
• JVM heap pressure - Elasticsearch likes a lot of RAM and OOMs badly when sized too small. Rule of thumb: 50% of the box for the JVM heap, 50% for OS page cache.

When is search infrastructure the wrong answer?

When the user is filtering, not searching. A faceted product list with checkboxes for category and price range is filtering - SQL WHERE clauses against indexed columns. No tsvector, no Elasticsearch, no relevance scoring. The mistake of treating filtering as search inflates infrastructure with no win. Use search when the user types free text and expects relevance ranking.

Where should you go from here?

Next chapter: auth styles in .NET - how to choose between JWT and cookie auth, when to add OIDC, and how the token lifecycle fits into a real .NET service. After that, the building-blocks group is complete and the reliability chapters compose on top.