pgvector 2026: Turn PostgreSQL into a High-Performance Vector Database — Setup, Tuning & RAG Integration Guide

-- HNSW index (recommended for most workloads)
CREATE INDEX ON documents
  USING hnsw (embedding vector_l2_ops)
  WITH (m = 16, ef_construction = 64);

-- IVFFlat index (faster builds, lower recall)
CREATE INDEX ON documents
  USING ivfflat (embedding vector_l2_ops)
  WITH (lists = 100);

-- L2 distance (smaller = more similar)
SELECT id, embedding <-> query_vec AS distance
FROM documents ORDER BY distance LIMIT 10;

-- Cosine distance (for normalized embeddings)
SELECT id, embedding <=> query_vec AS distance
FROM documents ORDER BY distance LIMIT 10;

docker run -d \
  --name pgvector-demo \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -e POSTGRES_DB=vectordb \
  -p 5432:5432 \
  pgvector/pgvector:0.8.2-pg18

# Verify
docker exec pgvector-demo psql -U postgres -d vectordb -c "SELECT * FROM pg_extension WHERE extname = 'vector';"

# Install build dependencies (Ubuntu/Debian)
sudo apt-get install postgresql-server-dev-18 build-essential git

# Clone and build pgvector
git clone --branch v0.8.2 https://github.com/pgvector/pgvector.git
cd pgvector
make
sudo make install

# Enable extension in database
psql -U postgres -d mydb -c "CREATE EXTENSION IF NOT EXISTS vector;"

-- pgvector is pre-installed on Supabase. Just enable it:
CREATE EXTENSION IF NOT EXISTS vector;

-- Verify version
SELECT extversion FROM pg_extension WHERE extname = 'vector';
-- Returns: 0.8.2

-- On RDS PostgreSQL 18, pgvector is available as an extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Check available extensions if needed
SELECT * FROM pg_available_extensions WHERE name = 'vector';

-- Check pgvector version
SELECT extversion FROM pg_extension WHERE extname = 'vector';
-- Expected: 0.8.2

-- Test vector type
SELECT '[1,2,3]'::vector(3) <-> '[4,5,6]'::vector(3) AS l2_distance;
-- Expected: ~5.196

-- Create table with vector column (1536 dimensions = OpenAI embeddings)
CREATE TABLE documents (
    id          BIGSERIAL PRIMARY KEY,
    title       VARCHAR(512) NOT NULL,
    content     TEXT,
    embedding   vector(1536),
    metadata    JSONB DEFAULT '{}',
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    tenant_id   INTEGER NOT NULL DEFAULT 0
);

-- Add index for common filter columns
CREATE INDEX idx_docs_tenant ON documents(tenant_id);
CREATE INDEX idx_docs_created ON documents(created_at);

-- Insert a single document with embedding
INSERT INTO documents (title, content, embedding, metadata, tenant_id)
VALUES (
    'Introduction to pgvector',
    'pgvector adds vector support to PostgreSQL...',
    ARRAY_FILL(0.0::real, ARRAY[1536])::vector(1536),  -- placeholder
    '{"author": "dibi8", "category": "database"}',
    1
);

-- Insert with actual OpenAI embedding (from Python)
-- See RAG Integration section below for full example

# Batch insert vectors from Python
import psycopg2
import numpy as np

conn = psycopg2.connect("dbname=vectordb user=postgres password=mysecretpassword host=localhost")
cur = conn.cursor()

# Generate 10K random vectors as sample
batch_size = 10000
titles = [f"document_{i}" for i in range(batch_size)]
contents = [f"Content for document {i}" for i in range(batch_size)]
embeddings = np.random.randn(batch_size, 1536).astype(np.float32)
tenant_ids = [i % 10 for i in range(batch_size)]

# Use executemany for batch insert
args = [(titles[i], contents[i], embeddings[i].tolist(), tenant_ids[i]) for i in range(batch_size)]
cur.executemany(
    "INSERT INTO documents (title, content, embedding, tenant_id) VALUES (%s, %s, %s::vector, %s)",
    args
)
conn.commit()
print(f"Inserted {batch_size} documents")

