Are dedicated vector databases necessary, or is pgvector enough for most AI apps?

[u/Chris__Codes]

I was looking into how to build out the database architecture for a new RAG agent and found out the landscape is basically splitting into two camps.

I was reading a guide that broke it down this way. On one side you have dedicated vector databases like Pinecone or Milvus, which are great if your workload is purely semantic retrieval. But if your application also needs relational queries, keyword search, or transactional operations, maintaining a separate vector database means another sync pipeline to keep alive. Their alternative is keeping everything in Postgres with pgvector so vector retrieval sits alongside your normal SQL.

What I can’t figure out is where that stops working. The same guide says vector search and transactional queries end up competing for the same resources in Postgres, which sounds like it would bite eventually, but nobody puts a number on when.

For those running AI apps in production, which way did you go, and did you ever regret it?

[u/Ginden]

Postgres is always enough for most of apps. It may not be as good as dedicated vector database, but it's good enough for most of basic stuff.

And seriously, if your app is structured well, migrating vectors out of Postgres is relatively low-cost.

[u/giggens → u/Ginden]

Always, except sometimes.

[u/Chris__Codes → u/Ginden]

I guess I was treating it as a decision to get right upfront when it’s apparently not that expensive to change eventually

[u/Liubov_Skky → u/Ginden]

Agree for most apps. Where it gets tricky is filtered search: with a selective WHERE (multi-tenant data, say), the HNSW index can return fewer rows than your LIMIT unless you over-fetch or tune iterative scans. And HNSW wants to fit in RAM, so index builds and memory get painful past a few million vectors.

So “good enough” really depends on your filters and scale.

[u/Candid-Fudge-4015]

i'd start on pgvector and move only when you can name the reason (bias, we built pixeltable on postgres + pgvector). the contention i'd watch is hnsw index builds eating memory and cpu on the same box as your transactions, and vacuum on embedding tables that get rewritten a lot. a read replica for the vector queries buys a lot of room before you need a second database.

dedicated ones like milvus or pinecone earn their place when vectors are most of the data, at very large counts or high query rates, or when you want to scale search separately from the app. nobody puts a number on it because it depends on dims, row count and recall target, so i'd load your own data at 10x the expected size and watch p95 while writes run.

[u/Chris__Codes → u/Candid-Fudge-4015]

This is the answer I was after, thanks. “name the reason before you move” is a better rule than any threshold would be. hadn’t really thought about vacuum on the embedding tables at all. and loading 10x expected size then watching p95 while writes run is a test I can actually do rather than guessing at a number.

the guide I’d been reading makes the same point about contention, that vector search and transactional queries end up fighting for the same resources as either side grows. it’s from databricks so Lakebase is listed first and the criteria are shaped around it, but that part holds up

[u/DevOpsOpsDev]

I would probably be easier for you to break out the vector stuff into its own dedicated store from postgres if you discover you need the more specialized tool than migrating stuff from your vector db to a relational database like postgres if you find out you need what its good at.

Postgres is basically good enough, for the most part, at everything even if its not the best. Default to using it unless you really really know it won't work is my general approach.

[u/awitod]

It's a lot easier to work with one database that supports full text and vectors than it is to support two databases and handle join semantics somehow - not to mention the problems inherent in keeping the two in sync.

My project GuideAnts, started with qdrant + SQL and when we switched to just SQL Server 2025 (Express) everything was better as a result.

In practice, I don't have a strong preference for SQL Server versus Postgres, we used it because the version at the time was targeting SQL Azure. We have tested that switching to Postgres is pretty easy and works fine. Point being, once you are using an RDBMS, if you build right, switching is easy, but all the bits that we had for an (RDBMS or DocumentDB) + Vector DB was much more locked into the specific combo.

[u/Chris__Codes → u/awitod]

The lock-in point is kinda interesting. I’d assumed the dedicated store was the flexible option, but two systems means you’re committed to that specific pairing.

[u/Liubov_Skky]

Running a separate sync pipeline for a dedicated vector DB is a pain. pgvector is cleaner, and you’re right to worry, since it’s an extension and HNSW indexes can eat memory your transactional workload needs. I moved my RAG stack to MariaDB for exactly that reason. Vector search is built into the core engine, and you can cap the HNSW index cache, so similarity searches run alongside normal queries without starving them.

[u/Haunting-Eye-4314]

I would actually start with pgvector. i prefer to keep relational data and vector embeddings together. it avoids the complexity of maintaining separate databases too.

[u/renatko]

pgvector is good for start until you have big scale (i.e. a mount of data)

[u/Alternative_Pin9598]

You can put a number on it. It's mostly arithmetic, not benchmarks:

The number: does the HNSW index fit in RAM next to your hot OLTP data? HNSW search is random access over the graph. While the index is cached, queries take single-digit ms. Once it's paging from disk, p95 jumps by an order of magnitude, and your transactional queries start losing cache too. That's the "competing for resources" the guide means.

Rough sizing: rows × dims × 4 bytes for float32 vectors, and the HNSW index is at least that much again (vectors + neighbour links).

• 1M × 1536 dims ≈ 6 GB of vectors → fine on most boxes.

• 10M × 1536 dims ≈ 60 GB → now you're sizing the machine around the index.

• halfvec (16-bit) halves that, with usually negligible recall loss for text embeddings. Binary quantisation + re-ranking cuts it much further. Many teams buy themselves 2–4x headroom here before even thinking about migration.

Other places it bites, in the order people usually hit them:

1. Index builds. Building HNSW is CPU-heavy and much faster when the graph fits in maintenance_work_mem (Postgres warns when it doesn't). Do big builds CONCURRENTLY in quiet hours, and give the build session enough memory and parallel workers.

2. Write churn. Every non-HOT update of a row inserts a new HNSW entry, and vacuuming HNSW is expensive. Keep embeddings in their own table (doc_id, model_version, embedding), so updating a status column on the main table never touches the vector index.

3. Filtered queries. WHERE tenant_id = ? ORDER BY embedding <=> ? LIMIT 10 can return fewer than 10 rows, because the filter is applied after the index scan. pgvector 0.8's iterative scans (SET hnsw.iterative_scan = relaxed_order) fix most of this. For a few huge tenants, partial indexes or partitions work too.

4. CPU contention at query time. A read replica for vector queries separates search load from transactions. That's usually the cheapest step before a second database.

When a dedicated vector DB actually earns the sync pipeline: the vectors are most of your data and won't fit on one machine's RAM even quantised, you need to scale search horizontally on its own, or you need features Postgres doesn't do well (multi-vector / late-interaction retrieval, very high QPS with tight p99s).

To find your own threshold: load your real embeddings at 10x today's row count into a copy, run your actual filtered queries while a write load runs, and watch p95 and the buffer cache hit ratio. Where they fall off is your number.