Build a Search Endpoint in Go With Postgres Full Text

Search is the feature everyone wants on day two. The first instinct is WHERE title LIKE '%word%', which cannot use an index, does not understand plurals or phrases and ranks nothing. The second instinct is to deploy a search cluster. For a few hundred thousand documents in a database you already run, there is a better middle step: PostgreSQL has full text search built in, and it is good enough to ship.

This project builds a search endpoint in Go on top of it. It covers the schema, the query syntax your users can type, ranking, highlighted snippets, a typo fallback, and the limits that keep the endpoint from becoming a denial-of-service tool. It follows the structure of a CRUD API and uses the pagination ideas from API pagination. The lab starts a throwaway PostgreSQL 18 container, so you need Docker and nothing else. Everything ran on Go 1.25.6.

What Postgres full text search does

Two types do the work. A tsvector is a document reduced to normalised words (lexemes) with positions: lower-cased, stop words removed, and stemmed, so "requests" and "request" become request. A tsquery is the same treatment applied to the query, with operators for AND, OR, NOT and phrase. The @@ operator asks whether a vector matches a query. A GIN index over the vector column makes that fast.

The decisions that matter are which language configuration to use (english here, which sets the stemmer and the stop words), which text goes into the vector with what weight, and which function turns user input into a query.

The schema

The table stores the vector in a generated column, so it is always consistent with the text it came from and no trigger or application code has to maintain it:

CREATE TABLE articles (
  id    serial PRIMARY KEY,
  title text NOT NULL,
  body  text NOT NULL,
  search tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', body), 'B')
  ) STORED
);
CREATE INDEX articles_search_idx ON articles USING GIN (search);

setweight labels lexemes from the title A and from the body B. The ranking function gives A a higher weight than B by default, so a title match beats a body match. The STORED generated column costs disk space and write time; in exchange, the query and index use a precomputed value and updates stay in one place.

The lab also creates a trigram index on the title, which the typo fallback uses.

The query side

PostgreSQL has four ways to build a tsquery from text, and one of them is the right default for user input:

• to_tsquery expects tsquery syntax and raises an error on anything else, like an unbalanced quote. Not for user input.
• plainto_tsquery treats everything as AND, with no operators.
• phraseto_tsquery treats the text as one phrase.
• websearch_to_tsquery accepts the familiar web-search syntax: words are ANDed, "quoted phrases" match as phrases, or means OR, and -word excludes. It never raises a syntax error on user input.

That last property is why it is the default. You can pass the raw query string as a bound parameter and it will not throw, and it is not an injection route either, since it goes through a parameter and not string concatenation.

The search SQL ranks and highlights in one statement:

SELECT id, title,
       ts_headline('english', body, q, 'MaxFragments=1, MaxWords=12, MinWords=5, StartSel=[[, StopSel=]]') AS snippet,
       ts_rank(search, q) AS rank
FROM articles, websearch_to_tsquery('english', $1) AS q
WHERE search @@ q
ORDER BY rank DESC, id
LIMIT $2

The FROM articles, websearch_to_tsquery(...) AS q form computes the query once and lets both functions refer to it. The secondary sort on id makes ties deterministic, which matters for pagination.

The endpoint

The handler validates, applies a timeout, runs the query and falls back to fuzzy matching if nothing was found.

package main

import (
	"context"
	"encoding/json"
	"fmt"
	"net/http"
	"net/http/httptest"
	"os"
	"strconv"
	"strings"
	"time"

	"github.com/jackc/pgx/v5/pgxpool"
)

type Hit struct {
	ID      int     `json:"id"`
	Title   string  `json:"title"`
	Snippet string  `json:"snippet"`
	Rank    float64 `json:"rank"`
}

const schema = `
CREATE EXTENSION IF NOT EXISTS pg_trgm;
DROP TABLE IF EXISTS articles;
CREATE TABLE articles (
  id    serial PRIMARY KEY,
  title text NOT NULL,
  body  text NOT NULL,
  -- title counts more than body; stored so the index and the query use the same expression
  search tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', body), 'B')
  ) STORED
);
CREATE INDEX articles_search_idx ON articles USING GIN (search);
CREATE INDEX articles_title_trgm ON articles USING GIN (title gin_trgm_ops);
INSERT INTO articles (title, body) VALUES
 ('Graceful shutdown in Go', 'Handle SIGTERM, stop accepting connections and wait for in-flight requests with http.Server.Shutdown.'),
 ('Connection pools in database/sql', 'Set max open connections and idle connections so a burst of requests does not exhaust the database.'),
 ('Timeouts for HTTP servers', 'ReadHeaderTimeout and IdleTimeout protect a server from slow clients. Shutdown waits for requests to finish.'),
 ('Running tests in parallel', 'Use t.Parallel for independent tests and avoid shared state between requests.'),
 ('Context cancellation', 'Pass a context into database queries so a cancelled request stops the query and releases the connection.'),
 ('Structured logging with slog', 'Attach request ids to log records and keep the output machine readable.');
`

