pgvector: Add Vector Search to PostgreSQL Without New Infrastructure

Why pgvector Matters

Most AI applications already use PostgreSQL for their relational data, user accounts, orders, sessions, configurations. Adding a separate vector database means running two data stores, maintaining two connection pools, synchronizing data between them, and managing two sets of backups, monitoring, and security policies. pgvector eliminates this entirely.

With pgvector, your vector embeddings live in the same database as your relational data. You can JOIN vector search results with user tables, filter by foreign key relationships, use PostgreSQL's full-text search alongside vector similarity, and wrap everything in transactions. This level of integration is impossible with a standalone vector database.

The practical sweet spot for pgvector is applications with up to 5 million vectors that already run PostgreSQL. In this range, pgvector delivers acceptable performance (10 to 30ms queries) with zero additional infrastructure cost. Above 10 million vectors or under 5ms latency requirements, dedicated vector databases like Qdrant or Pinecone become the better choice.

Installation and Setup

pgvector is available as a package for all major Linux distributions, as a pre-installed extension on managed PostgreSQL services (AWS RDS, Google Cloud SQL, Azure Database for PostgreSQL, Supabase, Neon), and can be compiled from source for custom deployments.

On Ubuntu or Debian, install with apt: sudo apt install postgresql-16-pgvector (adjust the version number for your PostgreSQL version). On managed services, pgvector is typically pre-installed and just needs to be enabled. In your database, run CREATE EXTENSION vector; to activate it. That is the entire installation process.

Once enabled, you have access to the vector data type. Create a table with a vector column by specifying the dimension count: CREATE TABLE documents (id bigserial PRIMARY KEY, content text, metadata jsonb, embedding vector(1536)). The 1536 in parentheses matches the output dimension of your embedding model, OpenAI's text-embedding-3-small produces 1536 dimensions.

Insert vectors as bracketed comma-separated lists: INSERT INTO documents (content, embedding) VALUES ('Your document text here', '[0.0234, -0.0891, 0.1547, ...]'). In practice, you pass the vector from your embedding model's output through your PostgreSQL client library, which handles the formatting automatically.

Index Types

pgvector supports two index types for accelerating similarity search. Without an index, every query scans all rows (exact nearest neighbor search), which is accurate but becomes impractically slow above 50,000 to 100,000 vectors.

HNSW (recommended). Hierarchical Navigable Small World graphs, the same algorithm used by Qdrant and Weaviate. HNSW indexes provide fast approximate search with configurable recall. Create one with CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 200). The vector_cosine_ops operator class specifies cosine distance. Other options are vector_l2_ops (Euclidean) and vector_ip_ops (inner product).

HNSW index parameters: m controls the number of connections per node (default 16, higher values use more memory but improve recall), ef_construction controls build quality (default 64, increase to 200 for better recall at the cost of longer index build time). At query time, set hnsw.ef_search to control the search-time accuracy/speed trade-off: SET hnsw.ef_search = 100. Higher values increase recall and latency. The default of 40 provides roughly 95% recall.

IVFFlat. Inverted file with flat quantization. Clusters vectors into lists and searches only the nearest lists at query time. Create with CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100). The lists parameter should be approximately the square root of your row count for optimal performance. IVFFlat indexes are faster to build than HNSW but produce lower recall. They also need to be rebuilt periodically as data changes significantly. For most use cases in 2026, HNSW is the better choice.

Index build time is the main operational consideration. Building an HNSW index on 1 million 1536-dimensional vectors takes 10 to 30 minutes depending on hardware and parameters. During the build, the table is locked for writes (on older PostgreSQL versions) or builds concurrently (CREATE INDEX CONCURRENTLY, which avoids locking but takes longer). Plan index builds during maintenance windows or use concurrent builds for production tables.

Querying with SQL

Vector similarity search in pgvector uses distance operators in ORDER BY clauses. The three operators are: <=> for cosine distance, <-> for L2 (Euclidean) distance, and <#> for negative inner product. Lower distance values indicate more similar vectors.

A basic similarity search: SELECT id, content, embedding <=> '[query vector here]' AS distance FROM documents ORDER BY embedding <=> '[query vector]' LIMIT 10. This returns the 10 most similar documents ranked by cosine distance.

The power of pgvector is combining vector search with SQL. Find the 5 most similar billing documents created in the last 30 days: SELECT d.id, d.content, d.embedding <=> '[query]' AS distance FROM documents d WHERE d.metadata->>'category' = 'billing' AND d.created_at > NOW() - INTERVAL '30 days' ORDER BY d.embedding <=> '[query]' LIMIT 5.

JOIN operations unlock even more capability. Find similar documents and include the author's name: SELECT d.content, u.name AS author, d.embedding <=> '[query]' AS distance FROM documents d JOIN users u ON d.author_id = u.id ORDER BY d.embedding <=> '[query]' LIMIT 10. This kind of cross-table vector query is unique to pgvector, no standalone vector database can express it.

