pgvector Tutorial: Semantic Search in PostgreSQL with HNSW Indexes

TL;DR — pgvector adds native vector search to PostgreSQL with HNSW and IVFFlat indexes. MachineLearningMastery: "pgvector is an open-source PostgreSQL extension that adds native vector search. Keeps embeddings alongside relational data, preserving transactional guarantees, JOIN semantics, and full SQL. Vector data type, distance operators, HNSW and IVFFlat indexes." Markaicode: "Use pgvector/pgvector Docker image. CREATE EXTENSION vector. Create items table with vector(1536) column. Build HNSW index. Insert embeddings from OpenAI. Query with <=> cosine distance. Sub-10ms at 100K vectors." Calmops: "PostgreSQL 16+ recommended. Distance operators: <-> L2, <=> cosine, <#> inner product. HNSW: m=16, ef_construction=200. SET hnsw.ef at query time." AWS: "HNSW is default for production RAG. SET LOCAL hnsw.ef_search=100. Use SET LOCAL in transactions. vector_cosine_ops for text, vector_ip_ops for normalized." pgvector-python: "pip install pgvector. Supports Django, SQLAlchemy, Psycopg 3/2, asyncpg, pg8000, Peewee." Learn more with vector databases, embeddings, embedding models, and cosine similarity.

MachineLearningMastery introduces pgvector: "pgvector is an open-source PostgreSQL extension that adds native vector search to your existing database. Rather than moving your embeddings to a dedicated vector store, pgvector keeps them alongside your relational data, preserving PostgreSQL's transactional guarantees, JOIN semantics, point-in-time recovery, and the full SQL query language."

Markaicode provides the quick path: "Use the official pgvector/pgvector Docker image to get PostgreSQL + pgvector in one container. After mounting a volume, connect and run CREATE EXTENSION vector. Create an items table with a vector(1536) column and build an HNSW index for sub-10ms query latency at 100K vectors."

pgvector Architecture

pgvector Distance Operators

HNSW vs IVFFlat in pgvector

Implementation

import os
import json
import psycopg2
from pgvector.psycopg2 import register_vector
from dataclasses import dataclass
from typing import Optional

