Postgres full-text search is good enough to replace Elasticsearch for most apps

-- Option 1: Generated column (automatically updated)
CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    description TEXT,
    brand TEXT,
    tags TEXT[] DEFAULT '{}',
    
    -- Weighted tsvector: name has highest weight (A), brand (B), description (C)
    search_vector tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('english', COALESCE(name, '')), 'A') ||
        setweight(to_tsvector('english', COALESCE(brand, '')), 'B') ||
        setweight(to_tsvector('english', COALESCE(description, '')), 'C') ||
        setweight(to_tsvector('english', COALESCE(array_to_string(tags, ' '), '')), 'B')
    ) STORED
);

CREATE INDEX idx_products_search ON products USING gin(search_vector);

-- Simple search: match any word
SELECT name, ts_rank(search_vector, query) AS rank
FROM products, plainto_tsquery('english', 'wireless headphones') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

-- Phrase search: words must appear in order
SELECT name FROM products
WHERE search_vector @@ phraseto_tsquery('english', 'noise cancelling headphones');

-- Advanced query syntax: AND, OR, NOT, prefix
SELECT name FROM products
WHERE search_vector @@ to_tsquery('english', 'wireless & headphones & !wired');

-- Prefix matching (good for autocomplete)
SELECT name FROM products
WHERE search_vector @@ to_tsquery('english', 'headph:*')
LIMIT 10;

SELECT 
    id,
    name,
    brand,
    price_cents / 100.0 AS price,
    -- ts_rank: position-based ranking
    ts_rank(search_vector, query) AS text_rank,
    -- ts_rank_cd: cover density (prefers matches close together)
    ts_rank_cd(search_vector, query) AS cover_density_rank,
    -- Combined with business score (e.g., sales velocity)
    ts_rank(search_vector, query) * 0.7 + (sales_last_30d / 1000.0) * 0.3 AS final_rank
FROM products,
     plainto_tsquery('english', $1) AS query
WHERE search_vector @@ query
  AND in_stock = true
ORDER BY final_rank DESC
LIMIT 20;

SELECT
    id,
    name,
    -- Highlight matching terms in the description
    ts_headline(
        'english',
        description,
        plainto_tsquery('english', $1),
        'StartSel = , StopSel = , MaxWords = 40, MinWords = 20'
    ) AS description_highlighted
FROM products
WHERE search_vector @@ plainto_tsquery('english', $1)
LIMIT 10;

-- For autocomplete, combine prefix FTS with trigram similarity
-- pg_trgm handles typos; prefix FTS handles completions

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING gin(name gin_trgm_ops);

-- Autocomplete: prefix match OR fuzzy match
SELECT DISTINCT name
FROM products
WHERE (
    -- Prefix match: fast and exact
    to_tsvector('english', name) @@ to_tsquery('english', $1 || ':*')
    OR
    -- Trigram similarity: handles typos (similarity > 0.3)
    similarity(name, $1) > 0.3
)
  AND in_stock = true
ORDER BY 
    CASE WHEN name ILIKE $1 || '%' THEN 0 ELSE 1 END,  -- Prefix first
    similarity(name, $1) DESC
LIMIT 10;