Quickstart#
- Enable the extension (once per database; install it first).
psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7
demo=# CREATE EXTENSION IF NOT EXISTS vector; CREATE EXTENSION demo=# SELECT extversion FROM pg_extension WHERE extname = 'vector'; extversion ------------ 0.8.7 (1 row) - Create a table with a column sized to your embedding model.
psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7
demo=# CREATE TABLE items ( demo-# id bigserial PRIMARY KEY, demo-# content text, demo-# embedding vector(3) demo-# ); CREATE TABLE - Insert embeddings. In production your application sends arrays through a language binding.
psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7
demo=# INSERT INTO items (content, embedding) VALUES demo-# ('cat', '[1, 0.9, 0.1]'), demo-# ('kitten', '[0.95, 1, 0.2]'), demo-# ('car', '[0.1, 0.2, 1]'), demo-# ('truck', '[0.2, 0.1, 0.9]'); INSERT 0 4 - Query nearest neighbours with the operator for your distance.
psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7
demo=# SELECT content, embedding <=> '[1, 1, 0]' AS cosine_distance, demo-# 1 - (embedding <=> '[1, 1, 0]') AS cosine_similarity demo-# FROM items ORDER BY embedding <=> '[1, 1, 0]' LIMIT 3; content | cosine_distance | cosine_similarity ---------+----------------------+--------------------- cat | 0.004129399346548546 | 0.9958706006534515 kitten | 0.010674679844520418 | 0.9893253201554796 truck | 0.7712521293869518 | 0.22874787061304824 (3 rows) - Add an index once you have more than a few thousand rows.
Choosing settings: HNSW guide · index planner.sql
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);
Types and limits#
| Type | Element | Max dims (stored) | Max dims (indexed) | Storage per value | Added in |
|---|---|---|---|---|---|
vector(n) | 4-byte float | 16,000 | 2,000 | 4 × n + 8 bytes | 0.1.0 |
halfvec(n) | 2-byte float (±65,504) | 16,000 | 4,000 | 2 × n + 8 bytes | 0.7.0 |
bit(n) | 1 bit (Postgres built-in type) | — | 64,000 | n / 8 + 8 bytes | indexable 0.7.0 |
sparsevec(n) | index + 4-byte float per non-zero | 1,000,000,000 (16,000 non-zeros) | 1,000 non-zeros | 8 × non-zeros + 16 bytes | 0.7.0 |
The type modifier (n) is optional for storage but required for indexing (error if missing). Exceeding limits raises real errors, e.g.:
demo=# CREATE TABLE toomany (embedding vector(16001));
ERROR: dimensions for type vector cannot exceed 16000
LINE 1: CREATE TABLE toomany (embedding vector(16001));
^Input formats: '[1,2,3]' for vector/halfvec, '101' for bit, '{1:1.5,3:2}/5' (1-based index:value, then total dimensions) for sparsevec. NaN and infinities are rejected.
How values are stored#
Since pgvector 0.6.0 the types use EXTERNAL storage: values over about 2 KB — any vector beyond ~500 dimensions — are moved to the table's TOAST relation uncompressed. That keeps the main table narrow, so filters on other columns scan fast, but reading a vector costs an extra lookup. Measured table sizes for 100,000 rows:
| Column | Bytes per value | Table size (100K rows) |
|---|---|---|
vector(1536) | 6,152 | 795 MB |
halfvec(1536) | 3,080 | 400 MB |
vector(3072) (99K rows) | 12,296 | 1.28 GB |
Estimate your own with the storage calculator.
Distance operators#
Every operator returns a distance where smaller means closer, so ORDER BY … LIMIT k always means "k nearest". The full catalog from our server:
demo=# SELECT o.oprname AS operator, lt.typname AS left_type, rt.typname AS right_type, p.proname AS function
demo-# FROM pg_operator o
demo-# JOIN pg_type lt ON lt.oid = o.oprleft
demo-# JOIN pg_type rt ON rt.oid = o.oprright
demo-# JOIN pg_proc p ON p.oid = o.oprcode
demo-# WHERE o.oprname IN ('<->', '<=>', '<#>', '<+>', '<~>', '<%>')
demo-# AND lt.typname IN ('vector', 'halfvec', 'sparsevec', 'bit')
demo-# ORDER BY 1, 2;
operator | left_type | right_type | function
----------+-----------+------------+----------------------------------
<#> | halfvec | halfvec | halfvec_negative_inner_product
<#> | sparsevec | sparsevec | sparsevec_negative_inner_product
<#> | vector | vector | vector_negative_inner_product
<%> | bit | bit | jaccard_distance
<+> | halfvec | halfvec | l1_distance
<+> | sparsevec | sparsevec | l1_distance
<+> | vector | vector | l1_distance
<-> | halfvec | halfvec | l2_distance
<-> | sparsevec | sparsevec | l2_distance
<-> | vector | vector | l2_distance
<=> | halfvec | halfvec | cosine_distance
<=> | sparsevec | sparsevec | cosine_distance
<=> | vector | vector | cosine_distance
<~> | bit | bit | hamming_distance
(14 rows)| Operator | Distance | Range | Notes |
|---|---|---|---|
<-> | L2 / Euclidean | 0 → ∞ | |
<=> | Cosine distance | 0 → 2 | Similarity = 1 - (a <=> b). NaN if either vector is all zeros. |
<#> | Negative inner product | −∞ → ∞ | Multiply by −1 for the inner product. Fastest choice for normalised vectors. |
<+> | L1 / taxicab | 0 → ∞ | 0.7.0+ |
<~> | Hamming (bit) | 0 → n | Number of differing bits |
<%> | Jaccard (bit) | 0 → 1 |
Which one to use for your embeddings — and why it matters less than matching the index — is shown visually in the distance playground.
Functions#
demo=# SELECT l2_distance('[1,2,3]'::vector, '[4,5,6]') AS l2,
demo-# inner_product('[1,2,3]'::vector, '[4,5,6]') AS ip,
demo-# cosine_distance('[1,2,3]'::vector, '[4,5,6]') AS cosine,
demo-# l1_distance('[1,2,3]'::vector, '[4,5,6]') AS l1;
l2 | ip | cosine | l1
-------------------+----+----------------------+----
5.196152422706632 | 32 | 0.025368153802923787 | 9
(1 row)
demo=# SELECT vector_dims('[1,2,3]'::vector) AS dims,
demo-# vector_norm('[3,4]'::vector) AS norm,
demo-# l2_normalize('[3,4]'::vector) AS normalized,
demo-# subvector('[1,2,3,4,5]'::vector, 2, 3) AS sub;
dims | norm | normalized | sub
------+------+------------+---------
3 | 5 | [0.6,0.8] | [2,3,4]
(1 row)
demo=# SELECT '[1,2]'::vector + '[3,4]' AS added, '[1,2]'::vector - '[3,4]' AS subtracted,
demo-# '[1,2]'::vector * '[3,4]' AS multiplied, '[1,2]'::vector || '[3]' AS concatenated;
added | subtracted | multiplied | concatenated
-------+------------+------------+--------------
[4,6] | [-2,-2] | [3,8] | [1,2,3]
(1 row)| Function | Types | Returns |
|---|---|---|
l2_distance(a, b), cosine_distance, inner_product, l1_distance | vector, halfvec, sparsevec | double precision (inner_product is positive) |
hamming_distance(a, b), jaccard_distance(a, b) | bit | double precision |
vector_dims(v) | vector, halfvec | integer |
vector_norm(v) / l2_norm(v) | vector / halfvec, sparsevec | double precision |
l2_normalize(v) | vector, halfvec, sparsevec | same type, unit length (zero vector stays zero) |
binary_quantize(v) | vector, halfvec | bit — 1 where element > 0 |
subvector(v, start, count) | vector, halfvec | same type; start is 1-based |
Arithmetic (verified on both types): +, -, * (element-wise) and || (concatenate) on vector and halfvec.
Aggregates#
demo=# SELECT avg(embedding) AS centroid, sum(embedding) AS total FROM items;
centroid | total
--------------------+----------------------------
[0.5625,0.55,0.55] | [2.25,2.1999998,2.1999998]
(1 row)
demo=# SELECT content ~ '^(cat|kitten)$' AS is_cat, avg(embedding) FROM items GROUP BY 1;
is_cat | avg
--------+-------------------
f | [0.15,0.15,0.95]
t | [0.975,0.95,0.15]
(2 rows)avg() and sum() work on vector and halfvec — useful for centroids, user-profile vectors and clustering.
Casts#
demo=# SELECT ARRAY[1, 2, 3]::vector AS from_int_array,
demo-# '[1,2,3]'::vector::real[] AS to_real_array,
demo-# ARRAY[1.5, 2.5]::float8[]::vector AS from_float8;
from_int_array | to_real_array | from_float8
----------------+---------------+-------------
[1,2,3] | {1,2,3} | [1.5,2.5]
(1 row)Also: vector ↔ halfvec, vector ↔ sparsevec, halfvec ↔ sparsevec, integer/real/double/numeric arrays → vector, and arrays → sparsevec (0.8.0+).
Index operator classes#
An index only serves queries that use its operator class's operator. Catalog from our server:
demo=# SELECT am.amname AS index_type, opc.opcname AS operator_class, t.typname AS type
demo-# FROM pg_opclass opc
demo-# JOIN pg_am am ON am.oid = opc.opcmethod
demo-# JOIN pg_type t ON t.oid = opc.opcintype
demo-# WHERE am.amname IN ('hnsw', 'ivfflat')
demo-# ORDER BY 1, 3, 2;
index_type | operator_class | type
------------+----------------------+-----------
hnsw | bit_hamming_ops | bit
hnsw | bit_jaccard_ops | bit
hnsw | halfvec_cosine_ops | halfvec
hnsw | halfvec_ip_ops | halfvec
hnsw | halfvec_l1_ops | halfvec
hnsw | halfvec_l2_ops | halfvec
hnsw | sparsevec_cosine_ops | sparsevec
hnsw | sparsevec_ip_ops | sparsevec
hnsw | sparsevec_l1_ops | sparsevec
hnsw | sparsevec_l2_ops | sparsevec
hnsw | vector_cosine_ops | vector
hnsw | vector_ip_ops | vector
hnsw | vector_l1_ops | vector
hnsw | vector_l2_ops | vector
ivfflat | bit_hamming_ops | bit
ivfflat | halfvec_cosine_ops | halfvec
ivfflat | halfvec_ip_ops | halfvec
ivfflat | halfvec_l2_ops | halfvec
ivfflat | vector_cosine_ops | vector
ivfflat | vector_ip_ops | vector
ivfflat | vector_l2_ops | vector
(21 rows)IVFFlat has no operator classes for L1, Jaccard or sparsevec; use HNSW for those.
Index options#
| Index | Option | Default | Allowed | Effect |
|---|---|---|---|---|
| HNSW | m | 16 | 2–100 | Links per node; more = higher recall ceiling, bigger and slower to build |
| HNSW | ef_construction | 64 | 4–1,000 (≥ 2 × m) | Candidate list while building; more = better graph, slower build |
| IVFFlat | lists | 100 | 1–32,768 | Number of k-means clusters |
Each value above was checked by trying to exceed it (errors here). What they do to recall, latency and build time: HNSW, IVFFlat.
Settings (GUCs)#
demo=# SELECT name, setting, min_val, max_val, enumvals, short_desc
demo-# FROM pg_settings WHERE name LIKE 'hnsw.%' OR name LIKE 'ivfflat.%' ORDER BY name;
name | setting | min_val | max_val | enumvals | short_desc
--------------------------+---------+---------+------------+----------------------------------+------------------------------------------------------------
hnsw.ef_search | 40 | 1 | 1000 | | Sets the size of the dynamic candidate list for search
hnsw.iterative_scan | off | | | {off,relaxed_order,strict_order} | Sets the mode for iterative scans
hnsw.max_scan_tuples | 20000 | 1 | 2147483647 | | Sets the max number of tuples to visit for iterative scans
hnsw.scan_mem_multiplier | 1 | 1 | 1000 | | Sets the multiple of work_mem to use for iterative scans
ivfflat.iterative_scan | off | | | {off,relaxed_order} | Sets the mode for iterative scans
ivfflat.max_probes | 32768 | 1 | 32768 | | Sets the max number of probes for iterative scans
ivfflat.probes | 1 | 1 | 32768 | | Sets the number of probes
(7 rows)Set them per session (SET), per transaction (SET LOCAL), per role or database (ALTER ROLE … SET), or in postgresql.conf. Also relevant: maintenance_work_mem and max_parallel_maintenance_workers for builds, max_parallel_workers_per_gather for exact scans.
Several models in one table#
Store different-sized embeddings in an untyped vector column and give each model its own expression + partial index:
demo=# CREATE TABLE multi (id bigserial PRIMARY KEY, model_id int, embedding vector);
CREATE TABLE
demo=# INSERT INTO multi (model_id, embedding) VALUES (1, '[1,2,3]'), (2, '[1,2,3,4,5]');
INSERT 0 2
demo=# CREATE INDEX ON multi USING hnsw ((embedding::vector(3)) vector_l2_ops) WHERE (model_id = 1);
CREATE INDEX
demo=# SET enable_seqscan = off;
SET
demo=# EXPLAIN (COSTS OFF) SELECT id FROM multi WHERE model_id = 1 ORDER BY embedding::vector(3) <-> '[3,1,2]' LIMIT 5;
QUERY PLAN
------------------------------------------------------------------
Limit
-> Index Scan using multi_embedding_idx on multi
Order By: ((embedding)::vector(3) <-> '[3,1,2]'::vector)
(3 rows)
demo=# RESET enable_seqscan;
RESETQueries must repeat both the cast and the WHERE condition to match the index. (enable_seqscan is off only because this demo table has two rows; on a real table the planner picks the index by itself.)
Indexing part of a vector#
For Matryoshka-style models, index the first k dimensions and re-rank with the full vector:
CREATE INDEX ON items USING hnsw ((subvector(embedding, 1, 512)::vector(512)) vector_cosine_ops);
SELECT id FROM (
SELECT id, embedding FROM items
ORDER BY subvector(embedding, 1, 512)::vector(512) <=> subvector($1::vector, 1, 512)
LIMIT 40
) candidates
ORDER BY embedding <=> $1
LIMIT 10;How much the first 512 dimensions of a text-embedding-3-large vector preserve, measured: shortening embeddings.
Monitoring#
- Build progress:
pg_stat_progress_create_index(query). - Index size:
pg_relation_size('index_name')(query). - Is the index used?
EXPLAIN (ANALYZE, BUFFERS)(what to look for). - Recall: compare with exact search (query).
- Slow queries:
pg_stat_statements.
Glossary#
| Embedding | A list of numbers a model produces for a piece of text, image or audio; similar inputs get nearby vectors. |
|---|---|
| k-NN / ANN | k-nearest-neighbour search. Exact k-NN compares the query with every row; approximate (ANN) indexes skip most rows and may miss some true neighbours. |
| Recall@k | Fraction of the true k nearest neighbours an approximate search returns. 0.95 = 19 of 20 correct. |
| HNSW | Hierarchical Navigable Small World — a layered proximity graph searched greedily. Guide. |
| IVFFlat | Inverted file with flat (uncompressed) lists — k-means clusters, of which a few are scanned per query. Guide. |
| Quantization | Storing vectors with fewer bits (half precision, 1 bit) to save space and time at some cost in accuracy. Guide. |
| Iterative index scan | pgvector 0.8.0+ feature that keeps scanning an index until enough rows pass a WHERE filter. Guide. |
Frequently asked questions
What is the maximum number of dimensions in pgvector?
A vector column can store up to 16,000 dimensions, but HNSW and IVFFlat indexes accept at most 2,000. halfvec stores up to 16,000 and indexes up to 4,000; bit indexes up to 64,000; sparsevec stores up to 1,000,000,000 dimensions with at most 16,000 non-zero elements, and indexes up to 1,000 non-zero elements.
How much storage does a 1,536-dimension vector use?
4 × 1,536 + 8 = 6,152 bytes as vector, or 3,080 bytes as halfvec. Because that's larger than PostgreSQL's ~2 KB TOAST threshold, values are stored out of line. In our benchmark, 100,000 such rows occupied 795 MB of table storage before indexes.