For hybrid search combining vector similarity and full-text search, use PostgreSQL's built-in tsvector alongside pgvector: SELECT content, ts_rank(to_tsvector(content), plainto_tsquery('error code')) AS text_rank, embedding <=> '[query]' AS vector_distance FROM documents WHERE to_tsvector(content) @@ plainto_tsquery('error code') ORDER BY embedding <=> '[query]' LIMIT 10. This finds documents matching the keyword "error code" and ranks them by semantic similarity, similar to Weaviate's hybrid search but using standard SQL.

Performance Tuning

pgvector performance depends heavily on PostgreSQL configuration and hardware. The default PostgreSQL settings are conservative and will produce poor vector search performance without tuning.

shared_buffers: Set to 25% of available RAM. This controls how much data PostgreSQL caches in memory. For vector workloads, having the HNSW index in cache is critical for performance.

effective_cache_size: Set to 75% of available RAM. This tells the query planner how much OS-level caching to expect, which affects its decisions about index usage.

work_mem: Set to 256MB or higher for vector queries. This controls how much memory is available for sort operations, which vector search uses heavily. The default of 4MB is far too low.

maintenance_work_mem: Set to 2GB or higher during index builds. This controls memory available for CREATE INDEX operations. Higher values produce faster index builds.

hnsw.ef_search: The most impactful query-time parameter. Default is 40. Set to 100 for 97% recall, 200 for 99% recall. Higher values increase latency linearly, going from 10ms at ef_search=40 to 25ms at ef_search=200 on a million-row table.

Hardware recommendations for pgvector: CPU matters less than RAM and storage speed. Ensure the HNSW index fits entirely in RAM for best performance. At 1536 dimensions, each vector consumes roughly 6KB in the index. One million vectors require approximately 8 to 10GB of index memory. Use NVMe SSDs for storage, spinning disks will produce 10 to 50x slower queries when the index does not fit in memory.

Performance at Scale

At 100,000 vectors: 3 to 8ms query latency with HNSW, no special tuning needed. pgvector is competitive with dedicated databases at this scale.

At 1 million vectors: 10 to 30ms query latency with HNSW (ef_search=100). Adequate for most AI agent workloads. Requires 8 to 10GB of RAM for the index.

At 5 million vectors: 20 to 60ms query latency. Still usable for non-real-time applications. Requires 40 to 50GB of RAM. Performance under concurrent load degrades more sharply than dedicated vector databases.

At 10 million vectors: 40 to 120ms query latency. Concurrent query performance becomes inconsistent. Dedicated databases outperform pgvector by 5 to 10x at this scale. This is the practical ceiling for most pgvector deployments.

Write performance: INSERT with HNSW index maintenance runs at 1,500 to 4,000 rows per second. Without an index (bulk loading), writes run at 10,000 to 30,000 rows per second. The recommended pattern for large data loads is: drop the index, bulk insert, rebuild the index concurrently.

Strengths

Zero new infrastructure. If you run PostgreSQL, pgvector is free and requires no additional services, APIs, or deployments. This is its defining advantage.

Full SQL integration. JOINs, subqueries, CTEs, window functions, transactions, and all PostgreSQL features work alongside vector operations. This eliminates the data synchronization problem that plagues architectures with separate relational and vector databases.

Mature operational tooling. PostgreSQL's backup (pg_dump, WAL archiving, pg_basebackup), replication (streaming, logical), monitoring (pg_stat_statements, auto_explain), and security (row-level security, SSL, SCRAM authentication) all work unchanged with pgvector data.

Cost. pgvector is free and open source. Your only cost is the PostgreSQL instance itself, which you are already paying for. There are no per-query charges, no per-GB storage premiums, and no managed service markups.

Limitations

Performance ceiling. Above 5 to 10 million vectors, pgvector cannot compete with purpose-built databases on latency or throughput. PostgreSQL's general-purpose storage engine and query planner are not optimized for the specific access patterns of vector search.

No built-in clustering for vector workloads. PostgreSQL's streaming replication handles read scaling, but there is no automatic sharding for distributing vectors across nodes. At scales requiring horizontal distribution, you need application-level sharding or a tool like Citus.

Index build locks. Standard CREATE INDEX locks the table for writes during the build. CREATE INDEX CONCURRENTLY avoids the lock but takes 2 to 3x longer and consumes more resources. For large tables, index rebuilds require careful scheduling.

Query planner decisions. PostgreSQL's query planner occasionally makes suboptimal choices when combining vector search with complex WHERE clauses, sometimes performing a sequential scan instead of using the HNSW index. You may need to adjust planner settings or restructure queries to force index usage.