Postgres + pgvector for AI-Powered SaaS Features in 2026

Most teams shopping for a vector database are about to add a second source of truth to a product that only has one. The AI feature they want is usually small: semantic search across a customer's own documents, or a "find similar tickets" button sitting next to a support queue. The data already lives in Postgres. It is already scoped by organization_id, already protected by policies somebody wrote and reviewed.

pgvector puts the embeddings in that same table. A CREATE EXTENSION, a column, an index, and a rewrite of one query. Similarity search becomes a SELECT that joins to the rest of your schema inside a single transaction, under the tenant isolation you already trust.

That is the pitch for a Postgres pgvector SaaS build, and under roughly ten million vectors it holds up. What the pitch skips is the part that bites: the second you add WHERE organization_id = $1 to a vector query, you have left the benchmark everyone quotes and entered a regime where the index can quietly return you nothing at all. The index does not error. It returns fewer, worse results for one tenant, discovered three weeks later by a customer who reports that search "feels broken."

Here is what actually matters when you put vectors in the database you already run.

Do You Need a Vector Database for a SaaS AI Feature?

No, not below roughly ten million vectors — Postgres with pgvector handles that range while keeping embeddings in the same transaction as your relational rows. Above that band the calculus changes, but almost no early-stage feature starts there. A B2B SaaS product where each customer uploads a few thousand documents is a low-single-digit-millions problem for years.

The technical argument for staying in one database is transactional. When a user deletes a document, the row and its embedding disappear together or neither does. Run a separate vector service and that becomes a distributed write you have to reconcile, which in practice means a nightly reconciliation job and a Slack thread where someone asks why the assistant is still citing a deleted contract.

The organisational argument is smaller and more honest: one fewer service to pay for and to explain in a security questionnaire. Pinecone's Standard tier starts at a $50/month minimum before usage, with Enterprise at $500. That is not much money. It is a lot of procurement friction for a feature that might get cut in the next roadmap review.

If you are choosing foundations rather than retrofitting, PostgreSQL is the boring correct answer, and pgvector is why it stays correct once AI features land. The extension is at version 0.8.6 as of 29 July 2026, with five patch releases shipped this year alone — including a buffer-overflow fix in parallel HNSW builds. Actively maintained, not a science project.

The Multi-Tenant Filter Is Where pgvector Actually Breaks

Approximate nearest neighbour search and a WHERE clause do not compose the way your intuition says they do. The HNSW index walks a graph to find the top k vectors in the whole table. Postgres then applies your tenant filter to those results. If a tenant owns 5% of the rows, the top ten globally might contain one of theirs. Might contain zero.

ClickHouse's engineering write-up on scaling vector search in Postgres puts the failure plainly: the index surfaces candidates that all fail the filter, and recall drops unless the scan goes deeper. Deeper is the operative word — the fix is not a better embedding model, it is telling the index to keep going.

pgvector 0.8.0, released 30 October 2024, added iterative index scans for exactly this. Set hnsw.iterative_scan to relaxed_order and the index re-enters the graph when the filtered result set comes up short, bounded by hnsw.max_scan_tuples, which defaults to 20,000. The other knob is hnsw.ef_search, default 40, which is far too low for any query with a selective predicate on it.

Two patterns, and they solve different shapes of problem:

• Iterative scans for ad-hoc filters — a date range, a document type, a folder a user picked from a dropdown. You do not know the predicate in advance, so you let the index work harder at query time.
• Partial HNSW indexes for stable, low-cardinality filters you query constantly. CREATE INDEX ... WHERE organization_id = 42 gives that tenant a private graph with exact recall. Beautiful for your ten enterprise accounts. Completely impractical at four thousand tenants, because you would be maintaining four thousand indexes and Postgres will let you find that out the slow way.

Row Level Security sits on top of both, and it composes better than people expect. Supabase's RAG with permissions guide notes that semantic search continues to respect existing RLS policies, and recommends RLS as the default precisely because it stays applied as new queries get written later. That is the same argument I make for React and Supabase with row level security on any multi-tenant surface: the isolation belongs in the database, not in the twelve places your application code queries it from. On Pizzeria Bestek the Postgres instance is Supabase, and adding vector search there would be an extension and a policy, not an architecture change.

One caveat worth internalising. RLS turns your WHERE clause into something the planner has to reason about alongside the index scan, which is exactly the composition problem above wearing a different hat. Measure it with EXPLAIN ANALYZE on real tenant distributions, not on a seed database where every tenant owns the same number of rows.

