---

name: pgvector description: >- pgvector is a PostgreSQL extension that stores vector embeddings in ordinary tables and searches them by similarity, so no separate vector database is needed. Use when someone asks for "vector search in Postgres", "store embeddings", "pgvector", "similarity search", "RAG with Postgres", "semantic search in an existing database", or "HNSW vs IVFFlat". Covers vector columns, indexes, filtered search, and Node.js and Drizzle usage. license: Apache-2.0 compatibility: "PostgreSQL 13+ with pgvector 0.8.x (managed on Supabase, Neon, RDS, Cloud SQL and others, or self-installed). Clients for Node.js, Python and most languages." metadata: author: terminal-skills version: "1.1.0" category: data-ai repository: https://github.com/pgvector/pgvector tags: ["vector", "embeddings", "postgres", "pgvector", "rag"]

pgvector

Overview

pgvector adds vector types, distance operators and approximate indexes to PostgreSQL. Embeddings sit next to your relational data, so filters, joins, transactions and backups work as usual. Types: vector (up to 16,000 dimensions stored, 2,000 indexable), halfvec (half precision, 4,000 indexable), bit (binary) and sparsevec. Distance operators: <-> L2, <=> cosine, <#> negative inner product, <+> L1, <~> Hamming, <%> Jaccard. Latest release at the time of writing: 0.8.7 (2026-10-01); 0.8.x fixed several HNSW and IVFFlat bugs, so use the newest patch your provider offers.

Without an index, search is exact (perfect recall). With HNSW or IVFFlat it is approximate: results can differ from the exact answer.

Instructions

Install and enable

Hosted Postgres usually has it already; run CREATE EXTENSION vector; once per database. Self-hosted options: the pgvector/pgvector Docker image (for example pgvector/pgvector:pg17), apt install postgresql-17-pgvector from the PGDG repository, Homebrew, or building the tagged source (git clone --branch v0.8.7 https://github.com/pgvector/pgvector.git && make && make install). Check the version with SELECT extversion FROM pg_extension WHERE extname = 'vector';.

Schema and index

CREATE TABLE documents (
  id         bigserial PRIMARY KEY,
  tenant_id  int NOT NULL,
  title      text NOT NULL,
  content    text NOT NULL,
  metadata   jsonb NOT NULL DEFAULT '{}',
  embedding  vector(1536) NOT NULL,      -- must equal the embedding model's output size
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);
CREATE INDEX ON documents (tenant_id);   -- supports exact filtered search

Pick the operator class that matches the operator you query with: vector_cosine_ops for <=>, vector_l2_ops for <->, vector_ip_ops for <#>. Use halfvec_cosine_ops on halfvec columns. HNSW has better speed/recall than IVFFlat but builds slower and uses more memory; it needs no training data, so it can be created on an empty table. IVFFlat (USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100)) must be built after the data is loaded, and its quality depends on that data. In production build with CREATE INDEX CONCURRENTLY and raise maintenance_work_mem so the HNSW graph fits in memory.

For embeddings above 2,000 dimensions, store halfvec (index up to 4,000) or index a cast expression: CREATE INDEX ON documents USING hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops); and query with the same cast.

Query

-- nearest 5 by cosine distance; similarity = 1 - distance
SELECT id, title, 1 - (embedding <=> $1) AS similarity
FROM documents
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 5;

An index is used only for ORDER BY embedding <=> $1 LIMIT n (an ORDER BY on the distance operator). Tune recall per query inside a transaction: SET LOCAL hnsw.ef_search = 100; (default 40) or, for IVFFlat, SET LOCAL ivfflat.probes = 10; (default 1).

Approximate indexes filter after the scan, so a selective WHERE can return fewer than LIMIT rows. Since 0.8.0, enable iterative scans: SET LOCAL hnsw.iterative_scan = relaxed_order; (or strict_order; ivfflat.iterative_scan = relaxed_order). Other options: a B-tree on the filter column, partial indexes for a few fixed values, or partitioning by tenant. Check with EXPLAIN (ANALYZE, BUFFERS).

Node.js

// db.ts - node-postgres with pgvector-node (npm install pg pgvector openai)
import pg from "pg";
import pgvector from "pgvector/pg";
import OpenAI from "openai";

