Full Text Search in Postgres Used From a Go Service

A search box is one of the first features users ask for, and a service that already uses PostgreSQL can have a good one without adding a search server. Full text search in PostgreSQL stems words, ignores stop words, ranks by relevance and uses an index. It will not match what Elasticsearch does at scale, and for tens or hundreds of thousands of documents it is plenty. This article builds it from Go 1.25.6 with pgx against PostgreSQL 18.6: the column, the index, the query and the ranking, on 100,000 rows with measured timings.

It is one of the PostgreSQL techniques in the database cornerstone. When search outgrows it, Elasticsearch and OpenSearch from Go covers the next step.

The data type and the column

Full text search works on two types. A tsvector is a document reduced to normalised words (lexemes) with their positions. A tsquery is a search expression. The operator @@ asks whether a vector matches a query.

The program creates the table with the vector as a stored generated column, so it can never drift from the text:

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"time"

	_ "github.com/jackc/pgx/v5/stdlib"
)

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

func exec(db *sql.DB, q string, args ...any) {
	if _, err := db.Exec(q, args...); err != nil {
		log.Fatalf("%s: %v", q, err)
	}
}

func timeIt(db *sql.DB, label, q string, args ...any) {
	start := time.Now()
	var n int
	if err := db.QueryRow(q, args...).Scan(&n); err != nil {
		log.Fatal(err)
	}
	fmt.Printf("%-44s %6d rows  %v\n", label, n, time.Since(start).Round(100*time.Microsecond))
}

func main() {
	ctx := context.Background()
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	exec(db, "drop table if exists docs614")
	exec(db, `create table docs614 (
		id bigserial primary key,
		title text not null,
		body text not null,
		tsv tsvector generated always as (setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', body), 'B')) stored
	)`)
	// 100,000 documents of 40 words: 15 percent from twelve real terms, the rest from 300 filler tokens.
	exec(db, `insert into docs614 (title, body)
		select 'note ' || i, (select string_agg(case when random() < 0.15 then ('{go,goroutine,channel,mutex,context,sql,index,query,cache,leak,timeout,handler}'::text[])[1 + (random() * 11)::int] else 'w' || (random() * 300)::int end, ' ') from generate_series(1, 40) where i > 0)
		from generate_series(1, 100000) i`)
	exec(db, "insert into docs614 (title, body) values ('Finding a goroutine leak', 'A goroutine leak keeps memory alive. Use a pprof goroutine profile to find the leak.'), ('Context timeouts', 'Every handler needs a context with a timeout so a slow query cannot pile up goroutines.')")
	exec(db, "analyze docs614")

	fmt.Println("search for the phrase: goroutine leak")
	timeIt(db, "common terms, no index (seq scan)", "select count(*) from docs614 where tsv @@ websearch_to_tsquery('english', 'goroutine leak')")
	timeIt(db, "rare term (pprof), no index (seq scan)", "select count(*) from docs614 where tsv @@ plainto_tsquery('english', 'pprof')")
	exec(db, "create index docs614_tsv on docs614 using gin (tsv)")
	exec(db, "analyze docs614")
	timeIt(db, "common terms, GIN index", "select count(*) from docs614 where tsv @@ websearch_to_tsquery('english', 'goroutine leak')")
	timeIt(db, "rare term (pprof), GIN index", "select count(*) from docs614 where tsv @@ plainto_tsquery('english', 'pprof')")

	// Ranking and headlines for the user facing query.
	rows, err := db.QueryContext(ctx, `
		select id, title, round(ts_rank(tsv, q)::numeric, 4),
		       ts_headline('english', body, q, 'StartSel=[, StopSel=], MaxWords=12, MinWords=6')
		from docs614, websearch_to_tsquery('english', 'goroutine leak pprof') q
		where tsv @@ q
		order by ts_rank(tsv, q) desc, id limit 3`)
	if err != nil {
		log.Fatal(err)
	}
	defer rows.Close()
	fmt.Println("top matches for: goroutine leak pprof")
	for rows.Next() {
		var id int64
		var title, head string
		var rank float64
		if err := rows.Scan(&id, &title, &rank, &head); err != nil {
			log.Fatal(err)
		}
		fmt.Printf("  id=%d rank=%v title=%q headline=%q\n", id, rank, title, head)
	}

	// How the query text is interpreted.
	for _, q := range []string{`goroutine leak`, `"goroutine leak"`, `goroutine -leak`, `goroutine or leak`} {
		var t string
		_ = db.QueryRow("select websearch_to_tsquery('english', $1)::text", q).Scan(&t)
		fmt.Printf("websearch_to_tsquery(%-18q) = %s\n", q, t)
	}
	var stem string
	_ = db.QueryRow("select to_tsvector('english', 'Leaking goroutines leaked: the handlers')::text").Scan(&stem)
	fmt.Println("english stemming:", stem)
}

search for the phrase: goroutine leak
common terms, no index (seq scan)             17358 rows  19.1ms
rare term (pprof), no index (seq scan)            1 rows  14.1ms
common terms, GIN index                       17358 rows  12.4ms
rare term (pprof), GIN index                      1 rows  500µs
top matches for: goroutine leak pprof
  id=100001 rank=1 title="Finding a goroutine leak" headline="[goroutine] [leak] keeps memory alive. Use a [pprof]"
websearch_to_tsquery("goroutine leak"  ) = 'goroutin' & 'leak'
websearch_to_tsquery("\"goroutine leak\"") = 'goroutin' <-> 'leak'
websearch_to_tsquery("goroutine -leak" ) = 'goroutin' & !'leak'
websearch_to_tsquery("goroutine or leak") = 'goroutin' | 'leak'
english stemming: 'goroutin':2 'handler':5 'leak':1,3