-- Set parameters for parallel index build
SET maintenance_work_mem = '8GB';
SET max_parallel_maintenance_workers = 4;

-- Build HNSW index with tuned parameters
CREATE INDEX idx_docs_embedding_hnsw ON documents
  USING hnsw (embedding vector_l2_ops)
  WITH (
    m = 16,              -- Number of connections per layer (default: 16)
    ef_construction = 128  -- Size of dynamic candidate list (default: 64)
  );

-- Check index size
SELECT pg_size_pretty(pg_relation_size('idx_docs_embedding_hnsw'));
-- Typical: ~450 MB for 100K vectors of 1536 dimensions

-- Basic ANN search
SELECT id, title, embedding <-> $1::vector AS distance
FROM documents
ORDER BY embedding <-> $1::vector
LIMIT 10;

-- Filtered vector search (most common production pattern)
SELECT id, title, embedding <-> $1::vector AS distance
FROM documents
WHERE tenant_id = 42
  AND created_at > NOW() - INTERVAL '30 days'
  AND metadata->>'category' = 'tech'
ORDER BY embedding <-> $1::vector
LIMIT 20;

-- First, enable pg_trgm for text search
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Combined vector similarity + text relevance
SELECT
    d.id,
    d.title,
    d.embedding <-> $1::vector AS vec_distance,
    similarity(d.title, $2) AS text_similarity
FROM documents d
WHERE d.title % $2  -- trigram similarity filter
ORDER BY
    (d.embedding <-> $1::vector) * 0.7 + (1 - similarity(d.title, $2)) * 0.3
LIMIT 10;

-- Tune ef_search for recall vs. latency
-- Higher = better recall, slower queries
SET hnsw.ef_search = 100;  -- Default is 40; 64-128 is typical for production

-- Check actual query plan
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, embedding <-> $1::vector AS distance
FROM documents
ORDER BY embedding <-> $1::vector
LIMIT 10;
-- Look for: Index Scan using idx_docs_embedding_hnsw

-- Add halfvec column for quantized storage
ALTER TABLE documents ADD COLUMN embedding_half halfvec(1536);

-- Convert from full-precision
UPDATE documents SET embedding_half = embedding::halfvec;

-- Create HNSW index on halfvec
CREATE INDEX idx_docs_embedding_half_hnsw ON documents
  USING hnsw (embedding_half halfvec_l2_ops);

-- Query using halfvec (nearly identical results)
SELECT id, title, embedding_half <-> $1::halfvec AS distance
FROM documents
ORDER BY embedding_half <-> $1::halfvec
LIMIT 10;

-- Check space savings
SELECT
    pg_size_pretty(pg_relation_size('idx_docs_embedding_hnsw')) AS full_size,
    pg_size_pretty(pg_relation_size('idx_docs_embedding_half_hnsw')) AS half_size;
-- half_size is typically ~45-50% of full_size

; pgbouncer.ini for pgvector workloads
[databases]
vectordb = host=localhost port=5432 dbname=vectordb

[pgbouncer]
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 50
reserve_pool_size = 10

pip install langchain-postgres==0.0.13 langchain-openai==0.3.0

from langchain_postgres import PGVector
from langchain_openai import OpenAIEmbeddings

embeddings = OpenAIEmbeddings(model="text-embedding-3-large")

vector_store = PGVector(
    connection="postgresql://postgres:mysecretpassword@localhost:5432/vectordb",
    embeddings=embeddings,
    collection_name="documents",
    use_jsonb=True,
)

# Add documents
from langchain_core.documents import Document
docs = [
    Document(page_content="pgvector turns PostgreSQL into a vector database", metadata={"source": "blog"}),
    Document(page_content="HNSW indexes provide fast ANN search", metadata={"source": "docs"}),
]
vector_store.add_documents(docs)

# Similarity search with filter
results = vector_store.similarity_search(
    "vector database for RAG",
    k=5,
    filter={"source": "blog"}
)
for doc in results:
    print(f"Content: {doc.page_content}")

pip install llama-index-vector-stores-postgres==0.4.2

from llama_index.vector_stores.postgres import PGVectorStore
from llama_index.core import VectorStoreIndex, SimpleDirectoryReader
from llama_index.embeddings.openai import OpenAIEmbedding

