From Rows to Vectors: pgvector inside Postgres

Say your product needs search that understands meaning. A user types "the tax due when you sell shares at a profit" and expects the page on capital gains tax, even though none of those words appears in its title. The usual next step is a new piece of infrastructure: Pinecone, Qdrant, Milvus or Weaviate, with its own bill, its own SLA and a sync job to keep it in step with your real data.

There is another option: keep the embeddings in pgvector, inside the PostgreSQL database you already run. This article explains what a vector database does, how its indexes work, and what to watch for when you run one inside Postgres, including the traps that are easy to ship without noticing.

Two kinds of questions

A relational database answers "which rows are equal to this?"

SELECT * FROM orders WHERE customer_id = 42;

The answer is exact. A row matches, or it does not.

A vector database answers "which rows are like this?"

SELECT id, body FROM documents
ORDER BY embedding <=> $1
LIMIT 5;

The answer is a ranking. Every row sits some distance away from the query, and you ask for the closest few. Everything else in this article follows from that one change of contract.

pgvector puts the right-hand column inside the left-hand one.

From meaning to numbers

An embedding model turns a piece of content into a fixed-length list of numbers. It is trained so that similar meanings produce similar numbers. OpenAI's text-embedding-3-small returns 1,536 numbers by default, and text-embedding-3-large returns 3,072. Both accept a dimensions parameter to return fewer (OpenAI embeddings guide).

Two properties matter in practice:

• Fixed length. ** A single word and a full page produce the same number of dimensions.
• One model for both sides. ** Stored rows and incoming queries must go through the same model, with the same settings. Swap the model and every stored vector needs re-embedding.

Meaning as a place

Picture those numbers as coordinates. Real embeddings have 1,536 axes, but two are enough to get the idea. "cat", "dog" and "puppy" land close together. "pizza", "pasta" and "bread" form their own cluster. "capital gains tax", "CGT" and "tax on a share sale" sit in a third.

Search becomes geometry. Embed the query with the same model, then ask for its nearest neighbours. A query for "kitten" finds "cat" and "puppy", although "kitten" is a different word from both. No keyword matching is involved.

"Near" needs a definition

pgvector ships several distance operators (pgvector README, Querying):

Note: <#> returns the negative inner product because Postgres only supports ascending-order index scans on operators (pgvector README, Getting Started). Multiply by -1 if you want the real value.

The pgvector README states that OpenAI embeddings are normalised to length 1, and recommends inner product for best performance on normalised vectors (pgvector README, Exact Search). For unit-length vectors, inner product ranks results in the same order as cosine.

Why a B-tree can't help

A B-tree handles equality and range queries on data that can be sorted into an order (PostgreSQL docs, Index Types). WHERE id = 42 takes a few hops down the tree and lands on the row.

"Nearest to this vector" has no fixed order. Every query has its own centre, so nothing can be sorted in advance. Without an approximate index, pgvector performs exact nearest neighbour search: it compares the query against every row (pgvector README, Indexing).

The cost adds up quickly. Take one million rows of 1,536-dimensional vectors:

• 1.5 billion multiply-adds per query (1,000,000 × 1,536).
• About 6.2 GB read per query. ** pgvector stores each vector in 4 × dimensions + 8 bytes, so 6,152 bytes per row (pgvector README, Vector Type).

Exact search returns perfect recall, and its cost grows in a straight line with the table.

Approximate nearest neighbours

Vector indexes give up a little accuracy to stop reading everything. The measure for that accuracy is recall: the share of the true k nearest neighbours that actually come back.

Two things follow:

• Recall is a dial. ** Each index type has a query-time setting that trades speed for recall. You choose where to sit on the curve.
• Adding an index can change your results. The pgvector README says it directly: you will see different results for queries after adding an approximate index (pgvector README, Indexing). A test that passed against a sequential scan can fail the day the index ships.

IVFFlat: cluster first, then search a few clusters

