Database / pgvector basics Interview Questions

# Example: generating embeddings with OpenAI
from openai import OpenAI
client = OpenAI()

texts = [
    "I love programming in Python.",
    "Python is my favourite coding language.",
    "I enjoy outdoor hiking on weekends.",
]

response = client.embeddings.create(
    model="text-embedding-3-small",
    input=texts,
)
embeddings = [item.embedding for item in response.data]
# embeddings[0] and embeddings[1] will be numerically close
# embeddings[0] and embeddings[2] will be numerically far apart
print(f"Dimension: {len(embeddings[0])}")  # 1536
print(f"Type: {type(embeddings[0][0])}")  # float

-- Step 1: Enable in the database (run once per database)
CREATE EXTENSION vector;

-- Verify it is installed:
SELECT * FROM pg_extension WHERE extname = 'vector';

-- Upgrade an existing installation:
ALTER EXTENSION vector UPDATE;

-- On managed services (e.g. Azure), allowlist it first:
-- Then CREATE EXTENSION vector; in the database

-- Build from source (Linux):
-- git clone --branch v0.8.4 https://github.com/pgvector/pgvector.git
-- cd pgvector && make && sudo make install
-- Then in psql: CREATE EXTENSION vector;

-- Basic vector column (fixed dimensions)
CREATE TABLE documents (
    id         BIGSERIAL PRIMARY KEY,
    content    TEXT,
    embedding  VECTOR(1536)    -- 1536-dim for text-embedding-3-small
);

-- Half-precision to save storage (2x space reduction)
CREATE TABLE documents_small (
    id         BIGSERIAL PRIMARY KEY,
    embedding  HALFVEC(1536)
);

-- Binary vectors (very compact; special distance operators)
CREATE TABLE binary_items (
    id         BIGSERIAL PRIMARY KEY,
    embedding  BIT(1536)
);

-- Variable dimensions (no dimension constraint)
CREATE TABLE flexible (
    id         BIGSERIAL PRIMARY KEY,
    embedding  VECTOR         -- accepts any dimension
);
-- NOTE: indexes on variable-dim columns require expression/partial indexes

-- L2 distance: find 5 nearest neighbours to '[3,1,2]'
SELECT id, content, embedding <-> '[3,1,2]' AS distance
FROM documents
ORDER BY distance
LIMIT 5;

-- Cosine distance: best for text embeddings
SELECT id, content, embedding <=> '[3,1,2]' AS cosine_dist
FROM documents
ORDER BY cosine_dist
LIMIT 5;

-- Inner product: NOTE <#> returns NEGATIVE inner product
-- Negate the result to get actual similarity score:
SELECT id, -(embedding <#> '[3,1,2]') AS inner_product_sim
FROM documents
ORDER BY embedding <#> '[3,1,2]'  -- ASC = most similar (most negative)
LIMIT 5;

-- Get actual distance value (not just ordering):
SELECT id, embedding <-> '[3,1,2]' AS l2_dist,
            embedding <=> '[3,1,2]' AS cos_dist
FROM documents
LIMIT 10;

-- Insert a single row with a vector literal
INSERT INTO documents (content, embedding)
VALUES ('Hello world', '[0.1, 0.2, 0.3, ...]');

-- Insert multiple rows in one statement
INSERT INTO documents (id, content, embedding) VALUES
    (1, 'Python tutorial', '[0.11, 0.22, 0.33]'),
    (2, 'JavaScript guide', '[0.44, 0.55, 0.66]'),
    (3, 'Hiking tips', '[0.77, 0.88, 0.99]');

-- Insert from a Python list (psycopg2)
# import psycopg2
# embedding = [0.1, 0.2, 0.3]   # list of floats from embedding model
# cur.execute(
#     "INSERT INTO documents (content, embedding) VALUES (%s, %s)",
#     (text, embedding)   # psycopg2 converts list to vector literal
# )

-- Update an existing embedding:
UPDATE documents
SET embedding = '[0.11, 0.21, 0.31]'
WHERE id = 1;

-- Upsert (insert or update on conflict):
INSERT INTO documents (id, content, embedding)
VALUES (1, 'Updated Python tutorial', '[0.12, 0.23, 0.34]')
ON CONFLICT (id) DO UPDATE SET
    content   = EXCLUDED.content,
    embedding = EXCLUDED.embedding;

-- Cast a text literal to vector explicitly:
SELECT '[1,2,3]'::vector;   -- explicit cast notation
SELECT CAST('[1,2,3]' AS vector(3));

-- K-Nearest Neighbour (KNN) query: find 5 most similar documents
-- Replace '[...]' with the actual query embedding from your ML model
SELECT id, content, embedding <-> '[0.1,0.2,0.3]' AS distance
FROM documents
ORDER BY distance          -- ORDER BY the distance expression
LIMIT 5;                   -- return only the top-5 nearest

-- Using cosine distance (common for text embeddings):
SELECT id, content, 1 - (embedding <=> '[0.1,0.2,0.3]') AS similarity
FROM documents
ORDER BY embedding <=> '[0.1,0.2,0.3]'
LIMIT 10;

-- Self-similarity: find items similar to a known row (not itself)
SELECT b.id, b.content, a.embedding <-> b.embedding AS distance
FROM documents a
CROSS JOIN documents b
WHERE a.id = 42 AND b.id != 42
ORDER BY distance
LIMIT 5;

-- Or using a subquery:
SELECT id, content,
       embedding <-> (SELECT embedding FROM documents WHERE id = 42) AS distance
FROM documents
WHERE id != 42
ORDER BY distance
LIMIT 5;

-- With a parameter placeholder (Python + psycopg2):
-- cur.execute(
--     'SELECT id, content, embedding <-> %s AS dist FROM documents ORDER BY dist LIMIT 5',
--     (query_embedding,)
-- )

-- EXACT search (no index): scans every row
-- Accurate but slow at scale
SELECT id, embedding <-> '[3,1,2]' AS dist
FROM items
ORDER BY dist
LIMIT 5;

-- APPROXIMATE search (with HNSW index): fast, high recall
-- Create the index:
CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);