export const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
});
pool.on("connect", (client) => pgvector.registerTypes(client));
const openai = new OpenAI(); // reads OPENAI_API_KEY

export async function embed(text: string): Promise<number[]> {
  const res = await openai.embeddings.create({ model: "text-embedding-3-small", input: text });
  return res.data[0].embedding; // 1536 dimensions
}

export async function storeDocument(tenantId: number, title: string, content: string) {
  await pool.query(
    "INSERT INTO documents (tenant_id, title, content, embedding) VALUES ($1, $2, $3, $4)",
    [tenantId, title, content, pgvector.toSql(await embed(content))],
  );
}

export async function semanticSearch(tenantId: number, query: string, limit = 5) {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await client.query("SET LOCAL hnsw.iterative_scan = relaxed_order");
    const { rows } = await client.query(
      `SELECT id, title, content, 1 - (embedding <=> $1) AS similarity
       FROM documents WHERE tenant_id = $2 ORDER BY embedding <=> $1 LIMIT $3`,
      [pgvector.toSql(await embed(query)), tenantId, limit],
    );
    await client.query("COMMIT");
    return rows;
  } finally {
    client.release();
  }
}

Apply a similarity cutoff in application code on the returned rows rather than in WHERE.

RAG

Retrieve with semanticSearch, join the chunks into the prompt, and ask the chat model to answer only from them. Chunk documents to roughly 300-800 tokens with a little overlap before embedding, store the source URL in metadata, and return it so answers can cite it.

Drizzle ORM

Drizzle has a native column type; the old hand-written customType is not needed. Create the extension in a custom migration (npx drizzle-kit generate --custom, then CREATE EXTENSION vector;).

import { index, pgTable, serial, text, vector } from "drizzle-orm/pg-core";
import { cosineDistance, desc, gt, sql } from "drizzle-orm";

export const guides = pgTable("guides", {
  id: serial("id").primaryKey(),
  title: text("title").notNull(),
  embedding: vector("embedding", { dimensions: 1536 }),
}, (t) => [index("guides_embedding_idx").using("hnsw", t.embedding.op("vector_cosine_ops"))]);

const similarity = sql<number>`1 - (${cosineDistance(guides.embedding, queryEmbedding)})`;
const rows = await db.select({ title: guides.title, similarity }).from(guides)
  .where(gt(similarity, 0.5)).orderBy(desc(similarity)).limit(5);

Examples

Example 1: Semantic search over existing articles

Request: "I have a Postgres database with articles. Add search by meaning, not just keywords."

Run ALTER TABLE articles ADD COLUMN embedding vector(1536);, backfill in batches of 100 rows by embedding title || E'\n' || body with text-embedding-3-small, then CREATE INDEX CONCURRENTLY ON articles USING hnsw (embedding vector_cosine_ops);. Expose GET /search?q= that embeds the query and runs the ORDER BY embedding <=> $1 LIMIT 10 query, keeping existing filters such as published = true in the same statement. For keyword plus meaning, combine with Postgres full-text search and merge results with reciprocal rank fusion.

Example 2: Q&A over internal docs

Request: "Build a Q&A bot over our handbook using the Postgres we already have."

Split each handbook page into chunks, store them in documents with tenant_id and metadata->>'url', fetch the top 5 with semanticSearch, and send them as context to the chat model with an instruction to say "not in the handbook" when the context lacks the answer. Result: answers with links to the source pages, and re-ingestion is an UPDATE.

Guidelines

• Dimensions must match the model: text-embedding-3-small returns 1536 by default, text-embedding-3-large 3072. Changing models means re-embedding everything; vectors from different models are not comparable.
• OpenAI embeddings are normalized, so inner product (<#>, with the sign flipped) is slightly faster than cosine.
• Never write WHERE distance < x and expect it to fix recall; filters run after an approximate scan. Use iterative scans, partial indexes or partitions.
• Load bulk data with COPY and create IVFFlat indexes afterwards. HNSW vacuum is slow; REINDEX INDEX CONCURRENTLY before VACUUM helps.
• Memory matters: an HNSW index that does not fit in RAM is slow. Use halfvec or binary quantization with re-ranking at large scale.
• Do not pass user-supplied text into SQL strings; use parameters. Treat retrieved chunks as untrusted when building prompts.
• At hundreds of millions of vectors, or when you need distributed ANN, a dedicated vector database may fit better.