Can I switch from pgvector to Pinecone later without a full rebuild?

Yes, if you kept the source documents and the chunking logic. Vectors are derived data — you can always regenerate them from the original text with the same embedding model, so a migration is a re-index job, not a data-loss event. What makes migrations painful is coupling: query code that speaks raw SQL to your embeddings table, filters expressed as SQL WHERE clauses that have no direct Pinecone equivalent, and joins between vectors and relational rows that a dedicated vector store cannot do at all. Put a thin retrieval interface in front of the store on day one — something like `search(query, filters, k)` — and the swap becomes one adapter rather than a rewrite. Budget for the re-embedding cost and for a period of dual-writing while you compare result quality between the two stores.

Does pgvector support hybrid search?

Postgres does, which is the real answer. pgvector gives you the vector similarity half; Postgres's own full-text search (tsvector, GIN indexes, ts_rank) gives you the keyword half, and you combine the two rankings yourself — usually with reciprocal rank fusion or a weighted score — in a single query against a single database. That's genuinely a strong position: hybrid search is often the difference between a demo and a system that finds the document containing an exact part number. Managed vector stores tend to offer their own hybrid or sparse-vector features, but you should verify the current capabilities and any limits directly against the vendor's documentation rather than trusting a comparison post's snapshot.

What about other options like Weaviate or Qdrant?

They're real contenders and the framework in this guide still applies — the axis that matters is who runs the thing and what happens at your vector count, not the brand. Qdrant and Weaviate both offer self-hosted open-source builds and managed cloud tiers, so they can sit anywhere on the spectrum between 'pgvector on your existing Postgres' and 'fully managed, zero-ops.' The practical reason I still default to pgvector first is that it adds no new system to an application that already has Postgres. The moment you're standing up a separate service anyway, the question stops being pgvector-or-not and becomes which dedicated store you want to operate or pay for — and at that point evaluate them on filtering behaviour, hybrid search, and cost at your projected scale.

pgvector vs. Pinecone: how to actually pick a vector database

The choice isn't which vector store is better — it's which one fits your scale and your ops model. A practical framework: cost at scale, operational overhead, performance by vector count, and the migration path if you outgrow your first pick.

Every few months someone asks me to settle this: pgvector or Pinecone. They want a winner. There isn't one, and the framing is the problem — it's the same category error as asking whether SQLite or Postgres is better.

The useful question is narrower. Given the infrastructure you already run, the number of vectors you'll actually have in eighteen months, and how much operational work your team can absorb — which of these fits? Answer that and the decision usually makes itself in about ten minutes.

Here's the framework I use.

The two things that actually decide it

Strip away the benchmarks and the marketing and there are two axes.

Who operates the index. With pgvector, you do. It's an extension on a Postgres database you're already running, which means index choice, index build times, tuning, memory headroom, and vacuum behaviour are yours. With Pinecone, nobody on your team operates anything — you call an API and capacity is somebody else's problem.

What your vector count will be. Not today's count — the count after you've indexed the second and third document corpus that product will inevitably ask for. Ten thousand vectors and ten million vectors are different engineering problems, and picking for the wrong one is the most common mistake I see.

Everything else — hybrid search, filtering, latency, migration risk — is downstream of those two.

The case for starting on pgvector

If your application already has a Postgres database, pgvector is the option with the lowest total cost, and cost here mostly isn't money.

You add an extension. Your embeddings live in a table next to the rows they describe. That last part is worth more than it sounds. A retrieval query that has to respect tenancy, permissions, publication status, and a date range is one SQL statement with a WHERE clause and an ORDER BY on vector distance. The filter is exact, it's applied by the same engine that owns the truth, and there's no window where your vector store and your relational store disagree about what exists.

That consistency property is the strongest argument for pgvector and it rarely appears in comparison tables. With a separate vector service you now have two systems that can drift: a document gets deleted in Postgres, the delete in the vector store fails, and your RAG pipeline confidently cites a document the user is no longer allowed to see. Preventing that means writing reconciliation jobs. Or you keep one database and the problem doesn't exist.

