PgVector vs ClickHouse: Architecting LLM Semantic Embeddings

Building an enterprise-scale retrieval-augmented generation (RAG) platform requires picking a vector database capable of indexing and querying millions of semantic text embeddings under millisecond thresholds. We benchmarked PostgreSQL running pgvector against ClickHouse, evaluating indexing speeds, retrieval latency, and query accuracy.

1. The pgvector HNSW Indexing Mechanics

PostgreSQL's pgvector extension added HNSW (Hierarchical Navigable Small World) index support, transforming relational tables into ultra-fast vector search spaces. For developers wanting database consistency without running a standalone database service, pgvector is an ideal solution.

-- Enable vector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Create table with embedding support
CREATE TABLE documents (
    id serial PRIMARY KEY,
    content text,
    embedding vector(1536) -- Matches OpenAI embedding dimensions
);

-- Build high-performance HNSW index
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

2. The ClickHouse Analytical Scale

ClickHouse handles vector search differently. Designed as a high-frequency columnar data warehouse, it shines when querying millions of log rows simultaneously. Running vector functions within clickhouse pipelines allows scaling analysis without separate microservices.

In our benchmark queries, pgvector resolved cosine similarity search in 1.4ms for datasets under 1 million entries, while ClickHouse excelled in processing parallel batches, finishing aggregate analytical operations 4x faster.