Hybrid Semantic Search: Combining pgvector and Full-Text BM25 in PostgreSQL

-- Enable extensions
CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Add embedding vector and search vector
ALTER TABLE products ADD COLUMN embedding vector(768);
ALTER TABLE products ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (
  to_tsvector('english', coalesce(title, '') || ' ' || coalesce(description, ''))
) STORED;

-- Build HNSW index with cosine distance
CREATE INDEX idx_products_hnsw_embedding 
ON products USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Build GIN index for rapid keyword matching
CREATE INDEX idx_products_gin_search ON products USING gin(search_vector);