Two years ago, adding semantic search to our internal document analysis tool involved a bloated Python script, a local HNSW index on a disk-heavy EC2 instance, and a prayer that the index wouldn't explode when we hit 500k embeddings. Updating the index meant a full offline rebuild, which resulted in 20 minutes of "Search currently unavailable" errors every Tuesday.
Today, that same stack uses a managed vector service. I push vectors via API, the index updates in near real-time, and my PagerDuty alerts for "Index Memory Pressure" have vanished. The transition wasn't just about moving to the cloud; it was about accepting that the vector index is a specialized piece of infrastructure, not just another table in your database.
Why I chose this topic: I’ve spent the last six years fighting with database performance in highly regulated environments where downtime isn't an option. I’m writing this because I keep seeing teams try to shoehorn vector indexing into their monolithic Postgres instances without understanding the operational tax of doing so at scale.
The contenders
We are comparing three distinct philosophies here:
- pgvector (v0.7.0+): The "it's just Postgres" approach. It uses the
ivfflatorhnswaccess methods. It’s perfect if your data already lives in RDS/Aurora and your embedding counts are in the low millions. - Pinecone: The "serverless-first" managed service. It abstracts away the index type, the shard management, and the hardware provisioning. You pay for throughput and storage, not instances.
- Databricks Mosaic AI Vector Search: The enterprise-grade engine. This is for the team that already has their data in Unity Catalog. It’s the "I don't want to think about data movement" choice.
Photo by Luke Jones on Unsplash
The operational tax
When you run pgvector, you are running Postgres. That means you are managing shared_buffers, work_mem, and the vacuuming cycle. If you hit a peak in query volume, your vector operations are competing for CPU cycles with your standard transactional UPDATE and SELECT statements.
In version 0.7.0, pgvector added support for hnsw indexes, which is a massive performance win. However, if you trigger a reindex on a table with 5 million vectors, your CPU will spike to 100% and your query latency will tank. I’ve seen production databases lock up because of an ill-timed CREATE INDEX CONCURRENTLY in an environment where the IOPS limit on an AWS gp3 volume was already near saturation.
Pinecone and Mosaic AI treat the index as a separate service. You aren't managing the memory pressure of the index; the vendor is. When I use Pinecone, I don't care about memory fragmentation or disk latency. I care about the p1 latency of my API calls.
The cost of complexity
Let’s talk money. pgvector on AWS RDS is cheap until it isn't. You’ll eventually hit the ceiling of a single instance, and then you’re looking at read replicas or partitioning strategies. Managing Postgres partitioning for vectors is a specialized skill that most teams don't have. If your team’s DBA time costs $200/hr, that "cheap" Postgres extension becomes the most expensive line item in your cloud bill.
Pinecone is billed by the "pod" or via serverless throughput. It looks expensive on a spreadsheet—until you calculate the labor cost of a single engineer spending three days debugging a hung Postgres process during a peak traffic event.
Mosaic AI is only "cheap" if you are already in the Databricks ecosystem. If your data is in Delta Lake, using anything else is madness. Exporting, embedding, and syncing to Pinecone adds latency and introduces a point of failure (the ETL pipeline). Mosaic AI stays within the perimeter.
Photo by Markus Stickling on Unsplash
Failure modes and recovery
The nightmare scenario for pgvector is a corrupted index or a vacuum loop that kills your database performance. Recovery means taking a snapshot of the entire multi-terabyte DB and restoring it to a new instance.
With Pinecone, the failure mode is usually an API timeout or rate limiting. These are handled with standard retry logic and exponential backoff. It’s a distributed systems problem, not a storage engine problem. If the index fails, I don't care about the underlying state; I just re-sync from my source of truth.
Mosaic AI’s failure mode is almost always related to the sync trigger between Delta Lake and the vector store. If your Delta table updates, but the vector index doesn't, you have a consistency issue. The fix is usually a TriggerNow call in the Mosaic console. It’s a cleaner failure mode than a database index crash.
What I'd pick, and why
If you have fewer than 1 million vectors and your data is already in Postgres, use pgvector. Period. The overhead of adding another vendor to your stack isn't worth it. Just make sure you tune your hnsw.ef_search correctly—start at 40 and move up only if recall drops below your SLA—and keep your vector column dimension count reasonable (e.g., 768 or 1536).
If you are scaling past 5 million vectors or you need high-concurrency search across multiple microservices, stop using Postgres for this. Move to Pinecone. The serverless performance is predictable, and the lack of operational overhead is a force multiplier for a small engineering team.
If you are a large enterprise already using Databricks for your ETL and AI lifecycle, use Mosaic AI. The integration with Unity Catalog is the "killer feature." It turns vector search from a separate infrastructure project into a simple data governance task.
Don’t be a hero. Unless you are at the scale where you need to manage your own hardware for latency reasons (sub-5ms), you shouldn't be managing your own vector indices. Spend that time on your model fine-tuning instead.
Tags: #postgres #databases #ai #infrastructure
Cover photo by Albert Stoynov on Unsplash.
Top comments (1)