0Pricing
SQL Academy · Lesson

Full-Text Search with tsvector and GIN

Build production full-text search with tsvector columns, GIN indexes, and configurable text-search dictionaries.

Full-Text Search with tsvector and GIN is a free SQL Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Beyond Substring: Word-Aware Search

Full-text search (FTS) understands words: stemming, stop words, ranking. PostgreSQL has it built in via the tsvector / tsquery types.

Build a tsvector

Parse a document:

SELECT to_tsvector('english', 'The quick brown foxes jumped over');
-- 'brown':3 'fox':4 'jump':5 'quick':2

Build a tsquery

Parse a query:

SELECT to_tsquery('english', 'fox & jumping');     -- 'fox' & 'jump'
SELECT websearch_to_tsquery('english', 'fox jumping -lazy');

Match: @@

Test a document against a query:

SELECT 'fox jumping'::tsvector @@ 'fox & jump'::tsquery;     -- t

Indexing for FTS

Add a tsvector column and GIN index:

ALTER TABLE articles
  ADD COLUMN search_doc tsvector
    GENERATED ALWAYS AS (
      to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))
    ) STORED;

CREATE INDEX articles_search_idx ON articles USING GIN (search_doc);

Querying

Fast filtered search:

SELECT id, title FROM articles
WHERE search_doc @@ websearch_to_tsquery('english', 'postgres performance')
ORDER BY ts_rank(search_doc, websearch_to_tsquery('english', 'postgres performance')) DESC
LIMIT 20;

Ranking with ts_rank

ts_rank scores results based on word frequency and position. ts_rank_cd uses Cover Density (position-aware):

SELECT id, ts_rank_cd(search_doc, q) AS rank
FROM articles, websearch_to_tsquery('english', $1) AS q
WHERE search_doc @@ q
ORDER BY rank DESC LIMIT 20;

Snippets with ts_headline

Show matched fragments with the matched words highlighted:

SELECT ts_headline('english', body,
  websearch_to_tsquery('english', $1),
  'StartSel=<mark>, StopSel=</mark>, MaxFragments=2')
FROM articles
WHERE search_doc @@ websearch_to_tsquery('english', $1);

Phrase Search

Find adjacent words:

SELECT * FROM articles
WHERE search_doc @@ phraseto_tsquery('english', 'database performance');

Multi-Field Boosts

Give title more weight than body using setweight:

ALTER TABLE articles
  ADD COLUMN search_doc tsvector
    GENERATED ALWAYS AS (
      setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
      setweight(to_tsvector('english', coalesce(body,'')),  'B')
    ) STORED;

Language Configuration

The english config defines stop words, stemming, etc. PostgreSQL ships with many languages; switch per language:

SELECT to_tsvector('turkish', 'Veritabanı performansı');

Limits of Built-In FTS

Good for English-like languages, basic ranking. For more advanced needs (BM25, vector embeddings, faceted search) consider Elasticsearch or OpenSearch alongside Postgres.

Recap

PostgreSQL FTS is a competent default.

  • tsvector + tsquery + @@ matching
  • GIN index for speed
  • websearch_to_tsquery for human input
  • ts_rank/ts_headline for ranked, highlighted results

Quick Check

You need to fast-filter articles by full-text query. Which index?

Frequently asked questions

Is the “Full-Text Search with tsvector and GIN” lesson free?

Yes — the full text of “Full-Text Search with tsvector and GIN” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Full-Text Search with tsvector and GIN”?

Build production full-text search with tsvector columns, GIN indexes, and configurable text-search dictionaries. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Full-Text Search with tsvector and GIN” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Trigram Search (pg_trgm) for Fuzzy Matching
  2. Full-Text Search with tsvector and GIN
  3. Geospatial Indexing with PostGIS
  4. Vector Search with pgvector
← Back to SQL Academy