Nov 20, 20267 min read

Postgres Full-Text Search for Backend APIs

How to build search on Postgres with tsvector, a generated column and a GIN index, parse user queries with websearch_to_tsquery, rank and highlight results, add typo tolerance with pg_trgm, know when to move to a dedicated search engine, and ship it as a Go API.

Illustration for Postgres Full-Text Search for Backend APIs

Most backends reach for search the week a customer cannot find an order by name, and the first instinct is Elasticsearch or a hosted search service. Postgres already has full-text search with stemming, ranking and highlighted snippets.

For catalogs, help centers and ticket queues it is usually fast enough with one column and one index. This guide covers that setup, safe query functions for user input, typo tolerance, and when a separate engine is worth it.

How Does Postgres Full-Text Search Work?

Full-text search turns documents into a tsvector, a sorted list of normalized words called lexemes with their positions, and queries into a tsquery. The @@ operator checks whether a vector matches a query:

SELECT to_tsvector('english', 'Refunds are processed within five business days'); -- 'busi':6 'day':7 'five':5 'process':3 'refund':1 'within':4 SELECT to_tsvector('english', 'Refunds are processed within five business days') @@ websearch_to_tsquery('english', 'refund processing'); -- true

The english configuration drops stop words like "are" and stems words, so "processing" and "processed" both become process. Pick the configuration that matches the content's language, and store it per row if the table holds several languages.

How Do You Store and Index the Search Vector?

Compute the vector once per write in a generated column and index it with GIN, so queries read the index instead of parsing every row:

CREATE TABLE help_articles ( id BIGSERIAL PRIMARY KEY, title TEXT NOT NULL, body TEXT NOT NULL, updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), search tsvector GENERATED ALWAYS AS ( setweight(to_tsvector('english', coalesce(title, '')), 'A') || setweight(to_tsvector('english', coalesce(body, '')), 'B') ) STORED ); CREATE INDEX help_articles_search ON help_articles USING GIN (search);

setweight labels title words as A and body words as B, which the ranking functions use to score a title match above a body match. The table and index section of the docs compares this approach with an expression index; the generated column is easier to get right because queries cannot accidentally use a slightly different expression and skip the index.

Which Query Function Should Handle User Input?

Use websearch_to_tsquery for anything typed into a search box. It understands quoted phrases, or and a leading - for exclusion, and it never raises a syntax error, while to_tsquery throws on input like refund &, as the query parsing docs describe:

SELECT id, title, ts_rank(search, q) AS rank, ts_headline('english', body, q, 'MaxFragments=2, MaxWords=20, MinWords=8') AS snippet FROM help_articles, websearch_to_tsquery('english', '"credit card" refund -paypal') AS q WHERE search @@ q ORDER BY rank DESC LIMIT 20;

ts_rank scores by how often and where the terms appear, using the weights, and ts_headline returns snippets with matches wrapped in <b> tags. ts_headline reparses the original text, so it is the expensive part of the query: run it only on the rows you return, which the LIMIT above already does because Postgres computes the select list after sorting.

How Do You Handle Typos?

Full-text search matches lexemes exactly, so "refnud" finds nothing, and the pg_trgm extension compares strings by shared three-letter sequences, which tolerates typos well on short fields. Add a trigram index on titles and use it as a fallback when the full-text query returns no rows:

CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE INDEX help_articles_title_trgm ON help_articles USING GIN (title gin_trgm_ops); SELECT id, title FROM help_articles WHERE title % 'refnud polcy' ORDER BY similarity(title, 'refnud polcy') DESC LIMIT 5;

When Should You Move to a Dedicated Search Engine?

Postgres search shares CPU and memory with your transactional workload, and it has no built-in faceting, synonyms per tenant, or learned relevance. Signs it is time for OpenSearch, Elasticsearch or a hosted engine are search queries showing up in the database's top CPU consumers, product requirements for facets with counts over large result sets, or typo tolerance needed on long text. Until then, a read replica for search traffic often buys a lot of headroom without a second system to keep in sync.

How Do You Ship Postgres Search as a Go API with Encore?

Encore

Encore is a backend framework with built-in infrastructure, so the search table, its indexes and the API that queries them live in one Go service, and the database is provisioned for you locally and as RDS or Cloud SQL in your own cloud account. Both Postgres extensions used here are available in Encore's local Postgres.

Set up the app

Get started
brew install encoredev/tap/encore &&
encore app create
Copy

Run in your terminal to get started locally.

~
$ encore app create help-center --example=hello-world$ cd help-center && mkdir -p help/migrations

The migration creates the table with the generated column and both indexes from the sections above:

-- help/migrations/1_articles.up.sql CREATE EXTENSION IF NOT EXISTS pg_trgm; CREATE TABLE help_articles ( id BIGSERIAL PRIMARY KEY, title TEXT NOT NULL, body TEXT NOT NULL, updated_at TIMESTAMPTZ NOT NULL DEFAULT now(), search tsvector GENERATED ALWAYS AS ( setweight(to_tsvector('english', coalesce(title, '')), 'A') || setweight(to_tsvector('english', coalesce(body, '')), 'B') ) STORED ); CREATE INDEX help_articles_search ON help_articles USING GIN (search); CREATE INDEX help_articles_title_trgm ON help_articles USING GIN (title gin_trgm_ops);

