Full-text Search

-- 1) Concept
-- tsvector  : preprocessed document (tokens + positions, stemmed, no stop words)
-- tsquery   : preprocessed query
-- @@@@      : match operator (tsvector @@@@ tsquery)

-- 2) Try it
SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');
-- 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2
-- Stop words removed; words stemmed.

SELECT to_tsquery('english', 'fox & dog');
-- 'fox' & 'dog'

SELECT to_tsvector('english', 'jumping foxes')
    @@@@ to_tsquery('english', 'fox & jump');
-- true — stemming matches plural / past tense

-- 3) Schema with a generated tsvector column
CREATE TABLE posts (
    id      bigserial PRIMARY KEY,
    title   text NOT NULL,
    body    text NOT NULL,
    tsv     tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(body,  '')), 'B')
    ) STORED,
    created_at timestamptz DEFAULT now()
);

-- setweight: A > B > C > D (used in ranking)

-- 4) GIN index on tsvector — fast full-text search
CREATE INDEX idx_posts_tsv ON posts USING gin(tsv);

-- 5) Basic search
SELECT id, title
FROM posts
WHERE tsv @@@@ to_tsquery('english', 'docker & container')
ORDER BY created_at DESC
LIMIT 20;

-- 6) Query syntax
to_tsquery('english', 'docker & container')      -- AND
to_tsquery('english', 'docker | container')      -- OR
to_tsquery('english', '!container')              -- NOT
to_tsquery('english', 'docker & !mongo')
to_tsquery('english', '(docker | kubernetes) & cluster')

-- Phrase + proximity
to_tsquery('english', 'docker <-> container')    -- 'docker' immediately followed by 'container'
to_tsquery('english', 'docker <2> container')    -- within 2 words

-- 7) Friendlier query parsers
-- plainto_tsquery — splits words, ANDs them, ignores syntax characters
SELECT * FROM posts WHERE tsv @@@@ plainto_tsquery('english', 'docker container basics');

-- phraseto_tsquery — treats input as a phrase
SELECT * FROM posts WHERE tsv @@@@ phraseto_tsquery('english', 'docker container');

-- websearch_to_tsquery — supports Google-style operators (quotes, OR, -word)
SELECT * FROM posts WHERE tsv @@@@ websearch_to_tsquery('english', '"docker compose" -kubernetes');

-- 8) Ranking results
SELECT id, title,
       ts_rank(tsv, q)         AS rank
FROM   posts, websearch_to_tsquery('english', 'docker container') q
WHERE  tsv @@@@ q
ORDER BY rank DESC
LIMIT 20;

-- ts_rank_cd — cover-density rank (better for short queries)
SELECT ts_rank_cd(tsv, q) FROM ...

-- 9) Highlight matches (ts_headline)
SELECT title,
       ts_headline('english', body, q, 'MaxFragments=2, MinWords=8, MaxWords=30, ShortWord=3, HighlightAll=false') AS snippet
FROM   posts, websearch_to_tsquery('english', 'docker container') q
WHERE  tsv @@@@ q;

-- 10) Trigram for fuzzy/typo matching — pg_trgm extension
CREATE EXTENSION IF NOT EXISTS pg_trgm;

SELECT id, title, similarity(title, 'dokcer') AS sim
FROM   posts
WHERE  title %% 'dokcer'                        -- %% = similar to (default threshold ~0.3)
ORDER BY sim DESC;

-- GIN index for trigram
CREATE INDEX idx_posts_title_trgm ON posts USING gin (title gin_trgm_ops);

-- ILIKE with trigram index — fast
SELECT * FROM posts WHERE title ILIKE '%docker%';

-- 11) Combine full-text + filters + trigram
SELECT p.*
FROM   posts p, websearch_to_tsquery('english', 'docker') q
WHERE  p.tsv @@@@ q
   AND p.created_at >= now() - interval '90 days'
   AND p.status = 'published'