-- Same query syntax - PostgreSQL uses the index automatically:
SELECT id, embedding <-> '[3,1,2]' AS dist
FROM items
ORDER BY dist
LIMIT 5;
-- Now uses HNSW index for fast approximate search

-- Check which index was used:
EXPLAIN SELECT id FROM items ORDER BY embedding <-> '[1,2,3]' LIMIT 5;
-- Look for 'Index Scan using items_embedding_idx' in the plan

-- Force exact search even when index exists:
SET enable_indexscan = off;
SELECT id FROM items ORDER BY embedding <-> '[1,2,3]' LIMIT 5;
RESET enable_indexscan;

-- Create an HNSW index (choose operator class to match your distance metric)

-- For L2 (Euclidean) distance <->:
CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);

-- For cosine distance <=>:
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

-- For inner product <#>:
CREATE INDEX ON items USING hnsw (embedding vector_ip_ops);

-- For L1 distance <+>:
CREATE INDEX ON items USING hnsw (embedding vector_l1_ops);

-- HNSW with custom build parameters:
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops)
WITH (
    m = 16,             -- max connections per node per layer (default 16)
    ef_construction = 64  -- candidate list size during build (default 64)
);

-- Tune QUERY performance at query time:
SET hnsw.ef_search = 100;  -- larger = better recall, slower query (default 40)
SELECT id FROM items ORDER BY embedding <=> '[...]' LIMIT 5;
RESET hnsw.ef_search;

-- Speed up index BUILD with parallel workers:
SET max_parallel_maintenance_workers = 7;  -- match your CPU cores
SET maintenance_work_mem = '2GB';
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

-- Create an IVFFlat index
-- IMPORTANT: table must have data before creating the index
-- (k-means clustering needs existing vectors to learn from)

-- For L2 distance:
CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops)
WITH (lists = 100);  -- number of clusters/lists

-- For cosine distance:
CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- Guideline for lists parameter:
-- rows <= 1,000,000: lists = sqrt(rows)  e.g. sqrt(100000) ~ 316
-- rows >  1,000,000: lists = rows / 1000  e.g. 2000000/1000 = 2000

-- Tune QUERY recall at query time:
SET ivfflat.probes = 10;  -- number of lists to search (default 1)
-- Higher probes = better recall, slower queries
-- probes = lists gives exact search (defeats purpose of index)

SELECT id FROM items ORDER BY embedding <-> '[...]' LIMIT 5;
RESET ivfflat.probes;

# Install dependencies
# pip install pgvector psycopg2-binary openai

import psycopg2
from pgvector.psycopg2 import register_vector
from openai import OpenAI

# Connect and register the vector type adapter
conn = psycopg2.connect("postgresql://user:pass@localhost/mydb")
register_vector(conn)  # allows Python lists <-> vector column
cur = conn.cursor()

# Create the schema
cur.execute("""
    CREATE TABLE IF NOT EXISTS documents (
        id        BIGSERIAL PRIMARY KEY,
        content   TEXT NOT NULL,
        embedding VECTOR(1536)
    )
""")
conn.commit()

# Generate embedding and insert
oai = OpenAI()
text = "How does pgvector work?"
response = oai.embeddings.create(model="text-embedding-3-small", input=text)
embedding = response.data[0].embedding  # list of 1536 floats

cur.execute(
    "INSERT INTO documents (content, embedding) VALUES (%s, %s)",
    (text, embedding)   # psycopg2 + register_vector handles the conversion
)
conn.commit()

# Query: find 5 most similar documents
query_text = "What is the pgvector extension?"
query_embedding = oai.embeddings.create(
    model="text-embedding-3-small", input=query_text
).data[0].embedding

cur.execute(
    "SELECT id, content, embedding <=> %s AS distance"
    " FROM documents ORDER BY distance LIMIT 5",
    (query_embedding,)
)
results = cur.fetchall()
for row in results:
    print(f"id={row[0]}, dist={row[2]:.4f}: {row[1]}")

cur.close()
conn.close()

# pip install pgvector sqlalchemy psycopg2-binary

from sqlalchemy import create_engine, Column, Integer, Text, select
from sqlalchemy.orm import declarative_base, Session
from pgvector.sqlalchemy import Vector

engine = create_engine("postgresql+psycopg2://user:pass@localhost/mydb")
Base = declarative_base()

