PGVector Isn’t Your Vector Databasing Cure-All - and That's Fine

Occasionally, I get asked is pgvector enough for Enterprise AI Solutions or should I migrate to a dedicated vector database. The honest answer is No Create EXTENSION vector; ALONE is not going to solve your solution for enterprise AI. That being said, I don't think you're asking the right question. Before you click off and start looking at dedicated vector databases, hear me out.

In most cases, you also don't need a dedicated vector database. For enterprise workflows I highly doubt that PostgreSQL is your only database. While it may be your source of truth, you likely have caching layers, and analytics tools, and probably a data lake or warehouse.

The biggest misconception is the belief that any one database should be doing all the work. None of them, including postgres, will scale to an enterprise workflow without paying a lot of money in hardware or engineers.

Making pgvector work requires… work

I'm not here to dunk on pgvector. I think pgvector is good enough for most workloads and is especially great for adding similarity search to a database you already run.

Develop and maintain a chunking strategy

But calculating distance on a single sentence against the vector of a full text document isn't going to be nearly as effective as you'd hope. You should chunk your data. This is a dedicated process of splitting your text into shorter segments. It's something that happens before the data is added to the database. Dedicated vector solutions talk about this as well and, to my knowledge, most of them do not provide a way to do this automatically, meaning that you will need to do this with some form of data pipeline. This is where problems start to arise. A 1-hour interview transcript could contain anywhere from 20-100 plus chunks based on your chunking strategy. This is your work to do, and also review, update, and maintain.

Choose a strong index type

As you continue to chunk and index your documents, your index will grow and grow. Alex Jacobs wrote a sharp piece that points out what teams actually hit in production, and independent benchmarks back up. Pgvector works well with little configuration, but only as a demo. As your indexes grow, your resource become strained.

You need to be thinking about your indexing strategy from day one to manage and combat the slowdown. HNSW (Higher Navigable Small World) has better recall than IVFFlat (Inverted File with Flat compression) but the latter is more accurate on smaller indexes. Neither is great on millions of indexes. A few million vectors can consume 10+ GB of RAM. When the RAM is consumed, things slow down significantly. pgvectorscale addresses this directly with the StreamingDiskANN (Approximate Nearest Neighbor) index which keeps the hot part of the graph in memory and streams the rest from disk. The result is you can search far more data without falling back to a sequential scan. Timescale's own benchmarks claim large latency and throughput wins versus Pinecone on tens of millions of vectors at high recall. Even if vendor numbers are taken with a grain of salt. You've created a system in your existing database that can compete, using two extensions instead of an entire additional database. Aiven for PostgreSQL includes both pgvector and pgvectorscale on all deployments.

The tax of adding a database

The moment a portion of your application data lives in a separate database, you've signed up for keeping two systems in sync.

When your vector store has documents that are regularly updated, you end up with "ghost documents" in vector databases that don't match up to your primary database. As documents change, embeddings will need to be regenerated and reindexed. Failure to do this causes the data to drift semantically away from the system of record. Chunked documents often need to point to a source of the entire document in a relational way, which is why a relational database like postgreSQL makes sense.

And then there's the bill. You just added another database. That means paying for uptime, storage, and data transfer. You also likely need to pay for engineers to manage the database, keeping data sync'd and to integrate a new database into your application workloads.

When you outgrow pg and pgvector alone

If your core business is serving RAG across billions of vectors, then yes you will eventually scale beyond what PostgreSQL can comfortably manage. I would challenge how common that is. There are approximately 7.2 million articles on wikipedia. If you did an average of 10 chunks per article, you'd still be nowhere near 1 billion vectors. If you're having performance issues, it's more than likely not pgvector itself that is the problem but the design of your architecture. Simply put, you should have already been working towards a healthier data environment well before you scaled to the size where pgvector is your bottleneck.

When building for scale, you want to solve problems with optimized networking or caching. Aiven for PostgreSQL supports read-replicas which can put data closer to its users and reduce latency with reduced worry of data drift because only one database is responsible for write operations. This also spreads out the connections (which should also be pooled since using pgbouncer) which reduces the amount of blocking issues that you run into with ingestion.

When you shouldn't reach for pgvector

As previously mentioned, the best tool for most of the jobs is usually what I recommend. Then you supplement with database solutions that handle the rest with as little data duplication possible.

If you're doing a lot of analytical processing, you likely would benefit from an Online Analytical Processing (OLAP) workflow like ClickHouse. ClickHouse supports vectorization while working with data in a columnar approach, making it fast and wonderful for aggregated information across larger datasets. Clickhouse also has a PostgreSQL engine which will allow you to perform those workflows with your PG data in place, reducing data duplication and drift opportunities.

If you want complex search techniques across large indexes of data or multi-faceted aggregation, then use OpenSearch. Opensearch has customizable hybrid search techniques with easy search weight management, supports index patterns, User Behaviour Insights (UBI) that can help you fine tune your search results, as well as custom dictionary support, query rewriting, result reranking and many other tools. In regards to vector search latency and cost optimization, there are vector quantization techniques, as well as doc value adjustments that can speed up vector search by 5x. Opensearch is great for log ingestion, analytics, and more. OpenSearch 3.6 also provides agent skills and workflows as well as long term support (LTS).

Then there's having a strong caching strategy. If you don't have a cache, you're missing out on an inexpensive solution to a lot of performance problems. Valkey, Aiven's chosen open-source caching solution, also supports vector search so you can perform semantic caching and store popular common queries to reduce the number of questions that reach pgvector.

All of these issues differ from common CRUD operations and workflows that benefit from an ACID compliant transactional database, like PostgreSQL. Your enterprise is likely already running some of these services or similar solutions, so there is no reason to add another database to the already multi-database stack.

With data spread across multiple services you can provide contextual awareness and manage governance and access using Aiven's DataHub.

Make PostgreSQL and PGVector work well for the long run

My honest opinion is that pgvector works well when you do a few things.

Limit the frequency of ingestion you're doing. Ingestion operations can block your index from use, slowing down search. You can and probably should set up concurrent indexing which removes the block, but has its own issues like search inconsistency and vacuum repair failures. All of these issues arise when you're doing a lot of singular writes to your index in a short time-frame. So instead of writing and processing your vectors as soon as they come in, consider batching and ingesting your data within reasonable windows. You can temporarily store your vectors in a cache like Valkey. Valkey supports vector search and you can use it as a supplemental short term store until scheduled batch jobs can save those vectors to long term storage.

Data pruning is just as important. Why store billions of vectors when you only need the last million? If your vector database is cluttered with old records that are never being searched for, it can make your results less reliable and take longer. In postgres, you can keep your database thin with extensions like pg_partman, and you can archive information real-time using change-data-capture (CDC) and streaming those changes to your data warehouse or data lake. The data-drift problem can arise when pruning so my recommendation is that you should be reindexing anytime you've removed partitions from postgres. Again this is usually something that you schedule for and do regularly to prevent large downtimes or outages.

Conclusion

So pgvector alone is not enough for Enterprise AI but for the same reason that PostgreSQL alone is often not enough for Enterprise solutions

For most RAG workloads today where they sit on top of other database operations, pgvector is enough, and it keeps your architecture blessedly boring, reliable, and manageable. If you're operating at billions of vectors with demanding complex search strategies, it does make sense to look at a dedicated engine like OpenSearch, but your architecture will likely have scaled to needing other databases before then, and many of those have vector search capabilities.