Every pgvector tutorial opens the same way. You CREATE EXTENSION vector, define a column, insert some embeddings, run a cosine distance query, and the demo works beautifully. The README looks friendly. The benchmarks look fast. You ship it.

Then production happens.

I’ve shipped pgvector into production on more than one real project. One of them is this site — xahidex.com/ai runs a RAG assistant that actually answers questions using vector search over real content, not a toy dataset. The other projects I can not name, but the lessons from all of them are the same.

This post is not another “what is pgvector” walkthrough. It is the collection of things I wish someone had told me before I hit them in production. Some of these are pgvector-specific. Some are about the broader RAG pipeline that pgvector lives inside. All of them cost real time to learn.

The index decision you make without realising it

When you first add pgvector to a table, you probably add an index. The docs show IVFFlat. You copy it, set some lists value you picked from a Stack Overflow answer, and move on. Six months later you are debugging why recall dropped after a bulk import.

pgvector ships two approximate nearest-neighbor index types: IVFFlat and HNSW. The difference matters more than most tutorials admit.

IVFFlat: clusters that freeze at build time

IVFFlat partitions your vector space into clusters using k-means. At query time, it compares your query vector against the cluster centroids and searches only inside the nearest ones. This makes queries fast because they skip most of the data.

The problem: the centroids are computed at build time and do not update as you insert new vectors. If you bulk-import 50,000 new documents after building the index, those vectors might not map cleanly to the existing clusters. Recall quietly drops. You will not notice until someone complains that the search is returning irrelevant results.

-- IVFFlat: fast to build, recall decays with data drift
CREATE INDEX ON document_chunks
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);
 
-- You must REINDEX after significant bulk inserts to rebuild the centroids
REINDEX INDEX CONCURRENTLY document_chunks_embedding_idx;

The lists parameter is the number of clusters. A common starting formula is sqrt(number_of_rows). For 1 million rows: lists = 1000. At query time, ivfflat.probes controls how many clusters get searched. Higher probes means better recall at the cost of slower queries.

-- Set probes at query time, not globally, so different queries can tune differently
SET LOCAL ivfflat.probes = 10;

HNSW: the better default for most production workloads

HNSW builds a multi-layer proximity graph. Each vector is connected to its nearest neighbors at multiple levels of granularity. Queries walk down through the layers, narrowing candidates at each level. The structure is fast to search and accepts live inserts without degrading recall the way IVFFlat does.

-- HNSW: better recall, handles live inserts gracefully
CREATE INDEX ON document_chunks
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 128);
 
-- ef_search controls recall vs latency at query time
SET LOCAL hnsw.ef_search = 100;

The m parameter controls the maximum number of connections per node per layer. Higher m means better recall and slower builds with larger index size. ef_construction controls how carefully the graph is built. Both are set at index creation and require a rebuild to change. ef_search is the one you can tune per query without rebuilding.

The quick decision rule

For everything I have built, I now start with HNSW and never look back. The build cost is higher but the operational headache is lower.

Embedding model choice locks you in more than you think

This one hurt me.

When you start, you pick an embedding model. Maybe text-embedding-3-small at 1536 dimensions. Or text-embedding-3-large at 3072 dimensions. Or an open-source model running locally. You create your table with that dimension count, embed your corpus, and ship.

Three months later you want to try a better model. Maybe a domain-specific one. Maybe a newer model with better benchmark scores. You can not just swap it in. The dimension count is baked into your column definition (vector(1536)), your index, and every stored embedding. A new model with a different dimension means re-embedding your entire corpus, dropping and recreating the index, and potentially a schema migration.

-- This is locked at column creation. Changing it means migrating the table.
ALTER TABLE document_chunks ADD COLUMN embedding vector(1536);
 
-- If you switch to a model that produces vector(768), you need:
ALTER TABLE document_chunks ADD COLUMN embedding_v2 vector(768);
-- Backfill embedding_v2 for every row
-- Rebuild the index on embedding_v2
-- Switch your query layer to use the new column
-- Drop the old column after validation