@dataclass
class PgvectorTutorial:
    """Complete pgvector setup: install, index, insert, query, tune."""

    def get_setup_sql(self) -> list:
        """SQL statements for pgvector setup."""
        return [
            # Step 1: Enable extension (one-time per database)
            "CREATE EXTENSION IF NOT EXISTS vector;",

            # Step 2: Create table with vector column
            """
            CREATE TABLE IF NOT EXISTS documents (
                id BIGSERIAL PRIMARY KEY,
                title TEXT NOT NULL,
                content TEXT NOT NULL,
                embedding vector(1536) NOT NULL,
                source TEXT,
                created_at TIMESTAMP DEFAULT NOW()
            );
            """,

            # Step 3: Create HNSW index for cosine distance
            """
            CREATE INDEX IF NOT EXISTS documents_embedding_hnsw_idx
            ON documents USING hnsw (embedding vector_cosine_ops)
            WITH (m = 16, ef_construction = 200);
            """,

            # Step 4: Create IVFFlat index (alternative)
            """
            CREATE INDEX IF NOT EXISTS documents_embedding_ivf_idx
            ON documents USING ivfflat (embedding vector_cosine_ops)
            WITH (lists = 100);
            """,
        ]

    def get_docker_setup(self) -> str:
        """Docker setup for pgvector."""
        return (
            "# Pull and run pgvector Docker image\n"
            "docker run --name pgvector-db \\\n"
            "  -e POSTGRES_PASSWORD=mysecretpassword \\\n"
            "  -v pgvector-data:/var/lib/postgresql/data \\\n"
            "  -p 5432:5432 \\\n"
            "  -d pgvector/pgvector:0.8.0-pg16\n"
            "\n"
            "# Connect via psql\n"
            "docker exec -it pgvector-db psql -U postgres\n"
            "\n"
            "# Enable extension\n"
            "CREATE EXTENSION IF NOT EXISTS vector;"
        )

    def connect_and_setup(self, dsn: str) -> psycopg2.extensions.connection:
        """Connect to PostgreSQL and register pgvector types."""
        conn = psycopg2.connect(dsn)
        register_vector(conn)
        return conn

    def insert_embeddings(self, conn, documents: list,
                          embedder=None) -> int:
        """Insert documents with embeddings into pgvector.

        documents: list of {"title", "content", "source"}
        embedder: callable that returns embedding vector
        """
        cur = conn.cursor()

        for doc in documents:
            # Generate embedding (in production: use OpenAI API)
            if embedder:
                embedding = embedder(doc["content"])
            else:
                embedding = [0.1] * 1536  # placeholder

            cur.execute(
                """
                INSERT INTO documents (title, content, embedding, source)
                VALUES (%s, %s, %s, %s)
                ON CONFLICT (id) DO UPDATE SET
                    title = EXCLUDED.title,
                    content = EXCLUDED.content,
                    embedding = EXCLUDED.embedding,
                    source = EXCLUDED.source
                """,
                (doc["title"], doc["content"],
                 embedding, doc.get("source", "")),
            )

        conn.commit()
        return len(documents)

    def similarity_search(self, conn, query_embedding: list,
                          top_k: int = 5,
                          filter_clause: str = None) -> list:
        """Perform cosine similarity search with optional filtering."""
        cur = conn.cursor()

        if filter_clause:
            sql = f"""
                SELECT id, title, content, source,
                       embedding <=> %s::vector AS distance
                FROM documents
                WHERE {filter_clause}
                ORDER BY embedding <=> %s::vector
                LIMIT %s
            """
            cur.execute(sql, (query_embedding, query_embedding, top_k))
        else:
            sql = """
                SELECT id, title, content, source,
                       embedding <=> %s::vector AS distance
                FROM documents
                ORDER BY embedding <=> %s::vector
                LIMIT %s
            """
            cur.execute(sql, (query_embedding, query_embedding, top_k))

        results = cur.fetchall()
        return [
            {
                "id": r[0],
                "title": r[1],
                "content": r[2],
                "source": r[3],
                "distance": float(r[4]),
                "similarity": 1 - float(r[4]),
            }
            for r in results
        ]

    def production_query(self, conn, query_embedding: list,
                         top_k: int = 5,
                         filter_clause: str = None,
                         ef_search: int = 100) -> list:
        """Production query with SET LOCAL in transaction."""
        cur = conn.cursor()

        with conn:  # transaction context
            cur.execute(
                "SET LOCAL hnsw.ef_search = %s", (ef_search,))
            cur.execute(
                "SET LOCAL hnsw.iterative_scan = relaxed_order")

            if filter_clause:
                sql = f"""
                    SELECT id, title, content, source,
                           embedding <=> %s::vector AS distance
                    FROM documents
                    WHERE {filter_clause}
                    ORDER BY embedding <=> %s::vector
                    LIMIT %s
                """
                cur.execute(sql,
                            (query_embedding, query_embedding, top_k))
            else:
                sql = """
                    SELECT id, title, content, source,
                           embedding <=> %s::vector AS distance
                    FROM documents
                    ORDER BY embedding <=> %s::vector
                    LIMIT %s
                """
                cur.execute(sql,
                            (query_embedding, query_embedding, top_k))

            results = cur.fetchall()

        return [
            {
                "id": r[0],
                "title": r[1],
                "content": r[2],
                "source": r[3],
                "distance": float(r[4]),
                "similarity": 1 - float(r[4]),
            }
            for r in results
        ]

    def get_async_example(self) -> str:
        """asyncpg example for async pgvector usage."""
        return (
            "import asyncpg\n"
            "from pgvector.asyncpg import register_vector\n"
            "\n"
            "async def search(query_embedding, top_k=5):\n"
            "    conn = await asyncpg.connect(\n"
            "        host='localhost', database='vectordb',\n"
            "        user='app_user',\n"
            "        password=os.environ['DB_PASSWORD']\n"
            "    )\n"
            "    await register_vector(conn)\n"
            "    \n"
            "    results = await conn.fetch(\n"
            "        '''SELECT id, title, content,\n"
            "                  embedding <=> $1::vector AS distance\n"
            "           FROM documents\n"
            "           ORDER BY embedding <=> $1::vector\n"
            "           LIMIT $2''',\n"
            "        query_embedding, top_k\n"
            "    )\n"
            "    await conn.close()\n"
            "    return results"
        )

    def get_tuning_recommendations(self) -> dict:
        """Production tuning recommendations for pgvector."""
        return {
            "maintenance_work_mem": "2GB (for index building)",
            "hnsw.ef_search": "100 (query-time, use SET LOCAL)",
            "hnsw.iterative_scan": "relaxed_order (for filtered queries)",
            "connection_pooling": "pgbouncer or app-level pooling",
            "upserts": "ON CONFLICT DO UPDATE for idempotent inserts",
            "index_choice": "HNSW for most workloads, IVFFlat for large static",
            "operator_class": "vector_cosine_ops for text, vector_ip_ops for normalized",
            "monitoring": [
                "Index size: SELECT pg_size_pretty(pg_relation_size('documents_embedding_hnsw_idx'))",
                "Recall: compare ANN results with exact search on sample",
                "Query latency: EXPLAIN ANALYZE on search queries",
                "Index build time: log maintenance_work_mem and build duration",
            ],
        }

