Full-Text Search

ILIKE '%word%' can't use normal indexes, doesn't understand word forms ("teach" vs "teaching") and can't rank results. PostgreSQL's full-text search can.

A tsvector is a document reduced to normalised words (lexemes); a tsquery is a search. websearch_to_tsquery accepts Google-style input like photosynthesis -plants "cell structure". Rank matches with ts_rank and highlight them with ts_headline.

Store the tsvector in a generated column with a GIN index so searches stay fast as the table grows. setweight lets title matches rank above body matches.

search.sqlSQL

CREATE TABLE lesson_notes (
    id     bigint GENERATED ALWAYS AS IDENTITY 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 idx_lesson_notes_search ON lesson_notes USING gin (search);

INSERT INTO lesson_notes (title, body) VALUES
    ('Photosynthesis', 'Green plants make food using sunlight, water and carbon dioxide in their chloroplasts.'),
    ('Cell Structure', 'Plant and animal cells have a nucleus, cytoplasm and a cell membrane. Plant cells also have chloroplasts.'),
    ('Respiration', 'Cells release energy from food. Aerobic respiration uses oxygen and produces carbon dioxide.'),
    ('Teaching Fractions', 'Teachers can use real objects such as oranges to explain fractions.');

SELECT title,
       round(ts_rank(search, q)::numeric, 3) AS rank,
       ts_headline('english', body, q, 'StartSel=[, StopSel=]') AS snippet
FROM lesson_notes, websearch_to_tsquery('english', 'chloroplasts plants') AS q
WHERE search @@ q
ORDER BY rank DESC;

-- Word forms match: "teach" finds "Teaching" and "Teachers"
SELECT title FROM lesson_notes WHERE search @@ websearch_to_tsquery('english', 'teach');

-- Exclusion and phrases
SELECT title FROM lesson_notes
WHERE search @@ websearch_to_tsquery('english', '"carbon dioxide" -plants');

Runs in your browser · PostgreSQL

Key points

tsvector = normalised document, tsquery = search; @@ matches them.
Store the tsvector in a generated column and index it with GIN.
websearch_to_tsquery, ts_rank and ts_headline give users a familiar search experience.

Exercise

Add full-text search over the subjects and a new topics table so a single query searches both, returning the type (subject/topic), the title and the rank, ordered by relevance.

Show solution

Try the exercise yourself first — then compare your approach with this one.

Each table gets a stored tsvector column with a GIN index. A UNION ALL combines both searches into one result set with a type column, and the outer query orders everything by rank.

search-solution.sqlSQL

CREATE TABLE topics (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    subject_id  bigint NOT NULL REFERENCES subjects (id),
    title       text   NOT NULL,
    description text   NOT NULL,
    search      tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', description), 'B')
    ) STORED
);
CREATE INDEX idx_topics_search ON topics USING gin (search);

ALTER TABLE subjects ADD COLUMN search tsvector GENERATED ALWAYS AS (to_tsvector('english', name)) STORED;
CREATE INDEX idx_subjects_search ON subjects USING gin (search);

INSERT INTO topics (subject_id, title, description) VALUES
    (2, 'Cell Structure', 'Plant and animal cells, organelles and their functions'),
    (2, 'Ecology', 'Living things and their environment, food chains and food webs'),
    (1, 'Linear Programming', 'Maximising and minimising with linear inequalities'),
    (4, 'Organic Chemistry', 'Carbon compounds, hydrocarbons and their reactions');

WITH q AS (SELECT websearch_to_tsquery('english', 'biology or cells') AS query)
SELECT type, title, round(rank::numeric, 3) AS rank
FROM (
    SELECT 'subject' AS type, s.name AS title, ts_rank(s.search, q.query) AS rank
    FROM subjects s, q WHERE s.search @@ q.query
    UNION ALL
    SELECT 'topic', t.title, ts_rank(t.search, q.query)
    FROM topics t, q WHERE t.search @@ q.query
) results
ORDER BY rank DESC;
-- 'biology or cells' matches either word; plain 'biology cells' would require both in the same row

Runs in your browser · PostgreSQL

Key points

Exercise

Check your understanding