Full-Text Search (PostgreSQL tsvector)

-- Schema with a generated tsvector column
CREATE TABLE articles (
    id      BIGSERIAL PRIMARY KEY,
    title   TEXT NOT NULL,
    body    TEXT NOT NULL,
    search  tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(body,  '')), 'B')
    ) STORED
);

CREATE INDEX idx_articles_search ON articles USING gin (search);

-- Search
SELECT id, title,
       ts_rank(search, query) AS rank
FROM   articles, websearch_to_tsquery('english', 'rust async tokio') AS query
WHERE  search @@ query
ORDER  BY rank DESC
LIMIT  20;

-- Three query parsers:
--   to_tsquery         — strict, '&' '|' '!'
--   plainto_tsquery    — treats input as plain words
--   websearch_to_tsquery — Google-style "quotes" and -negation

-- Highlight matches
SELECT ts_headline('english', body, query, 'StartSel=<b>, StopSel=</b>')
FROM   articles, websearch_to_tsquery('english', 'rust') AS query
WHERE  search @@ query LIMIT 5;