postgres-semantic-search

# PostgreSQL Semantic Search ## Quick Start ### 1. Setup ```sql CREATE EXTENSION IF NOT EXISTS vector; CREATE TABLE documents ( id SERIAL PRIMARY KEY, content TEXT NOT NULL, embedding vector(1536) -- 1536-dim embedding -- Or: embedding halfvec(3072) -- 3072-dim embedding (halfvec = 50% memory) ); ``` ### 2. Basic Semantic Search ```sql SELECT id, content, 1 - (embedding <=> query_vec) AS similarity FROM documents ORDER BY embedding <=> query_vec LIMIT 10; ``` ### 3. Add Index (> 10k documents) ```sql CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops); ``` ### Docker Quick Start ```bash # pgvector with PostgreSQL 17 docker run -d --name pgvector-db \ -e POSTGRES_PASSWORD=postgres \ -p 5432:5432 \ pgvector/pgvector:pg17 # Or PostgreSQL 18 docker run -d --name pgvector-db \ -e POSTGRES_PASSWORD=postgres \ -p 5432:5432 \ pgvector/pgvector:pg18 # ParadeDB (includes pgvector + pg_search + BM25) docker run -d --name paradedb \ -e POSTGRES_PASSWORD=postgres \ -p 5432:5432 \ paradedb/paradedb:latest # `latest` is convenient for quick-start; pin to e.g. paradedb/paradedb:pg17 for reproducible builds ``` Connect: `psql postgresql://postgres:postgres@localhost:5432/postgres` ## Cheat Sheet ### Distance Operators ```sql embedding <=> query -- Cosine distance (1 - similarity) embedding <-> query -- L2/Euclidean distance embedding <#> query -- Negative inner...