pgvector From Install to First Query

pgvector adds a vector column type and three distance operators to Postgres. Everything else — indexes, filtering, joins, transactions, backups — is Postgres doing what it already did. This page goes from an empty database to an indexed similarity query, and then spends most of its length on the part the quickstarts skip: what the query plan is telling you.

Install the extension

pgvector ships as a Postgres extension, so the server needs the shared library on disk before any SQL will work. On a managed service it is usually already there and only needs enabling. On your own machine, install the package for your platform (postgresql-17-pgvector on Debian and Ubuntu, pgvector in Homebrew), then in the database you are going to use:

CREATE EXTENSION IF NOT EXISTS vector;

-- Which version you actually got. This matters more than it looks.
SELECT extversion FROM pg_extension WHERE extname = 'vector';
 extversion
------------
 0.8.0

Check that version before you follow any tutorial, this one included. HNSW indexes arrived in pgvector 0.5.0; parallel index builds in 0.6.0; the halfvec and sparsevec types and the <+> L1 operator in 0.7.0; iterative index scans in 0.8.0. Half the confused questions about pgvector are somebody running 0.4.4 against instructions written for 0.8.

The extension is per-database, not per-cluster. Creating it in postgres and then connecting to app gives you ERROR: type "vector" does not exist, which reads like a failed install and is not one.

The column and the table

A vector column has a fixed dimension, declared and enforced. Use the dimension of the embedding model you have chosen and do not leave it off — an unconstrained vector column cannot be indexed, and you will find that out after loading a million rows.

CREATE TABLE chunks (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  document_id  bigint NOT NULL,
  ord          int    NOT NULL,          -- position of this chunk in the document
  content      text   NOT NULL,
  embedding    vector(1536) NOT NULL,
  model        text   NOT NULL,          -- which model produced the embedding
  created_at   timestamptz NOT NULL DEFAULT now(),
  UNIQUE (document_id, ord)
);

The model column is not decoration. Vectors from two different embedding models are not comparable, and a table holding both silently returns nonsense — the query still runs, the distances still sort, the results are just wrong. Storing the model name means a mistake becomes a query you can write rather than a mystery. There is more on the schema around this in a documents table that survives re-indexing.

The type limit is 16,000 dimensions for storage but only 2,000 for an index, which is the constraint that actually binds. If your model emits 3,072, either truncate it (Matryoshka models are trained for exactly this) or store the full vector for reranking and index a truncated copy.

Getting vectors in

The wire format is a bracketed, comma-separated list of numbers in a string literal. Every client library that can send text can insert a vector, which is why pgvector works from anything.

INSERT INTO chunks (document_id, ord, content, embedding, model)
VALUES (1, 0, 'Postgres stores rows in 8 kB pages.',
        '[0.0123,-0.0456, ... ,0.0789]'::vector, 'text-embedding-3-small');

For a bulk load, COPY is an order of magnitude faster than individual inserts and is worth the small amount of formatting work. Write the vector column as the same bracketed literal:

-- chunks.csv:  document_id,ord,content,embedding,model
COPY chunks (document_id, ord, content, embedding, model)
FROM '/tmp/chunks.csv' WITH (FORMAT csv);

Load first, index second. Building an HNSW index over an empty table and then inserting a million rows is dramatically slower than inserting a million rows and then building the index once, because every insert pays a graph traversal. An IVFFlat index built on an empty table is worse than slow — it is wrong, because it has no data to cluster and will produce useless centroids.

The three operators

pgvector gives you distance operators, not similarity functions. They sort ascending: nearest first. Get the wrong one and your results are exactly backwards, which is the single most common pgvector bug.

If your embeddings are unit-normalised — most text embedding APIs return them that way — cosine distance and inner product rank identically, and inner product is marginally cheaper. Which metric changes results and which cannot is worked through in cosine similarity vs dot product. The query itself:

SELECT id, content, embedding <=> $1 AS distance
FROM chunks
ORDER BY embedding <=> $1
LIMIT 10;

The ORDER BY must use the same expression as the operator you want indexed, and the index only helps an ORDER BY … LIMIT. A query with no LIMIT, or one that orders by a wrapper function instead of the operator, will not use the index no matter how it is built.