The search endpoint runs the full-text query and falls back to trigram matching on titles when nothing matches:

// help/search.go package help import ( "context" "strings" "encore.dev/beta/errs" "encore.dev/storage/sqldb" ) var db = sqldb.NewDatabase("help", sqldb.DatabaseConfig{ Migrations: "./migrations", }) type SearchParams struct { Q string `query:"q"` Limit int `query:"limit"` } type Hit struct { ID int64 `json:"id"` Title string `json:"title"` Snippet string `json:"snippet,omitempty"` Rank float32 `json:"rank"` } type SearchResponse struct { Hits []Hit `json:"hits"` Suggested bool `json:"suggested"` } //encore:api public method=GET path=/help/search func Search(ctx context.Context, p *SearchParams) (*SearchResponse, error) { q := strings.TrimSpace(p.Q) if q == "" || len(q) > 200 { return nil, &errs.Error{Code: errs.InvalidArgument, Message: "q must be 1 to 200 characters"} } limit := p.Limit if limit <= 0 || limit > 50 { limit = 20 } hits, err := collect(db.Query(ctx, ` SELECT id, title, ts_headline('english', body, q, 'MaxFragments=2, MaxWords=20, MinWords=8'), ts_rank(search, q) FROM help_articles, websearch_to_tsquery('english', $1) AS q WHERE search @@ q ORDER BY ts_rank(search, q) DESC LIMIT $2`, q, limit)) if err != nil || len(hits) > 0 { return &SearchResponse{Hits: hits}, err } // No full-text match: suggest titles that are close, to catch typos. hits, err = collect(db.Query(ctx, ` SELECT id, title, '', similarity(title, $1) FROM help_articles WHERE title % $1 ORDER BY similarity(title, $1) DESC LIMIT 5`, q)) return &SearchResponse{Hits: hits, Suggested: true}, err } func collect(rows *sqldb.Rows, err error) ([]Hit, error) { if err != nil { return nil, err } defer rows.Close() hits := []Hit{} for rows.Next() { var h Hit if err := rows.Scan(&h.ID, &h.Title, &h.Snippet, &h.Rank); err != nil { return nil, err } hits = append(hits, h) } return hits, rows.Err() }
~/help-center
$ encore run$ encore db shell help --writehelp=> INSERT INTO help_articles (title, body) VALUES ('Refund policy', 'Refunds to a credit card are processed within five business days.');$ curl "localhost:4000/help/search?q=credit+card+refund"$ curl "localhost:4000/help/search?q=refnud+polcy"

encore run starts Postgres, enables the extension and applies the migration, so the same SQL is tested locally that runs against RDS or Cloud SQL after you deploy to your AWS or GCP account. The database docs cover migrations and the Postgres extensions page lists what is available.

An agent asked to also search ticket comments can add a second migration with the new column weight and update the query in one change, then run both searches locally against Postgres, without a search cluster to provision.

Best fit: teams that need search over catalogs, help content or records already in Postgres, with the schema, indexes and API in one service and the database in their own cloud.

Postgres Full-Text Search Checklist

  • Store the tsvector in a generated column with weights for title and body.
  • Index it with GIN and query the column with @@.
  • Parse user input with websearch_to_tsquery, never to_tsquery.
  • Rank with ts_rank and run ts_headline only on returned rows.
  • Add pg_trgm on short fields for typo suggestions.
  • Watch search CPU on the primary and consider a replica before a new engine.

Frequently asked questions

Is Postgres full-text search good enough for production?

Yes. For searching product catalogs, help centers, tickets or documents up to millions of rows, queries with a tsvector column and a GIN index usually take milliseconds. Move to a dedicated engine when you need typo tolerance and faceting across very large datasets, relevance tuning beyond weights, or search traffic that should not share resources with the primary database.

How do I make Postgres full-text search fast?

Store the tsvector in a generated column, index it with a GIN index, and query that column with the @@ operator. Computing to_tsvector inside the WHERE clause on every query cannot use the index unless the expression matches an expression index exactly.

What is the difference between to_tsquery and websearch_to_tsquery?

to_tsquery expects query syntax with operators such as & and | and throws an error on malformed input, so it is unsafe for raw user input. websearch_to_tsquery accepts search-box style text with quotes for phrases, OR and a leading minus for exclusion, and never raises a syntax error.

Does Postgres full-text search handle typos?

No. Full-text search matches normalized words, so a misspelled word finds nothing. Add the pg_trgm extension and a trigram index on short fields such as titles to suggest close matches when the full-text query returns no results.

Deploy to your cloud on AWS and Google Cloud
without managing infrastructure

Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.

~/orders — claude
Claude Code
Claude Code v2.1.180Opus 5.5~/orders