Even if the new model uses the same dimension count, you cannot mix embeddings from different models in the same index. Cosine distance between a vector from model A and a vector from model B is meaningless. They live in different semantic spaces.

What I do now: version the embedding model explicitly in the schema.

CREATE TABLE document_chunks (
  id           BIGSERIAL PRIMARY KEY,
  document_id  BIGINT REFERENCES documents(id) ON DELETE CASCADE,
  chunk_index  INT NOT NULL,
  content      TEXT NOT NULL,
  embedding    vector(1536),
  model_id     TEXT NOT NULL DEFAULT 'text-embedding-3-small', -- version it
  created_at   TIMESTAMPTZ DEFAULT now()
);

This way you can run two models side by side during a migration and gradually cut over. It costs storage, but it keeps you from having a hard cutover that breaks production.

Similarity scores are not universal — you have to calibrate yours

The <=> cosine distance operator in pgvector returns a number between 0 and 2. Lower is more similar. Most people flip it to a similarity score (1 - distance) so that 1.0 means identical and 0.0 means completely unrelated.

Here is the part nobody explains: what counts as “good enough” is entirely domain-specific. A score of 0.82 might be excellent for a general Q&A system and completely useless for a specialized domain.

When I built the RAG assistant on this site, I started with a threshold of 0.75 because that is what I had seen in tutorials. The results were terrible — returning tangentially related content that technically scored high but had nothing useful to offer the actual question. When I tightened it to 0.85, precision improved dramatically but I started getting empty result sets on niche queries.

There is no formula. You calibrate through evaluation.

-- Start by logging what scores your real queries produce
-- before deciding on a threshold
SELECT
  chunk.content,
  1 - (chunk.embedding <=> query_embedding) AS similarity,
  chunk.id
FROM document_chunks AS chunk
ORDER BY chunk.embedding <=> query_embedding
LIMIT 20;
 
-- Look at the score distribution across many real queries
-- before picking a cutoff

A practical approach: log the similarity scores of retrieved chunks alongside whether users found the result useful (thumbs up, clicked, ignored). After a few hundred interactions you have empirical data to set your threshold against.

The other calibration that matters is how many results you retrieve before passing to the LLM (LIMIT). Retrieving 20 chunks and then summarizing all of them is different from retrieving the top 5. More context means more token cost and more noise. Fewer means you risk missing the right chunk. I have settled on retrieving 8 to 12 candidates and then doing a lightweight re-ranking pass before passing to the LLM, but that number depends entirely on how dense your content is.

Supabase-specific realities that the docs underplay

Since Supabase is the most common way to run pgvector outside of managing raw Postgres yourself, and because I use it, there are a few things worth knowing that the getting-started guides gloss over.

RLS interacts with vector search in ways that can surprise you

If you use Row Level Security on your document_chunks table (and you probably should in a multi-tenant setup), you need to think carefully about how your match_documents RPC function is defined. A function with SECURITY DEFINER runs as the function owner and bypasses RLS. A function with SECURITY INVOKER runs as the calling user and respects RLS.

-- This bypasses RLS -- every user can see every chunk
CREATE OR REPLACE FUNCTION match_documents(
  query_embedding vector(1536),
  match_threshold float,
  match_count int
)
RETURNS TABLE (...)
LANGUAGE sql
STABLE
SECURITY DEFINER -- <-- bypasses RLS
AS $$
  SELECT ...
$$;
 
-- This respects RLS -- the calling user can only see their own data
CREATE OR REPLACE FUNCTION match_documents(...)
RETURNS TABLE (...)
LANGUAGE sql
STABLE
SECURITY INVOKER -- <-- respects RLS
AS $$
  SELECT ...
$$;

If you built on a tutorial that used SECURITY DEFINER and you have multi-tenant data, you may be silently leaking documents between tenants. Check this.

The connection pooler affects concurrent embedding jobs

Supabase’s connection pooler (Supavisor in transaction mode) is what most client connections go through. Transaction mode pooling works fine for short queries, but if you are running a bulk embedding job that opens a connection, embeds 10,000 rows one by one, and keeps the connection alive throughout, you will run into pooler timeouts or you will exhaust the connection pool for other users.