Building the index

Without an index the query above is correct and slow: Postgres computes the distance for every row and takes the top ten. That is exact search, and at a few tens of thousands of rows it is genuinely fine. Past that, build an HNSW index — note that the operator class must match the operator you query with.

SET maintenance_work_mem = '4GB';
SET max_parallel_maintenance_workers = 4;   -- pgvector 0.6.0+

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

Three operator classes exist, one per metric: vector_l2_ops, vector_cosine_ops and vector_ip_ops. Index with vector_l2_ops and query with <=> and Postgres will quietly ignore the index and sequential-scan instead. That is not an error; it is a query that got slower for no visible reason.

m and ef_construction are the two knobs, and their effect on memory, build time and recall is derived in choosing and tuning a pgvector index. The defaults (16 and 64) are reasonable; do not change them before you have measured the recall you get with them.

Reading the plan

This is the part worth the time. Run the query under EXPLAIN (ANALYZE, BUFFERS) and you get one of two plans. Here is the one you want, with the shape you should expect on an indexed table — the numbers will be yours, the structure will be this:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, embedding <=> '[...]'::vector AS distance
FROM chunks ORDER BY embedding <=> '[...]'::vector LIMIT 10;

 Limit  (cost=36.18..72.94 rows=10 width=24)
        (actual time=1.712..1.883 rows=10 loops=1)
   Buffers: shared hit=471
   ->  Index Scan using chunks_embedding_hnsw on chunks
         (cost=36.18..3707.42 rows=1000 width=24)
         (actual time=1.710..1.878 rows=10 loops=1)
         Order By: (embedding <=> '[...]'::vector)
         Buffers: shared hit=471
 Planning Time: 0.134 ms
 Execution Time: 1.934 ms

Four lines carry all the information, and three of them are structural — they will look like this on any healthy pgvector installation.

• Index Scan using chunks_embedding_hnsw — the index is being used. If this says Seq Scan, nothing else on the page matters until you have fixed that.
• Order By: (embedding <=> …) — and crucially, no Sort node above it. The index is producing rows in distance order. An Index Scan with a Sort on top means the index is being used for something else and the ordering is being redone in memory.
• rows=1000 in the estimate — ignore it. The planner has no meaningful selectivity estimate for an approximate nearest-neighbour scan and emits a placeholder. The actual … rows=10 is the real one.
• Buffers: shared hit=471 — 471 8 kB pages touched, all from cache. This is the number to watch. shared read instead of hit means the index is being fetched from disk, and an HNSW graph traversed from disk is slower by roughly the ratio of your disk latency to your memory latency. If read is large and persistent, the index does not fit in shared_buffers and no parameter tuning will fix it.

The plan you do not want looks like this, and the giveaway is not the time — it is the node types:

 Limit  (cost=277014.11..277014.13 rows=10 width=24)
        (actual time=414.882..414.885 rows=10 loops=1)
   ->  Sort  (cost=277014.11..279514.11 rows=1000000 width=24)
         Sort Key: ((embedding <=> '[...]'::vector))
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Seq Scan on chunks
               (cost=0.00..255391.00 rows=1000000 width=24)
               (actual time=0.031..372.104 rows=1000000 loops=1)
 Execution Time: 414.940 ms

Seq Scan plus Sort plus actual … rows=1000000 at the scan node means every row was read and every distance computed. That is exact search. It is not a bug — the answer is more correct than the indexed one — but it is linear in your table size, so it will keep getting slower forever.

Four things cause it, in the order they are usually the culprit: operator class mismatched to operator; no LIMIT; a WHERE clause selective enough that Postgres decided a filtered scan was cheaper (see filtering and vector search in one query); or the index genuinely does not exist because CREATE INDEX CONCURRENTLY failed halfway and left it invalid. That last one is silent. Check it:

SELECT indexrelid::regclass AS index, indisvalid
FROM pg_index
WHERE NOT indisvalid;

The four errors you will hit first

The last one deserves a moment. It is emitted at build time with a HINT telling you to raise maintenance_work_mem, and it is easy to miss in a psql session that is scrolling. If a build that should have taken twenty minutes is still running after two hours, scroll up and look for it before you kill anything.