Vector Search

-- Create a vector column and HNSW index
CREATE TABLE documents (
    id        BIGSERIAL PRIMARY KEY,
    title     TEXT,
    body      TEXT,
    embedding VECTOR(1536)   -- OpenAI text-embedding-3 dimensions
);

-- HNSW index with cosine distance metric
CREATE INDEX idx_docs_embedding
    ON documents USING HNSW (embedding)
    WITH (metric = 'cosine', m = 16, ef_construction = 200);

-- HNSW index with L2 (Euclidean) distance
CREATE INDEX idx_docs_l2
    ON documents USING HNSW (embedding)
    WITH (metric = 'l2');

-- Cosine similarity (most common for text)
SELECT id, title, embedding <=> '[0.1, 0.2, ...]'::vector AS dist
FROM documents ORDER BY dist LIMIT 10;

-- L2 (Euclidean) distance
SELECT id, title, embedding <-> '[0.1, 0.2, ...]'::vector AS dist
FROM documents ORDER BY dist LIMIT 10;

-- Inner product (for dot-product models)
SELECT id, title, embedding <#> '[0.1, 0.2, ...]'::vector AS dist
FROM documents ORDER BY dist LIMIT 10;

-- Product Quantisation (16x compression, best recall/size trade-off)
CREATE INDEX idx_pq ON documents USING HNSW (embedding)
WITH (metric = 'cosine', quantisation = 'pq', pq_segments = 16);

-- Scalar Quantisation (4x compression, near-lossless)
CREATE INDEX idx_sq ON documents USING HNSW (embedding)
WITH (metric = 'cosine', quantisation = 'sq8');

-- Binary Quantisation (32x compression, fastest compute)
CREATE INDEX idx_bq ON documents USING HNSW (embedding)
WITH (metric = 'cosine', quantisation = 'binary');

-- Store full 3072-dim embeddings (text-embedding-3-large)
CREATE TABLE docs (
    id        BIGSERIAL PRIMARY KEY,
    body      TEXT,
    embedding VECTOR(3072)
);

-- Query with truncated dimensions (faster, smaller index)
-- absdb_truncate() returns the first N dimensions
SELECT id, body,
       absdb_truncate(embedding, 256) <=> absdb_truncate(EMBED('text-embedding-3-large', $1), 256)
FROM docs
ORDER BY 3 LIMIT 10;

-- Create table with sparse vector column
CREATE TABLE sparse_docs (
    id       BIGSERIAL PRIMARY KEY,
    body     TEXT,
    svec     SPARSEVEC(30000)
);

-- Insert a sparse vector (text format: {dim:val, dim:val, ...})
INSERT INTO sparse_docs (body, svec) VALUES
('Raft consensus protocol', '{42:1.3, 107:0.9, 512:2.1, 1024:0.7}');

-- Sparse vector similarity search
SELECT id, body,
       svec <=> '{42:1.3, 107:0.9}'::sparsevec AS score
FROM sparse_docs
ORDER BY score DESC
LIMIT 10;

-- Hybrid BM25 + vector search with RRF fusion
SELECT id, title, body
FROM documents
WHERE body MATCH 'distributed consensus algorithm'
ORDER BY embedding <=> EMBED('minilm', 'distributed consensus algorithm')
LIMIT 10;

-- Explicit RRF with weight control
SELECT * FROM absdb_hybrid_search(
    table   => 'documents',
    query   => 'distributed consensus',
    vec     => EMBED('minilm', 'distributed consensus'),
    bm25_weight => 0.4,
    vec_weight  => 0.6,
    top_k       => 10
);

-- Store per-token embeddings as a multi-vector
CREATE TABLE colbert_docs (
    id      BIGSERIAL PRIMARY KEY,
    title   TEXT,
    tokens  VECTOR(128)[]   -- array of token vectors
);

-- MaxSim search: highest per-token similarity across all tokens
SELECT id, title,
       absdb_maxsim(tokens, EMBED_TOKENS('colbert', $1)) AS score
FROM colbert_docs
ORDER BY score DESC
LIMIT 10;

-- Enable auto-embedding: body column → embedding column
ALTER TABLE documents
    SET (
        auto_embed      = true,
        embed_column    = 'body',
        embed_target    = 'embedding',
        embed_model     = 'minilm'
    );

-- Now plain INSERTs automatically populate the embedding column
INSERT INTO documents (title, body) VALUES
    ('Raft Paper', 'Raft is a consensus algorithm designed for understandability...');
-- embedding column is populated automatically

-- Supported built-in models: minilm, e5-small, multilingual-e5
-- External ONNX models: register with absdb_model_register()

-- Create tenant-scoped vector index
CREATE INDEX idx_tenant_a_embedding
    ON documents USING HNSW (embedding)
    WITH (metric = 'cosine', namespace = 'tenant_a');

-- Tenant A's searches are automatically scoped
-- to their namespace via row-level security
SET app.tenant_id = 'tenant_a';
SELECT id, title, embedding <=> $1 AS dist
FROM documents
ORDER BY dist LIMIT 10;

-- Full RAG pipeline (retrieve + re-rank)
SELECT * FROM absdb_rag_query(
    query      => 'How does Raft handle leader failure?',
    table      => 'knowledge_base',
    top_k      => 5,
    rerank     => true,
    rerank_model => 'cross-encoder'
);

-- Result columns: id, chunk_text, score, rank

-- Manual pipeline: chunk → embed → store
SELECT absdb_chunk_and_embed(
    source_table => 'raw_documents',
    target_table => 'knowledge_base',
    chunk_size   => 512,
    chunk_overlap => 64,
    model        => 'minilm'
);