Vector Search in PostgreSQL with pgvector

You don't always need a separate vector database. The pgvector extension adds a vector type, distance operators and approximate indexes to PostgreSQL, so embeddings live next to the rest of your data.

Set up the table

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE doc_chunks (
  id         BIGSERIAL PRIMARY KEY,
  doc_id     BIGINT NOT NULL,
  content    TEXT NOT NULL,
  embedding  vector(384) NOT NULL,   -- must match your model's dimensions
  tenant_id  BIGINT NOT NULL
);

Distance operators

• <-> Euclidean (L2) distance
• <=> cosine distance (1 - cosine similarity)
• <#> negative inner product

Find the nearest chunks

SELECT id, content, 1 - (embedding <=> $1) AS similarity
FROM doc_chunks
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 5;

From Node.js

import pg from 'pg';
const pool = new pg.Pool();

// pgvector accepts the text form '[0.1,0.2,...]'
const toVector = (arr) => `[${arr.join(',')}]`;

await pool.query('INSERT INTO doc_chunks (doc_id, content, embedding, tenant_id) VALUES ($1, $2, $3, $4)', [
  docId, text, toVector(embedding), tenantId,
]);

const { rows } = await pool.query(
  'SELECT content FROM doc_chunks WHERE tenant_id = $2 ORDER BY embedding <=> $1 LIMIT 5',
  [toVector(queryEmbedding), tenantId]
);

Add an index

Without an index, every query compares against every row. HNSW gives fast approximate search with good recall:

CREATE INDEX doc_chunks_embedding_idx
  ON doc_chunks USING hnsw (embedding vector_cosine_ops);

-- Higher = better recall, slower queries (default 40)
SET hnsw.ef_search = 100;

Use the operator class that matches the operator in your queries (vector_cosine_ops for <=>), or the index won't be used.

Why pgvector is a good default

• Transactions, backups and access control you already have.
• Join vectors with normal columns: tenant filters, dates, permissions.
• Comfortably handles millions of vectors on a single well-sized server.