# Define the model with a vector column
class Document(Base):
    __tablename__ = "documents"
    id        = Column(Integer, primary_key=True)
    content   = Column(Text)
    embedding = Column(Vector(1536))  # pgvector Vector type

Base.metadata.create_all(engine)

# Insert a document
with Session(engine) as session:
    doc = Document(content="Python tutorial", embedding=[0.1, 0.2, ...])
    session.add(doc)
    session.commit()

# Query: find 5 nearest neighbours using cosine distance
from pgvector.sqlalchemy import cosine_distance

query_vector = [0.1, 0.2, ...]
with Session(engine) as session:
    results = session.scalars(
        select(Document)
        .order_by(cosine_distance(Document.embedding, query_vector))
        .limit(5)
    ).all()
    for doc in results:
        print(doc.content)

# Create HNSW index via SQLAlchemy:
from sqlalchemy import Index, text
Index(
    "ix_documents_embedding_hnsw",
    Document.embedding,
    postgresql_using="hnsw",
    postgresql_ops={"embedding": "vector_cosine_ops"},
    postgresql_with={"m": 16, "ef_construction": 64},
)

-- Filtered vector search: find similar documents in a specific category
SELECT id, content, embedding <-> '[...]' AS distance
FROM documents
WHERE category = 'technical'          -- metadata filter
  AND created_at > NOW() - INTERVAL '30 days'  -- date filter
ORDER BY distance
LIMIT 5;

-- Filter by user ownership:
SELECT id, title, embedding <=> '[...]' AS sim_dist
FROM articles
WHERE user_id = 42
  AND is_published = TRUE
ORDER BY sim_dist
LIMIT 10;

-- JOIN with another table:
SELECT d.id, d.content, d.embedding <-> '[...]' AS distance,
       c.name AS category_name
FROM documents d
JOIN categories c ON d.category_id = c.id
WHERE c.name IN ('AI', 'Machine Learning')
ORDER BY distance
LIMIT 5;

-- Distance threshold (only return results within a distance)
SELECT id, content, embedding <-> '[...]' AS distance
FROM documents
WHERE embedding <-> '[...]' < 0.5   -- only close vectors
ORDER BY distance
LIMIT 20;

-- NOTE: ANN indexes (HNSW/IVFFlat) may have reduced recall with filters
-- Workaround: increase ef_search / probes before the query
SET hnsw.ef_search = 100;
SELECT id FROM documents
WHERE category = 'technical'
ORDER BY embedding <-> '[...]' LIMIT 5;
RESET hnsw.ef_search;

-- Method 1: Multi-row INSERT (batch inserts)
INSERT INTO documents (content, embedding) VALUES
    ('Document 1', '[0.1,0.2,0.3]'),
    ('Document 2', '[0.4,0.5,0.6]'),
    ('Document 3', '[0.7,0.8,0.9]');
-- Send batches of 100-1000 rows per INSERT statement

-- Method 2: COPY command (fastest for very large datasets)
COPY documents (content, embedding)
FROM '/path/to/data.csv'
WITH (FORMAT TEXT, DELIMITER '\t');
-- CSV format: text<TAB>[v1,v2,v3,...]

-- Method 3: psycopg2 executemany or copy_from (Python)
# cur.executemany(
#     "INSERT INTO documents (content, embedding) VALUES (%s, %s)",
#     [(text1, emb1), (text2, emb2), ...]
# )

-- Method 4: Disable index during load, rebuild after
-- (Much faster for large bulk loads)

-- Step 1: Create table WITHOUT index
CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    embedding VECTOR(1536)
);

-- Step 2: Bulk load all data
COPY documents FROM '/data/embeddings.csv';

-- Step 3: Increase memory and workers for faster index build
SET maintenance_work_mem = '4GB';
SET max_parallel_maintenance_workers = 8;

-- Step 4: Build the index AFTER loading all data
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m=16, ef_construction=64);

-- CORRECT: operator class matches the query operator
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);
SELECT * FROM items ORDER BY embedding <=> '[...]' LIMIT 5;
-- ^ PostgreSQL uses the index above

-- WRONG: mismatched operator class and query operator
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);
SELECT * FROM items ORDER BY embedding <-> '[...]' LIMIT 5;
-- ^ PostgreSQL CANNOT use the index (different metric)
-- This falls back to a slow sequential scan!

-- Multiple indexes for different metrics on the same column:
CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);
-- Now queries using either <-> or <=> can use an index

-- Check index usage with EXPLAIN:
EXPLAIN SELECT * FROM items ORDER BY embedding <=> '[1,2,3]' LIMIT 5;
-- Should show: Index Scan using items_embedding_idx

import psycopg2
from pgvector.psycopg2 import register_vector
from openai import OpenAI

conn = psycopg2.connect("postgresql://user:pass@localhost/mydb")
register_vector(conn)
cur = conn.cursor()
oai = OpenAI()

# STAGE 1: INGEST - embed and store documents
documents = [
    "pgvector is a PostgreSQL extension for vector search.",
    "HNSW indexes provide fast approximate nearest neighbour search.",
    "Cosine distance is commonly used for text embeddings.",
]
for text in documents:
    emb = oai.embeddings.create(
        model="text-embedding-3-small", input=text
    ).data[0].embedding
    cur.execute(
        "INSERT INTO documents (content, embedding) VALUES (%s, %s)",
        (text, emb)
    )
