As the AI landscape evolves in 2026, the question is no longer if you should use a vector database, but where your vectors should live. For many, the answer is increasingly PostgreSQL. With the combination of pgvector for core functionality and pgvectorscale for massive-scale performance, PostgreSQL has transformed from a relational workhorse into a high-performance vector engine capable of powering complex RAG (Retrieval-Augmented Generation) pipelines.

Why PostgreSQL for Vector Search?

The primary advantage of using PostgreSQL is architectural simplicity. Instead of managing a separate vector database (like Pinecone or Qdrant) and building complex synchronization pipelines, you can keep your transactional data and embeddings in the same table. This ensures atomic updates, consistent backups, and the ability to combine semantic search with standard SQL filters in a single query.

Core Components: pgvector vs. pgvectorscale

• pgvector: The foundational extension. It introduces the VECTOR data type and supports L2 distance, cosine similarity, and inner product operations. It provides HNSW and IVFFlat indexing for smaller-to-medium datasets.
• pgvectorscale: A performance-focused extension that complements pgvector. It introduces StreamingDiskANN indexing, which is optimized for datasets with millions of vectors where memory is a constraint.

Step 1: Installation and Setup

First, ensure the extensions are enabled in your database:

CREATE EXTENSION vector;
CREATE EXTENSION vectorscale CASCADE;

Step 2: Defining Your Schema

Create a table that stores your raw text alongside its vector embedding. For most modern OpenAI or Anthropic models, a vector size of 1536 is standard.

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    metadata JSONB,
    embedding vector(1536)
);

Step 3: Choosing the Right Index

Selecting the right index is critical for performance. Here are the 2026 best practices:

HNSW (Hierarchical Navigable Small World)

Best for datasets under 1 million vectors where the index can fit entirely in RAM. It offers the fastest query speeds and high recall.

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

StreamingDiskANN (via pgvectorscale)

The “game changer” for large-scale applications. Use this when you have millions of vectors and want to minimize memory costs by keeping the index primarily on disk (NVMe).

CREATE INDEX ON documents USING diskann (embedding vector_cosine_ops);

Step 4: Querying with Filtered Semantic Search

One of PostgreSQL’s “killer features” is the ability to combine similarity search with arbitrary SQL filters. For example, finding similar documents only within a specific category or date range:

SELECT content, metadata->>'author' as author
FROM documents
WHERE metadata @> '{"category": "PostgreSQL"}'
ORDER BY embedding <=> '[0.012, -0.023, ...]' -- Your query vector
LIMIT 5;

Automating the Pipeline with pgai

Maintaining embeddings can be a hassle. The pgai extension allows you to declare “Vectorizers” that automatically create and sync embeddings as your table data changes. It handles chunking, API rate limits, and retries natively within SQL.

SELECT ai.create_vectorizer(
     'documents'::regclass,
     loading => ai.loading_column(column_name=>'content'),
     destination => ai.destination_table(target_table=>'doc_embeddings'),
     embedding => ai.embedding_openai(model=>'text-embedding-3-small')
);

Summary of Best Practices (2026)

Scale	Recommended Index	Why
< 100k vectors	No Index / IVFFlat	Simple setup; flat scan is fast enough.
100k – 1M vectors	HNSW	Fastest latency if RAM permits.
> 1M vectors	StreamingDiskANN	Cost-efficient; 28x lower p95 latency at scale.

By leveraging these extensions, you can build a production-grade AI search engine that scales from a prototype to millions of documents, all while staying within the reliable and familiar ecosystem of PostgreSQL. Ready to build your next RAG app? Start with pgvector.