Vector Databases in Production: pgvector vs Pinecone vs Qdrant vs Weaviate

The fundamental choice in vector database architecture is between a standalone vector store (requiring dual writes and external joins) versus an integrated relational vector database (where vectors live alongside user accounts and workspace tables):

When you store user accounts in PostgreSQL and vectors in Pinecone or Weaviate, you introduce the Dual-Database Anti-Pattern:

• Distributed Sync Failures: When a user deletes a website page in your app, what happens if the network fails before deleting the vector from Pinecone? The AI continues answering questions using stale, deleted data.
• Authorization Leakage: To ensure tenant A never retrieves tenant B’s documents, external vector databases require passing user IDs inside metadata filter JSON objects. If an application developer forgets a filter flag, data leaks across tenants.
• Network Latency Penalty: Every user chat requires an HTTP request to your relational DB (for auth/session) plus a separate HTTP roundtrip to your vector cloud, adding 100–300ms of latency.

Storing vectors directly inside PostgreSQL with pgvector provides atomic transactions: deleting a document instantly and irrevocably deletes its embeddings in the exact same database commit.

In `pgvector`, two index types dominate vector retrieval:

• IVFFlat (Inverted File Flat): Groups vectors into clusters. Extremely fast index build times and minimal memory footprint, but requires periodic rebuilding as new documents are crawled.
• HNSW (Hierarchical Navigable Small World): Builds a multi-layer graph of vectors. Provides sub-10ms query execution times and 99%+ recall without requiring full rebuilds on new inserts.