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.

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.
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.
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.
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.
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;
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.
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.
Run in your terminal to get started locally.
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()
}
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.
tsvector in a generated column with weights for title and body.@@.websearch_to_tsquery, never to_tsquery.ts_rank and run ts_headline only on returned rows.pg_trgm on short fields for typo suggestions.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.
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.
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.
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.