PostgreSQL,ClickHouse or OpenSearch- Practical Guide to Picking a Vector Strategy for Production AI

Topic was presented at Munich Datageeks by Geethu and Vadim - May 2026

Abstract

This talk presents a practical framework for choosing a vector search strategy for production AI systems, anchored to a concrete scenario: building an image-based product search engine for an online shop that has to scale from a small prototype to millions of products with heavy metadata filtering. The speaker compares three approaches to vector search — PostgreSQL with pgvector, ClickHouse, and OpenSearch — covering how each indexes vectors, handles metadata filtering, and scales, backed by a hands-on performance benchmark. The core argument is that metadata filtering, not raw vector search, is where most production systems break, and that the right database choice depends on data volume, filtering needs, and a team's operational capacity rather than on benchmark superiority alone. A second speaker then walks through a real customer project — a RAG chatbot for a German information portal operator — showing how this same framework led the team to migrate a retrieval pipeline from Azure back to a simple, on-premises PostgreSQL/pgvector setup, and what concrete trade-offs that decision involved.

About the speaker

Geethu is a solutions architect at Aiven, where her day-to-day work involves designing data platforms built on services such as Kafka, PostgreSQL, and ClickHouse for different companies. Before this role she worked as a data integration developer and holds a master's degree in data science. This talk was her first official public talk. Outside of work, she enjoys books, movies, and traveling.

Vadim is a senior data scientist at Steadforce, a Munich-based consultancy and software development company, where he builds end-to-end machine learning and generative AI pipelines both in the cloud and on-premises. He studied data science at LMU Munich. Outside of work, he also enjoys traveling.

Transcript summary

Setting the scene: Aiven and the example use case

Aiven provides a range of open-source data services — including Kafka, PostgreSQL, ClickHouse, and OpenSearch — as fully managed offerings, removing the operational burden of running these databases from its customers. This gives the speaker visibility into how similar workloads behave across different databases and customer environments, and she notes she has no personal preference among the three databases discussed, since Aiven runs managed versions of all of them.

To ground the talk, Geethu introduces a recurring scenario: an online shop wants to let a customer upload a photo of a sneaker and search for similar products under a given price, in a specific size, and in stock. A simple prototype (around 1,000 products, embedded with OpenAI, stored in PostgreSQL, queried with pgvector) works well initially. In production, however, the catalog grows to around 5 million products across 200 categories with multiple filters, and that is when latency spikes and recall drops — the problem the rest of the talk addresses.

Vector search fundamentals

An embedding converts an item (text, image, or other data) into a list of numbers a vector in a fixed-dimensional space determined by the embedding model (for example, 1536 dimensions for a certain model). Items with similar meaning end up as nearby points in this vector space, so a similarity search finds the nearest neighbors to a query vector rather than an exact match.

Three points to remember about embeddings: semantically similar inputs produce vectors that are close together even if the wording differs; the vector dimensionality is fixed by the embedding model used; and different parts of a dataset must never be embedded with different models within the same index, since mixing models makes the resulting vector space meaningless for search. In short, vector search finds results by distance, not by equality — everything beyond that basic idea is engineering.

Exact vs. approximate search, and recall

Exact search compares a query against every vector in the database and returns the correct top results, but becomes slow at scale (roughly above 100K vectors). Approximate Nearest Neighbor (ANN) search instead uses an index to skip most of the dataset and search only a relevant subset, trading a small amount of accuracy for much better speed — the standard approach for production systems.

The key trade-off metric is recall: the fraction of the true top results that the approximate search actually returns. For a top-10 query, recall@10 is the number of correctly returned items divided by 10. For retrieval-augmented generation (RAG) applications, a recall between 0.85 and 0.95 is generally desirable; a recall of 1.0 effectively means exact search is being used rather than an approximate one.

ANN indexing algorithms: HNSW and IVF Flat

Two ANN algorithms come up repeatedly:

HNSW (Hierarchical Navigable Small World) works like a postal hierarchy: a search starts at a coarse top layer and progressively narrows down through finer layers until it reaches the closest match. This gives fast query speed but comes with slower index build times and higher memory requirements.
IVF Flat (Inverted File with Flat compression) works like sorting books by genre: the vector space is divided into clusters, the search identifies the closest cluster, and then searches only within it. This produces faster, lower-memory index builds, but query speed is generally slower than HNSW.

Three database approaches to vector search

The talk compares three ways of adding vector search to an existing data stack:

PostgreSQL + pgvector: pgvector is an open-source extension that adds a vector data type, HNSW and IVF Flat index types, and distance operators to PostgreSQL, an OLTP-first engine. Vectors live directly alongside transactional application data. The main appeal is that teams already running PostgreSQL can reuse existing backups, monitoring, and security practices without adopting a new system.
ClickHouse: an analytical (OLAP), column-oriented database built for billions of rows of events, logs, and metrics. Vectors are stored as arrays of float32 values with a vector similarity index added on top. Its distinguishing feature for search is that it applies metadata filters before the vector similarity search (prefiltering), which it does naturally due to its column-oriented storage.
OpenSearch: a distributed search platform, forked from Elasticsearch, with vector search added via a k-NN plugin. It supports hybrid search — combining vector similarity with traditional keyword (lexical, BM25) search — making it a strong choice when retrieval quality itself is the core product feature.

