██████╗  ██████╗ ██╗   ██╗███████╗ ██████╗████████╗ ██████╗ ██████╗
██╔══██╗██╔════╝ ██║   ██║██╔════╝██╔════╝╚══██╔══╝██╔═══██╗██╔══██╗
██████╔╝██║  ███╗██║   ██║█████╗  ██║        ██║   ██║   ██║██████╔╝
██╔═══╝ ██║   ██║╚██╗ ██╔╝██╔══╝  ██║        ██║   ██║   ██║██╔══██╗
██║     ╚██████╔╝ ╚████╔╝ ███████╗╚██████╗   ██║   ╚██████╔╝██║  ██║
╚═╝      ╚═════╝   ╚═══╝  ╚══════╝ ╚═════╝   ╚═╝    ╚═════╝ ╚═╝  ╚═╝

pgvector Integration Guide

Focus: Vector search inside your existing Postgres (Supabase or Neon) — the cheapest RAG retrieval layer because it adds no new service. Store embeddings in a vector column, query with distance operators, and index with HNSW. The counterpoint to the managed pinecone guide.

Overview

Here is the whole pgvector journey at a glance — once you see the loop, the SQL below clicks into place.

pgvector is an open-source Postgres extension that adds a vector column type plus similarity-search operators and indexes. Because it lives in Postgres:

• Embeddings sit next to relational data — JOIN a query's nearest neighbors to user/ order rows in one SQL statement.
• RLS policies apply to vectors too — natural multi-tenant isolation.
• One database to back up, secure, and pay for — no separate vector service.

You bring your own embeddings (e.g. via the Vercel AI SDK / Gemini / OpenAI) — pgvector stores and searches them but does not generate them (unlike Pinecone's integrated inference). Both Supabase and Neon ship pgvector; you just enable the extension.

Official Documentation

1. Enable + create the table

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id        bigserial PRIMARY KEY,
  content   text,
  embedding vector(1536)        -- match your embedding model's dimension
);

2. Insert + query (raw SQL)

Distance operators: <-> L2/Euclidean, <=> cosine, <#> negative inner product.

INSERT INTO documents (content, embedding) VALUES ('hello world', '[0.1, 0.2, ...]');

-- nearest neighbors by cosine distance
SELECT id, content
FROM documents
ORDER BY embedding <=> '[0.05, 0.18, ...]'
LIMIT 5;

3. Index for speed (HNSW)

Picking an index is a quick, friendly decision — this tree gets you there in one question.

-- Build an approximate-nearest-neighbor index matching your query operator
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);
-- (IVFFlat is the lighter-weight alternative for smaller / write-heavy sets)

4. Tuning the index — recall vs speed

The defaults work, but every index has build-time knobs (set once, in the CREATE INDEX ... WITH (...)) and a query-time knob (set per session or per query). The query-time knob is the live dial: turn it up for better recall, down for lower latency — no rebuild needed.

This flowchart picks the one knob to reach for first.

HNSW knobs

-- Build-time (set once): higher = better recall, slower build / inserts
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);   -- defaults: m = 16, ef_construction = 64

-- Query-time (live dial): higher = better recall, slower query. Default 40.
SET hnsw.ef_search = 100;                -- whole session
-- ...or just for one query, scoped to a transaction:
BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT id, content FROM documents ORDER BY embedding <=> '[...]' LIMIT 5;
COMMIT;

• Rule of thumb: leave build defaults, then raise hnsw.ef_search until recall is good enough — it's the cheapest dial because it needs no rebuild.

IVFFlat knobs

-- Build-time (set once): lists ≈ rows / 1000 up to 1M rows, sqrt(rows) above 1M.
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);

-- Query-time (live dial): higher = better recall, slower query. Default 1.
SET ivfflat.probes = 10;                 -- whole session
-- ...or per query:
BEGIN;
SET LOCAL ivfflat.probes = 10;
SELECT id, content FROM documents ORDER BY embedding <=> '[...]' LIMIT 5;
COMMIT;

• Rule of thumb: start ivfflat.probes at sqrt(lists); raise toward lists for more recall (probes = lists means exact search).

Gotcha: build an IVFFlat index only after the table has data — it clusters existing rows into lists, so an empty-table build yields poor recall. HNSW has no such requirement, but both build far faster when the index fits in maintenance_work_mem (Postgres warns when it doesn't).

5. Node (node-postgres) — store your own embeddings

import pgvector from "pgvector/pg";
import { embed } from "ai"; // Vercel AI SDK generates the vector

await pgvector.registerTypes(client); // register the vector type once

const { embedding } = await embed({ model: "openai/text-embedding-3-small", value: text });
await client.query("INSERT INTO documents (content, embedding) VALUES ($1, $2)", [
  text,
  pgvector.toSql(embedding),
]);

const { rows } = await client.query(
  "SELECT id, content FROM documents ORDER BY embedding <=> $1 LIMIT 5",
  [pgvector.toSql(queryEmbedding)],
);

pgvector also has adapters for Prisma, Drizzle, Sequelize, and TypeORM; Python uses the pgvector package (+ Supabase's vecs client).

codeAmani notes

• Cheapest by default: reuses the Supabase/Neon Postgres already in the stack — no extra service, no extra bill. Best fit for SME-scale features and the cost-conscious AFRICAN_MARKET_GUIDE.md audience. Start here; graduate to pinecone only if you outgrow it.
• Colocate + isolate: keep embeddings in the same DB as their source rows so you can JOIN retrieval results to business data, and use RLS for per-tenant separation (see supabase / neon guides).
• Bring your own embeddings: generate vectors with the Vercel AI SDK (embed) — keep the dimension in the vector(N) column in sync with the model (e.g. 1536). Mismatches error.
• Index choice: HNSW for best recall/latency on read-heavy sets; IVFFlat when the set is smaller or write-heavy. Always match the index ops class to your distance operator.
• Security: the DB connection string / keys stay server-side (.env.local / Vercel env, or Infisical). RLS still applies — don't bypass it with the service role for vector queries.