Most applications do not need Algolia, Meilisearch or an Elasticsearch cluster. They need a search box that returns relevant results in under 50 milliseconds, with ranking, highlighting and a bit of typo tolerance. PostgreSQL has done exactly that since version 8.3, and it does it well enough for the vast majority of SaaS products, blogs, marketplaces and internal tools.

This tutorial shows how to implement PostgreSQL full-text search in a Prisma + Node.js application, the production way: a generated tsvector column, a GIN index, weighted fields, ranking with ts_rank_cd, highlighted snippets with ts_headline, and a trigram fallback for misspellings. Everything runs through Prisma with typed raw queries.

Why not just use Prisma’s built-in search filter?

Prisma ORM ships a full-text search filter for PostgreSQL behind the fullTextSearchPostgres preview feature. It looks like this:

const posts = await prisma.post.findMany({
  where: { title: { search: 'postgres & search' } },
})

It is convenient for a quick prototype, but it has real limitations that show up as soon as you have traffic:

• It generates to_tsvector(column) @@ to_tsquery(...) on the fly, which means a sequential scan unless you create a matching expression index by hand.
• There is no ranking. You cannot order results by relevance, only by a regular column.
• The query syntax is raw tsquery syntax (&, |, <->). A user typing best coffee shop with a space will throw a syntax error unless you sanitize the input yourself.
• No support for weighted fields (title should count more than body), no snippets, no highlighting.
• It has been a preview feature for years, so it is not something to build a core product feature on.

The approach below is more code up front, but it is stable, indexed, rankable and fully under your control. It is also portable: the SQL works on any PostgreSQL 12+ instance, whether that is RDS, Supabase, Neon, Prisma Postgres or a container on your own server. PostgreSQL: Documentation: 18: 12.1. Introduction is a useful companion to this.

How PostgreSQL full-text search actually works

Three concepts are enough to get started.

Concept	What it is	Example
tsvector	A document converted into normalized lexemes with positions. Stop words removed, words stemmed.	'databas':3 'fast':1 'postgres':2
tsquery	The parsed search query, with boolean operators between lexemes.	'fast' & 'databas'
@@	The match operator. Returns true when the vector satisfies the query.	search_vector @@ query

Add ts_rank_cd() for relevance scoring and a GIN index for speed, and you have the whole engine.

Choose the right query parser

PostgreSQL gives you three ways to turn user input into a tsquery. Picking the right one saves a lot of pain:

Function	Behaviour	Use it when
to_tsquery	Strict syntax, throws on invalid input	You build the query string programmatically
plainto_tsquery	Joins every word with AND, never throws	Simple internal search
websearch_to_tsquery	Understands quotes for phrases, OR, and - for exclusion, never throws	Public-facing search boxes (recommended)

websearch_to_tsquery is the one to use. It accepts input like "box software" -legacy postgres OR mysql without ever raising an exception, which removes an entire category of 500 errors.

Step 1: The Prisma schema

Prisma has no native tsvector scalar type, but it can map unknown column types with Unsupported() and it can declare GIN indexes. Here is the model:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Article {
  id          String    @id @default(uuid())
  title       String
  summary     String?
  body        String
  tags        String[]  @default([])
  publishedAt DateTime? @map("published_at")
  createdAt   DateTime  @default(now()) @map("created_at")

  // Generated by PostgreSQL, never written by the application
  searchVector Unsupported("tsvector")? @map("search_vector")

  @@index([searchVector], map: "article_search_vector_idx", type: Gin)
  @@map("articles")
}

Two important notes:

1. Because searchVector is an Unsupported field, Prisma Client will not return it in findMany results and will not let you write to it. That is exactly what we want, since PostgreSQL maintains it.
2. The field must be optional (?) or Prisma will complain that it cannot create records without it.

Step 2: The migration that does the real work

Prisma Migrate cannot generate a stored generated column, so create the migration file and edit it:

npx prisma migrate dev --name add_article_search --create-only

Open the generated SQL file and replace the search_vector column definition with a generated one:

-- 1. The table (Prisma already generated most of this)
ALTER TABLE "articles"
  ADD COLUMN "search_vector" tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce("title", '')), 'A') ||
    setweight(to_tsvector('english', coalesce("summary", '')), 'B') ||
    setweight(to_tsvector('english', array_to_string("tags", ' ')), 'B') ||
    setweight(to_tsvector('english', coalesce("body", '')), 'C')
  ) STORED;

-- 2. The index that makes it fast
CREATE INDEX "article_search_vector_idx"
  ON "articles" USING GIN ("search_vector");

Then apply it:

npx prisma migrate dev

What setweight buys you

