Adding Full-Text Search

Emails are going out reliably now, thanks to the queue and templating work from Sending Transactional Emails. The next feature request on almost every SaaS backlog is search: a box at the top of the screen where a user types a few words and expects to find the right project, ticket, or document in under a second.

This is part of the Full Stack SaaS Masterclass, and it's a good place to make a deliberate architectural decision early. Search is one of those features where the tempting answer, "add Elasticsearch," is usually the wrong first move. PostgreSQL already ships a full-text search engine, and for the vast majority of SaaS products it's the right place to start.

This article covers how Postgres full-text search actually works, how to wire it into a NestJS API and a Prisma or TypeORM schema, and where the real tradeoffs sit between "good enough" and "we need Elasticsearch." Why Postgres before Elasticsearch

The instinct to reach for a dedicated search engine comes from a reasonable place. Elasticsearch is genuinely excellent at search, and if you've worked on a product with millions of documents and complex relevance tuning, you've probably felt its ceiling is higher than anything Postgres offers.

But a new SaaS product doesn't have that problem yet. It has a handful of tables, a modest amount of data, and one search box that needs to find rows by matching words, not typos or synonyms across a corpus of a billion documents. Solving that with a second stateful system is overkill. An Elasticsearch cluster brings its own indexing pipeline, its own operational surface, and its own failure modes, and standing one up to solve a problem your primary database already solves is exactly the kind of premature complexity this series keeps warning against.

Postgres full-text search gives you ranked, stemmed, multi-column text matching using indexes you already have infrastructure to back up, monitor, and scale. It lives in the same transaction as the data it searches, so there's no separate indexing pipeline to keep in sync and no eventual-consistency window where a newly created row doesn't show up in results yet. That last point matters more than it sounds: a user who creates a project and immediately searches for it expects to find it, not to wait for a sync job.

The honest tradeoff is that Postgres full-text search is a keyword and ranking engine, not a semantic search engine. It won't understand that "invoice" and "bill" mean roughly the same thing unless you teach it to, and it doesn't do typo tolerance out of the box. Elasticsearch, or a purpose-built vector search layer, earns its place once you have that specific need and the data volume to justify running it. How Postgres full-text search actually works

Full-text search in Postgres revolves around two types: tsvector and tsquery. A tsvector is a preprocessed, normalized representation of a document's text: lowercased, stripped of stop words like "the" and "a", and reduced to word stems so…