Vector Databases Explained: pgvector vs Pinecone vs Others

Every AI product that retrieves information - which is most of them - needs somewhere to store and search vector embeddings. The market is full of options, from "just use Postgres" to dedicated vector databases, and the choice has real consequences for cost, complexity, and your access-control model.

This article explains what vector databases do, why you need one, and how to choose - including the case for not running a separate one at all.

What a vector database does

To make AI answer from your data, you convert your content into embeddings - numerical representations of meaning - and store them. When a question comes in, you embed it the same way and find the stored content whose embedding is closest. That's semantic search: matching on meaning, not keywords.

A vector database is built to do that "find the closest embeddings" operation fast, at scale. The core job is approximate nearest-neighbour search over high-dimensional vectors.

Why "approximate"?

Finding the exact closest vectors among millions is slow. Vector databases use approximate nearest-neighbour (ANN) algorithms that trade a tiny bit of accuracy for enormous speed - returning the almost-certainly-closest results in milliseconds instead of seconds. For retrieval, this trade-off is invisible in quality and essential for performance.

The different vector databases largely differ in which ANN algorithm they use, how they scale it, and what else they bundle around it.

The main options

pgvector (Postgres extension)

pgvector adds vector storage and search to Postgres. If you're already on Postgres - which, on our stack, you are via Supabase - this means your vectors live in the same database as your relational data.

Strengths: one database, one access-control model (your row-level security applies to vector queries too), no extra service to run or pay for, transactional consistency between your data and your embeddings.

When it's enough: for the large majority of AI products - up to millions of vectors with reasonable latency - pgvector is plenty. It has improved a lot and handles real production workloads.

Limits: at very large scale (tens of millions+ of vectors with demanding latency requirements), a dedicated vector database may outperform it.

Pinecone (managed vector database)

A fully-managed, dedicated vector database. You don't run infrastructure; you call an API.

Strengths: scales to very large vector counts, low latency at scale, no ops burden, purpose-built features.

Trade-offs: it's a separate service - separate cost, separate system, and crucially a separate access-control model. Your vectors live apart from your relational data, so you have to enforce "this user can only retrieve their data" in your application layer rather than relying on the database. That's a real source of bugs in multi-tenant products.

When it fits: large scale, or when you specifically want a managed dedicated service and are willing to manage the two-system access-control carefully.

Other options

There are several other vector databases and vector-capable stores (Weaviate, Qdrant, Milvus, and vector features in other databases). They occupy similar territory - dedicated, scalable, with various hosting and feature trade-offs. The decision logic is the same: do you need a dedicated system, or does your existing database's vector support suffice?

The decision framework

Your situation	Reach for
Already on Postgres, up to millions of vectors	pgvector (same DB, same access control)
Multi-tenant product where access control is critical	pgvector (RLS covers vectors) unless you've outgrown it
Tens of millions+ vectors, demanding latency	Dedicated vector DB (Pinecone et al.)
You want zero database ops and don't mind two systems	Managed dedicated vector DB
Small / static knowledge base	You might not need a vector DB at all (see below)

When you don't need a vector database at all

For small or static knowledge bases, you may not need vector search:

• If the whole knowledge fits in the prompt, just put it there. No retrieval, no vectors. (Watch the token cost of long prompts, but for small knowledge it's fine.)
• If keyword search is good enough for your use case, a traditional full-text search may suffice without embeddings.

Don't add a vector database because it's the AI-product thing to do. Add it when you have enough content that you need semantic retrieval over it.

Our default

For most AI products we build, pgvector on Supabase is the default. The reasons:

• The vectors live with the relational data - one database, one mental model
• Access control is unified - the same RLS that protects records protects retrieval, so the AI can never surface data a user shouldn't see
• No extra service, cost, or ops
• It scales well past the point where most products would worry

We move to a dedicated vector database when scale genuinely demands it - which is later than most teams expect. Starting simple and migrating if needed beats starting complex for scale you may never reach.

What to do next

If you're building an AI product and want help choosing the right retrieval architecture, book a 30-minute discovery call.

Read next: Building an AI chatbot that knows your data and Building AI products on Supabase.