pgvector vs Pinecone for Enterprise RAG: When Postgres Is Enough

When a team starts building retrieval-augmented generation (RAG), one of the first questions is where the embeddings should live. Vendor marketing makes it sound like you need a dedicated vector database from day one. In our experience, most enterprise teams don’t. If you already run PostgreSQL, pgvector is the default choice, and it’s usually enough for years.

That’s not because pgvector is the fastest vector index in every benchmark. It’s because RAG is mostly a data problem: keeping chunks, metadata, permissions and embeddings in sync, and filtering by who is allowed to see what. Postgres already does that well.

This guide covers what pgvector does, how to tune HNSW, what AlloyDB and Aurora add, where pgvectorscale and VectorChord fit, and when Pinecone or OpenSearch is worth the extra system.

Is pgvector the best vector database for RAG on Postgres?

Yes, for most teams it’s the standard starting point. pgvector is an open-source PostgreSQL extension for vector similarity search. Every major managed Postgres service supports it, including Amazon RDS and Aurora, Google Cloud SQL and AlloyDB, and Azure Database for PostgreSQL. Other Postgres vector extensions, such as pgvectorscale and VectorChord, build on its data types rather than replacing them.

What you get:

• Vector types. vector (indexable up to 2,000 dimensions), halfvec (half precision, up to 4,000), bit (binary, up to 64,000) and sparsevec.
• Two index types. HNSW and IVFFlat, with L2, inner product, cosine and other distance functions.
• SQL filtering and joins. Your access rules, tenant IDs, document dates and source systems are ordinary columns. A RAG query is one SELECT with a WHERE clause and an ORDER BY embedding <=> $1 LIMIT 10.
• Transactions. When a document is updated or deleted, its chunks and embeddings change in the same transaction. No sync job between two systems, and no stale vectors pointing at deleted content.
• Your existing operations. Backups, replicas, point-in-time recovery, encryption, auditing and IAM already work.

That last point matters more than any benchmark. Every extra datastore is another thing to secure, monitor, back up and pay for.

pgvector vs Pinecone: what’s the real difference?

Pinecone is a fully managed, purpose-built vector database. pgvector is a feature of the database you already run. The trade-off is less about raw speed and more about where the complexity lives.

	pgvector on Postgres	Pinecone (dedicated vector database)
Where data lives	Same database as your app data and metadata	Separate service; you sync data into it
Filtering and permissions	Full SQL, joins, row-level security	Metadata filters on stored fields
Consistency	Transactional with your source data	Eventually in sync via your pipeline
Scaling	Scale up the instance, add read replicas, partition	Managed by the vendor
Operations	Your existing Postgres skills and tooling	No database to run, but a new vendor to secure and govern
Cost model	Part of your database bill	Separate usage-based bill

For a typical internal RAG system, such as a policy assistant, a support knowledge base or an engineering document search, the corpus is in the thousands to low millions of chunks. That’s well within what a properly sized Postgres instance with an HNSW index handles.

Building RAG on data you already have in Postgres? Axio Intelligence designs and builds data and AI platforms, from chunking and embeddings to retrieval, evaluation and access control. We’ll tell you honestly whether you need another database. Book a scoping call.

How do you tune pgvector HNSW for RAG?

HNSW is the index to use for most RAG workloads. The pgvector docs describe it as having a better speed-recall trade-off than IVFFlat, at the cost of slower builds and more memory. It also doesn’t need training data, so you can create it on an empty table.

The settings that matter:

• m and ef_construction control graph quality at build time. The defaults (16 and 64) are a sensible start; raise them only if your recall tests say so.
• hnsw.ef_search controls how much of the graph each query explores. The default is 40. Raise it per query or per transaction (SET LOCAL hnsw.ef_search = 100) to trade speed for recall.
• maintenance_work_mem decides build speed. pgvector builds much faster when the graph fits in it, and it warns you when it doesn’t. Don’t set it so high that the server runs out of memory.
• Iterative index scans. Approximate indexes filter after scanning, so a selective WHERE clause can return fewer rows than you asked for. The docs give the example that a condition matching 10% of rows returns only about 4 rows on average at the default ef_search of 40. Since pgvector 0.8.0, SET hnsw.iterative_scan = strict_order (or relaxed_order) keeps scanning until enough rows match. Turn it on for any permission-filtered RAG query.
• halfvec. Half-precision values take 2 bytes instead of 4, so storing or indexing embeddings as halfvec roughly halves their size. Check the recall impact against your own queries.

Measure recall against exact search on a sample of real questions before and after each change. RAG answer quality depends on retrieval quality, and the only honest way to know is to test it.

Keep pgvector patched

On October 5, 2026, the pgvector project released 0.8.7, which fixes a buffer overflow in IVFFlat index builds (CVE-2026-103484) that can lead to arbitrary code execution. Users are encouraged to upgrade. On managed services the pgvector version is tied to the engine’s minor version, so check what your provider ships (SELECT extversion FROM pg_extension WHERE extname = 'vector';) and plan the minor upgrade.

What does AlloyDB vector search add?

AlloyDB is Google Cloud’s PostgreSQL-compatible database, and it runs pgvector. In a July 21, 2026 post, Google reported that caching pgvector HNSW indexes in AlloyDB’s columnar engine gives up to 4x higher queries per second than standard PostgreSQL HNSW, with no application changes. On the GloVe-100 benchmark (over 1 million vectors, measured on a 16 vCPU C4A machine), Google saw about 4.2x to 4.9x more QPS at 0.95 recall. Google notes that its baseline already had the index fully in PostgreSQL’s shared buffers, so the gain comes from the memory layout, not from avoiding disk.