pgvector Tutorial Checklist

• [ ] Install pgvector: Docker (pgvector/pgvector:0.8.0-pg16), source, or cloud provider
• [ ] Docker: docker run --name pgvector-db -e POSTGRES_PASSWORD=... -v pgvector-data:/var/lib/postgresql/data -d pgvector/pgvector:0.8.0-pg16
• [ ] Enable extension: CREATE EXTENSION IF NOT EXISTS vector; (one-time per database)
• [ ] PostgreSQL 13+ required, 16+ recommended for best performance
• [ ] pgvector 0.8.0: HNSW + IVFFlat, binary quantization, halfvec, sparsevec
• [ ] Create table with vector(N) column: CREATE TABLE items (id BIGSERIAL, embedding vector(1536))
• [ ] Build HNSW index: CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 200)
• [ ] HNSW gives sub-10ms query latency at 100K vectors
• [ ] HNSW can be created on empty table — no training step needed
• [ ] HNSW absorbs inserts without rebuilds — no recall drift
• [ ] IVFFlat alternative: CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100)
• [ ] IVFFlat requires data to build (k-means clustering) — cannot create on empty table
• [ ] IVFFlat recall drifts as data changes — best for large, mostly-static datasets
• [ ] Distance operator <=> for cosine distance (safe default for text/semantic embeddings)
• [ ] Distance operator <#> for negative inner product (faster when unit-normalized)
• [ ] Distance operator <-> for L2 Euclidean (rarely appropriate for semantic search)
• [ ] Operator class must match distance operator: <=> → vector_cosine_ops, <#> → vector_ip_ops, <-> → vector_l2_ops
• [ ] Install Python libs: pip install psycopg2-binary pgvector
• [ ] Register vector type: from pgvector.psycopg2 import register_vector; register_vector(conn)
• [ ] Insert: INSERT INTO items (embedding) VALUES (%s) with %s placeholder for list
• [ ] Query: SELECT * FROM items ORDER BY embedding <=> %s::vector LIMIT 5
• [ ] Use ON CONFLICT DO UPDATE for idempotent upserts
• [ ] For async: use asyncpg with from pgvector.asyncpg import register_vector
• [ ] pgvector-python supports: Django, SQLAlchemy, SQLModel, Psycopg 3/2, asyncpg, pg8000, Peewee
• [ ] Production: SET maintenance_work_mem = '2GB' before building indexes
• [ ] Production: SET LOCAL hnsw.ef_search = 100 in transaction (applies only to that query)
• [ ] Production: SET LOCAL hnsw.iterative_scan = relaxed_order for filtered queries
• [ ] Production: use connection pooling (pgbouncer or app-level)
• [ ] Production: wrap SET statements and SELECT in a transaction with SET LOCAL
• [ ] Production: swap vector_cosine_ops for vector_ip_ops if embeddings are unit-normalized
• [ ] Combine vector search with SQL WHERE filters for metadata-filtered similarity search
• [ ] pgvector keeps embeddings alongside relational data — preserves JOINs, transactions, SQL
• [ ] No separate vector database needed — use existing PostgreSQL
• [ ] pgvectorscale: StreamingDiskANN index, 10-100x faster filtered search than vanilla pgvector
• [ ] Supabase, Neon, Aurora, Cloud SQL all ship pgvector by default
• [ ] AWS Aurora: pgvector 0.8.0 on PostgreSQL 17.4+, 16.8+, 15.12+, 14.17+, 13.20+
• [ ] Monitor: index size with pg_size_pretty(pg_relation_size(...))
• [ ] Monitor: recall by comparing ANN results with exact search on sample
• [ ] Monitor: query latency with EXPLAIN ANALYZE
• [ ] Read vector databases for ANN fundamentals
• [ ] Read embeddings for vector fundamentals
• [ ] Read embedding models for model selection
• [ ] Read cosine similarity for distance metrics
• [ ] Read what is RAG for RAG architecture
• [ ] Build RAG from scratch with pgvector
• [ ] Add hybrid search with ParadeDB
• [ ] Add reranking after pgvector retrieval
• [ ] Test: HNSW index achieves sub-10ms latency at 100K vectors
• [ ] Test: cosine distance returns correct nearest neighbors
• [ ] Test: filtered search combines WHERE clause with vector search
• [ ] Test: upsert with ON CONFLICT updates embedding correctly
• [ ] Test: SET LOCAL hnsw.ef_search improves recall in transaction
• [ ] Document index type, parameters, operator class, and tuning settings

