Common pgvector Pitfalls

This post was written by an engineer at QueryPlane. QueryPlane is an app builder for your database: bring your own postgres db and you can create interactive applications to share with other developers, coworkers or even your customers. If you’re interested in trying it out, get started here.

---

pgvector is straightforward to set up, but there are several mistakes that can hurt performance or produce incorrect results. This post covers the most common pitfalls and how to avoid them.

In this post, we’ll cover:

• Missing indexes - The most common performance killer
• Wrong distance operators - Cosine vs L2 vs inner product
• Dimension mismatches - When your embeddings don’t fit
• Tuning parameters - probes and ef_search settings
• Index build issues - IVFFlat timing, list counts, and CONCURRENTLY
• Edge cases - NULL handling and type mistakes

1. Forgetting to create an index

Without an index, every query scans the entire table:

-- This does a sequential scan on ALL rows
SELECT * FROM documents
ORDER BY embedding <=> $1::vector
LIMIT 5;

For a table with 1 million vectors, this can take 800ms+ per query. With an HNSW index, the same query takes ~8ms.

Fix: Always create an index for production use:

-- HNSW index for cosine similarity
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops);

How to detect: Use EXPLAIN ANALYZE to check if your queries are using the index:

EXPLAIN ANALYZE
SELECT * FROM documents
ORDER BY embedding <=> $1::vector
LIMIT 5;

Look for Index Scan using ... rather than Seq Scan.

2. Using the wrong distance operator

pgvector has three distance operators, and using the wrong one produces poor results:

Operator	Distance	Index ops class
<->	L2 (Euclidean)	vector_l2_ops
<#>	Inner product	vector_ip_ops
<=>	Cosine	vector_cosine_ops

The problem: Most text embedding models (OpenAI, Cohere, etc.) are optimized for cosine similarity. If you use L2 distance (<->), your search results will be wrong.

Fix: Use cosine distance for text embeddings:

-- Wrong for text embeddings
SELECT * FROM documents ORDER BY embedding <-> $1::vector LIMIT 5;

-- Correct for text embeddings
SELECT * FROM documents ORDER BY embedding <=> $1::vector LIMIT 5;

Index alignment: Your index must match your query operator:

-- If you query with <=>, create index with vector_cosine_ops
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- If you query with <->, create index with vector_l2_ops
CREATE INDEX ON documents USING hnsw (embedding vector_l2_ops);

3. Dimension mismatch

The vector dimension in your table must match your embedding model exactly:

-- OpenAI ada-002 produces 1536-dimensional vectors
CREATE TABLE documents (
  id BIGSERIAL PRIMARY KEY,
  embedding vector(384)  -- Wrong! This is 384, not 1536
);

Inserting a 1536-dimensional vector into a vector(384) column will fail.

Fix: Check your embedding model’s documentation:

Model	Dimensions
OpenAI text-embedding-ada-002	1536
OpenAI text-embedding-3-small	1536
OpenAI text-embedding-3-large	3072
Cohere embed-english-v3.0	1024
Sentence Transformers (varies)	384-768

-- Correct for OpenAI ada-002
CREATE TABLE documents (
  id BIGSERIAL PRIMARY KEY,
  embedding vector(1536)
);

4. Not tuning probe/ef_search

pgvector’s approximate indexes trade accuracy for speed. The defaults prioritize speed, which can hurt recall.

IVFFlat: Default probes = 1 only searches 1 cluster out of many:

-- Check how many lists your index has
SELECT indexrelid::regclass, reloptions
FROM pg_index JOIN pg_class ON indexrelid = oid
WHERE indrelid = 'documents'::regclass;

-- Increase probes for better recall (default is 1)
SET ivfflat.probes = 10;

HNSW: Default ef_search = 40 may miss relevant results:

-- Increase ef_search for better recall (default is 40)
SET hnsw.ef_search = 100;

How to tune: Run tests with your actual queries and measure recall vs. latency. Start with higher values and decrease until you find an acceptable tradeoff.

5. Creating IVFFlat index before loading data

IVFFlat builds clusters from existing data. If you create the index on an empty table, it has nothing to cluster:

-- Wrong order
CREATE TABLE documents (id BIGSERIAL PRIMARY KEY, embedding vector(1536));
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
INSERT INTO documents ...  -- Index is useless

-- Correct order
CREATE TABLE documents (id BIGSERIAL PRIMARY KEY, embedding vector(1536));
INSERT INTO documents ...  -- Load your data first
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

Note: HNSW doesn’t have this problem—you can create it on an empty table and it updates incrementally.

6. Wrong number of IVFFlat lists

The lists parameter controls how many clusters IVFFlat creates. Too few or too many hurts performance:

-- Too few lists = large clusters = slow searches
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 10);

-- Too many lists = small clusters = poor recall
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 10000);

Rule of thumb:

• Under 1M rows: lists = rows / 1000
• Over 1M rows: lists = sqrt(rows)

For 1 million vectors: lists = 1000 For 10 million vectors: lists ≈ 3162

7. Not using CONCURRENTLY for production indexes

Creating an index locks the table, blocking writes:

-- Blocks all writes until complete (can take hours on large tables)
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

Fix: Use CONCURRENTLY in production:

-- Allows reads and writes during index creation
CREATE INDEX CONCURRENTLY ON documents USING hnsw (embedding vector_cosine_ops);

Caveats:

• Takes longer than regular index creation
• Cannot run inside a transaction
• If interrupted, leaves an invalid index (check pg_index.indisvalid)

8. Ignoring index build time and memory

HNSW indexes can take a long time to build and use significant memory. Rough estimates (varies by hardware):

Vectors	Dimensions	HNSW Build Time	Index Size
100K	1536	~30s	~200MB
1M	1536	~3min	~2GB
10M	1536	~30min	~20GB

Fix: Plan for index builds during low-traffic periods, and ensure you have enough memory:

-- Increase maintenance_work_mem for faster index builds
SET maintenance_work_mem = '2GB';
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

9. Not handling NULL embeddings

If some rows don’t have embeddings yet, queries can behave unexpectedly:

-- NULLs are excluded from index but included in sequential scans
SELECT * FROM documents WHERE embedding IS NOT NULL
ORDER BY embedding <=> $1::vector
LIMIT 5;

Fix: Either ensure all rows have embeddings, or add explicit NULL checks to your queries.

10. Storing embeddings as arrays instead of vectors

Using FLOAT[] instead of vector loses all the benefits:

-- Wrong: no index support, no distance operators
CREATE TABLE documents (embedding FLOAT[]);

-- Correct: full pgvector functionality
CREATE TABLE documents (embedding vector(1536));

If you have existing data as arrays, convert it:

ALTER TABLE documents
  ALTER COLUMN embedding TYPE vector(1536)
  USING embedding::vector(1536);

Debugging checklist

When pgvector queries are slow or returning bad results:

1. Check for index: SELECT * FROM pg_indexes WHERE tablename = 'your_table';
2. Check index is valid: SELECT indexrelid::regclass, indisvalid FROM pg_index WHERE indrelid = 'your_table'::regclass;
3. Check query plan: EXPLAIN ANALYZE SELECT ... ORDER BY embedding <=> $1 LIMIT 5;
4. Check distance operator matches index: cosine queries need vector_cosine_ops index
5. Check dimension matches: embedding model dimension = table column dimension
6. Tune accuracy: increase ivfflat.probes or hnsw.ef_search

Wrapping up

Most pgvector issues come from a few common mistakes: missing indexes, mismatched dimensions, or forgetting that ANN indexes are approximate. Keep this checklist handy when debugging, and you’ll resolve most problems quickly.