Per Google’s documentation, columnar engine acceleration for HNSW is generally available and requires PostgreSQL 17 or later. AlloyDB also offers Google’s ScaNN index, which Google positions for very large datasets that exceed memory.

If you’re already on Google Cloud and vector query throughput is your bottleneck, AlloyDB is a way to get more out of pgvector without leaving Postgres.

Aurora pgvector vs OpenSearch: which should you use on AWS?

On AWS, the usual choice is between pgvector on Aurora or RDS PostgreSQL and the vector engine in Amazon OpenSearch Service.

Choose Aurora or RDS with pgvector when your source data and metadata already live in Postgres, you need SQL joins and row-level permissions in retrieval, and the corpus fits a single well-sized cluster. AWS documents running pgvector in production on Aurora, and Aurora’s recent 17 and 18 releases ship pgvector 0.8.x.

Choose OpenSearch when you already run it for search or logs, keyword relevance tuning is central to your product, or you need to scale search horizontally across many nodes separately from your transactional database.

Hybrid search (keywords plus vectors) doesn’t force the decision. Postgres has built-in full-text search, and the pgvector docs show combining it with vector search, for example with reciprocal rank fusion.

Where do pgvectorscale and VectorChord fit?

They’re for the point where plain pgvector starts to strain, usually because the HNSW index no longer fits comfortably in memory. Both keep you inside Postgres and work with pgvector’s data types.

• pgvectorscale adds a StreamingDiskANN index (inspired by Microsoft’s DiskANN research), Statistical Binary Quantization, and label-based filtered search. It’s written in Rust.
• VectorChord uses RaBitQ quantization with reranking, focuses on large indexes and fast builds, and is compatible with pgvector types and syntax.
• On Azure, Azure Database for PostgreSQL offers a pg_diskann extension that builds on pgvector.

Two caveats. First, availability: these extensions aren’t on every managed service, so self-hosting or a specific provider may be required. Second, the projects publish their own benchmarks, and vendor benchmarks are run on the vendor’s chosen workload. Test with your data and your queries.

When is a dedicated vector database worth it?

A dedicated vector database such as Pinecone earns its place when vector search is its own large workload, not a feature of an application database. Signs you’re there:

• Very large corpora. Hundreds of millions to billions of vectors, where a single Postgres instance becomes expensive or awkward even with quantization.
• Search traffic that would hurt your OLTP database. High query volume that you’d rather isolate from transactions.
• Many tenants or indexes with very different sizes and traffic.
• No Postgres team. If nobody on staff runs Postgres and you want a fully managed service with no tuning, a managed vector database can be simpler.
• Your data doesn’t live in Postgres anyway. If the source is a data lake or document store, the “same database” advantage disappears.

If none of those apply, start with pgvector. Moving embeddings later is straightforward: they’re just arrays of numbers you can regenerate or export. Moving permissions logic and sync pipelines out of a second system is the hard part, which is another reason to avoid one until you need it.

What actually decides whether RAG works?

Rarely the vector database. In the RAG projects we see stall, the causes are usually:

• Chunking that splits tables, procedures or contracts in the wrong place.
• No evaluation set, so nobody can tell if a change made answers better or worse.
• Permissions bolted on late, so the assistant can retrieve documents a user shouldn’t see.
• Stale content, because nothing re-embeds documents when they change.

These are data and engineering problems, not database choices. If you’re deciding where the model itself should run, see our guide on on-prem LLMs vs frontier APIs. And if your documents still live in Oracle, moving to PostgreSQL can put your data and your vectors in one place; our database migration services cover that work.

FAQ

Is Postgres a good vector database?

Yes, for most RAG and semantic search workloads. With pgvector, PostgreSQL stores embeddings next to your data, indexes them with HNSW or IVFFlat, and filters results with SQL. It runs on all the major managed Postgres services.

Is pgvector better than Pinecone?

It depends on the workload. pgvector is usually the better fit when your data is already in Postgres and you need SQL filtering, joins and transactions. Pinecone is a better fit for very large, high-traffic vector workloads run as a separate service, or for teams that don’t want to run a database.

Should I use HNSW or IVFFlat in pgvector?

Use HNSW for most RAG workloads. It gives better query performance for the same recall. IVFFlat builds faster and uses less memory, but needs data in the table before you build it. If you use IVFFlat, upgrade to pgvector 0.8.7 for the CVE-2026-103484 fix.

How many vectors can pgvector handle?

There’s no single limit; it depends on dimensions, memory and recall targets. Millions of vectors on one well-sized instance are common. Beyond that, halfvec, binary quantization, partitioning, pgvectorscale or VectorChord extend the range before a separate database is needed.

Does AlloyDB support pgvector?

Yes. AlloyDB runs pgvector, and its columnar engine can cache HNSW indexes for up to 4x higher query throughput than standard PostgreSQL, according to Google. HNSW acceleration is generally available on PostgreSQL 17 and later.

Talk it through

If you’re choosing a vector store for a RAG project, or your pgvector setup has started to slow down, we’re happy to look at it with you. Book a call and bring your corpus size, query volume and where your data lives today.

PostgreSQL elephant logo © The PostgreSQL Global Development Group. Pinecone logo is a trademark of Pinecone Systems, Inc. Logos are used for identification only.