pgvector troubleshooting

Error index#

Jump straight to the message you're seeing.

Installation and setup#

extension "vector" is not available#

ERROR:  extension "vector" is not available
DETAIL:  Could not open extension control file "/usr/share/postgresql/16/extension/vector.control": No such file or directory.
HINT:  The extension must first be installed on the system where PostgreSQL is running.

Cause. The server you're connected to has no vector.control file. pgvector is compiled per Postgres major, so postgresql-18-pgvector does nothing for a version 16 server. The DETAIL line names the exact directory the server searched — compare it with where you installed.

Fix. Check SHOW server_version; and install the matching package (postgresql-16-pgvector, pgvector_16, …). In Docker, use the pgvector/pgvector image instead of postgres. On a managed service, the provider must offer it — see provider versions.

permission denied to create extension "vector"#

demo=# SET ROLE app_user;
SET

demo=# CREATE EXTENSION vector;
ERROR:  permission denied to create extension "vector"
HINT:  Must be superuser to create this extension.

demo=# RESET ROLE;
RESET

Cause. pgvector's control file doesn't mark it trusted, so only superusers can create it. Fix. Have a superuser (or your provider's admin role) run CREATE EXTENSION vector; once in the database. Application roles don't need any special privilege to use the type afterwards.

type "vector" does not exist#

demo=# \c other
You are now connected to database "other" as user "postgres".

other=# CREATE TABLE t (embedding vector(3));
ERROR:  type "vector" does not exist
LINE 1: CREATE TABLE t (embedding vector(3));
                                  ^

other=# \c demo
You are now connected to database "demo" as user "postgres".

Cause. Extensions are per database. Here it was created in demo but used in other. The same error appears when the extension lives in a schema that isn't on the search_path — Supabase installs it in extensions, for example. Fix. CREATE EXTENSION vector; in this database, or reference the type with its schema (extensions.vector(1536)), or add the schema to search_path.

version "0.8.7" of extension "vector" is already installed#

demo=# ALTER EXTENSION vector UPDATE;
NOTICE:  version "0.8.7" of extension "vector" is already installed
ALTER EXTENSION

This NOTICE is harmless when you really are current. If you expected an upgrade, the server still has the old files on disk: the new package was installed for a different Postgres major, the container wasn't recreated from the new image, or make install used the wrong pg_config. Compare SELECT default_version FROM pg_available_extensions WHERE name = 'vector'; with the version you think you installed.

Inserting and querying vectors#

expected 1536 dimensions, not 768#

demo=# INSERT INTO items (embedding) VALUES ('[1,2]');
ERROR:  expected 3 dimensions, not 2

Cause. A typed column such as vector(1536) only accepts exactly that many dimensions. In practice this means the embedding came from a different model than the column was designed for (e.g. text-embedding-3-small at 1,536 vs nomic-embed-text at 768), or a client passed a dimensions parameter.

Fix. Make every writer use the same model and dimension. If you deliberately store several models, use one column per model, or an untyped vector column with a model_id and per-model expression indexes (pattern here). To find the offending rows: SELECT vector_dims(embedding), count(*) FROM items GROUP BY 1;

different vector dimensions 3 and 2#

demo=# SELECT '[1,2,3]'::vector <-> '[1,2]'::vector;
ERROR:  different vector dimensions 3 and 2

The same problem at query time: the query vector doesn't match the stored vectors. Usually the query was embedded with another model than the documents. A related message appears when casting: expected 3 dimensions, not 4.

operator does not exist: vector <-> text#

demo=# PREPARE q(text) AS SELECT id FROM items ORDER BY embedding <-> $1 LIMIT 5;
ERROR:  operator does not exist: vector <-> text
LINE 1: ...(text) AS SELECT id FROM items ORDER BY embedding <-> $1 LIM...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

demo=# PREPARE q2(text) AS SELECT id FROM items ORDER BY embedding <-> $1::vector LIMIT 5;
PREPARE

Cause. Your driver bound the query vector as text (common with ORMs and with string-formatted embeddings), and there's no distance operator for vector and text. Fix. Cast the parameter — $1::vector — or register pgvector's type adapter so the driver sends a real vector (register_vector(conn) in pgvector-python, pgvector.registerTypes(client) in pgvector-node). See language bindings.

NaN not allowed in vector#

demo=# SELECT '[1,NaN,3]'::vector;
ERROR:  NaN not allowed in vector
LINE 1: SELECT '[1,NaN,3]'::vector;
               ^

demo=# SELECT '[1,Infinity,3]'::vector;
ERROR:  infinite value not allowed in vector
LINE 1: SELECT '[1,Infinity,3]'::vector;
               ^

pgvector rejects NaN and infinite elements because they make every distance undefined. They usually come from normalising a zero vector (division by zero) or from a failed embedding call that returned garbage. Validate in your pipeline before insert. Zero vectors are legal but cause a quieter version of the same problem — cosine distance to them is NaN:

demo=# SELECT l2_normalize('[0,0,0]'::vector) AS normalized_zero, '[0,0,0]'::vector <=> '[1,2,3]' AS cosine_to_zero;
 normalized_zero | cosine_to_zero 
-----------------+----------------
 [0,0,0]         |            NaN
(1 row)

Find them with SELECT count(*) FROM items WHERE vector_norm(embedding) = 0; and re-embed or delete those rows.

"70000" is out of range for type halfvec#

demo=# SELECT '[70000]'::halfvec;
ERROR:  "70000" is out of range for type halfvec
LINE 1: SELECT '[70000]'::halfvec;
               ^

Half precision tops out at ±65,504. Normalised embeddings are nowhere near that, so this points to unnormalised features (counts, prices) being stored as vectors. Keep those as vector, or scale them first.

different bit lengths 3 and 4#

demo=# SELECT '101'::bit(3) <~> '1011'::bit(4);
ERROR:  different bit lengths 3 and 4

Hamming and Jaccard distances need equal-length bit strings. With binary_quantize() the length equals the source vector's dimensions, so this again means mixed embedding sizes.

Creating indexes#

column cannot have more than 2000 dimensions for hnsw index#

demo=# CREATE TABLE big (id bigserial PRIMARY KEY, embedding vector(3072));
CREATE TABLE

demo=# CREATE INDEX ON big USING hnsw (embedding vector_cosine_ops);
ERROR:  column cannot have more than 2000 dimensions for hnsw index

demo=# CREATE INDEX ON big USING ivfflat (embedding vector_cosine_ops);
ERROR:  column cannot have more than 2000 dimensions for ivfflat index

Cause. A vector index entry must fit on an 8 KB page, which caps vector indexes at 2,000 dimensions for both HNSW and IVFFlat. It's the most common error with OpenAI's text-embedding-3-large (3,072) and other large models.

Fix 1 — index a halfvec cast (up to 4,000 dimensions). Keep the full-precision column; index a half-precision expression:

demo=# CREATE INDEX ON big USING hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops);
CREATE INDEX

Queries must use the same expression to hit the index: ORDER BY embedding::halfvec(3072) <=> $1::halfvec(3072) LIMIT 10. On our 3,072-dim DBpedia test this index reached recall@10 of 0.937 at the default ef_search of 40 (4.1 ms median).

Fix 2 — shorten the embedding. OpenAI's v3 models accept a dimensions parameter. In our measurements, truncating 3,072-dim vectors to 1,536 kept 89.0% of the exact top-10 neighbours. Full analysis in halfvec & quantization.

Fix 3 — binary quantization with re-ranking (up to 64,000 dimensions). Details in the quantization guide.

column cannot have more than 4000 dimensions for hnsw index#

demo=# CREATE TABLE huge (id bigserial PRIMARY KEY, embedding vector(4096));
CREATE TABLE

demo=# CREATE INDEX ON huge USING hnsw ((embedding::halfvec(4096)) halfvec_cosine_ops);
ERROR:  column cannot have more than 4000 dimensions for hnsw index

The halfvec limit. Above 4,000 dimensions your options are binary quantization (bit indexes go to 64,000), indexing a subvector, or reducing dimensions. Separately, the vector type itself can't exceed 16,000 dimensions:

demo=# CREATE TABLE toomany (embedding vector(16001));
ERROR:  dimensions for type vector cannot exceed 16000
LINE 1: CREATE TABLE toomany (embedding vector(16001));
                                        ^

column does not have dimensions#

demo=# CREATE TABLE docs (id bigserial PRIMARY KEY, embedding vector);
CREATE TABLE

demo=# CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);
ERROR:  column does not have dimensions

Indexes need a fixed size. Either alter the column (ALTER TABLE docs ALTER COLUMN embedding TYPE vector(1536);) or, if the column deliberately holds several sizes, index a cast with a partial-index condition: CREATE INDEX ON docs USING hnsw ((embedding::vector(1536)) vector_cosine_ops) WHERE model_id = 1;

ef_construction must be greater than or equal to 2 * m#

demo=# CREATE INDEX ON items USING hnsw (embedding vector_l2_ops) WITH (m = 32, ef_construction = 40);
ERROR:  ef_construction must be greater than or equal to 2 * m

pgvector requires ef_construction ≥ 2 × m. With m = 32, use at least 64. Our build-option measurements show the defaults (m = 16, ef_construction = 64) are a good start for 1,536-dim embeddings.

value 200 out of bounds for option "m"#

demo=# CREATE INDEX ON items USING hnsw (embedding vector_l2_ops) WITH (m = 200);
ERROR:  value 200 out of bounds for option "m"
DETAIL:  Valid values are between "2" and "100".

Allowed ranges: HNSW m 2–100, ef_construction 4–1,000; IVFFlat lists 1–32,768. Values far above the defaults rarely help — see the measured trade-offs in the HNSW guide.

2000 is outside the valid range for parameter "hnsw.ef_search"#

demo=# SET hnsw.ef_search = 2000;
ERROR:  2000 is outside the valid range for parameter "hnsw.ef_search" (1 .. 1000)

hnsw.ef_search accepts 1–1,000. If you need more than 1,000 candidates (for example to re-rank or to survive a selective filter), use iterative index scans instead. By contrast, ivfflat.probes accepts up to 32,768:

demo=# SET ivfflat.probes = 2000;
SET

demo=# SHOW ivfflat.probes;
 ivfflat.probes 
----------------
 2000
(1 row)

invalid value for parameter "ivfflat.iterative_scan"#

demo=# SET ivfflat.iterative_scan = strict_order;
ERROR:  invalid value for parameter "ivfflat.iterative_scan": "strict_order"
HINT:  Available values: off, relaxed_order.

IVFFlat only supports off and relaxed_order; strict_order exists only for hnsw.iterative_scan. The HINT line lists the valid values.

memory required is 6 MB, maintenance_work_mem is 1 MB#

demo=# CREATE TABLE ivf_mem (id bigserial PRIMARY KEY, embedding vector(1536));
CREATE TABLE

demo=# INSERT INTO ivf_mem (embedding) SELECT (SELECT array_agg(random())::vector(1536) FROM generate_series(1, 1536) WHERE g > 0) FROM generate_series(1, 2000) g;
INSERT 0 2000

demo=# SET maintenance_work_mem = '1MB';
SET

demo=# CREATE INDEX ON ivf_mem USING ivfflat (embedding vector_l2_ops) WITH (lists = 1000);
ERROR:  memory required is 6 MB, maintenance_work_mem is 1 MB

demo=# RESET maintenance_work_mem;
RESET

IVFFlat holds every list centre in memory during the build (lists × dimensions × 4 bytes, plus overhead). Raise maintenance_work_mem for the session — SET maintenance_work_mem = '1GB'; — or use fewer lists.

hnsw graph no longer fits into maintenance_work_mem#

Not an error, but the most expensive notice in pgvector:

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

The build keeps going, but switches from fast in-memory construction to inserting through the buffer cache. In our test the same 100,000-row index took 10.4 min with 64 MB versus 3.6 min with 4 GB — 2.9× slower. Set maintenance_work_mem above your expected index size before building (the storage calculator estimates it), but below the RAM you can spare.

ivfflat index created with little data#

demo=# CREATE TABLE empty_items (id bigserial PRIMARY KEY, embedding vector(3));
CREATE TABLE

demo=# CREATE INDEX ON empty_items USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);
NOTICE:  ivfflat index created with little data
DETAIL:  This will cause low recall.
HINT:  Drop the index until the table has more data.
CREATE INDEX

IVFFlat learns its list centres from the rows present at build time. Built on an empty table, every later row lands in arbitrary lists and recall collapses. Load your data first, then create the index — or use HNSW, which has no training step.

could not resize shared memory segment … No space left on device#

Docker gives containers a 64 MB /dev/shm by default, and parallel index builds allocate shared memory up to maintenance_work_mem. Start the container with --shm-size at least as large as maintenance_work_mem (e.g. --shm-size=2g, or shm_size: 2g in Compose). The upstream README documents this; our Docker command builder sets both together.

Performance and result problems#

The index isn't used#

Run EXPLAIN. You want Index Scan using … with an Order By line. Here is one HNSW index built with vector_l2_ops, and five queries — only some of them can use it:

demo=# CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);
CREATE INDEX

demo=# SET enable_seqscan = off;
SET

demo=# EXPLAIN (COSTS OFF) SELECT id FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;
                     QUERY PLAN                      
-----------------------------------------------------
 Limit
   ->  Index Scan using items_embedding_idx on items
         Order By: (embedding <-> '[3,1,2]'::vector)
(3 rows)

demo=# EXPLAIN (COSTS OFF) SELECT id FROM items ORDER BY embedding <=> '[3,1,2]' LIMIT 5;
                      QUERY PLAN                       
-------------------------------------------------------
 Limit
   ->  Sort
         Sort Key: ((embedding <=> '[3,1,2]'::vector))
         ->  Seq Scan on items
               Disabled: true
(5 rows)

demo=# EXPLAIN (COSTS OFF) SELECT id FROM items ORDER BY embedding <-> '[3,1,2]' DESC LIMIT 5;
                         QUERY PLAN                         
------------------------------------------------------------
 Limit
   ->  Sort
         Sort Key: ((embedding <-> '[3,1,2]'::vector)) DESC
         ->  Seq Scan on items
               Disabled: true
(5 rows)

demo=# EXPLAIN (COSTS OFF) SELECT id FROM items ORDER BY embedding <-> '[3,1,2]';
                  QUERY PLAN                   
-----------------------------------------------
 Index Scan using items_embedding_idx on items
   Order By: (embedding <-> '[3,1,2]'::vector)
(2 rows)

demo=# EXPLAIN (COSTS OFF) SELECT id, embedding <-> '[3,1,2]' AS distance FROM items ORDER BY distance LIMIT 5;
                     QUERY PLAN                      
-----------------------------------------------------
 Limit
   ->  Index Scan using items_embedding_idx on items
         Order By: (embedding <-> '[3,1,2]'::vector)
(3 rows)

demo=# EXPLAIN (COSTS OFF) SELECT id FROM items ORDER BY 1 - (embedding <=> '[3,1,2]') DESC LIMIT 5;
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Limit
   ->  Sort
         Sort Key: (('1'::double precision - (embedding <=> '[3,1,2]'::vector))) DESC
         ->  Seq Scan on items
               Disabled: true
(5 rows)

demo=# RESET enable_seqscan;
RESET

What the plans tell you:

• The operator must match the operator class. An L2 index (vector_l2_ops) serves only <->; the <=> query fell back to a sort. Create the index with the opclass for the distance you query: vector_cosine_ops for <=>, vector_ip_ops for <#>.
• Order by the distance ascending. DESC can't use the index, and neither can ORDER BY 1 - (embedding <=> $1) DESC — the "similarity" form many tutorials use. Order by the raw distance and compute similarity in the select list.
• A select-list alias is fine. ORDER BY distance where distance is the distance expression uses the index.
• No LIMIT is a trap. The planner will use the index without a LIMIT if forced, but an HNSW scan returns at most ef_search rows (40 by default) unless iterative scans are on. Always add a LIMIT.

Distance thresholds are a special case — a WHERE distance < x filter can't use the index on its own. Order and limit in a subquery, then filter:

demo=# SET enable_seqscan = off;
SET

demo=# EXPLAIN (COSTS OFF) SELECT id FROM items WHERE embedding <-> '[3,1,2]' < 5;
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Seq Scan on items
   Disabled: true
   Filter: ((embedding <-> '[3,1,2]'::vector) < '5'::double precision)
(3 rows)

demo=# EXPLAIN (COSTS OFF) SELECT id FROM (SELECT id, embedding <-> '[3,1,2]' AS distance FROM items ORDER BY distance LIMIT 5) t WHERE distance < 5;
                        QUERY PLAN                         
-----------------------------------------------------------
 Subquery Scan on t
   Filter: (t.distance < '5'::double precision)
   ->  Limit
         ->  Index Scan using items_embedding_idx on items
               Order By: (embedding <-> '[3,1,2]'::vector)
(5 rows)

demo=# RESET enable_seqscan;
RESET

On a small table the planner may also legitimately prefer a sequential scan — that's a cost decision, not a bug, and it disappears as the table grows.

Fewer rows than the LIMIT#

With a WHERE filter, an approximate index finds ef_search candidates first and Postgres filters them afterwards. If the filter is selective, few candidates survive. We measured it on 100,000 DBpedia embeddings with a filter that matches 1% of rows, forcing the HNSW index (ef_search = 40, LIMIT 10):

Fix it with SET hnsw.iterative_scan = relaxed_order; (pgvector 0.8.0+), a partial index for a hot filter value, or a B-tree on the filter column for very selective filters. The full analysis across four selectivities is in the filtering guide.

Results look wrong or miss obvious matches#

Approximate indexes trade recall for speed. With pgvector's defaults on our 100,000 × 1,536 dataset, HNSW found 94.7% of the true top-10 neighbours; IVFFlat with 316 lists and the default probes = 1 found only 55.0%. Raise the search-time knob and re-measure:

SET hnsw.ef_search = 100;   -- HNSW: default 40
SET ivfflat.probes = 16;    -- IVFFlat: default 1 (!)

To measure your own recall, compare the index's answer with an exact scan inside a transaction where index scans are disabled (SET LOCAL enable_indexscan = off;) — the cheatsheet has a ready query.

Index builds take forever#

Our measured build of an HNSW index on 100,000 × 1,536-dim vectors, varying only memory and parallelism:

Check three things:

• maintenance_work_mem large enough for the graph (about 6.7 KB per 1,536-dim row), or you'll see the "no longer fits" notice.
• Parallel workers that actually start. With 1,536-dim vectors the main heap is tiny (the vectors live in TOAST), and below 8 MB of heap — about 160,000 rows — PostgreSQL plans zero workers no matter what max_parallel_maintenance_workers says. We counted: 0 workers and 3.4 min; after ALTER TABLE items SET (parallel_workers = 3);, 3 workers and 56 s. Details.
• Load first, index second — inserting the same 100,000 rows into an already-indexed table took 13.8 min.

Build-from-source errors#

Still stuck? Email hello@pgvector.app with the full error and your SELECT version(); — we add new cases to this page. For bugs in pgvector itself, the upstream issue tracker is the right place.

Frequently asked questions