Full-Text Search for 7,000 Recipes with PostgreSQL tsvector

When you have 7,000+ recipes in a database, browsing by category only gets you so far. Users want to search for "quick weeknight chicken dinner" and get relevant results — not just exact keyword matches. PostgreSQL's built-in full-text search, with its tsvector type and GIN indexes, handles this beautifully without needing Elasticsearch or any external search service.

The Setup

The recipes table has a GIN index on a computed tsvector expression:

CREATE INDEX ix_recipes_search ON recipes
USING GIN (to_tsvector('english',
  coalesce(title, '') || ' ' || coalesce(description, '')
));

This indexes every recipe by its lexemes — the linguistic roots of words. Search for "baking" and it matches "bake". Search for "running" and it finds "ran". PostgreSQL handles stemming, stop word removal, and ranking out of the box.

How Queries Work

The API endpoint at GET /api/v1/recipes?q=quick+chicken+dinner uses PostgreSQL's plainto_tsquery function:

const tsQuery = sql`
  to_tsvector('english',
    coalesce(${recipes.title}, '') || ' ' ||
    coalesce(${recipes.description}, '')
  ) @@ plainto_tsquery('english', ${opts.q})
`;

The @@ operator is the match operator: it checks if the tsvector matches the tsquery. plainto_tsquery takes a raw user string and converts it to a tsquery automatically, applying the same stemming rules. A user typing "roasted chicken vegetables" gets results for "roast chicken vegetable" behind the scenes.

Combining with Filters

The search compounds naturally with other filters through Drizzle ORM's and() combinator:

const finalWhere = conditions.length > 0
  ? and(...conditions, tsQuery)
  : tsQuery;

So ?q=chicken&cuisine=Italian&max_time=30 filters to Italian chicken recipes under 30 minutes, with full-text ranking applied on top.

Performance

With 7,000+ rows, the GIN index keeps search responses well under 50ms on Neon's serverless Postgres. The index is roughly proportional to the total text volume — even at 100,000 recipes, GIN indexes remain efficient because they're designed for this exact workload.

Why Not Just Use ILIKE?

A naive WHERE title ILIKE '%chicken%' approach would:

• Miss "chicken" if someone types "chikin"
• Match "chickenpox" if someone types "chicken" (no word boundary awareness)
• Require a full table scan (no index support for leading-wildcard patterns)

tsvector avoids all three problems with proper linguistics and indexed lookups.

One Caveat

This approach works well for English-language content. PostgreSQL supports 11 other text search configurations (German, French, Spanish, etc.), but RecipeScrape is English-only for Phase 1. Adding multi-language support would mean indexing the same text with multiple configurations — doable, but a problem for later.