The Problem: Full-Text Search That Silently Cheats

Every app eventually needs search. And "search" usually starts as WHERE title LIKE '%query%' — slow, brittle, and completely ignoring how the database wants to be used.

PostgreSQL has a proper answer: full-text search via tsvector and tsquery, backed by GIN indexes. It's fast, it ranks results by relevance, and it supports things like prefix matching, boolean operators, phrases, and even fuzzy matching.

So why does almost every example you find in the wild either call raw SQL or — worse — silently fall back to client-side evaluation, loading your whole table into memory and "searching" it in .NET?

That's exactly the trap this project refuses to fall into.

The Project: PgSearchSharp

PgSearchSharp is a .NET 9 console app that demonstrates real, server-side PostgreSQL full-text search with Entity Framework Core 9 and Npgsql.

DheerGupta35959 / PgSearchSharp

GitHub

C# ★ 0 0 MIT

View on GitHub →

It's built around three honest principles:

1. SQL-translatable search helpers. Every helper in SearchExtensions.cs produces an IQueryable that Npgsql translates to real PostgreSQL — no .Compile() tricks, no sneaky client-side LINQ. The demo proves it: every search pattern prints the generated SQL alongside its results.

2. An honest benchmark. It compares a persisted tsvector + GIN index against on-the-fly tsvector generation, measured on a configurable 100k-row synthetic corpus. Warm-up runs keep the Postgres page cache honest, and a hit-count parity check makes sure both strategies search the same rows — so the timing is comparing equal work, not apples to oranges.

3. Verifiable. Unit tests assert the SQL translation offline via ToQueryString() — no database server needed — and an opt-in integration suite runs the real thing against a live PostgreSQL instance.

What It Looks Like

The search patterns cover everything from simple to advanced:

And the code reads like normal LINQ:

var results = await context.Records
    .WhereSearchVectorMatches(SearchExtensions.CombineWithAnd("jack", "black"))
    .OrderBySearchRelevance(SearchExtensions.CombineWithAnd("jack", "black"))
    .Take(10)
    .ToListAsync();

The key detail: the helpers are typed to DbRecord, not generic. A generic Expression<Func<TEntity, NpgsqlTsVector>> selector can't be translated by EF Core without LinqKit-style expression expansion — the naive generic version silently evaluates client-side and breaks when you run it against PostgreSQL. The typed version always translates to SQL.

The Honest Benchmark

The benchmark is the part most tutorials skip.

• Persisted search: a generated SearchVector column backed by a GIN index. The index does the heavy lifting.
• Dynamic search: tsvector computed on the fly for each query. No storage overhead, more CPU per query.

Both run against a 100k-row corpus with a mix of single-term, boolean, prefix, and phrase queries. Warm-up runs happen before timing starts. And the parity check confirms both strategies return the same hits — so the timing is a real comparison.

The lesson is usually the same: for read-heavy workloads, the persisted GIN index wins. But you get to see the actual numbers and decide for yourself, rather than trusting a blog post.

Testing Without a Server

Here's the part that saves you real pain: the SQL translation tests run offline.

dotnet test PgSearchSharp/PgSearchSharp.Tests

ToQueryString() lets the tests assert that the helpers translate to real to_tsquery / to_tsvector / ts_rank_cd SQL — without needing a PostgreSQL instance. So a regression that would silently turn your search into a client-side full-table scan gets caught in CI, not in production.

The integration tests, which need a real database, are skipped by default and opt-in.

Getting Started

Prerequisites are just .NET 9 SDK and Docker:

docker-compose up -d
dotnet run --project PgSearchSharp

The app applies migrations, seeds 20 sample records, demonstrates every search pattern with its generated SQL, then grows the corpus to 100k rows and runs the benchmark. PostgreSQL runs on localhost:5432, with pgAdmin on http://localhost:5050.

Why This Matters

Client-side evaluation is the silent killer of "production-ready" search demos. It works in development, passes the happy-path test, and then melts when your table outgrows your machine's memory.

PgSearchSharp shows the honest version: search that lives in PostgreSQL, where it belongs, with proof it's actually running there — printed SQL, offline translation tests, and a benchmark that compares equal work.

If you're adding search to a .NET + PostgreSQL app, this is a better starting point than the tutorial that quietly loads everything into memory.