pgvector: a complete overview

-- pgvector: vectors live in an ordinary table next to the rest of the data
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE products (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku       text UNIQUE NOT NULL,
    title     text NOT NULL,
    category  text NOT NULL,
    in_stock  boolean NOT NULL DEFAULT true,
    embedding vector(1024)   -- the size is set by the embedding model
);

-- HNSW index for cosine distance: fast approximate search
CREATE INDEX products_embedding_idx
    ON products USING hnsw (embedding vector_cosine_ops);

-- Similar products in stock; the vector of the query comes from the application as $1
SET hnsw.iterative_scan = relaxed_order;  -- pgvector 0.8+: keep scanning while the filter discards rows

SELECT sku, title, 1 - (embedding <=> $1) AS similarity
FROM products
WHERE in_stock AND category = 'tables'
ORDER BY embedding <=> $1
LIMIT 10;

-- Hybrid search: by meaning (pgvector) and by words (full-text search),
-- the two lists are merged by rank — Reciprocal Rank Fusion
WITH semantic AS (
    SELECT id, row_number() OVER (ORDER BY embedding <=> $1) AS rank
    FROM products
    ORDER BY embedding <=> $1
    LIMIT 20
),
keyword AS (
    SELECT id, row_number() OVER (ORDER BY ts_rank_cd(to_tsvector('english', title), q) DESC) AS rank
    FROM products, plainto_tsquery('english', $2) AS q
    WHERE to_tsvector('english', title) @@ q
    ORDER BY ts_rank_cd(to_tsvector('english', title), q) DESC
    LIMIT 20
)
SELECT p.sku, p.title,
       coalesce(1.0 / (60 + s.rank), 0) + coalesce(1.0 / (60 + k.rank), 0) AS score
FROM semantic s
FULL OUTER JOIN keyword k ON k.id = s.id
JOIN products p ON p.id = coalesce(s.id, k.id)
ORDER BY score DESC
LIMIT 10;