Weights A, B, C, D carry default multipliers of 1.0, 0.4, 0.2 and 0.1. Putting the title in A and the body in C means a match in the title outranks a match buried in paragraph twelve. This single line is what makes Postgres results feel “smart” instead of random.

Why a generated column instead of a trigger

• Zero application code. Inserts and updates through Prisma Client automatically refresh the vector.
• No drift. A trigger can be dropped or forgotten during a restore; a generated column is part of the table definition.
• Works with bulk operations. createMany, updateMany, COPY and raw SQL all stay consistent.

The only constraint is that the expression must be IMMUTABLE, which is why you must pass the dictionary as a literal ('english') rather than relying on default_text_search_config. If you need multilingual content, add a language column and create one generated column per language, or fall back to a trigger. prisma.io has covered this at length.

Step 3: Querying with Prisma raw SQL

Prisma’s tagged template $queryRaw parameterizes values safely, so user input never gets concatenated into SQL. Here is a complete search function with ranking and pagination:

import { PrismaClient, Prisma } from '@prisma/client'

const prisma = new PrismaClient()

export type SearchHit = {
  id: string
  title: string
  snippet: string
  rank: number
  published_at: Date | null
}

export async function searchArticles(
  term: string,
  { page = 1, perPage = 20 }: { page?: number; perPage?: number } = {},
) {
  const query = term.trim()
  if (!query) return { hits: [], total: 0 }

  const offset = (page - 1) * perPage

  const hits = await prisma.$queryRaw<SearchHit[]>`
    SELECT
      a.id,
      a.title,
      a.published_at,
      ts_rank_cd(a.search_vector, q, 32) AS rank,
      ts_headline(
        'english',
        a.body,
        q,
        'StartSel=<mark>, StopSel=</mark>, MaxFragments=2, FragmentDelimiter=" ... ", MinWords=10, MaxWords=28'
      ) AS snippet
    FROM articles a, websearch_to_tsquery('english', ${query}) q
    WHERE a.search_vector @@ q
      AND a.published_at IS NOT NULL
    ORDER BY rank DESC, a.published_at DESC NULLS LAST
    LIMIT ${perPage} OFFSET ${offset};
  `

  const [{ count }] = await prisma.$queryRaw<{ count: bigint }[]>`
    SELECT COUNT(*)::bigint AS count
    FROM articles a, websearch_to_tsquery('english', ${query}) q
    WHERE a.search_vector @@ q AND a.published_at IS NOT NULL;
  `

  return { hits, total: Number(count) }
}

Details worth knowing:

• The FROM articles a, websearch_to_tsquery(...) q form is a lateral cross join with a single row. It lets you reference q three times without re-parsing the query.
• ts_rank_cd is the cover density ranking function. It rewards documents where the search terms appear close together, which usually matches human expectations better than plain ts_rank.
• The third argument 32 is a normalization flag meaning rank / (rank + 1), producing a score between 0 and 1. That is handy if you later blend the text score with a popularity score.
• COUNT(*) comes back as a BigInt. Cast it with Number() before serializing to JSON or you will hit Do not know how to serialize a BigInt.
• Never interpolate ${orderByColumn} directly. If you need a dynamic sort, use a whitelist and Prisma.raw() only on values you control.

Blending relevance with business signals

Pure text relevance is rarely the final answer. A common production formula:

ORDER BY
  (ts_rank_cd(a.search_vector, q, 32) * 0.7)
  + (LEAST(a.view_count, 10000)::float / 10000 * 0.2)
  + (CASE WHEN a.published_at > now() - interval '90 days' THEN 0.1 ELSE 0 END)
  DESC

This is the kind of tuning that hosted search products charge for, and it is three lines of SQL here.

Step 4: Combining full-text search with Prisma filters

A frequent objection to raw queries is losing Prisma’s ergonomic filters. Two patterns solve it.

Pattern A: build the WHERE clause with Prisma.sql

import { Prisma } from '@prisma/client'

const conditions: Prisma.Sql[] = [Prisma.sql`a.search_vector @@ q`]

if (categoryId) conditions.push(Prisma.sql`a.category_id = ${categoryId}`)
if (onlyPublished) conditions.push(Prisma.sql`a.published_at IS NOT NULL`)
if (tags?.length) conditions.push(Prisma.sql`a.tags && ${tags}::text[]`)

const where = Prisma.join(conditions, ' AND ')

const hits = await prisma.$queryRaw<SearchHit[]>`
  SELECT a.id, a.title, ts_rank_cd(a.search_vector, q, 32) AS rank
  FROM articles a, websearch_to_tsquery('english', ${query}) q
  WHERE ${where}
  ORDER BY rank DESC
  LIMIT ${perPage} OFFSET ${offset};
`

Pattern B: two-step, IDs first

Get ranked IDs from raw SQL, then hydrate full objects with Prisma Client and its include relations:

const ranked = await prisma.$queryRaw<{ id: string; rank: number }[]>`
  SELECT a.id, ts_rank_cd(a.search_vector, q, 32) AS rank
  FROM articles a, websearch_to_tsquery('english', ${query}) q
  WHERE a.search_vector @@ q
  ORDER BY rank DESC
  LIMIT 20;
`

const ids = ranked.map(r => r.id)
const articles = await prisma.article.findMany({
  where: { id: { in: ids } },
  include: { author: true, category: true },
})

// Restore the relevance order Postgres computed
const byId = new Map(articles.map(a => [a.id, a]))
const ordered = ids.map(id => byId.get(id)!).filter(Boolean)

Pattern B keeps type safety and relation loading intact. It costs one extra round trip, which is negligible compared to a network hop to an external search service.

Step 5: Typo tolerance with pg_trgm

Full-text search matches stems, not misspellings. Searching postgersql returns nothing. Trigram similarity fixes that as a fallback layer.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX article_title_trgm_idx
  ON articles USING GIN (title gin_trgm_ops);

Then run the fuzzy query only when the main search returns nothing:

export async function fuzzySearch(term: string) {
  return prisma.$queryRaw<{ id: string; title: string; sim: number }[]>`
    SELECT a.id, a.title, similarity(a.title, ${term}) AS sim
    FROM articles a
    WHERE a.title % ${term}
    ORDER BY sim DESC
    LIMIT 10;
  `
}

The % operator uses pg_trgm.similarity_threshold (0.3 by default). Lower it per session with SET pg_trgm.similarity_threshold = 0.25; if you want to be more forgiving. A common production flow is:

1. Run the tsvector search.
2. If zero results, run the trigram search and present it as “Did you mean…”.
3. If still zero, offer popular content instead of an empty page.

Autocomplete: use prefix matching, not trigrams

For an as-you-type box, append :* to the last token:

const prefixQuery = term.trim().split(/\s+/).join(' & ') + ':*'

const suggestions = await prisma.$queryRaw<{ title: string }[]>`
  SELECT a.title
  FROM articles a
  WHERE a.search_vector @@ to_tsquery('english', ${prefixQuery})
  ORDER BY ts_rank_cd(a.search_vector, to_tsquery('english', ${prefixQuery})) DESC
  LIMIT 8;
`

Because this uses strict to_tsquery, sanitize the input first: strip everything except letters, digits and spaces before joining tokens.

Step 6: Accents, stop words and dictionaries

If your content contains accented characters, add the unaccent extension so cafe finds café:

CREATE EXTENSION IF NOT EXISTS unaccent;

CREATE TEXT SEARCH CONFIGURATION public.english_unaccent ( COPY = pg_catalog.english );

ALTER TEXT SEARCH CONFIGURATION public.english_unaccent
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, english_stem;

Then use 'public.english_unaccent' everywhere you used 'english', in both the generated column and the query functions. The dictionary must be identical on both sides or matches will silently fail.

One gotcha: a custom configuration created in a schema is only immutable if you reference it with its fully qualified name and keep search_path stable. If ALTER TABLE refuses the generated column, wrap it in a small IMMUTABLE SQL function that hardcodes the config name.

Performance: what to expect

Indicative numbers on a small managed instance (2 vCPU, 8 GB RAM), a table of 500,000 articles averaging 4 KB of text:

Query	Without GIN index	With GIN index
Match only (@@)	1.5 s to 4 s (seq scan)	3 ms to 15 ms
Match + ts_rank_cd + LIMIT 20	same seq scan cost	10 ms to 40 ms
Match + ts_headline on 20 rows	n/a	+15 ms to +60 ms

Practical tuning rules:

• Always run EXPLAIN ANALYZE and confirm you see Bitmap Index Scan on article_search_vector_idx. If you see Seq Scan, your query is not using the stored column (a common cause is calling to_tsvector(body) in the WHERE clause instead of referencing search_vector).
• ts_headline is the expensive part because it re-parses the original document. Apply it only to the page you return, never before the LIMIT. The query above does this correctly because ts_headline runs after the sort in the executor plan only when rows survive; if in doubt, wrap the ranked query in a CTE and join the headline on the outer level.
• GIN indexes are slower to write than B-tree. If you do heavy bulk inserts, consider SET maintenance_work_mem higher and let gin_pending_list_limit batch updates.
• Deep pagination (OFFSET 5000) is slow with ranking. Cap search results at a few hundred; nobody clicks page 30.
• Consider RUM indexes (an external extension) only if you need ranking data stored inside the index. GIN is enough for most workloads.

Postgres FTS vs Algolia vs Elasticsearch

