Postgres pgvector: vector search tested on PostgreSQL 18

Postgres pgvector is an extension that adds a vector column type, distance operators, and two approximate nearest neighbour indexes to PostgreSQL. It lets you store embeddings next to the rows they describe and run similarity search with an ORDER BY ... LIMIT query, without a separate vector database.

The short version: create the extension, add a vector(n) column that matches your embedding model, pick the operator that matches the model's distance metric, and add an HNSW index once the table is large enough for a sequential scan to hurt. Then check EXPLAIN, because a query written slightly differently from the index falls back to scanning every row without any warning.

Every command and output below comes from the pgvector/pgvector:pg18 Docker image, which ships PostgreSQL 18.6 with pgvector 0.8.6.

Installing pgvector

The quickest way to try it is the official image:

docker run -d --name pgvector --shm-size=1g \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 pgvector/pgvector:pg18

The --shm-size flag matters later, when you build an index. Our Postgres in Docker guide covers volumes and the rest of the setup.

Most managed Postgres services, including Supabase, Neon, and Amazon RDS, include pgvector, so on those you only need to enable it. On a self-managed server, install the package for your PostgreSQL version first. Then, in each database that needs it:

CREATE EXTENSION vector;

SELECT extversion FROM pg_extension WHERE extname = 'vector';

 extversion
------------
 0.8.6
(1 row)

The extension is called vector, not pgvector:

CREATE EXTENSION pgvector;

ERROR:  extension "pgvector" is not available
HINT:  The extension must first be installed on the system where PostgreSQL is running.

The hint is misleading here. The package is installed, it is only the name that is wrong.

Creating a vector column

A vector column takes the number of dimensions as its type modifier. Use the number your embedding model produces:

CREATE TABLE items (
  id bigserial PRIMARY KEY,
  name text,
  embedding vector(3)
);

INSERT INTO items (name, embedding) VALUES
  ('apple', '[1,0,0]'),
  ('pear',  '[0.9,0.1,0]'),
  ('car',   '[0,0,1]'),
  ('truck', '[0,0.1,0.9]');

Real embeddings have hundreds or thousands of dimensions, but three keeps the output readable. Vectors are written as a bracketed list in a string, which is also the format most client libraries send.

The dimension count is enforced:

INSERT INTO items (name, embedding) VALUES ('bad', '[1,2]');

ERROR:  expected 3 dimensions, not 2

Declare the dimension. A bare vector column accepts any length, but it cannot be indexed:

CREATE TABLE nodim (embedding vector);
CREATE INDEX ON nodim USING hnsw (embedding vector_cosine_ops);

ERROR:  column does not have dimensions

Each element is a 4-byte float, so a vector takes 4 * dimensions + 8 bytes. A 1,536-dimension embedding is a little over 6 KB per row before any index.

pgvector distance operators

pgvector adds one operator per distance function:

Here are the first four against [1,0,0]:

SELECT name,
       embedding <-> '[1,0,0]' AS l2,
       embedding <=> '[1,0,0]' AS cosine_dist,
       embedding <#> '[1,0,0]' AS neg_ip,
       embedding <+> '[1,0,0]' AS l1
FROM items
ORDER BY embedding <-> '[1,0,0]';

 name  |         l2          |     cosine_dist      |       neg_ip        |         l1
-------+---------------------+----------------------+---------------------+---------------------
 apple |                   0 |                    0 |                  -1 |                   0
 pear  | 0.14142137441313676 | 0.006116251198662548 | -0.8999999761581421 | 0.20000001788139343
 truck |  1.3490737315813386 |                    1 |                  -0 |                   2
 car   |  1.4142135623730951 |                    1 |                  -0 |                   2
(4 rows)

Three things in that output trip people up:

• Every operator returns a distance, so smaller is closer and you always sort ascending.
• <#> returns the inner product negated, so that ascending order still puts the best match first. Apple's inner product is 1, shown as -1.
• <=> is cosine distance, not similarity. For a similarity score, subtract it from 1:

SELECT name, 1 - (embedding <=> '[1,0,0]') AS cosine_similarity
FROM items
ORDER BY embedding <=> '[1,0,0]'
LIMIT 2;

 name  | cosine_similarity
-------+--------------------
 apple |                  1
 pear  | 0.9938837488013375
(2 rows)

Select the similarity, but keep ordering by the raw distance. The reason is in the EXPLAIN section below.

Which operator should you use?

Use whatever your embedding model was trained for, which its documentation states. Cosine distance is the common default for text embeddings. If the vectors are normalised to length 1, as OpenAI's are, cosine distance and inner product give the same ranking, and the pgvector README recommends inner product for best performance.

Comparing vectors of different lengths is an error rather than a wrong answer:

ERROR:  different vector dimensions 3 and 2

A table big enough to need an index

To see the indexes work, I loaded 100,000 rows of 128-dimension vectors into this table. The vectors are synthetic, grouped into 1,000 clusters so that each one has close neighbours, the way real embeddings do.

CREATE TABLE docs (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  embedding vector(128) NOT NULL
);

The table is 56 MB. Without an index, a nearest neighbour query reads all of it:

EXPLAIN (ANALYZE, COSTS OFF, BUFFERS OFF)
SELECT id FROM docs
ORDER BY embedding <=> (SELECT embedding FROM docs WHERE id = 42)
LIMIT 10;

 Limit (actual time=50.921..54.839 rows=10.00 loops=1)
   InitPlan 1
     ->  Index Scan using docs_pkey on docs docs_1 (actual time=0.009..0.009 rows=1.00 loops=1)
           Index Cond: (id = 42)
           Index Searches: 1
   ->  Gather Merge (actual time=50.920..54.834 rows=10.00 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Sort (actual time=45.929..45.930 rows=7.33 loops=3)
               Sort Key: ((docs.embedding <=> (InitPlan 1).col1))
               Sort Method: top-N heapsort  Memory: 25kB
               Worker 0:  Sort Method: top-N heapsort  Memory: 25kB
               Worker 1:  Sort Method: top-N heapsort  Memory: 25kB
               ->  Parallel Seq Scan on docs (actual time=0.029..40.960 rows=33333.33 loops=3)
 Planning Time: 0.201 ms
 Execution Time: 54.881 ms

Parallel Seq Scan plus Sort is an exact search: PostgreSQL computes the distance to every row and keeps the ten smallest. Across repeated runs on my laptop this took between 14 and 55 ms. That is fine at 100,000 rows. The cost grows linearly with the table, and at tens of millions of rows the same query takes seconds.

An exact scan is also the only way to get perfect recall. If your table is small, you may not need an index at all.

HNSW vs IVFFlat indexes

pgvector has two index types. Both are approximate: they return close neighbours quickly, but not always the exact top results.

The pgvector README summarises it the same way: HNSW has better query performance for the recall you get, at the cost of slower builds and more memory. Start with HNSW unless build time or memory is the constraint.

Creating an HNSW index

SET maintenance_work_mem = '1GB';

CREATE INDEX docs_embedding_idx ON docs
  USING hnsw (embedding vector_cosine_ops);

The operator class must match the operator you query with. vector_cosine_ops serves <=> queries and nothing else.

The optional build parameters are m (connections per node, default 16) and ef_construction (candidate list size during the build, default 64). Higher values raise recall and make the build slower. The defaults are a reasonable place to start.

maintenance_work_mem decides the build time

My first HNSW build ran with the default maintenance_work_mem of 64 MB and printed:

NOTICE:  hnsw graph no longer fits into maintenance_work_mem after 52686 tuples
DETAIL:  Building will take significantly more time.
HINT:  Increase maintenance_work_mem to speed up builds.

That build took 46.8 seconds. With maintenance_work_mem set to 1 GB, the same index built in 7.4 to 8.4 seconds. Raise it for the session that builds the index.

In Docker, raising it can produce a different error:

ERROR:  could not resize shared memory segment "/PostgreSQL.3230820248" to 533761472 bytes: No space left on device

Parallel index builds use shared memory, and Docker gives containers 64 MB of /dev/shm by default. The pgvector README says to set --shm-size to at least the value of maintenance_work_mem, which is why the docker run command above includes it.

DB Pro

Work With Your Databases Like A Pro

Query, explore, and manage your databases with a beautiful desktop app and built-in AI.

Download Now

Confirming the index is used with EXPLAIN

Same query, with the HNSW index in place:

 Limit (actual time=0.118..0.124 rows=10.00 loops=1)
   InitPlan 1
     ->  Index Scan using docs_pkey on docs docs_1 (actual time=0.003..0.003 rows=1.00 loops=1)
           Index Cond: (id = 42)
           Index Searches: 1
   ->  Index Scan using docs_embedding_idx on docs (actual time=0.118..0.123 rows=10.00 loops=1)
         Order By: (embedding <=> (InitPlan 1).col1)
         Index Searches: 1
 Planning Time: 0.045 ms
 Execution Time: 0.132 ms

The line to look for is Index Scan using docs_embedding_idx with an Order By on the distance operator. The query went from tens of milliseconds to between 0.1 and 0.5 ms across runs.

pgvector only uses the index for one query shape: ORDER BY <column> <operator> <value> LIMIT n. With the same cosine index in place, each of these falls back to a sequential scan.

The wrong operator. Ordering by L2 distance when the index is built for cosine:

EXPLAIN (COSTS OFF)
SELECT id FROM docs
ORDER BY embedding <-> (SELECT embedding FROM docs WHERE id = 42)
LIMIT 10;

 Limit
   InitPlan 1
     ->  Index Scan using docs_pkey on docs docs_1
           Index Cond: (id = 42)
   ->  Gather Merge
         Workers Planned: 2
         ->  Sort
               Sort Key: ((docs.embedding <-> (InitPlan 1).col1))
               ->  Parallel Seq Scan on docs

Ordering by similarity instead of distance. ORDER BY 1 - (embedding <=> ...) DESC returns the same rows, but the planner cannot match the expression to the index:

         ->  Sort
               Sort Key: (('1'::double precision - (docs.embedding <=> (InitPlan 1).col1))) DESC
               ->  Parallel Seq Scan on docs

No LIMIT. Without a limit the plan is a plain Sort over a Seq Scan on docs.

A distance threshold in WHERE. Filtering with WHERE embedding <=> ... < 0.1 and no ORDER BY scans every row:

 Seq Scan on docs
   Filter: ((embedding <=> (InitPlan 1).col1) < '0.1'::double precision)

If you need a threshold, order and limit first, then filter the result.

Creating an IVFFlat index

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

IVFFlat clusters the existing rows into lists groups, then searches only the groups closest to the query. The README suggests rows / 1000 lists for up to a million rows and sqrt(rows) above that, which gives 100 here. The plan looks the same as HNSW, with Index Scan using docs_embedding_ivf.

Because IVFFlat learns its lists from existing data, building it on an empty table produces an index with poor recall. pgvector warns you:

NOTICE:  ivfflat index created with little data
DETAIL:  This will cause low recall.
HINT:  Drop the index until the table has more data.

Load the data first, then create the index.

The default of one probe searches a single list. SET ivfflat.probes = 10 raised this query from 0.25 ms to 2.2 ms while searching ten times as many candidates. The README suggests sqrt(lists) as a starting point.

Index dimension limits

Both index types accept vector columns up to 2,000 dimensions:

ERROR:  column cannot have more than 2000 dimensions for hnsw index

For larger embeddings, index a half-precision cast, which supports up to 4,000 dimensions:

CREATE INDEX ON wide USING hnsw ((embedding::halfvec(3000)) halfvec_cosine_ops);

Queries must use the same cast, ORDER BY embedding::halfvec(3000) <=> ..., or the index is skipped.

Filtering with WHERE

Combining a filter with vector search is where most pgvector surprises come from. The approximate index finds candidates first, and PostgreSQL applies the WHERE clause afterwards.

I added a tenant_id column so that each tenant holds 1% of the rows, then asked for the ten nearest rows for one tenant:

SELECT count(*) FROM (
  SELECT id FROM docs
  WHERE tenant_id = 7
  ORDER BY embedding <=> (SELECT embedding FROM docs WHERE id = 42)
  LIMIT 10
) s;

 count
-------
     0
(1 row)

Zero rows, although the tenant has a thousand. HNSW gathered its default 40 candidates, none of them belonged to tenant 7, and nothing was left. The plan shows why:

   ->  Index Scan using docs_embedding_idx on docs
         Order By: (embedding <=> (InitPlan 1).col1)
         Filter: (tenant_id = 7)

pgvector 0.8.0 added iterative index scans, which keep searching until enough rows pass the filter:

SET hnsw.iterative_scan = relaxed_order;

The same query then returned all 10 rows. relaxed_order can return results slightly out of distance order. strict_order keeps them exact. The scan stops at hnsw.max_scan_tuples (20,000 by default) so a filter that matches nothing does not read every entry in the index.

For a filter with only a few distinct values, a partial index per value avoids the problem:

CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops)
  WHERE (tenant_id = 7);

