While dedicated vector databases dominate AI discussions, PostgreSQL with pgvector offers a compelling alternative that most engineers overlook. If you already have PostgreSQL in your stack, adding vector search might be simpler and more cost-effective than introducing a new database.

Through implementing both dedicated vector databases and pgvector in production systems, I’ve learned when each approach shines, and when PostgreSQL’s “good enough” vector capabilities are actually the optimal choice.

Why pgvector Deserves Consideration

pgvector isn’t just a workaround for teams that can’t adopt specialized databases. It offers genuine advantages:

Unified data layer eliminates the complexity of syncing between databases. Your documents, metadata, and embeddings live in the same database.

ACID transactions ensure consistency. Updating a document and its embedding happens atomically.

Familiar tooling means your existing PostgreSQL expertise transfers. Backup, monitoring, replication, and connection pooling work unchanged.

Cost efficiency avoids another managed service. Vector databases charge for storage and queries, but pgvector uses capacity you’re already paying for.

Getting Started with pgvector

Setting up pgvector requires enabling the extension and understanding its basic operations.

Installation and Setup

pgvector is a standard PostgreSQL extension:

Enable the extension in your database. This requires superuser or equivalent privileges.

Create vector columns with explicit dimensions. The dimension must match your embedding model: 1536 for OpenAI text-embedding-3-small, 3072 for text-embedding-3-large.

Store vectors as array literals or use client libraries that handle serialization. Most Python libraries like psycopg2 and asyncpg work seamlessly.

Basic Operations

Vector operations in pgvector are straightforward:

Insert embeddings as array values in vector columns. Standard INSERT statements work with appropriate casting.

Query by similarity using distance operators. The <-> operator computes L2 distance, <=> computes cosine distance, <#> computes inner product.

Combine with SQL for complex queries. Vector similarity search integrates with WHERE clauses, JOINs, and aggregations.

Indexing Strategies

Unindexed vector search scans every row, which is fine for small datasets but unusable at scale. pgvector offers two index types.

IVFFlat Indexes

IVFFlat (Inverted File Flat) partitions vectors into clusters:

Fast to build compared to HNSW. Index creation time grows linearly with data size.

Good recall with appropriate parameters. Configure lists (clusters) based on dataset size, typically sqrt(n) to n/1000.

Requires maintenance. Index quality degrades as data distribution changes. Periodic reindexing is needed.

Probes parameter controls accuracy/speed tradeoff. Higher probes check more clusters, improving recall at the cost of latency.

HNSW Indexes

HNSW (Hierarchical Navigable Small World) builds a graph structure:

Slower to build than IVFFlat. Graph construction is more computationally intensive.

Better recall for equivalent latency. The graph structure navigates to relevant vectors more efficiently.

No probes tuning required at query time. Configuration happens at index creation.

ef_construction and m parameters control index quality. Higher values improve recall but increase build time and memory.

Choosing Between Index Types

Use IVFFlat when:

• You need fast index creation
• Data changes frequently and reindexing is acceptable
• You can tune probes per query based on precision needs

Use HNSW when:

• Query performance matters most
• Data is relatively stable
• You want simpler query-time configuration

Hybrid Search Implementation

Pure vector search often underperforms hybrid approaches. pgvector integrates well with full-text search.

Combining Keyword and Vector Search

PostgreSQL’s native full-text search complements vector similarity:

ts_vector and ts_query for keyword matching. Standard PostgreSQL full-text search with ranking.

Vector similarity for semantic matching. Captures meaning that keywords miss.

Combine scores using weighted averaging or reciprocal rank fusion. The right combination depends on your data.

Implementation Patterns

Effective hybrid search typically follows one of these patterns:

Parallel search with merge runs both searches independently and combines results. Simple to implement but runs two queries.

Filtered vector search uses keyword matches as a filter before vector similarity. Reduces the vector search space.

Boosted scoring adds keyword match bonuses to vector similarity scores. Single query with combined ranking.

Reciprocal Rank Fusion

RRF is a simple but effective way to combine rankings:

Score based on rank rather than raw similarity. Each result’s score is 1/(k + rank) where k is a constant.