IVFFlat divides the vectors into lists, each grouped around a centroid, and a query searches only the lists closest to it. The number of lists it searches is probes, and the default is 1 (pgvector README, IVFFlat). The lists come from k-means over the rows present at build time; the index build reports a performing k-means phase.

The weakness is at the borders. If the true nearest neighbour sits just over the edge of a neighbouring list, a query with probes = 1 never looks there.

The README gives these starting points:

• lists = rows / 1000 for up to 1M rows, and sqrt(rows) above that.
• probes = sqrt(lists).
• Create the index after the table has some data.

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

SET ivfflat.probes = 10;

The README's troubleshooting section names the symptom too: an IVFFlat index created with too little data for its number of lists returns fewer results (pgvector README, Troubleshooting).

An easy way to hit this. ** Indexes usually arrive through migrations, and migrations usually run before any data exists. An IVFFlat index created that way builds its lists from an empty table. Leave probes at its default, and every query searches one list out of 100. The fix is a REINDEX once the data is loaded, and an explicit SET ivfflat.probes per query. Better still, use HNSW, which has no training step.

HNSW: a layered graph

HNSW (Hierarchical Navigable Small World) comes from a paper by Malkov and Yashunin (arXiv 1603.09320). It builds a graph in layers. The top layer has few nodes and long links; each layer below has more nodes and shorter links, down to layer 0, which holds every vector. A search starts at the top, hops to whichever neighbour is closer to the query, drops a layer, and repeats.

Its settings and their defaults (pgvector README, HNSW):

• m = 16: the maximum number of connections per layer.
• ef_construction = 64: the size of the candidate list while building.
• hnsw.ef_search = 40: the size of the candidate list per query. This is the recall dial.

HNSW has no training step, so you can create it on an empty table.

CREATE INDEX ON documents
  USING hnsw (embedding vector_cosine_ops);

SET hnsw.ef_search = 100;

Builds are much faster when the graph fits in maintenance_work_mem, and Postgres prints a notice when it no longer does (pgvector README, Index Build Time).

Choosing between them

All rows below come from the pgvector README.

My default is HNSW, unless build time or memory is the constraint. It gives a better speed-recall trade-off, and it removes the empty-table trap entirely.

pgvector: a vector database inside Postgres

pgvector is an open-source Postgres extension; the README's install instructions reference version 0.8.7 at the time of writing (pgvector README, Installation). It adds vector types, distance operators and the two index types above. Everything you already rely on stays: ACID transactions, joins, point-in-time recovery, replication and the query planner. On AWS, Aurora PostgreSQL supports it (AWS announcement: pgvector 0.8.0 on Aurora PostgreSQL).

CREATE EXTENSION vector;

Types and limits

One 1,536-dimension embedding takes this much space in each type. Storage formulas and index limits come from the pgvector README reference and its HNSW section.

Vectors can store up to 16,000 dimensions; only indexing is limited. A 3,072-dimension model such as text-embedding-3-large cannot be indexed as vector, but it can be indexed as halfvec (pgvector README FAQ).

Note: bit keeps only one bit per dimension. Use it as a fast first pass and re-rank with the full vector (see scaling below).

A table, an index, a query

CREATE TABLE documents (
  id        bigserial PRIMARY KEY,
  body      text NOT NULL,
  embedding vector(1536) NOT NULL
);

CREATE INDEX ON documents
  USING hnsw (embedding vector_cosine_ops);

-- the five nearest to a query vector
SET hnsw.ef_search = 100;

SELECT id, body FROM documents
ORDER BY embedding <=> $1
LIMIT 5;

Four details that cost people time:

• The dimension is part of the column type. A vector(1536) column holds 1,536-dimensional vectors; to mix dimensions, you need the unsized vector type (pgvector README FAQ).
• The index serves one distance function. The README says to add an index for each distance function you want to use (pgvector README, HNSW). An index built with vector_cosine_ops speeds up <=> only.
• hnsw.ef_search is per session. Use SET LOCAL inside a transaction to change it for one query (pgvector README, Query Options).
• The index needs both ORDER BY and LIMIT, and the ORDER BY must be the distance operator itself in ascending order. ORDER BY 1 - (embedding <=> $1) DESC skips the index (pgvector README, Troubleshooting).

Why cosine here, after I said inner product is fastest? For unit-length vectors, the two give identical rankings, and cosine stays correct if a future model does not normalise its output. I chose safety over a small speed gain.

The same query from Node.js

The pgvector npm package supports node-postgres, Knex.js, Kysely, Prisma, Drizzle ORM and several other libraries (pgvector-node README).

npm install pgvector pg openai

import OpenAI from 'openai'
import pg from 'pg'
import pgvector from 'pgvector/pg'

const question = 'the tax due when you sell shares at a profit'

const client = new pg.Client()
await client.connect()
await pgvector.registerTypes(client)

// reads OPENAI_API_KEY from the environment
const { data } = await new OpenAI().embeddings.create({
  model: 'text-embedding-3-small',
  input: question,
})

const { rows } = await client.query(
  `SELECT id, body FROM documents
   ORDER BY embedding <=> $1 LIMIT 5`,
  [pgvector.toSql(data[0].embedding)],
)

Where each part comes from:

• registerTypes and toSql follow the pgvector-node README, node-postgres section. With a pool, call registerTypes in the pool's onConnect hook.
• registerType (singular) is deprecated since pgvector-node 0.3.0 (pgvector-node README, Upgrading).
• new OpenAI() reads OPENAI_API_KEY from the environment (openai-node README).
• The response's data array holds one item per input, each with an embedding field (OpenAI API reference, Create embeddings).

Production trap 1: WHERE runs after the index scan

This one bites quietly. With an approximate index, filtering is applied after the index is scanned.

The pgvector README gives the numbers: if a condition matches 10% of rows, HNSW with the default ef_search of 40 leaves only about 4 matching rows on average (pgvector README, Filtering). Ask for 10, and you get about 4. No error, no warning: just fewer results.

-- asks for 10, typically gets about 4 back once an HNSW index exists
SELECT id, body FROM documents
WHERE course_id = 123
ORDER BY embedding <=> $1
LIMIT 10;

Since pgvector 0.8.0, iterative index scans fix this (pgvector CHANGELOG). When enabled, the scan keeps reading the index until enough results are found, or until it reaches hnsw.max_scan_tuples (default 20,000) or ivfflat.max_probes (pgvector README, Iterative Index Scans).

SET hnsw.iterative_scan = strict_order;
-- or, trading exact ordering for recall:
SET hnsw.iterative_scan = relaxed_order;

-- IVFFlat supports relaxed ordering only
SET ivfflat.iterative_scan = relaxed_order;

Other options from the README: an index on the filter column for conditions that match a low percentage of rows, a partial index when you filter by a few distinct values, and partitioning when you filter by many (pgvector README, Filtering). For tenant isolation, it recommends list partitioning or separate tables (pgvector README, Multitenancy).

Why this one survives code review. ** A filtered similarity query on a table with no vector index returns exact results, because Postgres scans everything. The day someone adds an index for speed, the same query starts returning fewer rows, with no error. A test that runs against a small, unindexed table will not catch it. If you filter, decide on iterative scans before you add the index.

Production trap 2: keywords and meaning need each other

Vector search misses exact identifiers. Search for a course code or a regulation number and the embedding treats it as noise. Full-text search has the opposite gap: it misses paraphrases. Hybrid search runs both and merges the results. The pgvector README suggests combining it with Postgres full-text search, using Reciprocal Rank Fusion or a cross-encoder to merge (pgvector README, Hybrid Search).

