pgvector Integration Guide

Technology: pgvector · Category: database · Last reviewed: 2026-08-23

Source: https://tech-stack.codeamanilabs.org/guide/pgvector

Insight:

The cheapest retrieval layer: pgvector is a Postgres extension, so vectors live in the Supabase/Neon database you already pay for — next to your relational rows. That unlocks SQL joins between embeddings and business data and RLS for per-tenant isolation, all under one backup and one bill. The trade vs. a dedicated vector DB (see [[pinecone]]): you bring your own embeddings (no integrated inference) and tune the index (HNSW vs IVFFlat) yourself. Ideal up to a few million vectors; beyond that, reach for Pinecone.

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

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.

flowchart LR
  A["Your text"] --> B["Embedding model<br/>Vercel AI SDK"]
  B --> C["Vector"]
  C --> D["Postgres documents table<br/>vector column"]
  E["User query"] --> B
  D --> F["Similarity search<br/>distance operator"]
  F --> G["Nearest neighbors<br/>JOIN to business rows"]

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

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. For local dev run the identical extension in a container — pgvector/pgvector:0.8.6-pg18-trixie (see the [[local-database]] guide) — so your HNSW index and operator choices are tested offline before they hit a hosted DB.

Current version: pgvector 0.8.6 (verify with SELECT extversion FROM pg_extension WHERE extname = 'vector';). Since 0.7.0 pgvector also ships halfvec (16-bit float), bit (binary), and sparsevec types plus the <+> L1 operator; 0.6.0 added parallel HNSW builds (2 workers by default) and 0.8.0 added iterative index scans (available but off by default — opt in with SET hnsw.iterative_scan). Supabase/Neon track upstream, but the installed version can lag the tag above.

Official Documentation

Resource URL
pgvector (core) https://github.com/pgvector/pgvector
pgvector-node https://github.com/pgvector/pgvector-node
Supabase pgvector https://supabase.com/docs/guides/database/extensions/pgvector
Neon pgvector https://neon.tech/docs/extensions/pgvector

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, <+> L1/taxicab (since 0.7.0). Cosine (<=>) is the default for text embeddings.

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.

flowchart TD
  Q1{"Read-heavy set?"} -->|"yes"| A["HNSW<br/>best recall and latency"]
  Q1 -->|"smaller or write-heavy"| B["IVFFlat<br/>lighter-weight"]
  A --> C["Match ops class<br/>to distance operator"]
  B --> C

-- 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.

flowchart TD
  Q1{"Recall too low?"} -->|"yes · HNSW"| A["Raise hnsw.ef_search<br/>per query · no rebuild"]
  Q1 -->|"yes · IVFFlat"| B["Raise ivfflat.probes<br/>per query · no rebuild"]
  Q1 -->|"build is the bottleneck"| C["HNSW · lower ef_construction<br/>IVFFlat · fewer lists"]
  A --> D["Still low · rebuild HNSW<br/>with higher m and ef_construction"]

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;

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;

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

Official docs: