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 that "running" and "run" match each other. A tsquery is the same kind of processed representation, built from the search terms a user types.

The @@ operator matches a tsvector against a tsquery. Here's the shape of it directly in SQL:

SELECT id, name, description
FROM projects
WHERE to_tsvector('english', name || ' ' || description) @@ to_tsquery('english', 'invoice & system');

Calling to_tsvector on every row at query time works for small tables but doesn't scale, since Postgres has to reprocess every row's text on every search. The fix is to store the tsvector as a generated column and index it, so the processing happens once at write time instead of on every read.

ALTER TABLE projects
  ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, ''))
  ) STORED;

CREATE INDEX projects_search_idx ON projects USING GIN (search_vector);

A GENERATED ALWAYS AS ... STORED column recomputes automatically whenever name or description changes, so there's no trigger to maintain and no risk of the index drifting out of sync with the source columns. The GIN index is what makes lookups against that column fast, since it's built specifically for the kind of "does this document contain these lexemes" query full-text search runs.

Wiring it into NestJS

The query layer is straightforward once the column and index exist. With a raw query through Prisma's $queryRaw, since generated tsvector columns and ranking functions aren't first-class in most ORM query builders yet:

// projects.service.ts
import { Injectable } from '@nestjs/common';
import { PrismaService } from '../prisma/prisma.service';

interface ProjectSearchResult {
  id: string;
  name: string;
  description: string;
  rank: number;
}

@Injectable()
export class ProjectsService {
  constructor(private readonly prisma: PrismaService) {}

  async search(organizationId: string, term: string): Promise<ProjectSearchResult[]> {
    return this.prisma.$queryRaw<ProjectSearchResult[]>`
      SELECT
        id,
        name,
        description,
        ts_rank(search_vector, websearch_to_tsquery('english', ${term})) AS rank
      FROM projects
      WHERE organization_id = ${organizationId}
        AND search_vector @@ websearch_to_tsquery('english', ${term})
      ORDER BY rank DESC
      LIMIT 20
    `;
  }
}

Two details in that query are worth calling out. websearch_to_tsquery is the function to reach for when the search term comes straight from a user, since it accepts natural, Google-style query syntax, quoted phrases, and/or, and a leading minus for exclusion, instead of the stricter operator syntax to_tsquery expects. And the organization_id filter runs in the same WHERE clause as the search match, not as a step applied afterward, which keeps tenant isolation enforced at the database level rather than relying on filtering results in application code.

ts_rank scores each match so results come back ordered by relevance rather than by whatever order the table happens to store them in. It's a simple relevance model: no learning-to-rank, no click-through feedback. But it correctly favors documents where the search terms appear more frequently or in more significant positions, and that's enough for most SaaS search boxes.

The controller stays thin, as it should:

// projects.controller.ts
import { Controller, Get, Query, UseGuards } from '@nestjs/common';
import { CurrentOrganization } from '../auth/current-organization.decorator';
import { JwtAuthGuard } from '../auth/jwt-auth.guard';
import { ProjectsService } from './projects.service';

@Controller('projects')
@UseGuards(JwtAuthGuard)
export class ProjectsController {
  constructor(private readonly projectsService: ProjectsService) {}

  @Get('search')
  search(@Query('q') term: string, @CurrentOrganization() organizationId: string) {
    return this.projectsService.search(organizationId, term ?? '');
  }
}

Validate the query parameter before it reaches the service; an empty or missing term should return an empty result set rather than hitting the database with a query that matches nothing usefully, and a search endpoint is exactly the kind of place a class-validator DTO or a simple length check earns its cost.

Searching across multiple tables

Most SaaS products don't search one table. A user typing into the global search box usually expects results from projects, tasks, and documents at once. The tempting shortcut is running three separate queries and merging results in the application layer, but that pushes ranking and pagination logic into code that has to reimplement what Postgres already does well.

A materialized view is a cleaner answer once search spans more than one or two tables:

CREATE MATERIALIZED VIEW search_index AS
SELECT
  id,
  organization_id,
  'project' AS entity_type,
  search_vector
FROM projects
UNION ALL
SELECT
  id,
  organization_id,
  'task' AS entity_type,
  search_vector
FROM tasks;

CREATE INDEX search_index_gin ON search_index USING GIN (search_vector);
CREATE UNIQUE INDEX search_index_id ON search_index (entity_type, id);

A materialized view snapshots its query result at refresh time rather than recomputing it live, so it needs REFRESH MATERIALIZED VIEW CONCURRENTLY search_index on a schedule, or triggered after writes, to stay current. That refresh introduces the same eventual-consistency question that real-time single-table search avoids, so it's worth reserving for the combined view across tables and keeping the fast, always-current single-table search for cases where results need to be exact the instant a row is created.

Production pitfalls

A few mistakes show up repeatedly once full-text search moves from a demo to real traffic.

Forgetting the GIN index and relying on a sequential scan is the most common one. It works fine on a development database with a few hundred rows. Then a customer shows up with tens of thousands of projects and it falls over, because every search now reads and reprocesses the whole table.

Language configuration is another one that's easy to overlook. to_tsvector('english', ...) applies English stemming and stop words; a multi-language product needs either a per-row language column feeding the right configuration, or an acceptance that search quality will be uneven for non-English content. Deciding this early is much cheaper than migrating a production index later.

Tenant isolation deserves the same scrutiny here as anywhere else in a multi-tenant system. A search query that filters by organization_id inside the same WHERE clause as the text match is safe. A search implementation that queries broadly and filters tenant visibility afterward, in application code, is a data leak waiting to happen, and it's the kind of bug that won't show up in testing with a single test organization.

Finally, resist the urge to add fuzzy matching, typo tolerance, and synonym expansion before anyone has asked for them. Postgres supports trigram similarity through the pg_trgm extension if a fuzzy-match need actually materializes, but adding that complexity speculatively, before real user search behavior tells you it's needed, is the same premature-optimization trap as reaching for Elasticsearch on day one.