Sum scores across methods to get final ranking. Results that rank highly in both searches score highest.

Works without score normalization. Different scoring scales don’t matter when using ranks.

Performance Tuning

Getting good performance from pgvector requires understanding its characteristics.

Memory Configuration

Vector operations are memory-intensive:

maintenance_work_mem affects index build time. Higher values allow faster index creation.

effective_cache_size helps the query planner. Vector indexes benefit from being in memory.

shared_buffers impacts overall PostgreSQL performance. Standard tuning applies.

Query Optimization

Vector queries have unique optimization patterns:

Use LIMIT to stop early. Vector search can terminate after finding enough results.

Analyze query plans with EXPLAIN. Vector index usage isn’t always automatic.

Consider partitioning for very large datasets. Time-based or category-based partitions can reduce search scope.

Batch Operations

Bulk operations perform better with attention to:

Batch inserts instead of individual statements. Standard PostgreSQL batch patterns apply.

Disable indexes during bulk loads and rebuild afterward. Index maintenance during inserts is expensive.

Use COPY for large data loads. Faster than INSERT for substantial datasets.

When to Choose pgvector

pgvector is the right choice in specific scenarios:

Choose pgvector When

Your dataset is under 1-5 million vectors. pgvector handles millions of vectors with proper indexing.

You already use PostgreSQL. Adding an extension is simpler than managing another database.

You need ACID guarantees. Transactional consistency between documents and embeddings.

Hybrid queries are important. Combining vector search with relational queries and full-text search.

Cost matters. Avoiding another managed service reduces expenses.

Choose Dedicated Vector Databases When

Scale exceeds 10+ million vectors. Dedicated systems optimize for this scale.

Query latency is critical. Specialized systems achieve lower p99 latency.

You need advanced features. Namespace isolation, real-time updates, or sophisticated filtering.

Team expertise aligns. If your team knows Pinecone or Weaviate, use that expertise.

Production Considerations

Running pgvector in production requires attention to operational aspects.

Monitoring

Track vector-specific metrics:

Query latency for vector similarity searches. Watch for degradation as data grows.

Index size relative to data size. Vector indexes are large, so plan storage accordingly.

Memory pressure from vector operations. Monitor for swapping or OOM conditions.

Backup and Recovery

Standard PostgreSQL practices apply:

pg_dump includes vector data. No special handling required.

Replication works normally. Vector columns replicate like any other data.

Point-in-time recovery protects against data loss. Standard PostgreSQL DR practices.

Scaling Strategies

When single-node performance isn’t enough:

Read replicas distribute query load. Route vector searches to replicas.

Connection pooling manages client connections. PgBouncer or similar tools.

Partitioning distributes data across multiple shards. Requires application-level query routing.

Integration Patterns

Practical patterns for using pgvector in applications.

With Python Async Applications

For FastAPI and other async frameworks:

asyncpg provides async PostgreSQL access. Native support for vector types.

Connection pools managed per application. Configure pool size for your concurrency.

Prepared statements improve repeated query performance.

With ORMs

Popular ORMs support pgvector:

SQLAlchemy with pgvector extension provides native support. Type handling works automatically.

Django with django-pgvector adds model field support. QuerySet methods for similarity search.

Prisma supports raw queries for vector operations. Less seamless but functional.

What AI Engineers Need to Know

pgvector proficiency means understanding:

1. When pgvector fits versus dedicated vector databases
2. Index selection between IVFFlat and HNSW
3. Hybrid search combining vectors with full-text search
4. Performance tuning for production workloads
5. Integration patterns with your application framework
6. Operational aspects including monitoring and scaling

The engineers who understand these patterns make informed infrastructure decisions that balance capability, complexity, and cost.

For more on vector database decisions, check out my comparisons of pgvector vs dedicated vector databases and my guide to building production RAG systems. Understanding these tradeoffs is essential for AI infrastructure decisions.

Ready to implement vector search in PostgreSQL? Watch the implementation on YouTube where I build real pgvector applications. And if you want to learn alongside other AI engineers, join our community where we discuss database choices and implementation patterns daily.