← read more

Full-Text Search in a SaaS: PostgreSQL First, Specialized Engines Second

A practical guide to adding search to a web app — what PostgreSQL full-text search covers out of the box, when to reach for Meilisearch or Typesense, and the mistakes that make search feel broken.

Full-Text Search in a SaaS: PostgreSQL First, Specialized Engines Second

"Add search" sounds like a weekend task and turns into a design decision that follows the product for years. The instinct is to reach for Elasticsearch before writing a single query — but most SaaS apps don't need that on day one, and some never do. Here's how we decide, and how we keep search from feeling broken once it ships.

Start with the database you already have

PostgreSQL ships with a full-text search engine that handles a surprising amount of real work:

-- One column, one index, done
ALTER TABLE documents ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
  ) STORED;

CREATE INDEX documents_search_idx ON documents USING GIN (search);

SELECT * FROM documents
WHERE search @@ websearch_to_tsquery('english', 'monthly invoice')
ORDER BY ts_rank(search, websearch_to_tsquery('english', 'monthly invoice')) DESC
LIMIT 20;

What you get for free:

  • Stemming and stop words — "invoices" matches "invoice" without any configuration.
  • tsvector indexes (GIN) — queries stay fast into hundreds of thousands of rows.
  • Ranking with ts_rank and ts_rank_cd, plus websearch_to_tsquery() that accepts user-typed syntax like "exact phrase" -exclude.
  • Highlighting with ts_headline for result snippets.

In Laravel this wraps cleanly into a scope, keeping the feature invisible to the rest of the codebase:

// Bad: LIKE '%term%' — no index, no ranking, no language awareness
// Good: a scope over the generated tsvector column
public function scopeSearch(Builder $query, string $term): Builder
{
    $tsquery = sprintf("plainto_tsquery('english', %s)", DB::getPdo()->quote($term));

    return $query->whereRaw('search @@ ' . $tsquery)
        ->orderByRaw(sprintf(
            'ts_rank(search, %s) DESC',
            $tsquery
        ));
}

When Postgres stops being enough

There are honest limits, and they show up as user complaints, not as server alarms:

  • Typo tolerance. Users type "invoise" and get zero results. PostgreSQL will not forgive them; a typo-tolerant engine will.
  • Relevance as a product feature. When search is the product — a catalog, a knowledge base — ranking quality becomes a roadmap item, and dedicated engines give you knobs (typo tolerance, synonym lists, per-field weights) Postgres doesn't.
  • Faceted filtering and instant-as-you-type. Counters per category, sub-50ms keystroke responses.

That's the point where we add Meilisearch or Typesense. Both are single-binary, easy to self-host next to the app, and solve the same shape of problem.

The pattern that keeps it maintainable

Whatever the engine, one architectural rule holds: the database stays the source of truth, and the search index is a rebuildable projection.

  • Sync on write. Model observers or queue jobs push the document to the index after commit — never inline before commit, or a rolled-back transaction leaves a ghost document in search results.
  • Sync asynchronously. Index updates belong on the queue (see our earlier piece on background jobs). A slow search provider must never slow down a checkout.
  • Design for full rebuilds. If you can't drop the index and rebuild it from the database in one command, the index has drifted into being a second source of truth. That's how "why is this row in search but not in the admin" bugs are born.

The mistakes that make search feel broken

Most "our search is bad" reports we've seen trace to three boring causes:

  1. Zero-result pages with no exit. Show suggestions, popular items, or a fuzzy retry. A dead end teaches users to stop using search.
  2. No logging of queries. Top searched terms — and especially top failed terms — are the cheapest product research you will ever get. Log them (without user identifiers, in line with our privacy-first approach) and read them weekly.
  3. Searching fields users don't think in. Matching internal IDs and slugs pads results with noise. Weight titles above bodies, bodies above metadata.

The decision in one line

Search over your own data, tens of thousands of rows, users who type exact words — PostgreSQL full-text search, shipped in an afternoon. Search as a product surface with typos and facets — Meilisearch or Typesense, synced from the database. Either way: one source of truth, an index you can rebuild, and query logs you actually read.