Why metadata filtering is where production systems break

Returning to the sneaker search scenario: a simple similarity search (level one) works well in any of the three databases. Adding a couple of filters, such as price and category (level two), still works in most databases, since a large share of the catalog still matches. But as more filters are added and queries become highly selective (level three) for example, filtering down to a small fraction of the catalog - recall can collapse.

The underlying issue is that an ANN index is built over the full dataset; applying filters on top of it effectively changes the dataset the search operates on, breaking the index's original assumptions and causing recall to drop sharply once filters narrow the result set significantly. This is not unique to e-commerce — it applies equally to multi-tenant systems filtering by tenant ID, compliance/access-control scenarios filtering by department or clearance, and time-scoped retrieval such as filtering by date.

There are two general strategies for handling this: post-filtering (search first, then discard results that don't match the filter) and pre-filtering (filter first, then search only within the reduced set). Post-filtering can return few or no useful results when the filter is highly selective, so pre-filtering is generally preferable when a database supports it efficiently. Of the three databases, ClickHouse performs pre-filtering natively due to its column-oriented storage; PostgreSQL defaults to post-filtering and can see degraded recall on selective queries; OpenSearch supports both but defaults to post-filtering, which can behave unpredictably depending on the query.

Query syntax also differs meaningfully: PostgreSQL and ClickHouse both use SQL-like syntax (with ClickHouse expressing distance as a function call and evaluating filter conditions before the vector scan), while OpenSearch queries are structured as JSON/REST requests with separate filter and vector (must) clauses.

Benchmark: comparing the three databases head-to-head

To make the comparison concrete, the speaker ran an identical workload (500,000 vectors, 768 dimensions) across PostgreSQL, ClickHouse, and OpenSearch, all deployed on Aiven with the same instance specification (32 GB RAM, 8 CPU). Key results:

Memory footprint: PostgreSQL used around 6 GB (index held in shared buffers and cache); ClickHouse used around 1.2 GB (compressed, columnar storage); OpenSearch used around 3 GB of storage plus roughly 4 GB of JVM heap.
Query speed: ClickHouse returned results fastest, around 30 milliseconds; PostgreSQL and OpenSearch were both around 50–55 milliseconds.
Recall under filters: ClickHouse achieved around 95% recall, OpenSearch around 92%, and PostgreSQL around 78%.
Horizontal scalability: PostgreSQL scales vertically well but needs additional extensions (such as read replicas) to scale horizontally; ClickHouse and OpenSearch support native sharding and scale horizontally more easily. PostgreSQL performs well up to roughly 100 million vectors, while ClickHouse and OpenSearch can handle more.
Operational complexity: PostgreSQL and ClickHouse are relatively low-to-medium complexity; OpenSearch has medium-to-high complexity due to JVM heap tuning and its off-heap storage model (FAISS-based indexing).

She also notes that embedding dimensionality directly drives memory and latency: using 768 dimensions as a baseline, dropping to 384 dimensions reduces memory and latency, while scaling up to 1024 or 1536 dimensions increases both roughly linearly.

Scorecard and opinionated take

Summarizing the comparison: PostgreSQL wins on operational simplicity and ecosystem familiarity; OpenSearch wins on hybrid retrieval features and is purpose-built for combining vector and lexical search (though comparatively weak on pure lexical search internally versus ClickHouse); ClickHouse wins on filtered recall at scale due to its pre-filtering behavior and handles very large data volumes well. PostgreSQL requires more work to scale horizontally, while ClickHouse and OpenSearch scale more natively.

Decision framework and scale thresholds

Three questions determine the right choice: how many vectors are expected now and in 12 months; whether queries combine vectors with heavy metadata filtering; and how much operational overhead the team can realistically absorb.

This translates into a decision tree: keep vectors inside the existing transactional database (PostgreSQL + pgvector) if retrieval needs to live in the same transactional boundary as the application data; use ClickHouse if retrieval should sit alongside large-scale analytical queries on columnar storage; use OpenSearch if retrieval itself is the core product and hybrid search is needed; and use a dedicated vector database if there are more than 100 million (or billions of) vectors with strict recall and latency requirements. If none of these conditions clearly apply, the recommendation is to start with PostgreSQL and pgvector and migrate later if evidence shows it's needed.

Concrete scale-based guidance: below 1 million vectors, use pgvector without further deliberation; between 1–5 million vectors, pgvector with a carefully tuned HNSW index; above 5 million vectors, evaluate ClickHouse or OpenSearch; above 50 million vectors, ClickHouse is generally preferred (especially for analytical use cases) or a dedicated vector database; and at billions of vectors, use a dedicated vector database such as Qdrant, Milvus, or Weaviate.

Operational overhead and closing takeaways

Operational overhead differs substantially: minimal for pgvector, medium for ClickHouse, and high for OpenSearch (which requires JVM tuning and sharding strategy management). The overall message is to avoid adopting more operational complexity than the team can realistically maintain.

Key takeaways: start with pgvector, since it has near-zero overhead and works well up to around 5 million vectors; move to ClickHouse when metadata filtering becomes complex and scale grows large; move to OpenSearch when hybrid search is genuinely needed or already in use; treat metadata filtering as a first-class production concern, since pre-filtering generally outperforms post-filtering; plan memory capacity around the embedding model's vector dimensionality, not just the number of vectors; and start simple, document everything, and migrate to a different database only once there is clear evidence it's needed — not speculatively.

Case study: migrating a RAG pipeline from Azure back to on-premises PostgreSQL

Vadim then presented a real project from Steadforce, a Munich-based consultancy working across data & AI, platform engineering, cloud modernization, and security, which builds solutions collaboratively with customer teams and is also an Aiven partner.

The customer is a German information portal operator running 17 separate information portals containing articles, contact databases, and other structured records. Users ask natural-language questions that may span any of the portals, and the goal was a single chatbot entry point across all of them — a classic RAG use case. The team initially implemented the full pipeline on Azure, and the customer was satisfied with the result. A few months later, however, the customer asked to move the entire solution off Azure and run it fully on-premises.

Three factors drove this decision, all pointing the same direction:

Customization: Azure AI offered limited control over how retrieval, chunking, and orchestration worked internally, making it difficult or impossible to experiment with different chunking sizes, strategies, or retrieval volumes, since the search and orchestration components behaved as black boxes. A local solution allowed full flexibility and reduced vendor lock-in.
Predictable costs: the Azure-based solution required Azure Search, Azure Blob Storage, and additional orchestration services. Moving to infrastructure the client already owned reduced ongoing costs.
Operational familiarity: the customer's IT team already had expertise running Docker on Windows Server and wanted to keep operating in that environment rather than learning and maintaining a new system.

Applying the same three-question framework from the first half of the talk: the customer's data volume was comfortably under 1 million vectors (around 20,000–25,000 chunks expected), so a dedicated vector database like ClickHouse or OpenSearch would have been overkill; metadata filtering was intentionally kept simple at first, with a filtering layer planned for later; and the client's capacity to absorb operational overhead was effectively zero, since any additional system would mean more monitoring, backup, and on-call burden than the client's team could support. This pointed directly to PostgreSQL with pgvector, requiring no new system and no new vendor relationship.

Architecture of the deployed solution

The resulting RAG pipeline works as follows: a user submits a question through the chatbot frontend, which sends it to an orchestrator microservice running in Docker on the customer's Windows servers. This orchestrator sends a search request to a retrieval microservice, which extracts relevant information from the knowledge base (stored in PostgreSQL with pgvector) and returns the retrieved chunks. The orchestrator then combines these chunks with the user's original question and sends them to an Azure OpenAI model — currently the only remaining cloud dependency in the solution — before returning the generated answer to the user through the same path. A monitoring and model-tracing layer logs every prompt, response, and retrieval step, which is important both for debugging and for the customer's compliance requirements.

Compared to the original Azure-based setup, which required separate Azure Search and Azure Blob Storage services (two billing relationships and two systems to maintain), the new architecture stores all text and vectors in a single PostgreSQL instance. Only the user's query and retrieved chunks ever leave the customer's infrastructure (to reach Azure OpenAI); all underlying data remains on-premises.

Data pipeline and configuration choices

Getting data into the system involved several steps: extracting content from the 17 portal websites and client databases (each with different schemas and conventions, which made consolidation time-consuming); cleaning the data by removing line breaks, extra whitespace, and legacy Unicode artifacts; chunking the content (256 tokens per chunk with 25 tokens of overlap, matching the approach used in the earlier Azure setup, with semantic chunking planned as a future optimization); and embedding the chunks using an OpenAI embedding model, indexed in pgvector using HNSW. In total, the system ingests around 3,500 source documents — a modest data volume that confirms pgvector, rather than ClickHouse, is the appropriate fit here.

Three configuration decisions each involved a real trade-off:

Indexing algorithm: HNSW was chosen despite its slower build time, since content updates happen periodically with rebuilds run overnight, so build cost doesn't matter — what matters is fast query latency at the moment a user asks a question.
Embedding model: an OpenAI embedding model was chosen for two reasons — it handles multilingual input well (German users often mix in English terms) and it matches on meaning rather than exact keywords. The team also tested open-source embedding models, which performed well on benchmarks but did not extract the needed information reliably once used in production.
Metadata filtering: not yet fully implemented; it is the next planned step toward a more complete RAG pipeline.

Closing takeaways from the case study

Three points to remember: first, pgvector wasn't necessarily the objectively "best" vector solution by benchmark standards, but it was the right solution for this specific customer, given their scale, team, and existing infrastructure — a distinction the speaker considers important to keep in mind. Second, choosing boring, familiar infrastructure is what allows a team to actually ship a working solution, since every new system added is another thing that must be maintained around the clock; in this case, no new service had to be introduced because the client already had a PostgreSQL database in place. Third, adopting a dedicated vector database is a scaling decision, not a default starting point — teams should begin with what they already have if it fits the data, and migrate only when there is clear evidence it's necessary, instrumenting and logging thoroughly along the way to support that decision.