PostgreSQL pgvector AI Search: Enterprise Vector Search in SQL

TL;DR — pgvector is an open-source PostgreSQL extension that transforms PostgreSQL into a vector database. Store embeddings, perform similarity search (cosine, L2, inner product), and build RAG pipelines — all in SQL. No separate vector database needed. HNSW index for best query performance (95%+ recall at high ef_search), IVFFlat for memory-constrained environments. Hybrid search combines vector + BM25 full-text search in a single SQL query with RRF fusion. Row-Level Security (RLS) provides document-level ACLs natively — users only see documents their permissions permit. For most enterprise RAG use cases (up to 10M vectors), pgvector is sufficient and simpler than Pinecone or Weaviate — one database, one backup, one set of ACLs, no vendor lock-in. Production tuning: maintenance_work_mem = '4GB', HNSW with m=16, ef_construction=64, ef_search=40 for queries. Use with FastAPI for complete RAG backend.

pgvector eliminates the need for a separate vector database. Vectors, metadata, ACLs, full-text search, and audit logs all live in PostgreSQL — one database, one backup, one set of security policies. No Pinecone bill. No Weaviate cluster. No sync issues between your metadata store and your vector store.

For enterprise RAG, this simplicity is a feature. Row-Level Security works natively with vector search. ACID transactions ensure consistency. Point-in-time recovery protects your data. And you already have a PostgreSQL DBA.

pgvector Architecture

Setup and Schema

-- Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Documents table: vectors + full-text + ACLs in one table
CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    source TEXT NOT NULL,
    source_url TEXT,
    acl TEXT[] NOT NULL DEFAULT '{}',
    content_hash TEXT UNIQUE NOT NULL,
    embedding VECTOR(1536),
    tsv TSVECTOR GENERATED ALWAYS AS (
        to_tsvector('english', title || ' ' || content)
    ) STORED,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- HNSW index for vector search (production default)
CREATE INDEX idx_documents_embedding
    ON documents USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- GIN index for full-text search
CREATE INDEX idx_documents_tsv ON documents USING gin(tsv);

-- GIN index for ACL array filtering
CREATE INDEX idx_documents_acl ON documents USING gin(acl);

-- Row-Level Security for document permissions
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY acl_filter ON documents
    USING (acl && current_setting('app.user_acls')::text[]);

Hybrid Search: Vector + BM25 in One Query

-- Hybrid search with RRF fusion and ACL enforcement
-- Run this after: SET app.user_acls = '{engineering, product}';

WITH vector_results AS (
    SELECT id, 
           ROW_NUMBER() OVER (ORDER BY embedding <=> $1) AS rank
    FROM documents
    WHERE embedding IS NOT NULL
    ORDER BY embedding <=> $1
    LIMIT 50
),
text_results AS (
    SELECT id,
           ROW_NUMBER() OVER (ORDER BY ts_rank(tsv, $2) DESC) AS rank
    FROM documents
    WHERE tsv @@ $2
    ORDER BY ts_rank(tsv, $2) DESC
    LIMIT 50
)
SELECT d.id, d.title, d.content, d.source, d.source_url,
       COALESCE(1.0 / (60 + v.rank), 0) + COALESCE(1.0 / (60 + t.rank), 0) AS rrf_score
FROM documents d
LEFT JOIN vector_results v ON d.id = v.id
LEFT JOIN text_results t ON d.id = t.id
WHERE v.id IS NOT NULL OR t.id IS NOT NULL
ORDER BY rrf_score DESC
LIMIT 10;

RLS automatically filters both vector and text results by the user's ACLs. No application-level filtering needed.

HNSW vs IVFFlat Index Comparison

HNSW Tuning

-- Production HNSW configuration
SET maintenance_work_mem = '4GB';  -- For index build

-- Create index (use CONCURRENTLY for zero-downtime)
CREATE INDEX CONCURRENTLY idx_documents_embedding
    ON documents USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- Query-time tuning (higher = better recall, slower)
SET hnsw.ef_search = 40;  -- Default: 40. Range: 1-1000

Row-Level Security for Enterprise ACLs

import asyncpg

class PgVectorSearch:
    """pgvector search with row-level security for ACLs."""

    def __init__(self, conn_string: str):
        self.conn_string = conn_string

    async def search(self, query_embedding, query_text, user_acls: list, top_k=10):
        pool = await asyncpg.create_pool(self.conn_string, min_size=2, max_size=10)

        async with pool.acquire() as conn:
            # Set user's ACLs as session variable (RLS reads this)
            await conn.execute(
                "SET app.user_acls = $1",
                f"{{{','.join(user_acls)}}}"
            )

            # Hybrid search — RLS automatically filters results
            results = await conn.fetch("""
                WITH vector_results AS (
                    SELECT id, ROW_NUMBER() OVER (
                        ORDER BY embedding <=> $1
                    ) AS rank
                    FROM documents
                    WHERE embedding IS NOT NULL
                    ORDER BY embedding <=> $1
                    LIMIT 50
                ),
                text_results AS (
                    SELECT id, ROW_NUMBER() OVER (
                        ORDER BY ts_rank(tsv, $2) DESC
                    ) AS rank
                    FROM documents
                    WHERE tsv @@ $2
                    ORDER BY ts_rank(tsv, $2) DESC
                    LIMIT 50
                )
                SELECT d.id, d.title, d.content, d.source,
                       COALESCE(1.0/(60+v.rank), 0) + COALESCE(1.0/(60+t.rank), 0) AS rrf_score
                FROM documents d
                LEFT JOIN vector_results v ON d.id = v.id
                LEFT JOIN text_results t ON d.id = t.id
                WHERE v.id IS NOT NULL OR t.id IS NOT NULL
                ORDER BY rrf_score DESC
                LIMIT $3
            """, query_embedding, query_text, top_k)

            return [dict(r) for r in results]