conn.commit()

# STAGE 2: RETRIEVE - semantic search for user query
user_question = "What kind of index should I use for fast search?"
q_emb = oai.embeddings.create(
    model="text-embedding-3-small", input=user_question
).data[0].embedding

cur.execute(
    "SELECT content FROM documents ORDER BY embedding <=> %s LIMIT 3",
    (q_emb,)
)
context_chunks = [r[0] for r in cur.fetchall()]

# STAGE 3: GENERATE - pass context to LLM
context = "\n".join(context_chunks)
completion = oai.chat.completions.create(
    model="gpt-4o-mini",
    messages=[
        {"role": "system", "content": f"Answer using this context:\n{context}"},
        {"role": "user",   "content": user_question}
    ]
)
print(completion.choices[0].message.content)

-- pgvector's <=> returns COSINE DISTANCE (not similarity)
-- Lower value = more similar
SELECT id, content,
       embedding <=> '[0.1,0.2,0.3]' AS cosine_distance
FROM documents
ORDER BY cosine_distance          -- ASC: smallest distance first = most similar
LIMIT 5;

-- Convert to cosine SIMILARITY:
SELECT id, content,
       1 - (embedding <=> '[0.1,0.2,0.3]') AS cosine_similarity
FROM documents
ORDER BY embedding <=> '[0.1,0.2,0.3]'  -- still ORDER BY distance
LIMIT 5;

-- For normalised vectors (most embedding models output unit vectors):
-- cosine_similarity = 1 - cosine_distance
-- Also: cosine_similarity ~= inner_product for unit vectors
-- So for OpenAI text-embedding-3-small (outputs unit vectors):
-- <=> and <#> (after negation) give equivalent rankings

-- Verify a vector is normalised (magnitude = 1.0):
SELECT id, |/( embedding <-> '[0,0,0]'^2 ) AS magnitude
-- or more simply:
SELECT id, sqrt(embedding <.> embedding) AS magnitude  -- inner product of self

-- Monitor index build progress:
SELECT
    phase,
    round(100.0 * blocks_done / NULLIF(blocks_total, 0), 1) AS pct_complete,
    blocks_done,
    blocks_total,
    tuples_done,
    tuples_total
FROM pg_stat_progress_create_index;

-- Typical phases for HNSW index:
-- 'initializing'
-- 'loading tuples'
-- 'done'

-- Typical phases for IVFFlat index:
-- 'initializing'
-- 'performing k-means'
-- 'assigning tuples'
-- 'loading tuples'

-- List existing indexes on a table:
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'documents';

-- Check index size:
SELECT pg_size_pretty(pg_indexes_size('documents'));

-- Speed up index builds with more memory and parallel workers:
SET maintenance_work_mem = '4GB';            -- more memory = faster build
SET max_parallel_maintenance_workers = 7;    -- use multiple CPU cores
-- Then create the index:

-- Check current dimension limit at runtime:
SELECT current_setting('vector.max_dimensions');

-- If you need > 16,000 dimensions (rare in practice):
-- Option 1: Use sparsevec if vector is sparse
CREATE TABLE items (
    id        BIGSERIAL PRIMARY KEY,
    embedding SPARSEVEC(100000)  -- up to 1M dims
);

-- Option 2: Dimensionality reduction before storage
-- Use PCA or UMAP to reduce from e.g. 65536-dim to 2048-dim
-- then store as vector(2048)

-- Option 3: Split vector across columns (workaround, ugly)
-- Not recommended but possible for extreme edge cases

-- Real-world dimension reference:
-- text-embedding-3-small: 1,536 dims (well within limit)
-- text-embedding-3-large: 3,072 dims (well within limit)
-- CLIP image embeddings: 512-768 dims (well within limit)
-- Custom deep models: may go up to 4096 dims
-- All well within the 16,000 dim limit for vector(n)

# pip install langchain langchain-postgres langchain-openai

from langchain_postgres import PGVector
from langchain_openai import OpenAIEmbeddings
from langchain_core.documents import Document

# Connection string:
CONNECTION = "postgresql+psycopg://user:pass@localhost/mydb"

# Initialise the vector store (creates table and extension if needed)
vectorstore = PGVector(
    connection=CONNECTION,
    collection_name="documents",
    embeddings=OpenAIEmbeddings(model="text-embedding-3-small"),
    use_jsonb=True,           # store metadata in JSONB column
)

# Add documents
docs = [
    Document(page_content="pgvector enables vector search in PostgreSQL",
             metadata={"source": "docs", "category": "database"}),
    Document(page_content="HNSW is the recommended index for most use cases",
             metadata={"source": "docs", "category": "indexing"}),
]
vectorstore.add_documents(docs)

# Similarity search:
results = vectorstore.similarity_search(
    "What index should I use for fast search?",
    k=3,
)
for doc in results:
    print(doc.page_content, doc.metadata)

# Search with score:
results_with_score = vectorstore.similarity_search_with_score(
    "fast nearest neighbour search",
    k=3,
)
for doc, score in results_with_score:
    print(f"score={score:.4f}: {doc.page_content}")

