Claude Code with pgvector: Vector Search Inside Postgres
Why pgvector without CLAUDE.md returns slow, irrelevant results
pgvector is the simplest production-ready vector database available to teams that already run Postgres in 2026. The extension adds a vector data type, three distance operators, and two index types (IVFFlat and HNSW). The simplicity is also the trap when Claude Code touches it. Without explicit constraints, Claude generates schemas with the wrong vector dimension, queries that pick the wrong distance operator, sequential scans because the index is missing or unused, and result sets that mix unranked similarity scores with normal SQL filters in ways that defeat the index.
The most common Claude defaults that hurt pgvector performance: declaring vector(1536) when the embedding model is OpenAI's text-embedding-3-small at 1536 dimensions but the model used in the app is actually text-embedding-3-large at 3072, using the L2 distance operator <-> when the embeddings were normalised for cosine similarity, putting the ORDER BY embedding <=> :query clause after a WHERE filter on a non-indexed column (which forces a full table scan before similarity ranking), and creating an IVFFlat index without setting lists to a reasonable value (Claude defaults to the GitHub example's lists = 100 regardless of table size).
This guide covers the CLAUDE.md configuration that locks Claude Code into pgvector's correct model: the embedding dimension declared once and referenced everywhere, the distance operator that matches the embedding model's normalisation, HNSW for production search, and the hybrid search pattern that combines vector similarity with full-text relevance. If you are building the Postgres layer that pgvector sits inside, Claude Code with PostgreSQL covers the broader database setup. For a Supabase-hosted Postgres, Claude Code with Supabase shows the platform-specific pgvector activation.
The pgvector CLAUDE.md template
The CLAUDE.md at your project root is read at the start of every Claude Code session. For a pgvector integration it needs to declare: the embedding model and its exact dimension, the distance operator policy, the index type and tuning parameters, the query pattern for similarity search, and the hard rules that block the mistakes Claude makes most often.
# pgvector rules
## Stack
- PostgreSQL 16.x with pgvector extension ^0.7
- Embeddings: text-embedding-3-small (1536 dims, cosine-normalised)
- Driver: pg ^8.x (node-postgres) or asyncpg ^0.29 (Python)
- Connection pooling: PgBouncer in transaction mode
## Embedding model (HARD CONSTRAINT)
- Model: text-embedding-3-small
- Dimensions: 1536 (NEVER deviate, EVERY vector column must match)
- Normalisation: model outputs unit-normalised vectors (use cosine distance)
- API call: OpenAI embeddings endpoint, batch of 100 max per call
If the model changes:
1. Update EMBEDDING_DIMENSIONS constant in src/lib/embeddings.ts
2. Run migration to ALTER COLUMN vector type
3. Re-embed entire corpus, NEVER mix dimensions in one table
## Schema patterns
- Vector columns: vector(1536) NOT NULL (always size-bound, never untyped)
- ALWAYS pair vector column with a text source column (content, chunk_text)
- ALWAYS add created_at TIMESTAMPTZ for staleness checks
- ALWAYS add tenant_id UUID NOT NULL for multi-tenant tables
## Distance operator (MATCH the embedding normalisation)
- text-embedding-3-* are cosine-normalised: use <=> (cosine distance)
- Raw / unnormalised embeddings: use <-> (L2 distance)
- Maximum inner product (legacy): use <#> with NEGATIVE sign caveat
NEVER mix operators on the same vector column. Pick once, document, enforce.
## Index strategy
- Tables < 10k rows: NO index, sequential scan is faster
- Tables 10k - 1M rows: HNSW with m = 16, ef_construction = 64
- Tables 1M - 50M rows: HNSW with m = 32, ef_construction = 128
- Tables > 50M rows: IVFFlat with lists = sqrt(row_count), partial indexes per tenant
- ALWAYS run ANALYZE after bulk insert before benchmarking query plans
## Query pattern (MANDATORY)
For similarity search with filters:
- Put indexable WHERE conditions BEFORE the ORDER BY
- Filters on indexed columns (tenant_id, user_id) MUST come first
- Use LIMIT, never SELECT all matches
- ALWAYS verify the plan uses Index Scan, never Seq Scan in production
## Connection pooling
- PgBouncer transaction mode breaks prepared statements with vector parameters
- ALWAYS set statement_cache_size = 0 in asyncpg, or use simple query mode
- For pg (node-postgres): disable parse phase caching for vector queries
## Hard rules
- NEVER store unnormalised embeddings in a column declared for cosine search
- NEVER omit the vector dimension from the column type (vector vs vector(1536))
- NEVER run similarity search without a LIMIT clause
- NEVER mix embeddings from different models in the same table
- ALWAYS create the HNSW index AFTER the bulk load, not before
- ALWAYS rebuild ANALYZE statistics after bulk inserts of > 10% of rows
Five rules here prevent the majority of production failures Claude generates without them.
The dimension constant rule prevents the silent schema drift that breaks production. An embedding column declared as vector(1536) rejects inserts of any other size. But Claude often generates vector (no dimension) which accepts any size and lets mismatched embeddings into the table, where they fail similarity comparisons silently with confusing distance values. Declaring the exact dimension once and referring to it everywhere keeps the schema and the application code in sync.
The distance operator matching the normalisation rule prevents the most common ranking bug. OpenAI's text-embedding-3-* models output unit-normalised vectors. Cosine distance (<=>) is the correct operator. L2 distance (<->) on normalised vectors mathematically reduces to a function of cosine distance but with different numerical properties that can mislead similarity thresholds. Claude picks operators inconsistently because all three look correct in isolation. Locking the choice in CLAUDE.md fixes the inconsistency.
The filter order rule prevents the most common performance bug. Postgres uses the HNSW index to find approximate nearest neighbours, then applies the WHERE filter. If the filter is on an indexed column (tenant_id, user_id), put it first so Postgres can use that index to narrow the search space before the vector index runs. The query planner often picks the wrong order without explicit hints; the rewrite pattern in this guide forces the right shape.
Installation and dimension setup
Install pgvector. On a self-hosted Postgres:
# In the Postgres image or as a build dependency
apt-get install postgresql-16-pgvector
For managed Postgres (Supabase, Neon, AWS RDS, GCP Cloud SQL), enable the extension through the platform UI or via SQL once you have superuser access. Then in your database:
CREATE EXTENSION IF NOT EXISTS vector;
Verify the version:
SELECT extversion FROM pg_extension WHERE extname = 'vector';
-- 0.7.x or higher for HNSW with m and ef_construction tuning
Declare the embedding dimension once in a shared constants file:
// src/lib/embeddings.ts
import OpenAI from 'openai';
export const EMBEDDING_MODEL = 'text-embedding-3-small';
export const EMBEDDING_DIMENSIONS = 1536;
const openai = new OpenAI({ apiKey: process.env.OPENAI_API_KEY });
export async function embed(text: string): Promise<number[]> {
const response = await openai.embeddings.create({
model: EMBEDDING_MODEL,
input: text,
});
const vector = response.data[0].embedding;
if (vector.length !== EMBEDDING_DIMENSIONS) {
throw new Error(
`Embedding dimension mismatch: expected ${EMBEDDING_DIMENSIONS}, got ${vector.length}`
);
}
return vector;
}
export async function embedBatch(texts: string[]): Promise<number[][]> {
if (texts.length === 0) return [];
if (texts.length > 100) {
throw new Error('Batch too large, chunk into 100-item groups');
}
const response = await openai.embeddings.create({
model: EMBEDDING_MODEL,
input: texts,
});
return response.data.map((d) => d.embedding);
}
The dimension check inside embed() is the runtime guard against silent dimension drift. If a future model update changes the dimension, the function throws immediately instead of inserting bad data. Claude omits this check by default because the OpenAI SDK does not enforce it.
Schema and HNSW index
The schema for a document chunks table:
CREATE TABLE document_chunks (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL,
document_id UUID NOT NULL,
chunk_index INT NOT NULL,
content TEXT NOT NULL,
embedding vector(1536) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
metadata JSONB NOT NULL DEFAULT '{}'::jsonb
);
CREATE INDEX idx_chunks_tenant ON document_chunks (tenant_id);
CREATE INDEX idx_chunks_document ON document_chunks (document_id);
After bulk loading the table (not before), create the HNSW index:
SET maintenance_work_mem = '2GB';
CREATE INDEX idx_chunks_embedding ON document_chunks
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
ANALYZE document_chunks;
Two things matter about this index creation step.
First, create the index after the bulk load. Building an HNSW index incrementally on insert is much slower than building it once at the end. For a fresh ingestion of millions of rows, expect to load the data first, then build the index in a single statement. The maintenance_work_mem bump speeds up the build.
Second, the operator class (vector_cosine_ops) determines which distance operator the index supports. Pick vector_cosine_ops for cosine distance (<=>), vector_l2_ops for L2 (<->), or vector_ip_ops for inner product (<#>). The index only accelerates queries that use the matching operator. Claude often creates vector_l2_ops indexes regardless of the query pattern because the GitHub example uses L2.
Add a schema section to CLAUDE.md:
## Schema rules
- Every vector column MUST declare its dimension: vector(1536)
- Every vector column MUST be NOT NULL
- Every chunks/embeddings table MUST have tenant_id UUID NOT NULL
- Every chunks/embeddings table MUST have created_at TIMESTAMPTZ
- HNSW index operator class MUST match the runtime distance operator
- HNSW index is created AFTER bulk load, not before
- ALWAYS run ANALYZE after creating or rebuilding the HNSW index
Tuning HNSW parameters
The HNSW index has two build-time parameters and one query-time parameter that control the speed vs recall tradeoff.
m (build time): the maximum number of connections each node can have. Higher m improves recall but uses more memory and slower writes. Defaults from the GitHub README are conservative; production tables benefit from larger values.
ef_construction (build time): the size of the dynamic candidate list during index build. Higher values build a more accurate index at the cost of build time. This is a one-time cost so set it generously.
ef_search (query time): the size of the candidate list during search. Higher values improve recall at the cost of query time. Set per session.
Sensible defaults by table size:
| Rows | m | ef_construction | ef_search |
|---|---|---|---|
| 10k to 1M | 16 | 64 | 40 |
| 1M to 10M | 32 | 128 | 80 |
| 10M to 50M | 32 | 256 | 100 |
| 50M+ | Consider IVFFlat or sharding |
Setting ef_search for a session:
SET hnsw.ef_search = 100;
SELECT id, content, embedding <=> :query AS distance
FROM document_chunks
WHERE tenant_id = :tenant_id
ORDER BY embedding <=> :query
LIMIT 20;
Add a tuning section to CLAUDE.md:
## HNSW tuning
Build-time:
m = 16, ef_construction = 64 : tables 10k - 1M rows
m = 32, ef_construction = 128 : tables 1M - 10M rows
m = 32, ef_construction = 256 : tables 10M - 50M rows
Query-time:
SET hnsw.ef_search = 40 : default, fast
SET hnsw.ef_search = 80 : higher recall, ~2x slower
SET hnsw.ef_search = 200 : maximum recall, slowest
Set ef_search per session, NEVER hardcode in the SQL string.
For batch backfill: SET hnsw.ef_search = 200 to maximise recall.
The similarity query pattern
The query pattern that uses the index and produces ranked results:
// src/lib/search.ts
import { Pool } from 'pg';
import { embed } from './embeddings';
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
});
export async function search(
tenantId: string,
query: string,
limit = 20
): Promise<Array<{ id: string; content: string; distance: number }>> {
const queryVector = await embed(query);
// Convert the JS array to the vector literal format pgvector expects
const vectorLiteral = `[${queryVector.join(',')}]`;
const result = await pool.query(
`
SELECT
id,
content,
embedding <=> $1::vector AS distance
FROM document_chunks
WHERE tenant_id = $2
ORDER BY embedding <=> $1::vector
LIMIT $3
`,
[vectorLiteral, tenantId, limit]
);
return result.rows;
}
Three things to notice.
The cast to ::vector after the parameter placeholder is required when the driver sends the parameter as text. Without the cast, Postgres treats the parameter as text and refuses to use the index. Claude sometimes omits this cast and ends up with a sequential scan that still returns correct results, just slowly.
The tenant_id filter sits in the WHERE clause before the ORDER BY. The index on tenant_id narrows the rows before the HNSW index ranks them. Putting the tenant filter inside a CTE or after the ORDER BY (which Claude sometimes generates) forces Postgres to rank the entire table first and then filter, which defeats the purpose of indexing tenant_id.
The LIMIT is essential. Vector similarity queries without a limit return every row sorted by distance. For a million-row table, that is gigabytes of data returned to the application for no purpose.
Hybrid search with reciprocal rank fusion
Pure vector similarity is excellent at semantic matching but weak at exact term matching. A user searching for "Stripe webhook signature" expects results that mention "Stripe" and "webhook" literally, not just semantically. Hybrid search combines vector similarity with Postgres full-text search and merges the rankings.
The reciprocal rank fusion pattern:
WITH vector_results AS (
SELECT
id,
ROW_NUMBER() OVER (ORDER BY embedding <=> $1::vector) AS rank
FROM document_chunks
WHERE tenant_id = $2
ORDER BY embedding <=> $1::vector
LIMIT 50
),
text_results AS (
SELECT
id,
ROW_NUMBER() OVER (
ORDER BY ts_rank_cd(to_tsvector('english', content), plainto_tsquery('english', $3)) DESC
) AS rank
FROM document_chunks
WHERE
tenant_id = $2
AND to_tsvector('english', content) @@ plainto_tsquery('english', $3)
LIMIT 50
),
fused AS (
SELECT
COALESCE(v.id, t.id) AS id,
COALESCE(1.0 / (60 + v.rank), 0) + COALESCE(1.0 / (60 + t.rank), 0) AS score
FROM vector_results v
FULL OUTER JOIN text_results t USING (id)
)
SELECT c.id, c.content, f.score
FROM fused f
JOIN document_chunks c ON c.id = f.id
ORDER BY f.score DESC
LIMIT 20;
The constant 60 in 1.0 / (60 + rank) is the standard RRF parameter from the original paper. Higher values flatten the ranking, lower values give more weight to the top results from each method. 60 is the published default and works well for most workloads.
Add a hybrid search section to CLAUDE.md:
## Hybrid search
- Vector search alone: weak on exact term matches (model names, codes, IDs)
- Full-text alone: weak on semantic matches (paraphrases, synonyms)
- Hybrid: RRF fusion of both, gives best practical recall
Pattern:
- Vector branch: HNSW + cosine, LIMIT 50
- Text branch: GIN index on to_tsvector(content), LIMIT 50
- Fusion: 1 / (60 + rank), sum across branches
- Final: ORDER BY fused score, LIMIT user-facing N
Required indexes:
- HNSW on embedding (vector_cosine_ops)
- GIN on to_tsvector('english', content)
- B-tree on tenant_id
Run BOTH branches in parallel via UNION ALL or CTEs, NEVER sequentially.
The GIN index for full-text search:
CREATE INDEX idx_chunks_content_fts ON document_chunks
USING GIN (to_tsvector('english', content));
Connection pooling and prepared statements
Production Postgres deployments use PgBouncer for connection pooling. PgBouncer's transaction pooling mode (the default for most production setups) does not preserve session state between transactions. Prepared statements created in one transaction are not visible in the next.
For vector queries this matters because most drivers (asyncpg, pg-node with pgsql parsing) cache prepared statements per connection. When PgBouncer switches the underlying connection between transactions, the cached statement reference becomes stale and the query fails with prepared statement "S_1" does not exist.
The fix depends on the driver.
asyncpg (Python): set statement_cache_size=0 on the connection or pool:
import asyncpg
pool = await asyncpg.create_pool(
dsn=DATABASE_URL,
statement_cache_size=0, # required for PgBouncer transaction mode
min_size=4,
max_size=20,
)
pg / node-postgres: the driver does not cache prepared statements by default in simple query mode, but if you opt into named prepared statements with pg-extended or pg-cursor, switch to unnamed parameterised queries instead. The pattern shown in the search example earlier ($1::vector) is unnamed and PgBouncer-safe.
Add a pooling section to CLAUDE.md:
## Connection pooling
- Production uses PgBouncer transaction mode by default
- ALWAYS set statement_cache_size = 0 in asyncpg pools
- ALWAYS use unnamed parameterised queries in node-postgres ($1, $2 syntax)
- NEVER opt into pg-cursor or persistent prepared statements behind PgBouncer
- Connection string: include `?pgbouncer=true` for Supabase pooler URLs
For a deeper guide on the broader Postgres connection patterns, Claude Code with PostgreSQL covers the pooling and migration patterns at the database layer.
Common Claude Code mistakes with pgvector
Six patterns Claude generates incorrectly without CLAUDE.md constraints, with the correct replacement for each.
1. Untyped vector column
Claude generates: embedding vector NOT NULL with no dimension.
Correct pattern: embedding vector(1536) NOT NULL so the column rejects inserts of the wrong dimension.
2. Wrong distance operator for the embedding model
Claude generates: ORDER BY embedding <-> :query (L2 distance) for cosine-normalised OpenAI embeddings.
Correct pattern: ORDER BY embedding <=> :query (cosine distance) with a matching vector_cosine_ops index.
3. HNSW index built before bulk load
Claude generates the index definition immediately after the CREATE TABLE statement, then ingests millions of rows one at a time.
Correct pattern: load the table first, build the HNSW index after all data is in place, then ANALYZE.
4. Filter after ORDER BY (no index usage)
Claude generates a subquery that does similarity ranking on the whole table, then filters by tenant_id in the outer query.
Correct pattern: WHERE tenant_id = :id ORDER BY embedding <=> :query LIMIT N, putting the filter in the inner query so Postgres uses the tenant_id index to narrow first.
5. Missing ::vector cast
Claude generates: embedding <=> $1 without casting the parameter to vector.
Correct pattern: embedding <=> $1::vector so the planner recognises the operator type and uses the HNSW index.
6. Prepared statements behind PgBouncer
Claude generates code that relies on driver-level prepared statement caching against a connection string that points to a PgBouncer transaction-mode pooler.
Correct pattern: disable the statement cache or use unnamed parameter syntax, matching the pooling mode in use.
Add a common mistakes section to CLAUDE.md with these six pairs. Claude benefits from explicit before/after comparisons because it can match the pattern it would otherwise generate to the corrected form.
Permission hooks for pgvector scripts
A pgvector project accumulates scripts: embedding backfill jobs, index rebuilds, schema migrations, search benchmark runs. Some are read-only. Some rewrite millions of rows or rebuild indexes that take hours. Permission hooks gate the destructive ones.
In .claude/settings.local.json:
{
"permissions": {
"allow": [
"Bash(npx tsx scripts/search-benchmark.ts*)",
"Bash(npx tsx scripts/verify-dimensions.ts*)",
"Bash(psql $DATABASE_URL -c 'EXPLAIN*"
],
"deny": [
"Bash(npx tsx scripts/backfill-embeddings.ts*)",
"Bash(npx tsx scripts/rebuild-hnsw.ts*)",
"Bash(psql $DATABASE_URL -c 'DROP INDEX*",
"Bash(psql $DATABASE_URL -c 'TRUNCATE*"
]
}
}
Read-only benchmarks and EXPLAIN queries are safe. Backfills that call the embedding API and rewrite every row, index rebuilds that lock the table for hours, and destructive DDL require explicit confirmation. The deny list forces Claude to surface those operations as prompts rather than running them as part of an automated workflow.
Building vector search that actually retrieves the right results
The pgvector CLAUDE.md in this guide produces search implementations where the embedding dimension is fixed and verified at runtime, the distance operator matches the embedding normalisation, the HNSW index is built once after the bulk load with parameters tuned to the row count, the similarity query filters tenant_id before ranking, and hybrid search fuses vector and full-text rankings for production-grade recall.
The underlying principle is the same as any database integration with Claude Code. pgvector without a CLAUDE.md produces queries that look correct, return results, and pass basic tests, but ship to production with the wrong distance operator, no index usage, and silent dimension mismatches. The CLAUDE.md template removes each failure mode by making the correct pattern the only pattern Claude can generate.
For the broader stack that runs around pgvector, Claude Code with Drizzle covers the TypeScript ORM layer that builds these queries safely, and Claudify includes a pgvector-specific CLAUDE.md template with the dimension constants, HNSW tuning table, hybrid search pattern, and all six common-mistake rules pre-configured.
Get Claudify. A complete Claude Code operating system with database and vector search templates ready to drop in.
More like this
Ready to upgrade your Claude Code setup?
Get Claudify