vector_store = PGVectorStore.from_params(
    host="localhost",
    port=5432,
    database="vectordb",
    user="postgres",
    password="mysecretpassword",
    table_name="llama_index_docs",
    embed_dim=1536,
    hnsw_kwargs={
        "hnsw_m": 16,
        "hnsw_ef_construction": 128,
        "hnsw_ef_search": 100,
        "hnsw_dist_method": "vector_l2_ops",
    },
)

# Load documents
documents = SimpleDirectoryReader("./data").load_data()
index = VectorStoreIndex.from_documents(documents, vector_store=vector_store)

# Query
query_engine = index.as_query_engine()
response = query_engine.query("How does pgvector work?")
print(response)

import psycopg2
from openai import OpenAI
import numpy as np

client = OpenAI()
conn = psycopg2.connect("dbname=vectordb user=postgres password=mysecretpassword host=localhost")

def get_embedding(text: str) -> list[float]:
    resp = client.embeddings.create(
        model="text-embedding-3-large", input=text, dimensions=1536
    )
    return resp.data[0].embedding

def retrieve_documents(query: str, top_k: int = 5, tenant_id: int = 1):
    query_vec = get_embedding(query)
    cur = conn.cursor()
    cur.execute("""
        SELECT title, content, embedding <=> %s::vector AS distance
        FROM documents
        WHERE tenant_id = %s
        ORDER BY embedding <=> %s::vector
        LIMIT %s
    """, (query_vec, tenant_id, query_vec, top_k))
    return cur.fetchall()

# Full RAG pipeline
def rag_query(user_question: str) -> str:
    docs = retrieve_documents(user_question, top_k=5)
    context = "\n\n".join([f"Title: {d[0]}\n{d[1]}" for d in docs])
    
    response = client.chat.completions.create(
        model="gpt-4o",
        messages=[
            {"role": "system", "content": "Answer based on the provided context."},
            {"role": "user", "content": f"Context:\n{context}\n\nQuestion: {user_question}"}
        ]
    )
    return response.choices[0].message.content

print(rag_query("What is pgvector used for?"))

-- Enable RLS
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Policy: tenants can only see their own documents
CREATE POLICY tenant_isolation ON documents
    USING (tenant_id = current_setting('app.current_tenant')::INTEGER);

-- Set tenant per session
SET app.current_tenant = '42';

-- Now all queries automatically filter by tenant
SELECT * FROM documents;  -- Only tenant 42's documents visible

-- Track slow vector queries
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find top vector queries by latency
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
WHERE query LIKE '%<->%'
ORDER BY mean_exec_time DESC
LIMIT 10;

# Enable pg_stat_statements in postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

# Full backup including vector data
pg_dump -h localhost -U postgres -d vectordb -Fc > vectordb_backup.dump

# Restore
pg_restore -h localhost -U postgres -d vectordb_restore vectordb_backup.dump

# Vectors are backed up as text arrays and restored correctly

# Use connection pooling for production workloads
from psycopg2 import pool

conn_pool = pool.ThreadedConnectionPool(
    minconn=5,
    maxconn=50,
    host="localhost",
    database="vectordb",
    user="postgres",
    password="mysecretpassword"
)

def search_with_pool(query_vec, limit=10):
    conn = conn_pool.getconn()
    try:
        cur = conn.cursor()
        cur.execute(
            "SELECT id, title FROM documents ORDER BY embedding <-> %s::vector LIMIT %s",
            (query_vec, limit)
        )
        return cur.fetchall()
    finally:
        conn_pool.putconn(conn)

SELECT title, embedding <-> $1::vector AS distance
FROM documents
WHERE tenant_id = 42
  AND created_at > NOW() - INTERVAL '7 days'
  AND metadata @> '{"status": "published"}'
ORDER BY embedding <-> $1::vector
LIMIT 10;

-- Benchmark ef_search values
SET hnsw.ef_search = 64;
EXPLAIN ANALYZE SELECT ... ORDER BY embedding <-> $1 LIMIT 10;

SET hnsw.ef_search = 128;
EXPLAIN ANALYZE SELECT ... ORDER BY embedding <-> $1 LIMIT 10;