# Use as a LangChain retriever:
retriever = vectorstore.as_retriever(search_kwargs={"k": 5})

-- Create table allowing NULLs (default behaviour)
CREATE TABLE documents (
    id        BIGSERIAL PRIMARY KEY,
    content   TEXT,
    embedding VECTOR(1536)   -- nullable by default
);

-- Insert without embedding (embedding is NULL)
INSERT INTO documents (content) VALUES ('Article not yet embedded');

-- NULL rows are automatically excluded from similarity queries
-- (they cannot have a meaningful distance)
SELECT id, content FROM documents
ORDER BY embedding <-> '[0.1,0.2,...]'  -- NULLs won't appear in results
LIMIT 5;

-- Find rows missing embeddings (need to be processed):
SELECT id, content FROM documents WHERE embedding IS NULL;

-- Count unembedded documents:
SELECT COUNT(*) FROM documents WHERE embedding IS NULL;

-- Prevent NULLs if all rows must have embeddings:
CREATE TABLE documents_required (
    id        BIGSERIAL PRIMARY KEY,
    content   TEXT,
    embedding VECTOR(1536) NOT NULL  -- enforce non-null
);

-- Partial index to cover only non-NULL rows
-- (useful for variable-dimension or partially-embedded tables):
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WHERE embedding IS NOT NULL;

-- Partial index: only index documents in a specific category
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WHERE category = 'technical';  -- index only 'technical' rows

-- Queries using the same filter benefit from the smaller index:
SELECT id, content FROM documents
WHERE category = 'technical'                  -- matches the partial index
ORDER BY embedding <=> '[...]' LIMIT 5;      -- uses the partial index
-- Much faster: index is smaller and focused

-- Partial index for published articles only:
CREATE INDEX ON articles USING hnsw (embedding vector_cosine_ops)
WHERE is_published = TRUE;

-- Partial index to exclude NULL embeddings:
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WHERE embedding IS NOT NULL;
-- Useful if some rows have no embedding yet

-- Multiple partial indexes for different categories:
CREATE INDEX idx_tech  ON docs USING hnsw (embedding vector_cosine_ops) WHERE category = 'tech';
CREATE INDEX idx_legal ON docs USING hnsw (embedding vector_cosine_ops) WHERE category = 'legal';
-- Each index is smaller and faster for its category

-- PostgreSQL uses a partial index only when the query WHERE clause
-- includes the index's WHERE condition

-- Vector arithmetic operators:
-- + : element-wise addition
-- - : element-wise subtraction
-- * : element-wise multiplication

SELECT '[1,2,3]'::vector + '[4,5,6]'::vector;  -- [5,7,9]
SELECT '[4,5,6]'::vector - '[1,2,3]'::vector;  -- [3,3,3]
SELECT '[1,2,3]'::vector * '[2,2,2]'::vector;  -- [2,4,6]

-- Compute the L2 norm (magnitude) of a vector:
SELECT l2_norm('[1,2,2]'::vector);  -- sqrt(1+4+4) = 3.0

-- Normalise a vector (make unit length):
SELECT l2_normalize('[1,2,2]'::vector);
-- Result: [0.333, 0.667, 0.667] (each / 3.0)

-- Compute the average (centroid) of multiple vectors:
SELECT avg(embedding) FROM documents WHERE category = 'technical';
-- Returns the centroid vector of all 'technical' embeddings
-- Useful for finding the 'centre' of a cluster

-- Compute the sum of all vectors:
SELECT sum(embedding) FROM documents;

-- Round-trip: store normalised version of an embedding
UPDATE documents
SET embedding = l2_normalize(embedding)
WHERE id = 1;

-- Convert between types:
SELECT embedding::halfvec(1536) FROM documents WHERE id = 1;
SELECT embedding::vector(1536)  FROM documents WHERE id = 1;

-- L2 distance (most common):
SELECT id, embedding <-> '[1,2,3]' AS l2_dist
FROM items ORDER BY l2_dist LIMIT 5;

-- L1 (Manhattan) distance:
SELECT id, embedding <+> '[1,2,3]' AS l1_dist
FROM items ORDER BY l1_dist LIMIT 5;

-- Comparison on same data:
SELECT
    id,
    embedding <-> '[1,2,3]' AS l2_distance,
    embedding <+> '[1,2,3]' AS l1_distance,
    embedding <=> '[1,2,3]' AS cosine_distance
FROM items
ORDER BY l2_distance
LIMIT 10;

-- L1 index:
CREATE INDEX ON items USING hnsw (embedding vector_l1_ops);

-- Quick rule of thumb:
-- Text embeddings: cosine distance <=> (magnitude-independent)
-- General purpose: L2 <->
-- Sparse or outlier-prone data: L1 <+>

-- Pattern 1: Embedding column on the same table as the content
CREATE TABLE articles (
    id           BIGSERIAL   PRIMARY KEY,
    title        TEXT        NOT NULL,
    body         TEXT        NOT NULL,
    author_id    BIGINT      REFERENCES users(id),
    category     TEXT,
    published_at TIMESTAMPTZ,
    is_published BOOLEAN     DEFAULT FALSE,
    embedding    VECTOR(1536)                    -- text-embedding-3-small
);