For bulk embedding jobs, use a direct connection string (Supabase provides one) that bypasses the pooler. For production query traffic, use the pooled connection as usual.

// For bulk operations: use the direct connection URL
// supabase.co:5432 (direct, bypasses pooler)
const bulkClient = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_KEY!,
  {
    db: { schema: 'public' },
    // Use the direct connection for long-running operations
  }
);
 
// For real-time queries: use the pooled connection URL
// supabase.co:6543 (pooled, through Supavisor)
const queryClient = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_ANON_KEY!
);

The match_documents function pattern that actually works

Most tutorials show a basic version of this function. Here is a production-ready version that handles threshold filtering, a match_count limit, and returns metadata you actually need for attribution in your RAG pipeline:

CREATE OR REPLACE FUNCTION match_documents(
  query_embedding   vector(1536),
  match_threshold   float DEFAULT 0.78,
  match_count       int   DEFAULT 10,
  filter_source     text  DEFAULT NULL
)
RETURNS TABLE (
  id            bigint,
  document_id   bigint,
  content       text,
  similarity    float,
  metadata      jsonb
)
LANGUAGE sql
STABLE
SECURITY INVOKER
AS $$
  SELECT
    dc.id,
    dc.document_id,
    dc.content,
    1 - (dc.embedding <=> query_embedding) AS similarity,
    d.metadata
  FROM document_chunks dc
  JOIN documents d ON d.id = dc.document_id
  WHERE
    1 - (dc.embedding <=> query_embedding) > match_threshold
    AND (filter_source IS NULL OR d.source = filter_source)
  ORDER BY dc.embedding <=> query_embedding
  LIMIT match_count;
$$;

Two things to note: the WHERE clause on similarity threshold means the index scan can still filter early. The optional filter_source lets you scope retrieval to a specific content type without changing the function signature every time.

The two-pass retrieval pattern for better precision

One of the most underused patterns in pgvector-backed RAG is doing a coarse retrieval pass followed by a re-ranking pass before sending context to the LLM.

The idea: retrieve more candidates than you need (say, 20 or 30), then re-rank them using a more expensive but more accurate method (a cross-encoder model, or even a second LLM call), and pass only the top results to the final generation step.

// Step 1: coarse retrieval -- fast, uses pgvector index
const { data: candidates } = await supabase.rpc('match_documents', {
  query_embedding: queryEmbedding,
  match_threshold: 0.70, // lower threshold to cast a wider net
  match_count: 25,
});
 
// Step 2: re-rank candidates -- more expensive but runs on a small set
const reranked = await rerankWithCrossEncoder(query, candidates);
 
// Step 3: pass only the top N to the LLM
const context = reranked.slice(0, 8);

The cross-encoder reads the query and each candidate passage together, which gives it context that the bi-encoder (your embedding model) never had. The result is meaningfully better precision, especially for short or ambiguous queries where cosine distance alone struggles.

For budget-conscious setups, you can skip a dedicated cross-encoder and do a simpler keyword-overlap re-rank, or use a cheap LLM call to score relevance. The two-pass structure matters more than the specific re-ranker you use.

When pgvector is not the right tool

This section exists because credibility requires honesty.

pgvector is the right default for most applications that already run on Postgres. But there are real cases where you should reach for something else.

You have hundreds of millions of vectors with strict sub-10ms SLA requirements. pgvector can get fast, but purpose-built vector databases like Qdrant or Weaviate are built from the ground up for this. At that scale, the operational tooling and SIMD-optimized search paths in a dedicated system start to pull ahead.

You need multi-region active-active with per-tenant isolation. pgvector inherits Postgres replication semantics. If your architecture requires independent regional replicas with no coordination overhead, that is a hard fit for Postgres regardless of the extension.

Your embedding pipeline is your main product. If you are building a platform that does ingestion, chunking, embedding, and retrieval as a managed service for others, you probably want something with built-in pipelines rather than assembling the stack yourself.

