Full-text search is one of those features teams assume requires Elasticsearch or a separate search service. PostgreSQL has had it built in for years: tsvector, tsquery, GIN indexes, and relevance ranking are all part of the core database. For product catalogs, document libraries, log search, and site search up to millions of rows, it is often more than enough — with zero extra infrastructure. This tutorial takes you from an empty table to a ranked, indexed search in four steps.

What You Get with PostgreSQL Full-Text Search

PostgreSQL’s text search pipeline does the heavy lifting you would otherwise build yourself: it tokenizes text, removes stop words, applies language-aware stemming, and supports boolean queries and ranking. The two key types are tsvector (the parsed, searchable form of a document) and tsquery (the parsed query). The official full-text search documentation is the reference, but the practical pattern is easy to learn by example.

Step 1 — Create and Load a Sample Table

Start with a small documents table. The examples use Houston-flavored data — oilfield sensors, pipeline safety, and the regional power grid — but the SQL is identical for any content:

CREATE TABLE docs (
  id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title TEXT NOT NULL,
  body  TEXT NOT NULL
);

INSERT INTO docs (title, body) VALUES
  ('Well monitoring guide', 'How to collect and analyze pressure and temperature data from oilfield sensors in real time.'),
  ('Pipeline safety checklist', 'Inspection steps for natural gas pipelines, including leak detection and corrosion monitoring.'),
  ('Houston grid report', 'Quarterly summary of electricity demand, weather impact, and grid reliability for the Houston area.');

Step 2 — Search with tsvector and to_tsquery

to_tsvector('english', body) converts a document into searchable lexemes, and the @@ operator matches it against a query. Use plainto_tsquery() for raw user input and to_tsquery() when you need boolean operators:

-- Plain phrase-style search (safe for user input)
SELECT id, title
FROM docs
WHERE to_tsvector('english', body) @@ plainto_tsquery('english', 'pipeline leak');

-- Boolean: documents about pipelines that do NOT mention leaks
SELECT id, title
FROM docs
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'pipeline & !leak');

Step 3 — Rank Results with ts_rank

Search without ordering is only half the job. ts_rank() scores each match so the most relevant document rises to the top. The score is based on how often and where the terms appear — a match in the title outranks one buried in the body, and documents with more matches rank higher:

SELECT title,
       ts_rank(to_tsvector('english', body),
               plainto_tsquery('english', 'houston grid')) AS rank
FROM docs
WHERE to_tsvector('english', body) @@ plainto_tsquery('english', 'houston grid')
ORDER BY rank DESC;

Step 4 — Index with GIN for Speed

Without an index, every query rescans the whole table. A GIN index over the tsvector expression fixes that. Even better, store the vector once in a generated column so inserts and queries both stay cheap:

-- Expression index (no schema change)
CREATE INDEX docs_body_fts_idx
  ON docs USING GIN (to_tsvector('english', body));

-- Or persist the vector in a generated column:
ALTER TABLE docs
  ADD COLUMN body_tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('english', body)) STORED;

CREATE INDEX docs_body_tsv_idx ON docs USING GIN (body_tsv);

After either index, run EXPLAIN ANALYZE on your query and you should see a Bitmap Index Scan instead of a sequential scan.

When to Reach for Full-Text Search (and When Not To)

Full-text search shines for documents, articles, product descriptions, and log messages — anything where stemming and ranking matter. With a GIN index it comfortably handles millions of rows on a single instance, which covers the vast majority of application search needs. It is not a typo-tolerant fuzzy matcher; for “did you mean” behavior add the pg_trgm extension, and for semantic similarity by meaning, pair it with pgvector. Many teams run hybrid search: full-text (BM25-style) ranking for exact terms plus vector similarity for meaning — all inside one PostgreSQL database, no Elasticsearch cluster required.

Next Steps

Try combining this with JSONB columns to search semi-structured event payloads, or add pg_trgm for fuzzy prefix matching on autocomplete. The text search functions reference covers ranking weights, highlighting with ts_headline(), and per-language dictionaries — useful when your Houston users search in English, Spanish, or Vietnamese.