-- Pattern 2: Separate embeddings table (useful when embedding
-- the same content with multiple models or at multiple granularities)
CREATE TABLE article_chunks (
    id           BIGSERIAL   PRIMARY KEY,
    article_id   BIGINT      REFERENCES articles(id) ON DELETE CASCADE,
    chunk_index  INTEGER,    -- which chunk within the article
    chunk_text   TEXT,
    embedding    VECTOR(1536),
    model_name   TEXT        DEFAULT 'text-embedding-3-small'
);

-- Pattern 3: Multi-modal embeddings (different models on different columns)
CREATE TABLE products (
    id              BIGSERIAL   PRIMARY KEY,
    name            TEXT,
    description     TEXT,
    image_url       TEXT,
    text_embedding  VECTOR(1536),   -- text-embedding-3-small on description
    image_embedding VECTOR(512)     -- CLIP on product image
);

-- Indexes on each:
CREATE INDEX ON articles        USING hnsw (embedding vector_cosine_ops);
CREATE INDEX ON article_chunks  USING hnsw (embedding vector_cosine_ops);
CREATE INDEX ON products        USING hnsw (text_embedding  vector_cosine_ops);
CREATE INDEX ON products        USING hnsw (image_embedding vector_cosine_ops);

-- Standard vector: 4 bytes per dimension
-- halfvec:           2 bytes per dimension (50% storage reduction)

-- Create table with halfvec column:
CREATE TABLE documents_compact (
    id        BIGSERIAL   PRIMARY KEY,
    content   TEXT,
    embedding HALFVEC(1536)
);

-- For 1M documents at 1536 dimensions:
-- vector(1536):   1,000,000 * 1536 * 4 bytes = ~6 GB
-- halfvec(1536):  1,000,000 * 1536 * 2 bytes = ~3 GB
-- Saving: ~3 GB per million documents

-- Insert (same syntax as vector):
INSERT INTO documents_compact (content, embedding)
VALUES ('Example', '[0.1, 0.2, 0.3, ...]');
-- Values are automatically rounded to 16-bit precision

-- HNSW index for halfvec:
CREATE INDEX ON documents_compact
    USING hnsw (embedding halfvec_cosine_ops);  -- note: halfvec_cosine_ops

-- Convert from vector to halfvec:
SELECT embedding::halfvec(1536) FROM documents WHERE id = 1;

-- When to use halfvec:
-- Storage cost is significant (millions of documents, high dimensions)
-- Slight precision loss is acceptable (typically negligible for text)
-- When to stick with vector:
-- Highest accuracy required (scientific data, financial embeddings)
-- Precision is critical

-- FIXED-dimension column (recommended when all vectors have same size)
CREATE TABLE docs (embedding VECTOR(1536));  -- enforces exactly 1536 dims

-- ERROR: dimension mismatch
INSERT INTO docs (embedding) VALUES ('[1,2,3]');   -- ERROR: expected 1536 dims
INSERT INTO docs (embedding) VALUES (ARRAY_FILL(0.0::float4, ARRAY[1024])::vector);
-- ERROR: expected 1536 dimensions, got 1024

-- VARIABLE-dimension column (no constraint)
CREATE TABLE docs_flex (embedding VECTOR);   -- accepts any dimension
INSERT INTO docs_flex VALUES ('[1,2,3]');     -- OK: 3 dims
INSERT INTO docs_flex VALUES ('[1,2,3,4,5]');-- OK: 5 dims

-- CAVEAT: you cannot create a standard index on variable-dim columns!
-- Because all rows in the index must have the same dimensionality
-- Use a PARTIAL INDEX or EXPRESSION INDEX on a specific dimension:
CREATE INDEX ON docs_flex USING hnsw ((embedding::vector(1536)) vector_cosine_ops)
WHERE vector_dims(embedding) = 1536;

-- Check dimensions of a stored vector:
SELECT vector_dims(embedding) FROM docs_flex LIMIT 5;

-- Find rows with unexpected dimension count:
SELECT id, vector_dims(embedding) AS dims
FROM docs_flex
WHERE vector_dims(embedding) != 1536;

# pip install pgvector django psycopg2-binary

# settings.py - make sure django uses PostgreSQL:
DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "mydb",
        "USER": "user",
        "PASSWORD": "pass",
        "HOST": "localhost",
    }
}

# models.py
from django.db import models
from pgvector.django import VectorField, HnswIndex

class Document(models.Model):
    content   = models.TextField()
    embedding = VectorField(dimensions=1536)  # pgvector field

    class Meta:
        indexes = [
            HnswIndex(
                name="document_embedding_hnsw",
                fields=["embedding"],
                m=16,
                ef_construction=64,
                opclasses=["vector_cosine_ops"],
            )
        ]

# Migration: generates CREATE EXTENSION vector + table
# python manage.py makemigrations && python manage.py migrate

# views.py / management commands:
from pgvector.django import CosineDistance

# Insert
Document.objects.create(content="Example", embedding=[0.1, 0.2, ...])

