Postgres + pgvector vs a Dedicated Vector Database for Product Search

The choice, stated plainly

You can keep product embeddings in the same Postgres database that already holds your catalogue, using the pgvector extension — or you can run a dedicated vector database (Pinecone, Weaviate, Qdrant) alongside it. For a product-search workload at catalogue scale, pgvector vs a dedicated vector database usually comes out in favour of Postgres, and it's worth knowing exactly why and where that flips.

The case for pgvector at catalogue scale

• One system. One thing to run, patch, scale, back up, and monitor. One failure domain instead of two.
• Consistency with live data. Embeddings and the catalogue's price and stock live in the same database, so a search can filter on current availability in the same query, and re-embedding can be reasoned about as one system rather than two that silently drift. On our semantic search project this is what makes "results always reflect current price and stock" an enforceable property rather than a hope.
• Performance holds at this scale. Published HNSW benchmarks have tested pgvector from a few hundred thousand up to a million 1536-dimension vectors, with HNSW indexing delivering roughly three times the throughput of the older IVFFlat index at equal or better recall. A catalogue of a few hundred thousand SKUs sits comfortably inside that tested range.

The case for a dedicated vector database

• Built for scale beyond a single instance. Multi-million to billions of vectors, horizontal sharding, and metadata filtering that stays fast at that size.
• Decoupled scaling. Search load doesn't compete with transactional load on the same instance.
• Built-in features. Some ship hybrid search, multiple index types, and namespacing out of the box.

The honest crossover point, from the same research: a dedicated engine starts winning on raw throughput well past roughly ten million vectors, or when query volume outgrows what a single Postgres instance serves comfortably. Most retail catalogues — even large ones — sit one to two orders of magnitude below that.

What "consistency" buys you in practice

With embeddings and catalogue rows in one database, a search is a single query: retrieve the nearest vectors, filter to stock > 0 and the shopper's active facets, and order the result — one round trip, one transactional view of the data. Split across two systems, the vector store returns product IDs, then you fetch price and stock from Postgres, and there's a window where the two disagree — long enough for the search to rank an item that sold out a minute ago. You can engineer around that with careful cache invalidation, but it's work you're doing to paper over the split.

The operational cost of two systems

A dedicated vector database is a second thing with its own backup and restore story, its own scaling limits, its own upgrade cadence, its own on-call runbook, and its own client library to keep current. For a catalogue that fits comfortably in one Postgres instance, that's real ongoing overhead bought to solve a scaling problem you don't have yet.

Starting on pgvector doesn't lock you in

Embeddings are portable. If you cross the threshold, you move the index to a dedicated store; you don't rebuild the whole application. Treating "catalogue approaching the multi-million-vector range" and "query volume a single instance can't serve" as explicit triggers to revisit is enough — you don't have to pre-build for a scale you haven't hit.

Where this stops being right

• Multi-million vectors, or query volume past one instance. That's the dedicated-database conversation.
• You need ANN features Postgres doesn't offer — exotic index types, built-in cross-region replication of the vector index.
• You already run a vector database for other workloads — reuse it rather than adding pgvector.
• You need CDC-style replayable event streams to several independent consumers — that's a different architectural discussion than where the vectors live.

FAQ

Is pgvector "real" vector search? Yes — it uses HNSW indexing and the same approximate-nearest-neighbour algorithms as dedicated engines. The difference at catalogue scale is operational, not a capability gap.

When do we actually need to switch? When vector count approaches the multi-million range, or when query volume exceeds what one Postgres instance can serve. Track both as named triggers rather than guessing.

Does keeping vectors in Postgres slow our transactional queries? At catalogue scale, with a proper HNSW index and connection pooling, not meaningfully. Monitor it as the corpus grows — it's one of the signals that says it's time to revisit.

ISTRALLEN builds product search on the database you already run, and moves the index out only when scale genuinely demands it; see AI for Retail.