For everything else — a RAG assistant on your website, a semantic search bar in a SaaS product, a document retrieval system for an internal tool — pgvector on Postgres is not a compromise. It is the right answer.

The schema I actually use

For reference, here is the shape of what I run in production. This is not boilerplate — every field earns its place.

CREATE EXTENSION IF NOT EXISTS vector;
 
-- Parent documents: whatever you are making searchable
CREATE TABLE documents (
  id          BIGSERIAL PRIMARY KEY,
  source      TEXT NOT NULL,           -- 'blog', 'guide', 'faq', etc.
  external_id TEXT,                    -- slug, URL, or external ID
  title       TEXT,
  metadata    JSONB DEFAULT '{}',
  created_at  TIMESTAMPTZ DEFAULT now(),
  updated_at  TIMESTAMPTZ DEFAULT now()
);
 
-- Chunks: the actual units retrieved during search
CREATE TABLE document_chunks (
  id           BIGSERIAL PRIMARY KEY,
  document_id  BIGINT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  chunk_index  INT NOT NULL,
  content      TEXT NOT NULL,
  token_count  INT,                    -- useful for context window budgeting
  embedding    vector(1536) NOT NULL,
  model_id     TEXT NOT NULL DEFAULT 'text-embedding-3-small',
  created_at   TIMESTAMPTZ DEFAULT now(),
  UNIQUE (document_id, chunk_index)
);
 
-- HNSW index on cosine distance
CREATE INDEX document_chunks_embedding_idx
ON document_chunks
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 128);
 
-- Supporting indexes
CREATE INDEX document_chunks_document_id_idx ON document_chunks (document_id);
CREATE INDEX documents_source_idx ON documents (source);

The token_count column might look optional, but it is not once you are managing a real context window. Knowing how many tokens each chunk consumes lets you pack the retrieval results into the LLM’s context budget precisely rather than guessing.

Observability: know when things go wrong before your users do

The most dangerous failure mode in a production RAG system is silent recall degradation. The system keeps returning results. They just are not the right ones. No error is thrown. No alert fires.

A few metrics worth tracking:

Buffer cache hit ratio. If your HNSW index is spilling to disk on reads, query latency will jump and you will see this in your Postgres stats. On Supabase, you can query pg_statio_user_indexes to see buffer hits vs disk reads per index.

SELECT
  indexrelname,
  idx_blks_hit,
  idx_blks_read,
  round(idx_blks_hit::numeric / nullif(idx_blks_hit + idx_blks_read, 0) * 100, 1) AS cache_hit_pct
FROM pg_statio_user_indexes
WHERE indexrelname LIKE '%embedding%';

A healthy number here is above 95%. If it drops, your index is not fitting in shared buffers and you need either a larger instance or to look at quantization.

Query latency by similarity threshold. Log the latency and result count of every vector search your application makes. A sudden increase in empty result sets (after threshold filtering) usually means either your embedding model changed, your content drifted from your queries, or your index needs a rebuild.

Index bloat from deletes. If you regularly delete or update chunks, the HNSW graph accumulates dead entries. Recall stays acceptable for a while, then drops. Schedule REINDEX CONCURRENTLY during low-traffic windows.

-- Run this during off-peak hours, not in a transaction
REINDEX INDEX CONCURRENTLY document_chunks_embedding_idx;

The honest verdict

pgvector is genuinely production-ready. I use it. It works. The gap between “it works in a demo” and “it works reliably in production” is not about the extension itself — it is about the decisions you make around it.

Choose HNSW unless you have a specific reason not to. Version your embedding model in the schema from day one. Calibrate your similarity threshold against real queries, not benchmarks. If you are on Supabase, be deliberate about SECURITY DEFINER vs SECURITY INVOKER on your RPC functions, and separate your bulk embedding jobs from your pooled connection string.

None of these are deep secrets. They are just things that rarely make it into the quick-start guide, and that is exactly where you need them most.

If you are building with pgvector right now, I would genuinely like to know what you are running into. The gotchas above cover my experience — yours might add a few more chapters.