const searchSQL = `
SELECT id, title,
       ts_headline('english', body, q, 'MaxFragments=1, MaxWords=12, MinWords=5, StartSel=[[, StopSel=]]') AS snippet,
       ts_rank(search, q) AS rank
FROM articles, websearch_to_tsquery('english', $1) AS q
WHERE search @@ q
ORDER BY rank DESC, id
LIMIT $2`

// fuzzySQL is the fallback for typos: trigram similarity against the title, only used when full text found nothing.
const fuzzySQL = `
SELECT id, title, left(body, 40) AS snippet, word_similarity($1, title) AS rank
FROM articles
WHERE $1 <% title
ORDER BY rank DESC, id
LIMIT $2`

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
	if err != nil {
		panic(err)
	}
	defer pool.Close()
	if _, err := pool.Exec(ctx, schema); err != nil {
		panic(err)
	}

	mux := http.NewServeMux()
	mux.HandleFunc("GET /search", func(w http.ResponseWriter, r *http.Request) {
		q := strings.TrimSpace(r.URL.Query().Get("q"))
		if q == "" || len(q) > 200 {
			http.Error(w, `{"error":"q is required and at most 200 characters"}`, http.StatusBadRequest)
			return
		}
		limit, _ := strconv.Atoi(r.URL.Query().Get("limit"))
		if limit < 1 || limit > 50 {
			limit = 10
		}
		cctx, cancel := context.WithTimeout(r.Context(), 2*time.Second)
		defer cancel()
		run := func(sql string) ([]Hit, error) {
			rows, err := pool.Query(cctx, sql, q, limit)
			if err != nil {
				return nil, err
			}
			defer rows.Close()
			hits := []Hit{}
			for rows.Next() {
				var h Hit
				var rank float32
				if err := rows.Scan(&h.ID, &h.Title, &h.Snippet, &rank); err != nil {
					return nil, err
				}
				h.Rank = float64(int(rank*1000+0.5)) / 1000
				hits = append(hits, h)
			}
			return hits, rows.Err()
		}
		hits, err := run(searchSQL)
		mode := "fulltext"
		if err == nil && len(hits) == 0 {
			hits, err = run(fuzzySQL)
			mode = "fuzzy"
		}
		if err != nil {
			http.Error(w, `{"error":"search failed"}`, http.StatusInternalServerError)
			return
		}
		enc := json.NewEncoder(w)
		enc.SetEscapeHTML(false) // readable output; the client still has to escape every snippet before putting it in a page
		enc.Encode(map[string]any{"query": q, "mode": mode, "hits": hits})
	})
	srv := httptest.NewServer(mux)
	defer srv.Close()

	get := func(label, query string) {
		resp, _ := http.Get(srv.URL + "/search?" + query)
		var body strings.Builder
		buf := make([]byte, 4096)
		n, _ := resp.Body.Read(buf)
		body.Write(buf[:n])
		resp.Body.Close()
		fmt.Printf("%s -> %d\n%s\n", label, resp.StatusCode, strings.TrimSpace(body.String()))
	}
	get("shutdown", "q=shutdown")
	get("stemming: requests", "q=request+connections")
	get("phrase and exclusion", "q=%22idle+connections%22+-timeouts")
	get("word in title and body", "q=context")
	get("empty", "q=")
	get("typo", "q=shutdwn")

	var plan []string
	tx, _ := pool.Begin(ctx)
	tx.Exec(ctx, "SET LOCAL enable_seqscan = off") // six rows would never use the index on their own
	rows, _ := tx.Query(ctx, "EXPLAIN SELECT id FROM articles WHERE search @@ websearch_to_tsquery('english', 'shutdown')")
	for rows.Next() {
		var line string
		rows.Scan(&line)
		plan = append(plan, line)
	}
	rows.Close()
	tx.Rollback(ctx)
	fmt.Println(strings.Join(plan, "\n"))
}

The run.sh that makes the lab self-contained starts PostgreSQL in Docker, waits for it, runs the program and stops the container:

docker run -d --rm --name pg-search-lab -e POSTGRES_PASSWORD=secret -p 55432:5432 postgres:18 >/dev/null
for i in $(seq 1 60); do docker exec pg-search-lab pg_isready -U postgres >/dev/null 2>&1 && break; sleep 1; done
sleep 2
DATABASE_URL='postgres://postgres:secret@localhost:55432/postgres' go run .
docker stop pg-search-lab >/dev/null

shutdown -> 200
{"hits":[{"id":1,"title":"Graceful shutdown in Go","snippet":"Handle SIGTERM, stop accepting connections","rank":0.608},{"id":3,"title":"Timeouts for HTTP servers","snippet":"server from slow clients. [[Shutdown]] waits for requests to finish","rank":0.243}],"mode":"fulltext","query":"shutdown"}
stemming: requests -> 200
{"hits":[{"id":2,"title":"Connection pools in database/sql","snippet":"[[connections]] and idle [[connections]] so a burst of [[requests]] does not exhaust","rank":0.554},{"id":1,"title":"Graceful shutdown in Go","snippet":"stop accepting [[connections]] and wait for in-flight [[requests]] with http.Server.Shutdown","rank":0.305},{"id":5,"title":"Context cancellation","snippet":"cancelled [[request]] stops the query and releases the [[connection]]","rank":0.305}],"mode":"fulltext","query":"request connections"}
phrase and exclusion -> 200
{"hits":[{"id":2,"title":"Connection pools in database/sql","snippet":"open [[connections]] and [[idle]] [[connections]] so a burst of requests does","rank":0.754}],"mode":"fulltext","query":"\"idle connections\" -timeouts"}
word in title and body -> 200
{"hits":[{"id":5,"title":"Context cancellation","snippet":"Pass a [[context]] into database queries so a cancelled request stops","rank":0.669}],"mode":"fulltext","query":"context"}
empty -> 400
{"error":"q is required and at most 200 characters"}
typo -> 200
{"hits":[{"id":1,"title":"Graceful shutdown in Go","snippet":"Handle SIGTERM, stop accepting connectio","rank":0.625}],"mode":"fuzzy","query":"shutdwn"}
Bitmap Heap Scan on articles  (cost=12.82..19.93 rows=3 width=4)
  Recheck Cond: (search @@ '''shutdown'''::tsquery)
  ->  Bitmap Index Scan on articles_search_idx  (cost=0.00..12.82 rows=3 width=0)
        Index Cond: (search @@ '''shutdown'''::tsquery)

What the results show

Weighted ranking works. The query shutdown matched two articles. The one with the word in its title scored 0.608; the one that mentions it in the body scored 0.243. That ordering is the reason to bother with weights.

Stemming and multiple terms. request connections matched "requests" and "connection" through their stems. The article about connection pools came first because it contained both words repeatedly. Notice the snippet shows the original forms, [[requests]], not the stemmed ones.

Web-search syntax works as people expect. The quoted phrase "idle connections" with -timeouts matched only the pooling article, with idle highlighted. Not a line of parsing code on our side.

Validation returns a 400. An empty q is refused before the database is touched. The length cap of 200 characters and the limit bounds keep the cost of one request predictable.

Typos fall through to trigrams. shutdwn is not a word the English stemmer knows, so full text found nothing. The fallback uses pg_trgm's word_similarity against the title and returned the shutdown article with mode: "fuzzy". Exposing the mode lets the front end say "Showing results for similar words".

The index is usable. The EXPLAIN shows a Bitmap Index Scan on articles_search_idx. I had to disable sequential scans to get it, because with six rows a sequential scan is cheaper and the planner is right to choose it. The point of the line is that the query shape can use the index when the table is large enough to need it. Load a few hundred thousand rows and run EXPLAIN ANALYZE on your own data before trusting any number from a toy table.

Highlighting and the escaping trap

ts_headline returns an excerpt of the original text with matches wrapped in markers you choose. The default markers are <b> and </b>, and the text in between is the raw column content. If that content came from users and contains <script>, the snippet contains <script> too. Inserting the result into a page with innerHTML is a stored XSS bug.

The endpoint therefore uses [[ and ]], which are unlikely to appear in text. The client escapes the whole snippet as plain text first, and only then replaces the markers with <mark> elements. In a React app, split on the markers and render <mark> for the odd pieces, and no HTML string is ever built. This is a case where the tidy-looking default is the dangerous one. See the XSS entry in the OWASP cheat sheet series for output encoding rules.

The lab encodes JSON with SetEscapeHTML(false) so that the output is readable; Go's default escapes < and > in JSON strings, which is a useful safety net that you give up for readability only here.

Pagination and ranking