Criterion	PostgreSQL FTS	Algolia	Elasticsearch / OpenSearch
Extra infrastructure	None	SaaS + sync pipeline	Cluster + sync pipeline
Data freshness	Transactional, instant	Eventual (seconds to minutes)	Near real time
Typo tolerance	Manual (pg_trgm)	Built in, excellent	Built in (fuzziness)
Joins and permission filters	Native SQL, trivial	Denormalize everything	Denormalize everything
Scale ceiling	Millions of docs comfortably	Very high	Very high
Monthly cost at 1M docs	0 extra	Hundreds of dollars	Cluster + ops time
Operational burden	One migration file	Index sync, quota monitoring	Shards, mappings, upgrades, JVM

When you genuinely should move off Postgres

• You are past roughly 10 to 50 million documents with high query concurrency and search is competing with OLTP traffic on the same instance.
• You need instant-search at every keystroke with sub-20 ms responses served globally from edge locations.
• You need advanced relevance tooling: synonym dictionaries managed by non-developers, A/B testing of ranking, personalization models, click analytics out of the box.
• You need faceted aggregations over dozens of dimensions on huge result sets.

Everything short of that, Postgres handles. And when you do outgrow it, you will migrate with a clean, well-understood relevance baseline instead of guessing.

Production checklist

1. Generated tsvector column with setweight per field, stored, not computed at query time.
2. GIN index created and verified with EXPLAIN ANALYZE.
3. websearch_to_tsquery for user input, never raw to_tsquery.
4. All raw queries use Prisma tagged templates or Prisma.sql, never string concatenation.
5. Empty and whitespace-only queries short-circuited in application code.
6. Query length capped (200 characters is plenty) to avoid pathological parsing.
7. Results limited and paginated, with a hard ceiling on offset.
8. BigInt counts converted before JSON serialization.
9. pg_trgm fallback wired to the zero-results branch.
10. Search latency logged so you know when the picture changes.

FAQ

How do I perform a full-text search in PostgreSQL?

Convert your text into a tsvector (ideally a stored generated column), index it with GIN, and match it against a tsquery built from user input with websearch_to_tsquery. Order results by ts_rank_cd for relevance and generate snippets with ts_headline.

Does Prisma support tsvector natively?

Prisma ORM does not have a tsvector scalar type, but it maps the column with Unsupported("tsvector") and can declare the GIN index with @@index([field], type: Gin). Actual searching is done with $queryRaw, which is fully typed through a generic parameter. Prisma also offers a search filter behind the fullTextSearchPostgres preview flag, but it has no ranking and does not use your index by default.

Is PostgreSQL full-text search better than Elasticsearch?

It is not better in raw capability, it is better in total cost for most applications. Postgres keeps search transactionally consistent with your data, supports SQL joins and row-level permission filters natively, and requires no additional service. Elasticsearch wins on very large corpora, advanced relevance tuning, faceting at scale and distributed throughput.

What are the limitations of PostgreSQL full-text search?

• No built-in typo tolerance (you add pg_trgm or a spell-check layer).
• Ranking functions are simpler than BM25 implementations found in dedicated engines.
• Synonym and thesaurus management is done through configuration files on the server, which is awkward on managed hosting.
• Multilingual documents require one configuration or one column per language.
• ts_headline is CPU-heavy, so it must be applied only to the returned page.
• GIN indexes increase write cost and disk usage.

Should I use a generated column or a trigger to maintain the tsvector?

Use a generated column on PostgreSQL 12 and above. It is declarative, cannot be bypassed, and needs no maintenance. Use a trigger only when the expression cannot be immutable, for example a per-row dynamic dictionary or a value pulled from a joined table.

How do I keep pagination counts fast?

Exact counts require scanning every matching row. If your result sets are large, either cap the count (SELECT COUNT(*) FROM (SELECT 1 FROM ... LIMIT 1000) t and display “1000+”) or use keyset pagination on the rank plus a tiebreaker column.

Can I combine full-text search with vector or semantic search?

Yes. With pgvector in the same database you can run a hybrid query: retrieve candidates from the GIN index and from the vector index, then merge scores with Reciprocal Rank Fusion. Keeping both in Postgres avoids a second datastore entirely, which is the same reasoning that drives this whole tutorial.

Wrapping up

A generated tsvector column, a GIN index, websearch_to_tsquery, ts_rank_cd and a few typed Prisma raw queries. That is a complete, ranked, highlighted, typo-tolerant search feature with zero extra infrastructure, zero sync jobs and zero monthly search bill. Start here, measure real latency on real data, and only reach for a dedicated engine when the numbers tell you to.

Need help wiring this into an existing Node.js or Next.js codebase, or auditing a search feature that has become slow? The team at Box Software works on PostgreSQL performance and Prisma architecture every day. Get in touch and we will look at your query plans with you.