tsv tsvector generated always as (
    setweight(to_tsvector('english', title), 'A') ||
    setweight(to_tsvector('english', body), 'B')
) stored

to_tsvector('english', ...) tokenises, drops stop words, stems and records positions. The configuration name matters: it is the language, and the same one must be used when querying. setweight marks words from the title as weight A and the body as B, which ts_rank later uses to put title matches first. The column is rewritten whenever the title or body changes, so there is no trigger to write and none to forget.

The lab fills the table with 100,000 documents of 40 words each, 15 percent drawn from twelve real terms and the rest from 300 filler tokens, plus two real sentences about goroutine leaks and context timeouts.

What stemming does

The last line of the output shows the normalisation: 'Leaking goroutines leaked: the handlers' became 'goroutin':2 'handler':5 'leak':1,3. Three things happened. Both Leaking and leaked collapsed to the lexeme leak at positions 1 and 3. The plural goroutines became the stem goroutin, which is not an English word and does not need to be. And the vanished as a stop word. A query goes through the same process, so goroutine finds goroutines.

Stemming is a feature and a limit. It matches word forms, and it does not match synonyms or typos. Code identifiers such as http.NewRequest tokenise in ways that rarely suit them, so for searching a codebase use the simple configuration, which does not stem.

Turning user input into a query

PostgreSQL has four query parsers, and the choice decides how much cleaning you must do:

• plainto_tsquery ANDs all the words. Safe, no operators.
• phraseto_tsquery requires the words next to each other.
• to_tsquery takes the full operator syntax and raises a syntax error on stray punctuation. Never feed it raw user input.
• websearch_to_tsquery accepts input in the style of a web search and never raises a syntax error.

The lab prints how websearch_to_tsquery read four inputs, and the table shows three of them:

The fourth input, goroutine or leak, became the two lexemes joined by the OR operator. Spaces mean AND, quotes mean a phrase (<-> is "followed by"), a minus excludes and or means OR. For a search box this is the right default: users already know that syntax, and the function accepts any string. Pass the text as a bind parameter, never concatenated, and the query is safe from injection as with any other parameter; see SQL injection in Go.

The index, and when it helps

Without an index, every search scans the table and evaluates @@ on every row. A GIN index on the tsvector maps each lexeme to the rows containing it, which is the right structure for this.

The program times two queries before and after create index ... using gin (tsv):

The index is decisive for a selective query, 28 times faster here, and barely helps for a query that matches 17 percent of the table: PostgreSQL must still fetch and return 17,000 rows, and for that much of the table a scan is competitive. This is the general rule for any index, and it explains why search quality matters more than raw speed: real users search for specific things. A query that returns most of the table is a bad search, whatever the index. Always limit the result set (see below) so the cost depends on the page, not the match count.

Reading the ranking query's plan with explain analyze tells you whether the index is used; reading EXPLAIN shows how. A GIN index costs write time and disk, and fastupdate batches inserts into a pending list that is merged later; very write heavy tables may notice it.

Ranking and snippets

Matching is a yes or no; users want the best first. ts_rank(tsv, query) scores a match using term frequency and the weights set earlier. The program searches goroutine leak pprof and takes the top three by rank:

select id, title, ts_rank(tsv, q), ts_headline('english', body, q, 'StartSel=[, StopSel=]')
from docs614, websearch_to_tsquery('english', 'goroutine leak pprof') q
where tsv @@ q
order by ts_rank(tsv, q) desc, id limit 3

The output shows document 100001, the real article, with rank 1 and the headline [goroutine] [leak] keeps memory alive. Use a [pprof]. ts_headline takes the original text and returns a fragment with matched words wrapped in the markers you choose. Use markers your frontend can turn into <mark>, and escape the rest of the text, since the output is built from user supplied content.

Two cautions. ts_headline is slow because it re-parses the document, so call it only for the page of results you display, never for every match. And ranking has to compute a score for every matching row before sorting, so for a query matching tens of thousands of rows, the order by ts_rank is the expensive part. Cap what is ranked, or use a subquery to apply the limit to candidates first.

Putting it in a Go handler

The handler is short because the work is in SQL. Read the q parameter, reject an empty string, and run a parameterised query with a limit and an offset or, better, a cursor on (rank, id):

rows, err := db.QueryContext(ctx, `
    select id, title, ts_headline('english', body, q, 'StartSel=<mark>, StopSel=</mark>, MaxWords=25')
    from docs, websearch_to_tsquery('english', $1) q
    where tsv @@ q
    order by ts_rank(tsv, q) desc, id
    limit 20`, query)

Pass the request.Context() so an abandoned request cancels the query, set a statement timeout for searches in particular because one slow query can hold a connection for a long time, and cache popular queries for a few seconds if the traffic warrants it. Keep the language configuration in one constant used by the column, the index and every query. If the index expression and the query use different configurations, PostgreSQL cannot use the index.

When to move off it

Built in search is a good fit for a few hundred thousand documents, simple relevance and one or two languages. Signs you need something else: typo tolerance and fuzzy matching (the pg_trgm extension covers part of that), faceted counts over millions of rows, synonyms and per field boosting beyond the four weights, or search load that competes with your transactional traffic. At that point a separate engine fed by the outbox pattern is the usual design. The manual's chapter on full text search is thorough, and its page on GIN index behaviour explains the structure behind the timings.