You also get the whole Postgres toolbox for free: transactions, backups you already run, a replica you already have, EXPLAIN ANALYZE for the query that got slow, and full-text search you can fuse with vector similarity for hybrid retrieval without adding a third system.

The costs are real, though. You own indexing. pgvector offers more than one index type — broadly, a graph-based index that gives better recall and query latency at the price of slow, memory-hungry builds, and a partition-based one that builds fast but wants representative data present before you build it and degrades if your data distribution shifts. Choosing between them, sizing memory so the index isn't thrashing, and rebuilding after a bulk load are your job. Index builds on millions of rows can take a long time and compete with your production workload for the same CPU. And your vector workload shares an instance with your transactional workload, so a heavy re-index can show up as latency on your checkout endpoint.

There are also version-dependent limits worth checking — indexable dimension caps, which types are supported — that have moved between releases and that you should verify against the exact version you pin rather than against a blog post.

What Pinecone actually costs

Pinecone's pitch is that none of the preceding paragraph is your problem. That's true, and for some teams it's worth a great deal.

The pricing model has shifted over the years — from provisioned pods you pay for by the hour whether or not you query them, toward consumption-style billing where you pay for stored vectors plus read and write operations. Treat any specific number you read anywhere, including here, as stale until you check the current pricing page, which is why this page carries a fact-check note about exactly that.

The structural points that survive pricing changes are these:

Consumption billing tracks usage, not capacity. A low-traffic app with a modest corpus can be very cheap. That's the honest strength of the managed model at small scale.
Cost scales with two independent things: how much you store and how much you query. A retrieval-heavy product — an agent that fires several searches per user turn — can generate a bill driven by reads, not by corpus size. Model both.
There's usually a free or starter tier big enough to prototype on, which makes "try it" nearly free and "commit to it" a decision you should make with a spreadsheet.

Now compare that to pgvector's cost, which is not zero either. It's the marginal cost of a larger Postgres instance — more RAM so the index stays resident, more CPU for builds — plus the engineering hours to tune it. For a mid-size managed Postgres you're looking at a predictable monthly instance bill (verify current rates for your provider and size). The comparison people get wrong is pricing Pinecone against free. It isn't free versus paid; it's a variable API bill versus a fixed instance bill plus engineer time.

The crossover depends on your read volume more than anything else. Storage-heavy and query-light favours self-hosting. Query-heavy on a small corpus often favours managed.

The modelling exercise takes twenty minutes and is worth doing before you commit either way. Estimate four numbers: how many chunks your corpus produces at your chosen chunk size, how many dimensions your embedding model outputs, how many searches a single user interaction triggers, and how many of those interactions you expect per month. The first two give you storage; the last two give you reads. Then multiply the read estimate by three, because agent-style products almost always end up issuing more retrieval calls per turn than the original design assumed — a query rewrite, a follow-up search, a reranking pass over a wider candidate set. I have never seen that number go down after launch.

Operational overhead, concretely

"Zero ops" and "you own it" are abstractions until you list the actual tasks. On the pgvector side, over a year, the work looks roughly like this: choose an index type and build it; size instance memory so the index stays resident rather than spilling to disk; rebuild after any bulk load; keep an eye on autovacuum, because heavy update or delete churn on an embeddings table leaves dead tuples that quietly degrade scan performance; and decide, once the vector workload grows teeth, whether to move it onto a read replica or its own instance so a long index build doesn't compete with transactional traffic.

That's a real list, but notice what it isn't: it's a set of tasks your team already knows how to do, on a system you already monitor, with alerting you already have. The learning curve is about vector indexing specifically, not about operating a new piece of infrastructure. For a team that already runs Postgres well, this is a genuinely smaller commitment than the phrase "you own indexing and tuning" makes it sound.

