🐘 PostgreSQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

-- Modern PostgreSQL for AI applications
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    content TEXT NOT NULL,
    metadata JSONB DEFAULT '{}',
    embedding vector(1536),  -- OpenAI embeddings
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Hybrid search: semantic + keyword
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 100);
CREATE INDEX ON documents USING gin (to_tsvector('english', content));
CREATE INDEX ON documents USING gin (metadata jsonb_path_ops);

-- Query combining vector similarity and full-text
WITH semantic_results AS (
    SELECT id, content, metadata,
           1 - (embedding <=> $1::vector) AS semantic_score
    FROM documents
    ORDER BY embedding <=> $1::vector
    LIMIT 20
),
keyword_results AS (
    SELECT id, ts_rank(to_tsvector('english', content), 
                       plainto_tsquery('english', $2)) AS keyword_score
    FROM documents
    WHERE to_tsvector('english', content) @@ plainto_tsquery('english', $2)
)
SELECT s.id, s.content, s.metadata,
       0.7 * s.semantic_score + 0.3 * COALESCE(k.keyword_score, 0) AS combined_score
FROM semantic_results s
LEFT JOIN keyword_results k ON s.id = k.id
ORDER BY combined_score DESC
LIMIT 10;