The reflex for semantic search in modern apps is often to bolt on a dedicated vector database like Pinecone, Qdrant, or Weaviate. This adds a new bill, a new SLA, and a sync job that inevitably drifts from your source of truth. But for many use cases, pgvector inside PostgreSQL is sufficient. It keeps embeddings adjacent to the rows they describe, leveraging the ACID transactions, replication, and backup strategies you already maintain. The key is understanding the shift from exact matching to approximate ranking.

The Shift from Equality to Geometry

Relational databases answer "which rows are equal to this?" with exact matches using B-trees. Vector databases answer "which rows are like this?" by calculating distance in high-dimensional space. An embedding model, such as OpenAI’s text-embedding-3-small, converts text into a fixed-length list of numbersβ€”1,536 dimensions by default. Similar meanings result in similar coordinates. Search becomes geometry: you embed the query and ask for its nearest neighbors. This requires a change in mental model; you are no longer filtering for identity, but ranking for similarity.

Indexing Traps: IVFFlat vs. HNSW

Exact nearest neighbor search compares the query against every row, costing 1.5 billion multiply-adds for one million 1,536-dimension vectors. To avoid this, you need approximate indexes. HNSW (Hierarchical Navigable Small World) is generally superior to IVFFlat because it requires no training step and can be built on an empty table. IVFFlat relies on k-means clustering of existing data. If you create an IVFFlat index via migration before loading data, it builds lists from an empty set, resulting in poor recall unless you manually REINDEX. HNSW defaults to m = 16 and ef_construction = 64, offering a better speed-recall trade-off for most builders.

Production Pitfalls: Filtering and Hybrid Search

A common silent failure occurs when adding a WHERE clause to a vector query. With an approximate index, filtering happens after the index scan. If a condition matches only 10% of rows, HNSW with default ef_search = 40 may return only 4 results instead of the requested 10. Since pgvector 0.8.0, iterative index scans (hnsw.iterative_scan = strict_order) fix this by scanning until enough results are found. Additionally, vector search struggles with exact identifiers like course codes. Hybrid search combines vector similarity with Postgres full-text search, merging results using Reciprocal Rank Fusion (RRF) to balance paraphrase understanding with keyword precision.

When to Scale Out (Or Not)

Before migrating to a dedicated vector store, exhaust Postgres’s native scaling levers. Use halfvec types to halve storage size, employ binary quantization for a fast first pass followed by full-precision re-ranking, and ensure maintenance_work_mem is large enough for HNSW builds. Read replicas and sharding via Citus or PgDog can handle significant load. A dedicated vector store only earns its place when these optimizations fail to meet latency or recall targets, or when vector traffic must scale independently of transactional workloads.

Key Takeaways

  • Start with HNSW indexes; avoid IVFFlat unless memory constraints are severe.
  • Enable iterative scans in pgvector 0.8.0+ to prevent filtered queries from returning empty results.
  • Use hybrid search (Vector + Full-Text) for robust retrieval that handles both meaning and exact terms.
  • Measure recall against exact search (SET LOCAL enable_indexscan = off) before adding infrastructure.

The Bottom Line

Don't add infrastructure complexity until Postgres physically can't cope. pgvector 0.8.0 closes most gaps, making it the default choice for developers who value operational simplicity over premature optimization.