The Complete Production SOP: How to Deploy and Run PostgreSQL pgvector Vector Search Tuning in Autonomous AI Agents

-- Step 1: Ensure pgvector is enabled
CREATE EXTENSION IF NOT EXISTS vector;

-- Step 2: Prepare table with fixed-dimension vector column (e.g., text-embedding-3-small = 1536)
CREATE TABLE IF NOT EXISTS document_embeddings (
    id BIGSERIAL PRIMARY KEY,
    document_id UUID NOT NULL,
    content TEXT NOT NULL,
    embedding vector(1536) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Allocate temporary maintenance memory for HNSW graph creation
SET maintenance_work_mem = '2GB';

-- Construct HNSW Index with tuned parameters for high recall
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_document_embeddings_hnsw_cosine
ON document_embeddings
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Reset to connection-level default
RESET maintenance_work_mem;

-- Set search exploration depth for the current connection or transaction
SET LOCAL hnsw.ef_search = 40;

-- Retrieve top 10 most similar documents using cosine distance
SELECT
    id,
    document_id,
    content,
    1 - (embedding <=> $1::vector) AS cosine_similarity
FROM document_embeddings
ORDER BY embedding <=> $1::vector
LIMIT 10;

#!/usr/bin/env bash
set -euo pipefail

PLAN=$(psql "$DATABASE_URL" -t -A -c "EXPLAIN SELECT id FROM document_embeddings ORDER BY embedding <=> '[0.01, 0.02, ...]'::vector LIMIT 5;")

if echo "$PLAN" | grep -q "Index Scan"; then
    echo "Verification Passed: HNSW Index Scan confirmed."
    exit 0
else
    echo "Verification Failed: Query performed Seq Scan. Check index parameters."
    exit 1
fi