pgvector — Animate | AI Wisdom

← Back to topic
🎞 ANIMATE · Mode 3 of 5 · +100 XP— pgvector for Semantic Search
📋Schema
⚙️Index
🔄Search
✅Hybrid
🎯 Next: Test →
Step 1 / 4
Speed
Vector Schema
vector(1536) column stores OpenAI embeddings — 1536 float32 values per row💡 Analogy
Like storing GPS coordinates alongside an address — the vector is the semantic "location" of the text.
Install pgvector extension, then use vector(N) column type. 1536 dimensions = OpenAI text-embedding-3-small. Store embedding alongside the text it represents.
Console Output
> CREATE EXTENSION vector: ✅
> documents.embedding: vector(1536)
> Storage: 1536 × 4 bytes = 6KB per row
●pgvector.ts
Vector SchemaL1–10
1-- Enable pgvector extension
2CREATE EXTENSION IF NOT EXISTS vector;
3 
4-- Table with embedding column
5CREATE TABLE documents (
6    id         SERIAL PRIMARY KEY,
7    content    TEXT NOT NULL,
8    metadata   JSONB,
9    embedding  vector(1536)   -- OpenAI text-embedding-3-small dimension
10);
11 
12-- HNSW index for fast approximate nearest neighbor search
13CREATE INDEX ON documents
14    USING hnsw (embedding vector_cosine_ops)
15    WITH (m = 16, ef_construction = 64);
16 
17-- Semantic search: find top-5 similar documents
18SELECT id, content,
19       1 - (embedding <=> $1) AS similarity
20FROM documents
21WHERE metadata->>'category' = 'tech'
22ORDER BY embedding <=> $1   -- cosine distance (lower = more similar)
23LIMIT 5;
24 
25-- Hybrid search: BM25 keyword + vector semantic
26SELECT d.id, d.content,
27       ts_rank(to_tsvector(d.content), plainto_tsquery($2)) AS keyword_score,
28       1 - (embedding <=> $1) AS semantic_score,
29       (0.3 * ts_rank(...) + 0.7 * (1 - (embedding <=> $1))) AS combined
30FROM documents d
31WHERE to_tsvector(d.content) @@ plainto_tsquery($2)
32   OR embedding <=> $1 < 0.5
33ORDER BY combined DESC LIMIT 10;
33 lines
Timeline — 4 Steps
Vector Schema
vector(1536) column stores OpenAI embeddings — 1536 float32 values per row💡 Analogy
Like storing GPS coordinates alongside an address — the vector is the semantic "location" of the text.
Install pgvector extension, then use vector(N) column type. 1536 dimensions = OpenAI text-embedding-3-small. Store embedding alongside the text it represents.
Console Output
> CREATE EXTENSION vector: ✅
> documents.embedding: vector(1536)
> Storage: 1536 × 4 bytes = 6KB per row

Was this page helpful?

Discussion

vector(1536) column stores OpenAI embeddings — 1536 float32 values per row

💡 Analogy

Like storing GPS coordinates alongside an address — the vector is the semantic "location" of the text.

Install pgvector extension, then use vector(N) column type. 1536 dimensions = OpenAI text-embedding-3-small. Store embedding alongside the text it represents.

Console Output

> CREATE EXTENSION vector: ✅
> documents.embedding: vector(1536)
> Storage: 1536 × 4 bytes = 6KB per row

vector(1536) column stores OpenAI embeddings — 1536 float32 values per row

💡 Analogy

Like storing GPS coordinates alongside an address — the vector is the semantic "location" of the text.

Install pgvector extension, then use vector(N) column type. 1536 dimensions = OpenAI text-embedding-3-small. Store embedding alongside the text it represents.

Console Output

> CREATE EXTENSION vector: ✅
> documents.embedding: vector(1536)
> Storage: 1536 × 4 bytes = 6KB per row