The Convergence: Why Vector Search Merged Directly into Columnar SQL

In the early wave of generative AI, companies deployed dedicated standalone vector databases to store embeddings. But in production architectures, data rarely exists as isolated vectors: an enterprise document has strict tenant permissions, creation timestamps, user metadata, billing tiers, and relational constraints.

The Dual-Database Synchronization Disaster

Maintaining a dedicated vector database alongside a primary PostgreSQL or ClickHouse database required complex distributed synchronization pipelines (CDC / Kafka). In practice, this created severe operational failure modes:

Metadata Drift: A document deleted in the primary SQL database remained indexed in the vector database, causing unauthorized retrieval leaks.
Post-Filtering Latency: If a user searched across 10 million vectors with a filter for tenant_id = 42, the vector database fetched the top-100 semantic matches globally, only to discard 99 of them during metadata filtering.

[Dual-System Architecture: Brittle Sync Pipelines, Permission Leaks]
Postgres DB ──► Kafka / CDC ──► Vector DB ──► Semantic Match ──► Filter Metadata (90% matches dropped!)

[Unified Columnar SQL Engine (ClickHouse / pgvector): Single ACID Engine]
SELECT id, title, score FROM documents
WHERE tenant_id = 42 AND created_at > '2026-01-01'
ORDER BY vector_cosine_distance(embedding, [0.12, ...]) ASC
LIMIT 10;
(Zero Synchronization Lag, Perfect Role-Based Access Control, Single Query Optimizer!)

The Power of Unified Vector Engines

Modern analytical engines (like ClickHouse with native HNSW indexes and PostgreSQL with pgvector) integrate vector search directly into relational SQL engines:

Single Source of Truth: Deleting a row instantly removes its vector index in the same ACID transaction.
Pre-Filtered Vector Routing: The query optimizer applies SQL relational filters (tenant_id, department) *before* traversing the vector index, searching only across authorized records.

Vector embeddings are simply an analytical data type. Integrating them directly into mature relational engines eliminated a whole layer of architectural complexity.