pgvector

Why pgvector

Every AI application needs to store and search embeddings. The question is where. Dedicated vector databases like Pinecone and Weaviate exist. But they add infrastructure, sync complexity, and operational burden.

pgvector takes a different approach. It brings vector search to PostgreSQL. Your embeddings live alongside your regular data. One database. One backup strategy. One consistency model. For most applications, that simplicity wins.

The integration matters more than you’d think. Semantic search that filters by user permissions. Recommendations that respect business rules. RAG retrieval that considers document metadata. These queries combine vector similarity with relational conditions. In dedicated vector databases, you query vectors, then query PostgreSQL, then merge results. With pgvector, it’s one query.

ACID transactions apply to embeddings too. When you update a document, its embedding updates atomically. No sync lag. No eventual consistency bugs. Your AI features are as reliable as your CRUD operations.

What We Build With It

Semantic search is the entry point. We’ve built search systems that understand meaning, not just keywords. A user searches for “how to reset password” and finds articles about “account recovery” and “login issues.” The embedding captures intent. pgvector finds the matches.

RAG applications are everywhere now. Chatbots that answer questions using your company’s knowledge base. Customer support that surfaces relevant documentation. Internal tools that search across wikis, tickets, and documentation. We build the retrieval layer that makes LLMs useful with your data.

Recommendation systems benefit from embeddings. Product recommendations based on description similarity. Content suggestions based on user behavior embeddings. “Similar items” features that understand more than category matching. pgvector powers these without dedicated infrastructure.

Deduplication at scale uses vector similarity. Near-duplicate detection for content moderation. Matching resumes to job descriptions. Finding similar support tickets. These are similarity problems that embeddings solve naturally.

We’ve built semantic matching for marketplaces. Job boards where candidates match to positions. Freelancer platforms where skills match to projects. Dating apps where compatibility goes beyond explicit preferences. Vector similarity captures what rules-based matching misses.

Our Experience Level

We’ve built production AI applications with pgvector. Not just prototypes. Systems handling real traffic with latency requirements.

Embedding generation is the starting point. We work with OpenAI embeddings, Cohere, and open-source models. We understand the trade-offs: embedding dimension, model quality, API costs, and latency. We choose the right model for your use case.

Index selection matters for performance. pgvector supports IVFFlat and HNSW indexes. IVFFlat is faster to build, HNSW is faster to query. We know the tuning parameters: lists for IVFFlat, m and ef_construction for HNSW. We benchmark on your data and access patterns.

Query optimization in pgvector differs from regular PostgreSQL. We understand how the query planner handles vector operations. We know when pre-filtering helps and when post-filtering is faster. We write queries that use indexes effectively.

We’ve handled the full pipeline. Chunking strategies for long documents. Metadata extraction for filtering. Incremental updates when content changes. Monitoring for index quality and query latency.

When to Use It (And When Not To)

pgvector fits when:

• You’re already on PostgreSQL — No new infrastructure to operate
• Embeddings relate to relational data — Users, documents, products with metadata
• Scale is thousands to low millions — pgvector handles this well
• Hybrid queries matter — Vector similarity plus filters and joins

Consider alternatives when:

• Scale exceeds tens of millions — Dedicated vector databases optimize for this
• Ultra-low latency is critical — Specialized systems can be faster
• You need advanced vector features — Sparse vectors, product quantization, specific distance metrics
• PostgreSQL isn’t in your stack — Adding it just for vectors is overkill

The honest take: pgvector handles 90% of use cases. The teams that need Pinecone or Milvus usually know it. If you’re unsure, start with pgvector.

Common Challenges and How We Solve Them

Slow index builds on large datasets. HNSW indexes are expensive to create. We build indexes during off-peak hours, use maintenance_work_mem settings appropriately, and sometimes build on replicas first.

Query latency spikes under load. Vector operations are CPU-intensive. We tune probes for IVFFlat and ef_search for HNSW to balance recall and speed. We use connection pooling to prevent resource contention.

Embedding drift when models change. Switching embedding models means re-embedding everything. We version embeddings, run backfills carefully, and sometimes maintain multiple embedding columns during transitions.

Chunking strategies for long documents. How you split documents affects retrieval quality. We experiment with fixed-size chunks, semantic boundaries, and overlapping windows. We tune for your content type.

Combining vector search with complex filters. Filtering before or after vector search changes results and performance. We understand these trade-offs and design queries that balance relevance and efficiency.

Monitoring embedding quality. Search quality degrades silently. We implement feedback loops, measure retrieval precision, and track query patterns that indicate problems.

pgvector brings AI search to PostgreSQL. We make it work at production scale.