Building
Vector databases explained: how vector search works, pgvector vs Pinecone, and when you need one
What a vector database is, how vector search works, pgvector vs Pinecone with tested code and current prices, and the filtering mistake that silently drops results.
In short
A vector database stores embeddings, lists of numbers that represent what a piece of text means, and finds the ones nearest a query, so a search for “How do I get my money back?” finds the paragraph about refunds. Most use an approximate index (usually HNSW) to stay fast, trading a little recall for speed. If you already run Postgres, the pgvector extension is usually enough: vectors sit next to the rows they describe, filters are plain SQL and one transaction keeps them in sync. Pinecone is the managed alternative, with a namespace per customer, a free tier and paid plans from $20 a month. Whichever you use, watch for filters applied after the index, which silently return fewer results than you asked for.
Key takeaways
- If you already run Postgres or Supabase, start with pgvector; reach for a dedicated vector database when the vector workload needs its own scaling or you have no Postgres.
- Use one embedding model per column and store its name on every row; vectors from different models don’t compare, even at the same dimension.
- Match the query operator to the index’s operator class (<=> with vector_cosine_ops), or Postgres silently scans the whole table.
- Filters on an approximate index can return fewer rows than you asked for; turn on pgvector’s iterative scans (0.8.0+) or index the filter column.
- Treat the tenant filter as a security boundary: enforce it with RLS in Postgres, or one namespace per tenant in Pinecone.
- Add keyword search and merge with reciprocal rank fusion when exact terms such as order numbers and error codes matter.
What is a vector database?
A vector database stores embeddings, lists of numbers that represent the meaning of a piece of text, an image or a sound, and finds the ones closest to a query. Two texts that mean similar things get vectors that sit close together, even when they share no words. That is how a support agent can answer “How do I get my money back?” from a help-centre paragraph headed “Refunds”.
An embedding model does the conversion. OpenAI’s text-embedding-3-small, for example, turns any text up to 8,192 tokens into 1,536 numbers. The database’s job is the other half: keep millions of those vectors, and return the nearest few to a new one quickly, usually with filters such as “only this customer’s documents”.
What it’s used for, in the order we see it in practice:
- Retrieval-augmented generation (RAG): an AI agent looks up the passages relevant to a question and answers from them. Every support agent and voice agent we build that answers from company documents works this way.
- Semantic search: search that matches meaning, not just keywords.
- Recommendations and deduplication: “more like this”, or “is this ticket a duplicate of one we already have?”
“Vector database” covers two kinds of product. Some are built for nothing else: Pinecone, Qdrant, Weaviate, Milvus and Chroma. Others add vector search to a database you may already run, such as pgvector for Postgres, or to storage, such as Amazon S3 Vectors.
How does vector search work?
Embeddings
Every vector in one column must come from the same embedding model. Vectors from different models live in different spaces, so comparing them returns confident nonsense with no error. Changing models means re-embedding everything. The common models:
| Model | Dimensions | Price per 1M tokens |
|---|---|---|
OpenAI text-embedding-3-small | 1,536 (can be shortened) | $0.02 |
OpenAI text-embedding-3-large | 3,072 (can be shortened) | $0.13 |
Voyage voyage-4-lite / voyage-4 / voyage-4-large | 1,024 by default; 256, 512 or 2,048 | $0.02 / $0.06 / $0.12 |
Pinecone-hosted llama-text-embed-v2 | 1,024 by default; 384 to 2,048 | $0.16 |
Prices are from OpenAI’s pricing page, Voyage’s pricing and Pinecone’s pricing. Anthropic doesn’t offer its own embedding model and points to Voyage AI, so a Claude-based agent still needs one of these.
Distance
“Close” needs a measure. There are three in common use:
- Cosine distance: the angle between two vectors, ignoring their length. The usual choice for text.
- Inner product (dot product): the same ranking as cosine when vectors have length 1, and cheaper to compute. OpenAI and Voyage embeddings already have length 1, so for them, cosine and inner product give the same order.
- Euclidean (L2) distance: the straight-line distance.
In pgvector each has an operator, and they come up again below:
| Operator | Distance | Note |
|---|---|---|
<=> | Cosine distance | 0 is identical; similarity is 1 - distance |
<#> | Inner product, negated | Negative because Postgres indexes only sort ascending |
<-> | Euclidean (L2) distance | |
<+> | L1 (taxicab) distance | |
<~>, <%> | Hamming and Jaccard distance | For binary vectors |
Exact and approximate search
The simple way to find the nearest vectors is to compare the query with every row and sort. That is exact search, and it is correct by definition, but its cost grows with every row you add.
An approximate nearest neighbour (ANN) index skips most of that work, at the cost of sometimes missing a true neighbour. The share of true neighbours it finds is called recall. Two index types matter in practice:
- HNSW (hierarchical navigable small world) builds a layered graph in which each vector links to its near neighbours. A search starts at the top layer and walks downhill towards the query. It comes from a 2016 paper by Malkov and Yashunin and is now the default almost everywhere.
- IVFFlat clusters the vectors into lists and searches only the lists nearest the query.
pgvector’s README sums up the trade: HNSW “has better query performance than IVFFlat (in terms of speed-recall tradeoff), but has slower build times and uses more memory”. HNSW can also be created on an empty table, while IVFFlat should be created after the data is loaded, because it learns its clusters from the rows it sees. We use HNSW unless memory is tight.
Do you need a separate vector database?
Often not. If you already run Postgres, Supabase included, pgvector keeps the vectors in the same database as the rows they describe. That gets you a lot for free:
- A chunk and its document live in one transaction, so deleting a document deletes its vectors too, with nothing left to drift out of sync.
- Filters are ordinary SQL, joined to your real tables: tenant, permissions, dates, product line.
- Row-level security, backups and point-in-time recovery cover your vectors the same way they cover everything else.
- There is one bill and one system to watch.
A dedicated vector database earns its place when the vector workload is big enough to need its own hardware and scaling, when you don’t run Postgres at all, or when you want serverless pricing with nothing to tune.
| Situation | What we’d use |
|---|---|
| An app already on Postgres or Supabase, up to a few million chunks | pgvector |
| A RAG agent over a company’s documents | pgvector, or Pinecone if there’s no Postgres in the stack |
| Many customers, each with their own documents, searched heavily | Pinecone with a namespace per customer, or pgvector with partitions |
| Hundreds of millions of vectors or more, searched constantly | A dedicated engine: Pinecone, Qdrant, Milvus or Weaviate |
| Huge archives searched rarely, where storage cost dominates | Amazon S3 Vectors |
pgvector tutorial: vector search in Postgres
pgvector is an open-source Postgres extension. The current release is 0.8.7, from 1 October 2026. We ran every SQL statement in this article against Postgres 18.6 with pgvector 0.8.7, using PGlite, the embedded build of Postgres.
A table of chunks, each with its tenant, where it came from, its text and its vector:
create extension if not exists vector;
create table documents (
id bigint generated always as identity primary key,
tenant_id uuid not null,
source text not null, -- the file or page the chunk came from
content text not null, -- the text you'll give the model
embedding vector(1536) not null,
embedding_model text not null default 'text-embedding-3-small'
);
create index on documents (tenant_id);
create index on documents using hnsw (embedding vector_cosine_ops); On Supabase, create the extension with create extension vector with schema extensions;. And since 30 October, a new Supabase table isn’t visible to the Data API until you grant it, so add the grants and an RLS policy as described in our guide to the 42501 error.
Keep the text and its source next to the vector. You need the text to give to the model, the source to cite and to delete, and the model name for the day you switch models.
The nearest five chunks for one tenant:
select source, content, embedding <=> $1 as distance
from documents
where tenant_id = $2
order by embedding <=> $1
limit 5; The operator in the query must match the index’s operator class. The index above was built with vector_cosine_ops, so only <=> can use it. We checked: with the same index, a query ordered by <-> or <#> silently falls back to a sequential scan of the whole table. There is no error, just a query that gets slower as the table grows. Use vector_ip_ops with <#>, and vector_l2_ops with <->.
Loading and searching from TypeScript
The code below splits a document into chunks, embeds them with OpenAI and stores them, replacing any older version of the same document. We ran it with pg 8.23 and openai 6.49 against the same Postgres, with a local stand-in for the embeddings API.
import OpenAI from 'openai';
import pg from 'pg';
const openai = new OpenAI(); // reads OPENAI_API_KEY
const db = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const MODEL = 'text-embedding-3-small'; // 1,536 dimensions
/** Split a document on paragraph breaks into chunks of about `max` characters. */
export function chunk(text: string, max = 1000): string[] {
const chunks: string[] = [];
let current = '';
for (const para of text.split(/\n\s*\n/)) {
if (current && current.length + para.length > max) {
chunks.push(current.trim());
current = '';
}
current += para + '\n\n';
}
if (current.trim()) chunks.push(current.trim());
return chunks;
}
async function embed(input: string[]): Promise<number[][]> {
const res = await openai.embeddings.create({ model: MODEL, input });
return res.data.map((d) => d.embedding);
}
/** Store a document's chunks, replacing any older version of it. */
export async function ingest(tenantId: string, source: string, text: string) {
const chunks = chunk(text);
const vectors = await embed(chunks);
const client = await db.connect();
try {
await client.query('begin');
await client.query('delete from documents where tenant_id = $1 and source = $2', [tenantId, source]);
for (const [i, content] of chunks.entries()) {
await client.query(
`insert into documents (tenant_id, source, content, embedding, embedding_model)
values ($1, $2, $3, $4, $5)`,
[tenantId, source, content, JSON.stringify(vectors[i]), MODEL],
);
}
await client.query('commit');
} catch (e) {
await client.query('rollback');
throw e;
} finally {
client.release();
}
}
/** The k chunks closest in meaning to the question, from one tenant's documents. */
export async function search(tenantId: string, question: string, k = 5) {
const [vector] = await embed([question]);
const { rows } = await db.query(
`select source, content, embedding <=> $1 as distance
from documents
where tenant_id = $2
order by embedding <=> $1
limit $3`,
[JSON.stringify(vector), tenantId, k],
);
return rows as { source: string; content: string; distance: number }[];
} JSON.stringify of a number array is valid pgvector input ([0.1,0.2,…]), so no extra library is needed. The chunker is the simplest one that works. For documents with clear headings, split on the headings instead, so each chunk is one topic.
More than 2,000 dimensions
pgvector stores vectors of up to 16,000 dimensions, but an HNSW or IVFFlat index on the vector type takes at most 2,000. With text-embedding-3-large’s 3,072 dimensions, creating the index fails with column cannot have more than 2000 dimensions for hnsw index. There are two fixes, and both worked in our test:
- Ask the model for fewer dimensions. OpenAI calls the
dimensionsparameter “the suggested approach” for shortening embeddings:dimensions: 1536. - Index a half-precision copy, which allows up to 4,000 dimensions, and use the same expression when you query:
create index on documents
using hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops);
-- the query must use the same expression to use the index
select id from documents
order by embedding::halfvec(3072) <=> $1::halfvec(3072)
limit 5; Why does vector search with a filter return fewer results?
This is the mistake we see most in vector search code, and it fails quietly. With an approximate index, Postgres finds candidate rows in the index first and applies your where clause afterwards. In the README’s words, “if a condition matches 10% of rows, with HNSW and the default `hnsw.ef_search` of 40, only 4 rows will match on average”. You ask for five chunks, get two, and the agent answers from less context than you think.
We reproduced it with 3,000 random 1,536-dimension vectors spread across 50 tenants, so each tenant owned 2% of the rows, and forced the planner to use the HNSW index:
| Setting | Rows returned |
|---|---|
Default (hnsw.ef_search = 40) | 2 |
hnsw.ef_search = 100 | 2 |
hnsw.ef_search = 200 | 3 |
hnsw.iterative_scan = strict_order | 5 |
hnsw.iterative_scan = relaxed_order, re-sorted | 5 |
Left to itself, the planner chose differently. With a B-tree index on tenant_id, it fetched that tenant’s 60 rows and sorted them by exact distance, which is the right answer when each tenant is small. The trap opens when a tenant grows large enough that Postgres prefers the vector index. The fixes, from simplest:
- Index the filter column. A B-tree on
tenant_idlets Postgres search one tenant’s rows exactly when that’s cheaper. - Turn on iterative index scans (pgvector 0.8.0 and later). The index keeps scanning until enough rows pass the filter, up to
hnsw.max_scan_tuples(20,000 by default):
set hnsw.iterative_scan = strict_order;
-- or relaxed_order, which is faster but can return rows slightly out of
-- order; a materialized CTE puts them back in order
set hnsw.iterative_scan = relaxed_order;
with nearest as materialized (
select source, content, embedding <=> $1 as distance
from documents
where tenant_id = $2
order by distance
limit 5
)
select * from nearest order by distance;- Use a partial index for a filter with a few distinct values (
create index … where category = 'faq'), and partition the table when there are many, as the pgvector README suggests.
Iterative scans are still approximate. In our run they returned five rows, but not exactly the five that an exact search found. For a RAG agent, that is usually fine. For anything where completeness matters, search exactly.
The tenant filter is a security boundary
In a product where several customers upload documents, where tenant_id = $2 is the only thing keeping one customer’s files out of another customer’s answers. Forget it in one query and the agent will happily quote a stranger’s contract. On Supabase, enforce it with row-level security as well as in code. Write your match function as a plain language sql function: those run as the calling user by default (we checked: prosecdef is false), so RLS still applies. Don’t mark it security definer, because that runs it as its owner and skips RLS.
Hybrid search: vectors plus keywords
Embeddings are good at meaning and bad at exact strings. Order numbers, SKUs, error codes and people’s names often embed poorly, so a vector search for “error E-4012” can return chunks about errors in general. The fix is to run a keyword search as well and merge the two rankings.
Reciprocal rank fusion (RRF) does the merge without comparing incompatible scores: each result scores 1 / (k + rank) in each list it appears in, and the scores are added. The constant k = 60 comes from the paper that introduced it. In Postgres, with full-text search alongside pgvector:
alter table documents
add column fts tsvector
generated always as (to_tsvector('english', content)) stored;
create index on documents using gin (fts);
with semantic as (
select id, row_number() over (order by embedding <=> $1) as rank
from documents
where tenant_id = $3
order by embedding <=> $1
limit 20
),
keyword as (
select id, row_number() over (order by ts_rank_cd(fts, query) desc) as rank
from documents, websearch_to_tsquery('english', $2) as query
where tenant_id = $3 and fts @@ query
order by ts_rank_cd(fts, query) desc
limit 20
)
select d.id, d.source, d.content,
coalesce(1.0 / (60 + s.rank), 0) + coalesce(1.0 / (60 + k.rank), 0) as score
from semantic s
full outer join keyword k on k.id = s.id
join documents d on d.id = coalesce(s.id, k.id)
order by score desc
limit 5; $1 is the question’s embedding, $2 the question as typed, and $3 the tenant. A chunk found by both searches rises to the top. Supabase’s hybrid search guide wraps the same idea in a hybrid_search function, with a weight for each side.
What is Pinecone?
Pinecone is a managed, serverless vector database. You create an index, send vectors, and query them over an API, with no servers or index settings to manage. It suits teams without Postgres in their stack, or teams that want vector search entirely off their main database.
Two things make it a good fit for products with many customers:
- Namespaces. Pinecone’s guidance is to “use one namespace per tenant”. A query is scoped to one namespace, so one customer’s search can’t see another’s vectors, and it costs less, as the pricing below shows.
- Metadata filters with
$eq,$ne,$gt,$gte,$lt,$lte,$in,$nin,$exists,$and,$orand$not, on up to 40 KB of metadata per record.
The same job as the pgvector example, with the TypeScript SDK. We type-checked it against @pinecone-database/pinecone 9.1.0:
import { Pinecone } from '@pinecone-database/pinecone';
// tenantId, source, chunks and vectors as in the pgvector example above;
// questionVector is the embedded question.
const pc = new Pinecone(); // reads PINECONE_API_KEY
// Once: a serverless index for 1,536-dimension vectors compared by cosine.
const indexModel = await pc.indexes.create({
name: 'docs',
schema: { fields: { _values: { type: 'dense_vector', dimension: 1536, metric: 'cosine' } } },
deployment: { deploymentType: 'managed', cloud: 'aws', region: 'us-east-1' },
waitUntilReady: true,
});
// One namespace per tenant: a query here can only see this tenant's vectors.
const index = pc.index<{ source: string; content: string }>({
host: indexModel.host,
namespace: tenantId,
});
await index.upsert({
records: chunks.map((content, i) => ({
id: `${source}#${i}`,
values: vectors[i],
metadata: { source, content },
})),
});
const { matches } = await index.query({
vector: questionVector,
topK: 5,
includeMetadata: true,
filter: { source: { $eq: 'refund-policy.md' } },
}); Two version notes, because most tutorials online are out of date. SDK 9 needs Node.js 22 or later. It also deprecates the flat `pc.createIndex` in favour of pc.indexes.create, shown above. Pinecone can also embed text for you with a hosted model; the older way to do that, createIndexForModel with upsertRecords and searchRecords, is now labelled legacy.
The limits worth knowing, from Pinecone’s operation limits: up to 20,000 dimensions, upserts of up to 1,000 records or 2 MB per request, and topK up to 10,000.
How much does Pinecone cost?
From Pinecone’s pricing page, as of 3 November 2026:
| Plan | Price | What’s included |
|---|---|---|
| Starter | Free | Up to 2 GB storage, 2M write units and 1M read units a month, 5 indexes, AWS us-east-1 only |
| Builder | $20 a month, flat | 10 GB storage, 5M write units and 2M read units a month, all regions; usage over that is blocked, not billed |
| Standard | $50 a month minimum usage | Pay as you go: storage $0.33 per GB-month, write units $4–$4.50 per million, read units $16–$18 per million |
| Enterprise | $500 a month minimum usage | Higher limits; read and write units cost more per million |
How queries are metered is the part that shapes your design. A query costs 1 read unit for every 1 GB of the namespace it searches, with a minimum of 0.25. The number of results doesn’t change the cost, but the size of the namespace does. That is the cost argument for a namespace per tenant: a query in a customer’s own 1 GB namespace costs 1 read unit, and the same query filtered inside a shared 100 GB namespace costs 100.
A rough example. 100,000 chunks of 1,536 dimensions is about 0.6 GB of vectors (1,536 × 4 bytes each), plus the text you store with them; call it 0.7 GB. On Standard, storing that costs about $0.25 a month, and each query uses about 0.7 read units, so 100,000 queries a month comes to roughly $1.10–$1.30. Both are far below the $50 minimum. At that scale you pay for the plan, not for usage, and Builder at $20 would likely cover it.
pgvector vs Pinecone, and the other vector databases
| pgvector | Pinecone | |
|---|---|---|
| What it is | A Postgres extension | A managed, serverless vector database |
| Where vectors live | In your Postgres tables, next to your data | In Pinecone, separate from your data |
| Filtering | Any SQL, joined to your tables | Metadata filters on each record |
| Multi-tenancy | A tenant_id column with RLS, or partitions | One namespace per tenant |
| Keeping data in sync | One transaction covers the row and its vector | Your code writes to two systems |
| Tuning | You choose the index and settings (ef_search, iterative scans) | Managed for you |
| Cost | Part of your database bill | Free tier, then $20 flat or a $50 monthly minimum |
| Licence | Open source (PostgreSQL licence) | Proprietary service |
Searches for an open source vector database usually end at one of these:
| Database | Licence | Notes |
|---|---|---|
| pgvector | PostgreSQL licence | Vector search inside the Postgres you already run, Supabase included |
| Qdrant | Apache 2.0 | Written in Rust; self-hosted, or managed as Qdrant Cloud |
| Milvus | Apache 2.0 | Cloud-native and built to scale out; managed as Zilliz Cloud |
| Weaviate | BSD-3-Clause, with some parts under the Weaviate License | Can embed your text at import; supports hybrid search |
| Chroma | Apache 2.0 | Simple to start with; popular for prototypes |
On AWS, besides pgvector in Postgres, there is Amazon S3 Vectors, generally available since December 2025, stores and queries vectors directly in S3: up to two billion vectors per index, with vectors of up to 4,096 dimensions. It is built for large, cheaply stored collections rather than for the fastest queries.
How a vector database fits into RAG
The vector database is one step in a retrieval-augmented generation pipeline:
- Chunk each document into passages of one topic each.
- Embed each chunk and store it with its text, source and tenant.
- When a question arrives, embed the question with the same model.
- Retrieve the nearest chunks, with hybrid search if exact terms matter.
- Generate: give the model the chunks and the question, and tell it to answer only from the chunks and to say which source it used.
Most of the quality comes from steps 1 and 4, not from the choice of database. Before launching an agent, we write down 30 to 50 real questions with the document that should answer each. Then we check that the right chunk comes back in the top five. If it doesn’t, the fix is usually better chunking or hybrid search, not a different vector database. The AI agents guide covers the steps around retrieval.
Mistakes we see in vector search code
- Mixing embedding models. The documents were embedded with one model and the question with another, or a model upgrade re-embedded half the table. Shortening embeddings makes this worse: two models set to 1,536 dimensions produce vectors of the same size, and they still don’t compare. Store
embedding_modelon every row. - An operator that doesn’t match the index. A query with
<->against avector_cosine_opsindex scans the whole table, and nothing tells you. - Filtering after the index. The query returns fewer rows than asked for. Use iterative scans, or an index on the filter column.
- No tenant filter, or a match function marked
security definerthat skips RLS. Each turns a search bug into a data leak. - Building IVFFlat on an empty table. Its clusters come from the rows present when it’s built. Create it after loading data, or use HNSW.
- Storing vectors without their text and source. The agent can’t quote what it found, and you can’t delete one document’s chunks when the document changes.
Sources
- OpenAI docs — Vector embeddings
- OpenAI API pricing
- Voyage AI pricing
- Pinecone pricing
- Claude docs — Embeddings
- Malkov and Yashunin — Efficient and robust approximate nearest neighbor search using HNSW graphs (arXiv:1603.09320)
- pgvector on GitHub
- pgvector changelog
- Cormack, Clarke and Büttcher — Reciprocal Rank Fusion outperforms Condorcet and individual rank learning methods (SIGIR 2009)
- Supabase docs — Hybrid search
- Pinecone
- Pinecone docs — Implement multitenancy
- Pinecone docs — Indexing overview
- Pinecone docs — Node.js SDK
- Pinecone docs — Operation limits
- Pinecone docs — Understanding cost
- Qdrant on GitHub
- Milvus on GitHub
- Weaviate on GitHub
- Chroma on GitHub
- AWS — Amazon S3 Vectors now generally available
- Amazon S3 docs — S3 Vectors limitations
- Supabase docs — Vector indexes
- Pinecone docs — Filter by metadata
- Pinecone TypeScript SDK on GitHub
- Voyage AI docs — Text embeddings