FAQ

How do you set up pgvector for semantic search in PostgreSQL?

Install pgvector, enable the extension, create a table with a vector column, build an HNSW index, insert embeddings, and query with cosine distance. MachineLearningMastery: "pgvector is an open-source PostgreSQL extension that adds native vector search to your existing database. Rather than moving embeddings to a dedicated vector store, pgvector keeps them alongside your relational data, preserving PostgreSQL transactional guarantees, JOIN semantics, point-in-time recovery, and the full SQL query language. The extension adds a vector data type, SQL distance operators, and two index types — HNSW and IVFFlat." Markaicode: "Use the official pgvector/pgvector Docker image to get PostgreSQL + pgvector in one container. After mounting a volume, connect and run CREATE EXTENSION vector. Create an items table with a vector(1536) column and build an HNSW index. Insert embeddings from OpenAI text-embedding-3-small and query with the <=> cosine distance operator." Calmops: "PostgreSQL 16+ recommended for best performance. Sufficient memory for index building (at least 2GB for production workloads). CREATE EXTENSION IF NOT EXISTS vector." Steps: (1) Install pgvector (Docker, source, or cloud). (2) CREATE EXTENSION vector. (3) CREATE TABLE with vector(1536) column. (4) CREATE INDEX USING hnsw (embedding vector_cosine_ops). (5) Insert embeddings from Python. (6) Query with ORDER BY embedding <=> query_vector LIMIT 5.

What are the pgvector distance operators and when to use each?

