Before you sign up for a vector database, run one query on your own data: how many rows does your largest tenant own, and what share of the table is that? The answer tells you whether your first problem will be scale or filtering, and for most SaaS features it's filtering.
Most teams shopping for a vector database are about to add a second source of truth to a product that has only one. The AI feature they want is usually small: semantic search across a customer's own documents, or a "find similar tickets" button next to a support queue. The data already lives in Postgres, scoped by organization_id, protected by policies someone wrote and reviewed. pgvector puts the embeddings in that same table, so similarity search becomes a SELECT that joins to the rest of your schema inside one transaction.
Do You Need a Separate Vector Database for a SaaS AI Feature?
Usually not. pgvector stores embeddings in the Postgres table that already holds your content, so search, tenant filters, joins and deletes share one transaction, one backup and one permission model.
The argument for staying in one database is mostly about consistency. When a user deletes a document, the row and its embedding disappear together or neither does. With a separate vector service, that becomes a cross-system delete you have to reconcile yourself. For a B2B SaaS product where each customer uploads a modest number of documents, that operational simplicity is the point. I don't have a primary-source benchmark for where a single Postgres instance stops being enough, so treat any "up to N million vectors" figure you read as a claim to test on your own hardware, not a rule.
Cost is the smaller argument, and it's checkable. Pinecone's pricing page, read on 30 September 2026, lists a free Starter plan, Builder at $20 per month, Standard at a $50 per month minimum and Enterprise at a $500 per month minimum. Those minimums are small money. They're also another vendor, another SDK and another line in your security questionnaire for a feature that might not survive the next roadmap review.
pgvector is actively maintained. Its changelog lists 0.8.6 on 29 July 2026, following 0.8.2 on 25 February 2026 and three more patch releases between them and 0.8.6, and the PostgreSQL versioning page lists majors 14 through 18 as currently supported. If you're choosing foundations, PostgreSQL is the boring default, and pgvector is what keeps it a sensible default once AI features arrive.
How do pgvector and Pinecone compare for a small SaaS feature?
| pgvector | Pinecone | |
|---|---|---|
| Cost at small scale | Included in the Postgres you already run | Free Starter, then $20 Builder, $50 Standard minimum, $500 Enterprise minimum (pricing page, 30 Sep 2026) |
| Consistency with relational data | Same transaction, same backup | A separate store to keep in sync (my inference from the architecture) |
| Tenant filtering | WHERE clause, plus iterative scans or partial indexes | Handled by the vendor's own filtering, which I did not verify here |
| Hybrid keyword and vector search | One SQL query with Postgres full-text search | Not verified here |
| Operational surface | One database | A second service, SDK and vendor |
Only the first row and the operational row rest on sources I fetched or on plain architecture. If a decision hinges on the others, check Pinecone's documentation directly.
Why Does Filtered Vector Search Return Too Few Results?
Approximate indexes apply your WHERE clause after scanning the index, so a selective tenant filter can leave fewer results than you asked for, and the query returns them without any error.
The pgvector README states this directly and gives the arithmetic: if a condition matches 10% of rows, then with HNSW and the default hnsw.ef_search of 40, only 4 rows will match on average. Now picture a tenant that owns 1% of the table. The index walks its graph, hands back roughly the 40 closest candidates globally, and the tenant filter throws most of them away. Nothing errors, and every query still returns quickly, which is why this survives testing. One customer eventually reports that search "feels broken".
The fix arrived in pgvector 0.8.0, which the changelog dates to 30 October 2024. It added iterative index scans: with hnsw.iterative_scan set to strict_order or relaxed_order, the index keeps scanning until enough results are found or it reaches hnsw.max_scan_tuples, which is 20,000 by default. Relaxed ordering gives better recall but can return results slightly out of order by distance, and the README shows a materialized CTE if you need strict order back. Raising hnsw.ef_search above its default of 40 is the other lever.
Iterative scans, partial indexes or partitions?
| Option | Fits when | Cost |
|---|---|---|
| Iterative scan | The filter varies per query: a date range, a folder, a document type | Deeper scans on selective filters, bounded by hnsw.max_scan_tuples |
| Partial HNSW index | A stable filter you query constantly, such as one large tenant | One index per filter value, so it stops scaling with thousands of tenants (my reasoning) |
| Table partitioning | Access follows a natural key like tenant or time | Real operational weight, so try it after the cheaper options |
The README shows the partial index form (CREATE INDEX ... USING hnsw (...) WHERE (category_id = 123)) and PARTITION BY LIST for partitioning.
Row level security sits on top of any of these. Supabase's RAG with permissions guide states that semantic search over protected rows "will continue to respect these RLS policies". That's the same argument for keeping isolation in the database that I make in the React and Supabase stack with row level security. Consider a Postgres-backed app on Supabase, like my React and Supabase ordering platform Pizzeria Bestek: adding vector search there would be a hypothetical extension and a policy, not a new service. One caution from that same reasoning: RLS adds a predicate the planner has to weigh alongside the index scan, so measure recall and latency with EXPLAIN ANALYZE on a realistic spread of tenant sizes, not a seed database where every tenant is equal.
HNSW or IVFFlat, and How Much Memory Will You Need?
HNSW has the better speed-recall tradeoff but slower builds and higher memory use, while IVFFlat builds faster and uses less memory but has lower query performance, according to the pgvector README.
| HNSW | IVFFlat | |
|---|---|---|
| Query performance | Better speed-recall tradeoff | Lower |
| Build time | Slower | Faster |
| Memory | Higher | Lower |
| Training step | None, can be created on an empty table | Needs data to train lists |
| Iterative scan setting | hnsw.iterative_scan | ivfflat.iterative_scan |
For sizing, the README gives exact storage formulas. A vector takes 4 * dimensions + 8 bytes and a halfvec takes 2 * dimensions + 8, so a 1,536-dimension embedding is 6,152 bytes at full precision and 3,080 bytes as a half vector. Both types can be stored up to 16,000 dimensions, but the README limits indexing to 2,000 dimensions for vector and 4,000 for halfvec. Those are per-row numbers, and the README also advises using the default index options unless you see low recall, so resist tuning m and ef_construction before you have a measurement that justifies it. The index adds its own footprint, so measure it instead of guessing: SELECT pg_size_pretty(pg_relation_size('index_name')).
Do indexes need to fit in memory? The README says no, but you'll likely see better performance if they do. Its scaling section lists the levers in order: use halfvec instead of vector, use binary quantization for indexes with re-ranking, scale vertically, then scale horizontally with replicas or sharding tools. That ordering matches how I'd approach it: cheap storage wins first, architecture later.
What happens when you re-embed everything?
Plan for it as a maintenance event. The README notes that vacuuming can take a while for HNSW indexes and suggests reindexing first with REINDEX INDEX CONCURRENTLY. If you change embedding models, my recommendation is to write the new vectors to a new column and cut over behind a flag, rather than rewriting the old column in place.
How Do You Add Hybrid Search in Postgres?
Run a full-text query and a vector query over the same tenant-scoped rows, then combine the two rankings with Reciprocal Rank Fusion or a cross-encoder, both of which the pgvector README points to.
Pure vector search can miss exact tokens. Someone searching for an invoice number or a surname gets documents that are about invoices, not the one containing INV-20418. Full-text search is good at exact terms, vectors are good at meaning, and fusion gets both. The README's hybrid search section shows the full-text side with ts_rank_cd, and the pgvector-python RRF example shows the whole fusion in one SQL statement. The procedure:
- Run the vector query, take the top 20 by cosine distance, and keep each row's ID and rank.
- Run the keyword query over the same tenant-scoped rows with a GIN index on
to_tsvector, take the top 20 and keep ID and rank. - Full outer join the two sets on ID and score each row as
1.0 / (k + vector_rank) + 1.0 / (k + text_rank), treating a missing rank as zero. The example usesk = 60. - Order by the fused score. If quality matters more than latency, rerank the top results with a cross-encoder.
Keep the tenant predicate inside both branches from the first commit. Retrofitting isolation into a search path is rework in the same category as retrofitting billing, which is why it sits where it does in my SaaS MVP stack recommendations. Also measure recall against a hand-labelled set of real queries before calling it done, since the README suggests monitoring recall by comparing approximate results with exact search.
Now go and look at your own numbers. How many vectors will your largest tenant own in a year, and what fraction of the table is that? If it's a small slice, you have a filtering problem to solve before you have a scale problem, and SET hnsw.iterative_scan = relaxed_order is the first thing to try tonight.