The merge is the hard part, because the scores live on different scales. Cosine distance and ts_rank cannot be added together. Reciprocal Rank Fusion (RRF) sidesteps that by combining positions instead of scores. The constant k smooths the score so that a first place does not dominate:

score(doc) = Σ 1 / (k + rank)

With k = 50, the default rrf_k in Supabase's hybrid search guide:

Documents A and B appear in both lists, so they rise to the top. A document that only one method found still scores, just lower.

In Postgres, both halves live in the same table. Keep a tsvector column next to the vector column, run the two ranked queries as CTEs, and join them on the document ID. Supabase's guide shows a complete SQL function built this way.

Production trap 3: scaling before you buy

The usual argument for a dedicated vector store is scale. Before taking on another service, pull the Postgres levers first. The README lists them (pgvector README, Scaling); the order is mine:

1. halfvec columns. Half the bytes per vector, so a smaller working set.
2. Binary quantization for the index, then re-rank the shortlist at full precision.
3. maintenance_work_mem large enough for HNSW to build in memory.
4. A bigger instance: more memory, CPU and storage.
5. Read replicas, then Citus or PgDog to shard.

The binary quantisation pattern, adapted from the README:

CREATE INDEX ON documents
  USING hnsw ((binary_quantize(embedding)::bit(1536)) bit_hamming_ops);

SELECT * FROM (
  SELECT * FROM documents
  ORDER BY binary_quantize(embedding)::bit(1536) <~> binary_quantize($1)
  LIMIT 20
) shortlist
ORDER BY embedding <=> $1
LIMIT 5;

A dedicated store earns its place when those levers are pulled, and your recall or latency targets still miss, or when vector traffic has to scale independently of the transactional database.

I looked for an independent benchmark that names the size where Postgres stops being enough, and did not find one. Measure recall and latency on your own data before deciding. The README shows how: compare approximate results with exact search by running the query with SET LOCAL enable_indexscan = off (pgvector README, Monitoring).

When you don't need an index at all

Not every vector table needs an approximate index. Two common shapes:

• Few-shot examples for an LLM. ** You pick the most similar past examples to put in a prompt. These tables are often small, so exact search with no index is fast enough, and it gives perfect recall for free.
• A document library for retrieval. Uploaded documents are chunked and embedded, with a full-text column beside each vector, ready for hybrid search. This is the shape that grows, and where the index decisions above start to matter.

Start without an index, measure latency, and add one when the numbers ask for it. When you do, revisit every filtered query first.

Three things to take home

1. A vector database answers "like this". Embeddings turn meaning into distance; nearest neighbours turn distance into results.
2. Approximate means a dial. ef_search and probes trade speed for recall. Treat the defaults as a starting point and tune from there.
3. Start in Postgres; earn the next database. pgvector keeps vectors beside the rows they describe, in a system you already back up, monitor and understand. Measure recall and latency before adding a service to run.

And you? Did you start with a dedicated vector store, or keep everything in Postgres? If you have hit a recall or latency wall with pgvector, I would like to know the table size and the setting that broke it. Drop a comment if you have questions.

Bruno Souza, Software Engineer

Sources

• pgvector README: types, operators, HNSW, IVFFlat, filtering, iterative scans, hybrid search, scaling, monitoring
• pgvector CHANGELOG: 0.7.0 types, 0.8.0 iterative scans
• pgvector-node README: Node.js usage, supported libraries, 0.3.0 upgrade notes
• OpenAI embeddings guide: model dimensions and the dimensions parameter
• OpenAI API reference, Create embeddings: response shape
• openai-node README: client construction and API key
• PostgreSQL docs, Index Types: what a B-tree handles
• Malkov and Yashunin, Efficient and robust approximate nearest neighbour search using Hierarchical Navigable Small World graphs
• Supabase docs, hybrid search: RRF with rrf_k = 50
• AWS: pgvector 0.8.0 support in Aurora PostgreSQL

Facts checked on 20 October 2026.