On the managed side, the list is close to empty, which is the whole product. In exchange you accept a bill that moves with usage, a second system in your incident surface — when retrieval is down, you're reading someone else's status page — and the drift problem described above, which you now own in the form of reconciliation code. Neither column is free. Pick the one whose costs your team is better shaped to absorb.

Performance, by scale

Approximate thresholds — my working rules of thumb from projects, not benchmarked constants. Benchmark on your own hardware and data before you bet on any of them.

Under ~100k vectors. Everything works. Postgres will answer these queries in single-digit to low-double-digit milliseconds with a reasonable index, and you can even get away without an index at small enough counts. At this scale, choosing a dedicated vector service is choosing an extra system to operate for no measurable benefit. Use pgvector.

~100k to ~1M vectors. Still comfortably pgvector territory, but now you have to mean it. Index type matters, memory sizing matters, and you'll want the index to fit in RAM. This is where teams get bitten by building an index once on an empty table and never rebuilding after loading data. It's tuning work, but it's a bounded amount of tuning work — days, not a standing burden.

~1M to ~10M vectors. The honest gray zone. pgvector still works here and plenty of production systems run in this range, but you're now doing real database engineering: build times measured in hours, memory as a first-order constraint, and pressure to isolate the vector workload onto its own instance or replica. If you have someone who enjoys that work, stay. If not, the managed option starts earning its bill.

Beyond ~10M vectors, or with hard latency SLAs at high QPS. This is what dedicated vector infrastructure is built for — horizontal scaling and distributed index maintenance that you would otherwise have to invent. Running this on a shared Postgres instance is possible and usually a bad trade.

One more axis: filtering behaviour. Postgres applies your filter exactly, with the planner deciding how. Dedicated vector stores approach filtered search differently, and depending on the semantics, a narrow filter over a large index can return fewer results than you asked for. If your retrieval is heavily filtered — per-tenant, per-permission — test that specific case rather than the unfiltered benchmark.

The migration path

The reassuring part: migration is not a one-way door, because vectors are derived data. Keep the source documents and the chunking logic and you can rebuild the index in any store with a re-embedding job. What you're migrating is code and behaviour, not irreplaceable data.

Make it cheap in advance:

Put a retrieval interface in front of the store. One module exposing something like search(query, filters, k). No SQL and no vendor SDK calls anywhere else in the codebase.
Keep chunk text and metadata in your primary database. Even if you use a managed store, treat it as an index over source-of-truth data you own. Then a rebuild is a job you can run, not an export you have to negotiate.
Record the embedding model and version on every chunk. Vectors from different models are not comparable. Without this, a migration turns into an archaeology project.
Dual-write and compare before you cut over. Run both stores, send the same query to each, and diff the top-k. Retrieval quality changes are subtle and won't show up in error rates — only in worse answers.

Done that way, a migration is a re-index and an adapter swap. Skipped, it's a rewrite of every query path in the application.

The recommendation

Start on pgvector if you already run Postgres and expect to stay under roughly a million vectors. You add no new system, your filters stay exact, your vectors can't drift out of sync with your data, and you get hybrid search from Postgres's own full-text index. The tuning is a bounded, learnable amount of work.

Choose a managed vector store — Pinecone or a peer — if any of these is true: you don't run Postgres and don't want to start; your projected count is comfortably past the single-digit millions; you have latency or QPS commitments you'd otherwise have to engineer for; or you have no one who wants to own index tuning. Zero ops is a legitimate feature, and paying for it is a legitimate choice.

Don't choose based on a benchmark chart. The published numbers are run on someone else's data with someone else's filters. Load a representative slice of your own corpus into both, run your real queries with your real filters, and measure recall and p95 latency yourself. That afternoon of work will tell you more than every comparison post, including this one.

If you're standing up retrieval and want a second opinion on where your workload actually lands on these axes, that's the kind of question I'm happy to look at directly.