Ranked results are awkward to paginate. OFFSET gets slower with depth and shifts when documents are added between requests. Keyset pagination works, but the key has to be the sort key, (rank, id), and rank is a float computed per query. A reasonable approach: return the rank and id of the last row as a cursor, and add WHERE (rank, id) < ($cursor_rank, $cursor_id) using a subquery. For most search UIs the pragmatic choice is to cap the result at the top 50 or 100 and not offer page 40, since nobody reads that far. Say so in the API: limit has a maximum, and results are "best matches", not a stable list.

Operating it

Index maintenance. GIN indexes are slower to update than B-trees. PostgreSQL batches changes in a pending list, which is fast for single inserts and occasionally causes a write to pay for a flush. For bulk loads, create the index after loading.

Language. One configuration per column. If your content is multilingual, store the language per row and use to_tsvector(language_col::regconfig, ...), or index a simple configuration that does no stemming. Match the configuration in the query to the one in the index, or the index will not be used.

Timeouts and limits. The handler gives the query two seconds and caps both the query length and the result size. Add a per-IP or per-key rate limit, since search is the endpoint abusers love. See API rate limiting.

Permissions. The query above searches everything. In a multi-tenant or permissioned system, the WHERE must also filter by tenant and visibility, and the filter belongs in the same query, not applied to the result afterwards, which would break pagination and leak counts. The ideas are in multi-tenant APIs.

Relevance tuning. ts_rank uses term frequency; ts_rank_cd uses cover density, which rewards terms that are close together. Normalisation flags control how document length affects the score. Change one thing at a time against a fixed set of queries you care about.

Testing the endpoint

A search endpoint is easy to test badly. A test that asserts the exact rank value fails whenever PostgreSQL tweaks its scoring. Test properties instead: the article with the term in its title comes before the one with the term in its body, a query with a stop word and nothing else returns an empty list and not an error, a quoted phrase does not match the words in a different order, and the endpoint answers 400 on empty and oversize input.

Run these against a real PostgreSQL, not a mock, because the behaviour under test is the database's. The Postgres testing article and the Testcontainers article show how to start one per test run in a few seconds. Load a fixture of a dozen short documents with known relationships and keep it in the repository, so a failing test can be read without a database dump.

Test the security properties too. A query containing quotes, backslashes, parentheses, an ampersand and a very long string must come back as a normal response, never a 500. That is a table of hostile strings and one assertion, and it is the reason to pick websearch_to_tsquery over to_tsquery.

GIN or GiST

PostgreSQL offers two index types for tsvector. GIN stores, for each lexeme, the list of rows that contain it. Lookups are fast and the index is larger, and updates are slower. GiST uses a signature file that is smaller and faster to update, but lossy, so the planner rechecks rows and lookups are slower on big tables. The documentation's general advice is GIN for mostly-read data, which is what most searchable content is, and this article follows it. If your table takes heavy writes and few searches, measure GiST.

Searching more than one table

Real search boxes cover several kinds of thing: articles, authors, tags. Two designs work. A materialised search table holds one row per searchable entity with a kind, a target id, a title, a body and the vector, kept up to date by the code that writes the entities (or by triggers). One query searches everything and the results carry enough to build a link. The alternative is a UNION ALL of per-table queries, which avoids the duplicate data but makes ranking across tables arbitrary, since scores from different documents are not comparable in any strict sense.

Whichever you choose, keep the interface the endpoint exposes the same, so you can change the storage later: a list of hits, each with a kind, an id, a title, a snippet, and a rank that clients treat as an ordering and nothing more.

Changing the configuration later

Changing the language configuration, adding a column to the vector or switching to a different weight scheme means rebuilding the generated column and the index. On a large table, that is a long operation. Do it with a new column added beside the old one, CREATE INDEX CONCURRENTLY on it, then switch the query and drop the old column once traffic has moved. Version the SQL in your migrations, and record the configuration name in a comment next to the column, since an index built with english and a query using simple silently stops using the index.

Where it stops being enough

PostgreSQL full text search is a poor fit when you need typo tolerance as the main experience (the trigram fallback is a patch, not a design), faceted counts across millions of documents, per-language analysers for CJK text, or very fast response on a corpus too big for the database to share a host with transactions. At that point Meilisearch, Typesense, OpenSearch or a hosted service is the next step, and the endpoint contract above stays the same: only the function behind it changes. Until then, one table, one generated column and one index is about as little infrastructure as search gets. The official PostgreSQL text search documentation covers the dictionaries and ranking functions in detail, and the pgx documentation describes the pool used here.