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. This is 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, and your progress syncs across the web and the CoddyKit app. The SQL Academy course includes 4 lessons in total.

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. 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. The SQL Academy course includes 4 lessons in total.

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, so you can start here or from the beginning and move at your own pace. This is lesson 2 of 4.

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