For a selective filter on its own column, a B-tree index on that column can be better still, because PostgreSQL can fetch the matching rows and do an exact search over only those. See the CREATE INDEX guide for B-tree basics.

Measuring recall on your own data

Approximate indexes trade accuracy for speed, and the trade depends on your data. Measure it by comparing index results with an exact search for a sample of queries:

CREATE FUNCTION topk(q vector, k int) RETURNS SETOF bigint
LANGUAGE sql AS $$
  SELECT id FROM docs ORDER BY embedding <=> q LIMIT k
$$;

-- Exact results, with the index disabled
SET enable_indexscan = off;
CREATE TABLE exact AS
  SELECT qid, t AS id FROM sample_queries, LATERAL topk(v, 10) t;
RESET enable_indexscan;

-- Approximate results
SET hnsw.ef_search = 100;
CREATE TABLE approx AS
  SELECT qid, t AS id FROM sample_queries, LATERAL topk(v, 10) t;

-- Fraction of the true top 10 the index found
SELECT count(*)::numeric / (SELECT count(*) FROM exact) AS recall
FROM exact JOIN approx USING (qid, id);

sample_queries is a table of real query embeddings with a qid column. Raise hnsw.ef_search (or ivfflat.probes) until recall is acceptable, then check the latency is still acceptable too. Use SET LOCAL inside a transaction to raise it for one query only.

I have not published recall figures from my test table. Synthetic vectors do not behave like real embeddings, and a number measured on them would tell you little about your data.

When pgvector is the right choice

pgvector fits when your embeddings describe rows you already keep in Postgres. The vectors sit in the same transaction as the data, joins and filters use normal SQL, and backups, replication, and access control need no extra system. Most applications doing retrieval over documents, products, or support tickets are in this category.

A dedicated vector database starts to make sense when vector search is the main workload at a scale where index memory and build times dominate, or when you need features pgvector does not have. Measure first. Many teams hit the filtering and operator mismatches above and blame the extension, when the query was falling back to a sequential scan.

For how Postgres and its extensions compare with a dedicated analytics engine, see ClickHouse vs Postgres. For slow queries that are not vector related, see how to fix slow Postgres queries.

Quick reference