Full-Text Search in a SaaS: Postgres vs a Search Engine

Full-text search lets users find records by the words in them, not an exact match. For most SaaS, PostgreSQL full-text search with a tsvector column and a GIN index is enough, and keeps one fewer system to run. Add a dedicated search engine for typo tolerance, instant search-as-you-type at scale, or complex ranking. Either way, every query must stay scoped to the tenant.

If you would rather have search built for you, see how RAITHub would build this below.

What is full-text search, and when is Postgres enough?

Full-text search matches on words and their stems, with ranking, rather than the substring matching of a LIKE query. PostgreSQL has this built in, and for the majority of B2B SaaS it is the right first choice: no extra service, no sync to keep correct, and search that is transactionally consistent with your data.

RAITHub built search inside Sundor Skin, a B2B wholesale platform with 146 PostgreSQL tables, row-level security and 530+ tests, where buyers search a large catalogue and the search must never cross a buyer's data boundary. Postgres full-text search covered it without a second datastore. The parallel decision for storefront search is in ecommerce site search: Postgres, Meilisearch, Typesense or Algolia.

When should you reach for a dedicated search engine?

When a specific requirement outgrows what Postgres does comfortably. The honest comparison:

Requirement	PostgreSQL full-text	Dedicated search engine
Find records by words, with ranking	Yes, built in	Yes
Typo tolerance ("invioce" finds "invoice")	Limited; needs trigram tricks	Yes, out of the box
Instant search-as-you-type at scale	Workable to a point	Built for it, sub-50ms
Faceting and complex relevance tuning	Possible but manual	First-class
One fewer system to run and sync	Yes	No; you index and keep it in sync
Transactionally consistent with your data	Yes	No; eventually consistent

The cost of a search engine is not the engine; it is the indexing pipeline and the consistency gap. Start with Postgres, measure, and move specific features over only when you have a reason.

How do you build full-text search in PostgreSQL?

Add a generated tsvector column, index it with GIN, and query with the match operator. A generated column keeps the search vector in step with the row automatically. The feature is documented in the PostgreSQL full-text search reference.

-- A generated tsvector kept in sync with the row, weighted by field.
ALTER TABLE documents ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,  '')), 'B')
  ) STORED;

-- GIN index makes the match fast.
CREATE INDEX documents_search_idx ON documents USING gin (search);

-- Query: tenant-scoped first, ranked by relevance. websearch_to_tsquery
-- accepts human input like  red "annual report" -draft .
SELECT id, title, ts_rank(search, q) AS rank
FROM documents, websearch_to_tsquery('english', $2) AS q
WHERE workspace_id = $1            -- tenant isolation, never optional
  AND search @@ q
ORDER BY rank DESC
LIMIT 20;

The setweight calls make a title match rank above a body match. websearch_to_tsquery parses ordinary search-box input, including quoted phrases and negation, so you do not hand-build query strings from user text.

How do you keep search from leaking across tenants?

Put the tenant filter in the query itself, and back it with row-level security so a forgotten filter cannot leak data. Search is a common place for tenant leaks, because developers focus on relevance and forget the boundary.

• Always filter by tenant, and make that the first condition, so the planner narrows to the tenant before matching text.
• Enforce it at the database, with row-level security, so even a query that omits the filter returns nothing it should not. The pattern is in the Postgres row-level security guide.
• Apply result permissions too, so a user does not see records inside their tenant that their role cannot access, using the same check as elsewhere.
• Test it adversarially, with a search as tenant A that tries to surface tenant B's rows and must return none.

If you do add a search engine, how do you keep it in sync?

Index changes through the same outbox-and-queue discipline you use for other side effects, so the search index never drifts far from the database and a failed index attempt is retried, not lost.

• Write an index event with the change, in the same transaction, then process it from a queue, exactly as with webhooks in sending webhooks to your SaaS customers.
• Reconcile on a schedule, a nightly job that re-indexes anything the stream missed, so drift self-heals; the job runner is in a scheduler and recurring jobs for your SaaS.
• Keep tenant isolation in the engine too, as a mandatory filter on every query, with the same adversarial test.

Buy, build or hire?

Option	Examples	Choose this when	Watch out for
Hosted search SaaS	Managed search-as-a-service	You need instant typo-tolerant search and want no infrastructure	Per-record or per-search pricing; your data and an index live outside your database
Self-hosted search engine	Open-source search engines you run	You need engine features but want to keep data in your own infrastructure	You run and scale it, and own the sync pipeline
PostgreSQL full-text	tsvector and GIN, as above	Most B2B SaaS: word search with ranking, one system, consistent with data	Typo tolerance and huge-scale instant search need extra work
Hire a team to build it	RAITHub or another studio	Search must be relevant, fast and never cross a tenant boundary, and be proven	Insist the handover includes the cross-tenant search test

Do-it-yourself estimate: 2–5 days for a tenant-scoped Postgres full-text search with weighting, ranking and a GIN index; several days more to add and keep a search engine in sync. The main risk is a search query that forgets the tenant filter and surfaces another customer's records.

How RAITHub would build this

As part of a new SaaS build, or added to an existing product, scoped in writing after the free audit.

• Start on Postgres: a generated tsvector with field weighting, a GIN index and human-friendly query parsing.
• Tenant isolation first: every query scoped to the tenant and backed by row-level security, with an adversarial cross-tenant test.
• Add an engine only when justified, fed by an outbox-and-queue index pipeline with nightly reconciliation.
• Relevance and permissions: ranking tuned to the product, and results filtered to what the viewer's role may see.

Timeline: inside a new product, this is part of the 4–6 week fixed-scope SaaS build; added to an existing backend, it fits the 6–12 week backend range, with the exact scope in the quote.

You receive: automated tests and CI, including the cross-tenant search test, handover docs, and full IP assigned to you under NDA.

Next step: a free 15-minute technical audit, then a written fixed quote. See the SaaS development service, or book the audit.

Frequently asked questions

Is PostgreSQL full-text search good enough for a SaaS?

For most B2B SaaS, yes. A tsvector column with a GIN index gives word-based search with ranking, stays consistent with your data in the same transaction, and needs no extra service. Add a dedicated engine only when a specific requirement outgrows it.

When should I use a dedicated search engine instead of Postgres?

When you need typo tolerance, instant search-as-you-type at large scale, or heavy faceting and relevance tuning. The trade-off is an indexing pipeline to maintain and a consistency gap, since the engine is eventually consistent with your database.

How do I stop search leaking data across tenants?

Put the tenant filter in every search query as the first condition, and back it with row-level security so a forgotten filter returns nothing. Then test it with a search as one tenant that deliberately tries to surface another's rows and must find none.

What is a tsvector and a GIN index?

A tsvector is PostgreSQL's parsed, stemmed representation of a document's text; a GIN index makes matching against it fast. A generated tsvector column stays in sync with the row automatically, so you query with the match operator and rank with ts_rank.

How do I keep a search engine in sync with my database?

Write an index event in the same transaction as the change and process it from a queue, so a failed index attempt is retried rather than lost. Add a nightly reconciliation job that re-indexes anything the stream missed, so drift self-heals.