pgvector vs Pinecone: Which One Does a SaaS Feature Need?

pgvector wins on cost and on transactional consistency; Pinecone wins only when you genuinely operate at hundreds of millions of vectors.

The row that decides it for most products is the last one, not the first. Keeping retrieval in SQL means your filter, your join, your permissions, and your ranking all live in a single query plan you can read. That is worth more than a few milliseconds of p99.

How Do You Store Embeddings Without Melting the Instance?

Budget 20 to 25 kilobytes per vector in RAM, not the six kilobytes the dimension count suggests. HNSW graph metadata dominates once the index is built.

That gap is where capacity planning goes wrong. A 1,536-dimension OpenAI embedding at full FP32 precision is about 6 KB of raw data, but real-world deployments land at 20–25 KB per vector with common settings. Multiply before you provision. One million vectors is tens of gigabytes of working set — fine. Ten million and you are tuning memory seriously. A hundred million and you should be asking whether in-memory HNSW is still the right structure at all.

The failure mode when you get it wrong is not a crash. When the active graph stops fitting in memory, traversal starts fetching pages from storage and query latency degrades from milliseconds to seconds. Your monitoring shows a slow query. Your users show up in support.

Three practical moves, in the order I would reach for them:

1. Cast to halfvec. FP16 storage cuts the vector footprint by roughly half with minimal recall loss in a retrieve-then-rerank pipeline. If you are reranking the top 50 with a cross-encoder anyway, the precision you gave up never reaches the user.
2. Shrink the embedding. Matryoshka-style models let you truncate dimensions and renormalise. Going from 1,536 to 768 halves storage again, and pgvector caps vector and halfvec at 16,000 dimensions, so you have room to move in either direction.
3. Only then, partition. By tenant or by time, whatever your access pattern actually is. Partitioning is real operational weight, so earn it after the cheap wins.

Re-Embedding Is a Write Storm

pgvector indexes do not compact on UPDATE, only on REINDEX. When you swap embedding models and rewrite five hundred thousand rows, you are not doing an incremental update. You are doing a bloat event with a migration script wrapped around it. Plan the REINDEX. Plan the maintenance window. Or version your embeddings in a new column and cut over with a feature flag.

Hybrid Search Is the Cheapest Quality Win Available

Pure vector search fails on exact-match queries in a way that makes users distrust the whole feature. Someone searches an invoice number or a customer surname, and cosine similarity has no special respect for exact tokens, so it returns five documents that are about invoices and not the one containing INV-20418.

The fix is to run both retrievals and fuse the rankings. Reciprocal rank fusion scores each result as 1 / (k + rank) across both lists and sums them, which is what Elasticsearch does and what you can now do in one SQL statement:

1. Run the vector query, take the top 50 by cosine distance, keep the row IDs and their rank.
2. Run the keyword query over the same tenant-scoped rows, take the top 50, keep IDs and rank.
3. Full-outer-join the two result sets on ID and compute 1/(60 + vector_rank) + 1/(60 + text_rank). The constant 60 is the conventional default and it is not precious.
4. Order by the fused score, then rerank the top 20 with a cross-encoder if quality still matters more than latency.

Until recently the weak link was step 2, because Postgres shipped ts_rank rather than true BM25 — no inverse document frequency, no term-frequency saturation, no length normalisation. Tiger Data's pg_textsearch preview, released 23 October 2025, closes that gap, and their write-up cites ts_rank queries degrading from under a second to 25–30 seconds on 800,000 rows. Actually, let me be precise about what that number is: it is a latency complaint about the old ranking function, not a claim about the new one. The point stands that keyword ranking in Postgres stopped being the compromise it used to be.

What I Would Ship This Week

Start with one table, one halfvec column, one HNSW index, and hnsw.iterative_scan = relaxed_order set from day one so the tenant-filter cliff never surprises you. Write the retrieval query with the tenant predicate in it from the first commit — retrofitting isolation into a search path is the same category of rework as retrofitting billing, which is why it sits where it does in my SaaS MVP stack recommendations. Measure recall against a hand-labelled set of fifty real queries before anyone calls it done.

Then go look at your own numbers. How many vectors will your largest tenant own in eighteen months, and what fraction of the table is that? If the answer is under 5%, you have a filtering problem to solve before you have a scale problem — and that one is solvable tonight.