pgvector in practice

pgvector is an open-source extension that adds a vector type and similarity search to PostgreSQL. If you already run Postgres, it is often the fastest route to production: your vectors live next to your business data, with the same transactions, backups, permissions and SQL you already know.

Set up

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id          bigserial PRIMARY KEY,
  doc_id      text        NOT NULL,
  chunk_no    int         NOT NULL,
  tenant_id   text        NOT NULL,
  title       text,
  content     text        NOT NULL,
  updated_at  timestamptz NOT NULL DEFAULT now(),
  embedding   vector(1536) NOT NULL,          -- must match your model's dimensions
  UNIQUE (doc_id, chunk_no)
);

Managed Postgres services on the major clouds commonly offer pgvector — check that your provider supports the version you need.

Insert vectors

pgvector accepts vectors written like '[0.1, 0.2, 0.3]', so from Node.js you can pass a JSON array string:

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

await pool.query(
  `INSERT INTO documents (doc_id, chunk_no, tenant_id, title, content, embedding)
   VALUES ($1, $2, $3, $4, $5, $6)
   ON CONFLICT (doc_id, chunk_no)
   DO UPDATE SET content = EXCLUDED.content, embedding = EXCLUDED.embedding, updated_at = now()`,
  [doc.id, chunk.no, tenantId, doc.title, chunk.text, JSON.stringify(vector)]
);

Query: nearest neighbours

pgvector adds distance operators:

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

Without an index this is an exact, brute-force scan — fine for small tables.

Add an HNSW index

CREATE INDEX documents_embedding_hnsw
ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

• The operator class must match the operator you query with: vector_cosine_ops for <=>, vector_l2_ops for <->, vector_ip_ops for <#>.
• Tune recall at query time:

SET hnsw.ef_search = 100;   -- higher = better recall, slower; can be set per session or transaction

Or an IVFFlat index

CREATE INDEX documents_embedding_ivf
ON documents USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 1000);

SET ivfflat.probes = 10;

IVFFlat builds faster and uses less memory than HNSW, but must be created after the table has representative data (it trains its clusters on what's there), and usually needs more tuning for the same recall.

Filtering

Regular WHERE clauses work, and B-tree indexes on filter columns help:

CREATE INDEX ON documents (tenant_id);

With an approximate index, Postgres may scan the vector index first and then apply the filter — so a very selective filter can return fewer than LIMIT rows. Recent pgvector versions add iterative index scans to keep searching until enough rows pass the filter; for very selective filters (such as small tenants), a plain B-tree filter plus exact search can also be the right plan. Check EXPLAIN ANALYZE to see what Postgres chose.

Hybrid search in plain SQL

Postgres full-text search plus pgvector, fused with Reciprocal Rank Fusion:

WITH semantic AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> $1) AS rank
  FROM documents
  WHERE tenant_id = $3
  ORDER BY embedding <=> $1
  LIMIT 50
),
keyword AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY ts_rank_cd(to_tsvector('english', content), query) DESC) AS rank
  FROM documents, plainto_tsquery('english', $2) AS query
  WHERE tenant_id = $3 AND to_tsvector('english', content) @@ query
  ORDER BY ts_rank_cd(to_tsvector('english', content), query) DESC
  LIMIT 50
)
SELECT d.id, d.title,
       COALESCE(1.0 / (60 + s.rank), 0) + COALESCE(1.0 / (60 + k.rank), 0) AS rrf_score
FROM semantic s
FULL OUTER JOIN keyword k ON k.id = s.id
JOIN documents d ON d.id = COALESCE(s.id, k.id)
ORDER BY rrf_score DESC
LIMIT 10;

For production, store the tsvector in a generated column with a GIN index instead of computing it per query.

Saving space

pgvector also offers a half-precision halfvec type and other compact types. Half precision halves storage with little accuracy loss, and indexes on it are smaller too. Indexed vector columns also have dimension limits that differ by type — check the pgvector documentation for your version if your model has many dimensions.

Written by Bipin Singh

Senior Full-Stack Engineer · AI & AWS. I build production search, RAG and AI systems on AWS and Postgres.

Work with me