Vector search in PostgreSQL with pgvector

If your product already runs on PostgreSQL, you probably do not need a separate vector database. The pgvector extension adds vector search to PostgreSQL itself: a vector column type, distance operators and approximate nearest-neighbour indexes. Much of what I ship runs on Postgres-backed Node.js services, and my default for semantic search, recommendations and retrieval-augmented generation is to keep the vectors next to the rows they describe. This post covers the setup end to end.

Why keep vectors in Postgres

A dedicated vector store is one more system to provision, back up, secure and keep in sync. With pgvector you get:

• Joins and filters in the same query. "Nearest chunks for this tenant, published in the last year" is one SQL statement.
• Transactions. The document row and its embedding are written together, so they never drift apart.
• One backup, one access-control model, one connection pool.

The trade-off is that at very large scale (hundreds of millions of vectors, heavy write load) a specialised engine can be easier to tune. Most products never get there, and moving later is a data migration you can plan for.

Setup: extension, column and index

Enable the extension, add a column with a fixed dimension, and build an index. The dimension has to match your embedding model's output; 1536 below is a placeholder.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
  id          bigserial PRIMARY KEY,
  tenant_id   uuid NOT NULL,
  source      text NOT NULL,
  content     text NOT NULL,
  embedding   vector(1536) NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

-- HNSW: better recall/speed trade-off, slower to build, more memory
CREATE INDEX chunks_embedding_hnsw
  ON chunks USING hnsw (embedding vector_cosine_ops);

-- Alternative: IVFFlat, faster to build, needs data present first
-- CREATE INDEX chunks_embedding_ivf
--   ON chunks USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

CREATE INDEX chunks_tenant ON chunks (tenant_id);

SELECT id, source, 1 - (embedding <=> $1::vector) AS similarity
  FROM chunks
 WHERE tenant_id = $2
 ORDER BY embedding <=> $1::vector
 LIMIT 10;

On managed Postgres (RDS and most others) pgvector is available as a supported extension, so CREATE EXTENSION is usually all it takes. Check your provider's list of extensions and version first.

Choosing an operator and an index

Distance operators

pgvector gives you three operators, and the index's operator class has to match the one you query with:

• <=> cosine distance, paired with vector_cosine_ops.
• <-> Euclidean (L2) distance, paired with vector_l2_ops.
• <#> negative inner product, paired with vector_ip_ops.

Most text embedding models are designed for cosine similarity, so cosine is my default. If your vectors are normalised to length 1, inner product gives the same ranking and is slightly cheaper. The important rule: if you index with vector_cosine_ops and query with <->, Postgres cannot use the index and falls back to a sequential scan. Run EXPLAIN on your search query once and confirm you see the index in the plan.

If embeddings are new to you, embeddings explained for web developers covers what these distances actually measure.

HNSW or IVFFlat

Both are approximate indexes: they trade a little recall for a lot of speed.

HNSW builds a layered graph. Queries are fast with high recall, it works on an empty table, and it handles inserts well. It uses more memory and takes longer to build. You can raise recall at query time with SET hnsw.ef_search = 100; (the default is 40).

IVFFlat clusters vectors into lists and searches the closest few. It builds quickly and uses less memory, but it learns its clusters from the data present at build time, so create it after loading data and rebuild when the data changes a lot. Tune it with SET ivfflat.probes = 10;.

My default is HNSW. I'd only reach for IVFFlat when build time or memory becomes a real constraint.

Filtering and the recall trap

Approximate indexes return the nearest candidates first and then apply your WHERE clause. With a selective filter (one small tenant in a large table), the index can hand back 40 candidates that all belong to other tenants, and you get fewer results than your LIMIT. The fixes, roughly in the order I'd try them:

1. Raise hnsw.ef_search so more candidates survive the filter.
2. Use iterative index scans if your pgvector version supports them (added in 0.8.0).
3. Partition or use partial indexes per large tenant.
4. For tiny tenants, skip the index entirely. An exact scan over a few thousand rows is fast.

This is the same habit I apply to any Postgres performance problem: read the query plan before you add infrastructure.

Using pgvector from TypeORM

TypeORM's column types and pgvector do not always line up cleanly across versions, so I keep it simple: create the column and index in a migration with raw SQL, leave the embedding out of the entity's regular columns, and run searches through dataSource.query. The comparison in TypeORM vs Prisma explains why I'm comfortable dropping to raw SQL for the queries that matter.

import { DataSource } from "typeorm";

export async function searchChunks(
  dataSource: DataSource,
  tenantId: string,
  embedding: number[],
  limit = 10
) {
  return dataSource.query(
    `SELECT id, source, content, 1 - (embedding <=> $1::vector) AS similarity
       FROM chunks
      WHERE tenant_id = $2
      ORDER BY embedding <=> $1::vector
      LIMIT $3`,
    [JSON.stringify(embedding), tenantId, limit]
  );
}

Passing the vector as a JSON array string and casting with ::vector works with the plain pg driver too, so you need no extra package. Inserts follow the same pattern: INSERT ... VALUES ($1, $2::vector).

Operational notes

• Index builds are memory hungry. Raise maintenance_work_mem for the session that builds an HNSW index on a large table, and build with CREATE INDEX CONCURRENTLY on a live database.
• Dimension limits. Indexes on the vector type support up to 2,000 dimensions. Larger embeddings need halfvec or a model with a smaller output.
• Re-embedding is a migration. Switching embedding models changes every vector. Add a new column, backfill in the background, swap reads, then drop the old one.
• Combine with keyword search. Vector search is weak on exact identifiers. Pair it with Postgres full-text search, as described in hybrid search with keyword and vector retrieval.

The short version

Install pgvector on the Postgres you already run, use cosine distance with an HNSW index, keep tenant filters in SQL, and confirm the index appears in EXPLAIN. That covers most products. Move to a dedicated vector database only when measurements tell you to.