pgvector vs Dedicated Vector Databases

Production Performance Tuning

PostgreSQL pgvector AI Search Checklist

• [ ] Install pgvector: CREATE EXTENSION vector;
• [ ] Create documents table with VECTOR(1536) column (match your embedding model)
• [ ] Add tsvector generated column for BM25 full-text search
• [ ] Add acl TEXT[] column for document-level permissions
• [ ] Create HNSW index with m=16, ef_construction=64
• [ ] Create GIN index on tsvector for full-text search
• [ ] Create GIN index on acl array for permission filtering
• [ ] Enable Row-Level Security and create ACL policy
• [ ] Set app.user_acls session variable before each query
• [ ] Implement hybrid search with RRF fusion in one SQL query
• [ ] Set maintenance_work_mem = '4GB' for index builds
• [ ] Use CREATE INDEX CONCURRENTLY for zero-downtime index creation
• [ ] Tune hnsw.ef_search (start at 40, increase for better recall)
• [ ] Monitor recall: compare approximate vs exact search results
• [ ] Use pg_stat_statements to identify slow queries
• [ ] Consider pgvector vs Pinecone tradeoffs
• [ ] Implement content hash deduplication in ingestion
• [ ] Add cross-encoder reranking after pgvector retrieval
• [ ] Use semantic answer caching with pgvector for cache store
• [ ] Configure PostgreSQL PITR for point-in-time recovery
• [ ] Set up Docker Compose deployment with pgvector image
• [ ] Integrate with FastAPI stack for RAG API
• [ ] Consider temporal knowledge graphs for versioned facts
• [ ] Use query rewriting before vector search for complex queries
• [ ] Monitor index size and rebuild when data changes significantly
• [ ] Consider partitioning for very large tables (>50M vectors)
• [ ] Use pgvectorscale for streaming filtering and parallel builds at scale

FAQ

What is pgvector?

pgvector is an open-source PostgreSQL extension that adds vector data types and vector similarity search to PostgreSQL. It transforms PostgreSQL into a vector database, allowing you to store embeddings, perform similarity search (cosine, L2, inner product), and build RAG pipelines — all within your existing PostgreSQL database. No separate vector database needed. pgvector supports HNSW and IVFFlat indexes for approximate nearest neighbor search, handles millions of vectors, and integrates with PostgreSQL features like row-level security, ACID transactions, and full-text search.

Should I use pgvector or a dedicated vector database like Pinecone?

Use pgvector when: you want vectors and metadata in one database (no sync issues), you need ACID transactions, you need row-level security for ACLs, you're already using PostgreSQL, or you want to avoid vendor lock-in. Use Pinecone when: you need managed infrastructure (no DBA), you have 100M+ vectors with high query volume, or you need specialized vector features pgvector lacks. For most enterprise RAG use cases (up to 10M vectors), pgvector is sufficient and simpler — one database, one backup, one set of ACLs, no vendor dependency.

What is the difference between HNSW and IVFFlat in pgvector?

HNSW (Hierarchical Navigable Small World) builds a graph index with multiple layers for fast approximate search. It offers better recall (95%+ at high ef_search) and faster queries, but uses more memory and takes longer to build. IVFFlat divides vectors into clusters and searches only the most relevant clusters. It uses less memory and builds faster, but has lower recall and requires choosing the number of lists. Recommendation: start with HNSW for best query performance. Switch to IVFFlat only if memory is constrained. For 1M+ vectors, HNSW with m=16, ef_construction=64 is the production default.

How do you do hybrid search with pgvector?

Hybrid search in pgvector combines vector similarity search with PostgreSQL full-text search (tsvector + GIN index). Run both searches in a single SQL query using a CTE, then fuse results with Reciprocal Rank Fusion (RRF). The query uses WITH clauses for vector and text results, then combines them with RRF scoring. RLS automatically filters both result sets by the user's ACLs. This gives you BM25 + vector search in one query with permission enforcement.

How do you implement row-level security for vector search in pgvector?

PostgreSQL Row-Level Security (RLS) works natively with pgvector. Enable RLS on the documents table, create a policy that filters by the user's ACL groups, and set the current user's ACLs as a session variable before querying. The policy: CREATE POLICY acl_filter ON documents USING (acl && current_setting('app.user_acls')::text[]). Then before each query: SET app.user_acls = '{engineering, sales}'. Vector search automatically respects RLS — users only see documents their ACLs permit. This is impossible with most dedicated vector databases.

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