ORDER BY ts_rank(p.tsv, q) DESC;

-- 12) Multi-language
SELECT to_tsvector('french', 'Les chats jouent dans le jardin');
SELECT to_tsvector('german', 'Die Katzen spielen im Garten');

-- Use a language column to pick at query time:
CREATE TABLE posts2 (
    id    bigserial PRIMARY KEY,
    lang  regconfig NOT NULL DEFAULT 'english',
    title text NOT NULL,
    body  text NOT NULL,
    tsv   tsvector GENERATED ALWAYS AS (
        to_tsvector(lang, coalesce(title, '') || ' ' || coalesce(body, ''))
    ) STORED
);

-- 13) Custom dictionary (synonyms / stopwords) — text_search dictionaries
-- Useful for domain-specific search (e.g. 'k8s' = 'kubernetes')
CREATE TEXT SEARCH DICTIONARY my_synonyms (
    TEMPLATE = synonym,
    SYNONYMS = 'my_synonyms'
);
-- /usr/share/postgresql/16/tsearch_data/my_synonyms.syn
-- k8s    kubernetes
-- js     javascript

CREATE TEXT SEARCH CONFIGURATION my_english (COPY = english);
ALTER TEXT SEARCH CONFIGURATION my_english
    ALTER MAPPING FOR asciiword WITH my_synonyms, english_stem;

-- 14) JSONB full-text search
ALTER TABLE events
ADD COLUMN tsv tsvector GENERATED ALWAYS AS (
    to_tsvector('english', coalesce(payload->>'message', ''))
) STORED;

CREATE INDEX idx_events_tsv ON events USING gin(tsv);

-- 15) Performance tips
--   • Use STORED generated column — fast reads, no per-query compute
--   • GIN index on tsvector — millisecond search on millions of rows
--   • Use ts_rank in ORDER BY but combine with simpler ORDER BY (created_at) for ties
--   • Limit result set BEFORE rank — `SELECT * FROM posts WHERE tsv @@@@ q ORDER BY ts_rank(tsv, q) DESC LIMIT 20;`
--   • EXPLAIN ANALYZE to verify index usage (look for 'Bitmap Index Scan on tsv')

-- 16) When PG full-text is enough
--   • Up to ~10M documents — Postgres is fast
--   • English / common languages — ships with good defaults
--   • Simple ranking — ts_rank is fine
--   • You want one database for everything (vs. running ES too)

-- 17) When to graduate to Elasticsearch / Meilisearch / Typesense
--   • Geo + full-text + facets + filters at huge scale
--   • Sophisticated ranking (BM25 tweaks, multi-field weighting, learning-to-rank)
--   • Real-time autocomplete (sub-50ms)
--   • Distributed search across hundreds of millions of documents

-- 18) Real-world ranking strategy
-- - Weight title higher than body (setweight)
-- - Boost recent results (combine rank + recency)
-- - Penalise low-engagement posts (combine with views / likes)

SELECT id, title,
       ts_rank(tsv, q) * 1.0
     + log(1 + views) * 0.2
     - extract(epoch from now() - created_at) / 86400 * 0.01  AS combined_rank
FROM   posts, websearch_to_tsquery('english', 'docker') q
WHERE  tsv @@@@ q
ORDER BY combined_rank DESC
LIMIT 20;

-- 19) Test queries with explain
EXPLAIN ANALYZE
SELECT * FROM posts WHERE tsv @@@@ websearch_to_tsquery('english', 'docker') LIMIT 20;
-- Look for:
--   Bitmap Heap Scan ... Recheck Cond
--   Bitmap Index Scan on idx_posts_tsv
-- → using the GIN index. Good.

SELECT title, ts_rank(to_tsvector(body), q) AS rank
FROM posts, to_tsquery('postgres & search') q
WHERE to_tsvector(body) @@ q
ORDER BY rank DESC;