Full-Text Search & Fuzzy Matching

-- PostgreSQL Full-Text Search
-- Two core types: tsvector (document) and tsquery (search)

-- Convert text to tsvector (searchable tokens):
SELECT to_tsvector('english',
    'PostgreSQL is a powerful open-source database system');
-- Result: 'databas':6 'open':4 'open-sourc':4 'postgresql':1
--         'power':3 'sourc':5 'system':7
-- Notice: words are stemmed (database → databas)
-- Stop words removed (is, a)

-- Create a search query:
SELECT to_tsquery('english', 'database & powerful');
-- R
...

-- Speed up full-text search with GIN indexes

-- Option 1: Index a computed tsvector
CREATE INDEX idx_articles_fts
ON articles USING gin (
    to_tsvector('english', title || ' ' || content)
);

-- Option 2: Store tsvector in a dedicated column (faster)
ALTER TABLE articles ADD COLUMN search_vector tsvector;

UPDATE articles SET search_vector =
    setweight(to_tsvector('english', title), 'A') ||
    setweight(to_tsvector('english', content), 'B');
-- Weight A = title (highest priority)
-- Weig
...

-- Ranking and highlighting search results

-- ts_rank: relevance score based on term frequency
SELECT title,
       ts_rank(search_vector, query) AS rank,
       ts_rank_cd(search_vector, query) AS rank_cd
FROM articles,
     to_tsquery('english', 'postgresql & performance') AS query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- ts_rank_cd considers cover density (proximity of terms)
-- Use it when term proximity matters

-- ts_headline: highlight matching terms in results
SELECT title,

...

-- Fuzzy matching: find results despite typos/misspellings

-- 1. pg_trgm extension (trigram similarity)
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Similarity score (0 to 1):
SELECT similarity('PostgreSQL', 'Postgre SQL');  -- ~0.7
SELECT similarity('PostgreSQL', 'MySQL');         -- ~0.1

-- Find similar product names:
SELECT name, similarity(name, 'Samsnug Galaxy') AS sim
FROM products
WHERE similarity(name, 'Samsnug Galaxy') > 0.3
ORDER BY sim DESC;
-- Finds "Samsung Galaxy" despite the typo
...

-- MySQL Full-Text Search (MATCH AGAINST)

-- Create a FULLTEXT index:
-- CREATE FULLTEXT INDEX idx_articles_ft
-- ON articles (title, content);

-- Natural Language Mode (default):
-- SELECT title, content,
--        MATCH(title, content) AGAINST('database performance') AS score
-- FROM articles
-- WHERE MATCH(title, content) AGAINST('database performance')
-- ORDER BY score DESC;

-- Boolean Mode (AND, OR, NOT, phrases):
-- SELECT title FROM articles
-- WHERE MATCH(title, content) AGAINST(
-- 
...