# Query: 5 most similar documents
query_vector = get_embedding("search query")
results = Document.objects.annotate(
    distance=CosineDistance("embedding", query_vector)
).order_by("distance")[:5]

for doc in results:
    print(doc.content, doc.distance)

-- Full performance-tuned setup for large tables:

-- 1. Create table with efficient type
CREATE TABLE items (
    id        BIGSERIAL PRIMARY KEY,
    content   TEXT,
    category  TEXT,
    embedding HALFVEC(1536)   -- 2x storage savings
);

-- 2. Add composite B-tree index for filter columns:
CREATE INDEX ON items (category);   -- speeds up WHERE category = '...'

-- 3. Configure for fast HNSW build:
SET maintenance_work_mem = '4GB';
SET max_parallel_maintenance_workers = 7;

-- 4. Build HNSW index AFTER bulk loading data:
CREATE INDEX ON items USING hnsw (embedding halfvec_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- 5. At query time, tune recall if needed:
SET hnsw.ef_search = 100;  -- increase if recall is insufficient

-- 6. Check query plan:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, content FROM items
WHERE category = 'tech'
ORDER BY embedding <=> '[...]'::halfvec
LIMIT 10;
-- Verify: 'Index Scan using items_embedding_idx' appears

-- Return results only within a distance threshold
-- (distance < threshold means similar enough)

-- Cosine distance threshold (0 = identical, 1 = orthogonal)
SELECT id, content,
       1 - (embedding <=> '[...]') AS similarity,
       embedding <=> '[...]' AS distance
FROM documents
WHERE embedding <=> '[...]' < 0.3    -- only results within distance 0.3
ORDER BY distance
LIMIT 20;

-- L2 distance threshold (depends on your vector magnitude):
SELECT id, content, embedding <-> '[...]' AS distance
FROM documents
WHERE embedding <-> '[...]' < 1.5    -- only close vectors
ORDER BY distance
LIMIT 10;

-- Dynamic threshold: always return at least 1 result,
-- but enforce threshold if more than 1 exists
WITH ranked AS (
    SELECT id, content,
           embedding <=> '[...]' AS dist,
           ROW_NUMBER() OVER (ORDER BY embedding <=> '[...]') AS rn
    FROM documents
)
SELECT id, content, dist
FROM ranked
WHERE dist < 0.3 OR rn = 1  -- always return top result
ORDER BY dist
LIMIT 10;

-- Typical cosine distance thresholds (vary by model and use case):
-- 0.0 - 0.15: very similar (nearly duplicate content)
-- 0.15 - 0.30: similar (same topic, different wording)
-- 0.30 - 0.50: somewhat related
-- > 0.50: likely unrelated (for most text embedding models)

# pip install asyncpg pgvector

import asyncio
import asyncpg
from pgvector.asyncpg import register_vector
from openai import AsyncOpenAI

async def main():
    # Connect with asyncpg
    conn = await asyncpg.connect(
        "postgresql://user:pass@localhost/mydb"
    )
    # Register the vector codec (required for asyncpg)
    await register_vector(conn)

    # Create schema
    await conn.execute("""
        CREATE TABLE IF NOT EXISTS documents (
            id        BIGSERIAL PRIMARY KEY,
            content   TEXT,
            embedding VECTOR(1536)
        )
    """)

    # Generate and insert embedding
    client = AsyncOpenAI()
    text = "pgvector is a PostgreSQL extension"
    response = await client.embeddings.create(
        model="text-embedding-3-small", input=text
    )
    embedding = response.data[0].embedding

    await conn.execute(
        "INSERT INTO documents (content, embedding) VALUES ($1, $2)",
        text, embedding
    )

    # Query: async KNN search
    query_text = "vector search in PostgreSQL"
    q_emb = (await client.embeddings.create(
        model="text-embedding-3-small", input=query_text
    )).data[0].embedding

    rows = await conn.fetch(
        "SELECT id, content, embedding <=> $1 AS dist"
        " FROM documents ORDER BY dist LIMIT 5",
        q_emb
    )
    for row in rows:
        print(f"dist={row['dist']:.4f}: {row['content']}")

    await conn.close()

asyncio.run(main())

# For connection pooling with FastAPI:
# async with asyncpg.create_pool(dsn) as pool:
#     async with pool.acquire() as conn:
#         await register_vector(conn)
#         rows = await conn.fetch(...)

-- Binary quantisation: convert float vectors to binary (0/1 per dimension)
-- Each dimension becomes 1 bit instead of 32 bits = 32x compression!

-- Store binary quantised vectors:
CREATE TABLE items_binary (
    id              BIGSERIAL   PRIMARY KEY,
    content         TEXT,
    embedding       VECTOR(1536),    -- keep original for reranking
    embedding_bin   BIT(1536)        -- binary quantised for fast coarse search
);

-- Quantise: positive values -> 1, negative/zero -> 0
UPDATE items_binary
SET embedding_bin = (
    SELECT string_agg(
        CASE WHEN val > 0 THEN '1' ELSE '0' END, ''
        ORDER BY ordinality
    )::bit(1536)
    FROM unnest(embedding::float4[]) WITH ORDINALITY AS t(val, ordinality)
);

-- Two-stage retrieval with binary quantisation:
-- Stage 1: Fast coarse search on binary vectors (Hamming distance)
CREATE INDEX ON items_binary USING hnsw (embedding_bin bit_hamming_ops);

WITH candidates AS (
    SELECT id, embedding_bin <~> '[...]'::bit(1536) AS hamming_dist
    FROM items_binary
    ORDER BY hamming_dist
    LIMIT 100   -- get 100 candidates quickly from binary index
)
-- Stage 2: Rerank top candidates using full-precision cosine distance
SELECT i.id, i.content,
       i.embedding <=> '[...]' AS cosine_dist
FROM items_binary i
JOIN candidates c ON i.id = c.id
ORDER BY cosine_dist
LIMIT 5;  -- return final top-5 after precise reranking

-- Supabase example (Python):
from supabase import create_client

client = create_client(SUPABASE_URL, SUPABASE_KEY)

# pgvector is pre-installed; just use it:
client.rpc("match_documents", {
    "query_embedding": embedding,   # list of floats
    "match_threshold": 0.75,
    "match_count": 10
}).execute()

-- Neon example:
-- Same as regular PostgreSQL:
-- psql "postgresql://user:pass@ep-xxx.neon.tech/mydb?sslmode=require"
-- CREATE EXTENSION vector;
-- (works immediately - vector is pre-installed on Neon)

-- <#> returns the NEGATIVE inner product
-- For unit vectors: inner_product = cosine_similarity
-- ORDER BY <#> ASC = most similar first (most negative = highest similarity)

-- Inner product similarity search:
SELECT id, content,
       -(embedding <#> '[0.1,0.2,0.3]') AS inner_product_similarity
FROM documents
ORDER BY embedding <#> '[0.1,0.2,0.3]'  -- ASC order = most similar
LIMIT 5;
-- NOTE: negate in SELECT for display, but use the raw expression in ORDER BY

-- Why negative? PostgreSQL indexes only support ASC scans
-- High inner product = more similar, but ASC would put low values first
-- Solution: return the NEGATIVE so ASC scan gives most similar first

-- When inner product == cosine similarity:
-- When all vectors are unit-normalised (L2 norm = 1.0)
-- OpenAI text-embedding-3-small/large outputs ARE unit-normalised
-- So for OpenAI embeddings, <#> and <=> produce equivalent RANKINGS

-- Verify your vectors are normalised:
SELECT id, l2_norm(embedding) AS norm FROM documents LIMIT 5;
-- Should return values very close to 1.0 for normalised vectors

-- HNSW index for inner product:
CREATE INDEX ON documents USING hnsw (embedding vector_ip_ops);

-- IVFFlat index for inner product:
CREATE INDEX ON documents USING ivfflat (embedding vector_ip_ops) WITH (lists = 100);

-- Hybrid search: combine semantic similarity with full-text ranking
-- using Reciprocal Rank Fusion (RRF) to merge the two ranked lists

CREATE TABLE documents (
    id         BIGSERIAL PRIMARY KEY,
    content    TEXT NOT NULL,
    embedding  VECTOR(1536),
    content_ts TSVECTOR GENERATED ALWAYS AS
               (to_tsvector('english', content)) STORED  -- auto-updated FTS index
);
CREATE INDEX ON documents USING gin(content_ts);   -- FTS index
CREATE INDEX ON documents USING hnsw(embedding vector_cosine_ops);  -- vector index

-- Reciprocal Rank Fusion hybrid search:
WITH
semantic AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> '[...]') AS rank
    FROM documents
    ORDER BY embedding <=> '[...]'
    LIMIT 50
),
keyword AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY ts_rank(content_ts, query) DESC) AS rank,
           ts_rank(content_ts, query) AS ts_score
    FROM documents,
         to_tsquery('english', 'pgvector & PostgreSQL') AS query
    WHERE content_ts @@ query
    LIMIT 50
),
fused AS (
    SELECT
        COALESCE(s.id, k.id) AS id,
        COALESCE(1.0 / (60 + s.rank), 0) +
        COALESCE(1.0 / (60 + k.rank), 0) AS rrf_score
    FROM semantic s FULL OUTER JOIN keyword k USING (id)
)
SELECT d.id, d.content, f.rrf_score
FROM fused f JOIN documents d ON d.id = f.id
ORDER BY f.rrf_score DESC
LIMIT 10;

-- Permanently set in postgresql.conf or via ALTER SYSTEM:
ALTER SYSTEM SET maintenance_work_mem = '4GB';
ALTER SYSTEM SET max_parallel_maintenance_workers = 7;
ALTER SYSTEM SET work_mem = '128MB';
-- Apply config changes without full restart:
SELECT pg_reload_conf();

-- Set per-session (for index build or critical query):
SET maintenance_work_mem = '4GB';
SET max_parallel_maintenance_workers = 7;
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);
RESET maintenance_work_mem;
RESET max_parallel_maintenance_workers;

-- Per-query tuning:
SET hnsw.ef_search = 100;   -- increase recall for this query
SELECT * FROM items ORDER BY embedding <-> '[...]' LIMIT 5;
RESET hnsw.ef_search;

-- Verify current settings:
SHOW maintenance_work_mem;
SHOW hnsw.ef_search;
SHOW ivfflat.probes;