pgvector provides three distance operators: <-> for L2 (Euclidean), <=> for cosine distance, and <#> for negative inner product. MachineLearningMastery: "Operator <-> is L2 (Euclidean) distance — straight-line gap between two vectors. Operator <=> is cosine distance — angle between vectors, ignores magnitude. Operator <#> is negative inner product — use for dot product similarity." AWS: "Cosine distance (<=>) with vector_cosine_ops is the safe default for text or semantic embeddings. Ignores vector magnitude. Negative inner product (<#>) with vector_ip_ops is faster than cosine when vectors are already unit-normalized. L2 (Euclidean) distance (<->) with vector_l2_ops is rarely appropriate for semantic search." The operator class in your index must match the distance operator in your queries: <-> → vector_l2_ops, <=> → vector_cosine_ops, <#> → vector_ip_ops.

How do you create an HNSW index in pgvector?

Create an HNSW index with CREATE INDEX USING hnsw, specifying the operator class and tuning parameters m and ef_construction. MachineLearningMastery: "HNSW constructs a multi-layer graph where each node connects to a bounded number of neighbors across multiple levels. Queries navigate by entering at the coarsest layer and descending toward nearest neighbors. Because the graph is built incrementally, there is no training step — you can create an HNSW index on an empty table and add rows over time without rebuilding. HNSW gives the best speed-to-recall ratio but requires more memory and takes longer than IVFFlat." Calmops: "CREATE INDEX idx_documents_embedding_hnsw ON documents USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 200). Set hnsw.ef at query time: SET hnsw.ef = 100." Markaicode: "Build an HNSW index for sub-10ms query latency at 100K vectors." SQL: CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 200).

How do you use pgvector with Python and psycopg2?

Install pgvector and psycopg2, register the vector type, insert embeddings, and query with cosine distance. pgvector-python: "pip install pgvector. Supports Django, SQLAlchemy, SQLModel, Psycopg 3, Psycopg 2, asyncpg, pg8000, and Peewee. Register the types with your connection." Markaicode: "Install psycopg2-binary and pgvector. Connect and call register_vector(conn). Use %s placeholders for list embeddings. Insert with INSERT INTO items (embedding) VALUES (%s). Query with SELECT * FROM items ORDER BY embedding <=> %s::vector LIMIT 5. For speed, always create an HNSW index first." Calmops: "For async: use asyncpg with register_vector. INSERT INTO documents (title, content, embedding, source) VALUES ($1, $2, $3::vector(1536), $4)." Python code: import psycopg2; from pgvector.psycopg2 import register_vector; conn = psycopg2.connect(...); register_vector(conn); cur.execute('INSERT INTO items (embedding) VALUES (%s)', (embedding,)); cur.execute('SELECT * FROM items ORDER BY embedding <=> %s LIMIT 5', (query_vec,)).

How do you tune pgvector for production?

Tune pgvector for production by setting maintenance_work_mem, using SET LOCAL for query parameters, enabling iterative scan, and choosing the right index type. AWS: "For production RAG on Aurora PostgreSQL, HNSW is the default choice. Set hnsw.iterative_scan = relaxed_order and hnsw.ef_search = 100. In production code, wrap the SET statements and SELECT in a transaction and use SET LOCAL instead, so values apply only to that query. Swap vector_cosine_ops for vector_ip_ops if embeddings are unit-normalized." Calmops: "Vector search queries benefit from connection pooling. Set maintenance_work_mem for index building. Use ON CONFLICT for upserts." Markaicode: "Production steps include tuning maintenance_work_mem and using ON CONFLICT for upserts." Production tuning: (1) SET maintenance_work_mem = '2GB' before building indexes. (2) Use SET LOCAL hnsw.ef_search = 100 in transactions. (3) Enable SET hnsw.iterative_scan = relaxed_order for filtered queries. (4) Use connection pooling (pgbouncer or app-level). (5) Use ON CONFLICT DO UPDATE for upserts. (6) Monitor index size and recall.

Want a self-hosted AI company brain that does all of this out of the box?
Book a demo →