pgvector Documentation

• 1: Getting Started
• 2: Querying & Filtering
• 3: HNSW & IVFFlat Indexing
• 4: Quantization & Sparse Vectors
• 5: Reference, Tuning & FAQ

1 - Getting Started

Installation

Pigsty / Native Linux Packages (Recommended)

Pigsty provides prebuilt native pgvector packages (v0.8.2) for PostgreSQL 14–18 across Debian, Ubuntu, and RHEL/Rocky/AlmaLinux (x86_64 and ARM64):

pig repo add pgdg pigsty -u
pig ext install pgvector

If you also want disk-friendly RaBitQ vector indexes and BM25 ranking, install the full Vchord suite together:

pig ext install pgvector vchord vchord_bm25 pg_tokenizer

Docker

Run the official pgvector container image:

docker run -d \
  --name pgvector-demo \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  pgvector/pgvector:pg18

Or use the all-in-one Vchord Suite image, which bundles pgvector, vchord, vchord_bm25, and pg_tokenizer:

docker run -d \
  --name vchord-suite \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  tensorchord/vchord-suite:pg18-latest

Linux (PGDG APT / YUM)

• Debian / Ubuntu (via PostgreSQL APT Repository):

  sudo apt install postgresql-18-pgvector

• RHEL / Rocky / AlmaLinux / Fedora (via PostgreSQL YUM Repository):

  sudo dnf install pgvector_18

macOS (Homebrew)

brew install pgvector

Build from Source (Linux / macOS)

Ensure PostgreSQL development headers (postgresql-server-dev-* or postgresql*-devel), make, and a C compiler are installed:

cd /tmp
git clone --branch v0.8.2 https://github.com/pgvector/pgvector.git
cd pgvector
make
sudo make install

Enable the Extension

Connect to your target database and enable vector (this must be done once per database where you want to use it):

CREATE EXTENSION IF NOT EXISTS vector;

Check the installed version:

SELECT extversion FROM pg_extension WHERE extname = 'vector';

Upgrading pgvector

After installing a newer binary package on the server, upgrade the SQL catalog definitions inside each database:

ALTER EXTENSION vector UPDATE;

Quick Start

1. Create a Table with a vector Column

Create a table storing 3-dimensional single-precision (float32) vectors:

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

You can also add a vector column to an existing table:

ALTER TABLE items ADD COLUMN embedding vector(3);

2. Insert, Upsert, Update, and Delete Vectors

Insert vectors using the string literal format '[x1,x2,x3]':

INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');

Upsert vectors with ON CONFLICT:

INSERT INTO items (id, embedding) VALUES (1, '[1,2,3]'), (2, '[4,5,6]')
ON CONFLICT (id) DO UPDATE SET embedding = EXCLUDED.embedding;

Update and delete rows with standard SQL:

UPDATE items SET embedding = '[1,2,3]' WHERE id = 1;
DELETE FROM items WHERE id = 1;

3. Bulk Loading with COPY

For large datasets (millions of vectors), always prefer COPY over row-by-row INSERT:

COPY items (embedding) FROM STDIN WITH (FORMAT BINARY);

4. Query Nearest Neighbors

Find the 5 closest vectors by L2 (Euclidean) distance:

SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

Next, read Querying & Filtering for all distance metrics and filtering patterns, or HNSW & IVFFlat Indexing to accelerate queries over large tables.

2 - Querying & Filtering

Distance Operators

pgvector supports six distance operators for ordering (ORDER BY) and threshold filtering:

Nearest-Neighbor Queries

-- Top 5 nearest neighbors by L2 distance
SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

-- Top 5 nearest neighbors by cosine distance
SELECT * FROM items ORDER BY embedding <=> '[3,1,2]' LIMIT 5;

-- Top 5 nearest neighbors by maximum inner product
SELECT * FROM items ORDER BY embedding <#> '[3,1,2]' LIMIT 5;

Calculating Distances & Similarity Scores

Retrieve the distance alongside each row:

-- L2 distance
SELECT id, embedding <-> '[3,1,2]' AS distance FROM items;

-- Actual inner product (multiply negative inner product by -1)
SELECT id, (embedding <#> '[3,1,2]') * -1 AS inner_product FROM items;

-- Cosine similarity (1 - cosine distance)
SELECT id, 1 - (embedding <=> '[3,1,2]') AS cosine_similarity FROM items;

Distance Threshold Queries

Get all rows within a given distance threshold:

SELECT * FROM items WHERE embedding <-> '[3,1,2]' < 5;

Combine with ORDER BY and LIMIT to use an HNSW or IVFFlat index:

SELECT * FROM items
WHERE embedding <-> '[3,1,2]' < 5
ORDER BY embedding <-> '[3,1,2]'
LIMIT 5;

Aggregates

Compute the mean (AVG) or component-wise sum (SUM) of vectors:

SELECT AVG(embedding) FROM items;

SELECT category_id, AVG(embedding) AS centroid
FROM items
GROUP BY category_id;

Filtering (WHERE Clauses) & Iterative Index Scans

Combine vector search with relational predicates:

SELECT * FROM items WHERE category_id = 123 ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

Indexing Filter Columns

1. Standard B-tree index on the filter column (good when category_id = 123 matches a very small fraction of rows):

   CREATE INDEX ON items (category_id);

2. Partial vector index (ideal when you only filter by a few known values):

   CREATE INDEX ON items USING hnsw (embedding vector_l2_ops) WHERE (category_id = 123);

3. Partitioning (ideal when filtering across a bounded set of high-volume tenants or time ranges):

   CREATE TABLE items (embedding vector(3), category_id int) PARTITION BY LIST(category_id);

Iterative Index Scans (pgvector 0.8.0+)

By default, approximate vector index scans examine a fixed number of candidates (hnsw.ef_search or ivfflat.probes). If a WHERE filter rejects most of those candidates, the query may return fewer rows than LIMIT.

Starting in pgvector 0.8.0, enable iterative index scans so the index automatically continues scanning until enough matching rows are found (or max_scan_tuples / max_probes is reached):

-- HNSW iterative scan
SET hnsw.iterative_scan = relaxed_order;
-- Or strict_order to guarantee exact distance ordering across iterations:
-- SET hnsw.iterative_scan = strict_order;

-- IVFFlat iterative scan
SET ivfflat.iterative_scan = relaxed_order;

When using relaxed_order, results may be slightly out of order by distance; wrap the query in a CTE or subquery to re-sort the final LIMIT rows:

WITH relaxed_results AS MATERIALIZED (
    SELECT id, embedding <-> '[3,1,2]' AS distance
    FROM items
    WHERE category_id = 123
    ORDER BY distance
    LIMIT 5
)
SELECT * FROM relaxed_results ORDER BY distance + 0;

Controlling Iterative Scan Budgets

-- Maximum tuples HNSW will visit during an iterative scan (default: 20000)
SET hnsw.max_scan_tuples = 20000;

-- Maximum memory multiplier (multiple of work_mem) HNSW uses for visited state (default: 1)
SET hnsw.scan_mem_multiplier = 2;

-- Maximum probes IVFFlat will visit during an iterative scan
SET ivfflat.max_probes = 100;

Hybrid Search

You can combine pgvector semantic similarity search with lexical full-text search using Reciprocal Rank Fusion (RRF).

Hybrid Search with PostgreSQL tsvector

SELECT id, content FROM (
    SELECT id, content, 1.0 / (60 + RANK() OVER (ORDER BY embedding <=> '[3,1,2]')) AS score
    FROM items
    ORDER BY embedding <=> '[3,1,2]' LIMIT 20
    UNION ALL
    SELECT id, content, 1.0 / (60 + RANK() OVER (ORDER BY ts_rank_cd(textsearch, query) DESC)) AS score
    FROM items, plainto_tsquery('english', 'hello search') query
    WHERE textsearch @@ query
    ORDER BY ts_rank_cd(textsearch, query) DESC LIMIT 20
) searches
GROUP BY id, content
ORDER BY SUM(score) DESC
LIMIT 5;

Hybrid Search with vchord_bm25 or pg_bestmatch

For true Okapi BM25 ranking with document-length normalization and corpus IDF:

• Pair pgvector (or vchord) with vchord_bm25 and pg_tokenizer — see the Hybrid Search Guide.
• Or generate BM25-weighted sparsevec vectors directly inside PostgreSQL using pg_bestmatch and index them with pgvector HNSW (sparsevec_ip_ops).

3 - HNSW & IVFFlat Indexing

By default, pgvector performs exact nearest-neighbor search (sequential scan), which delivers 100% recall. When your dataset grows beyond tens of thousands of rows, add an approximate nearest-neighbor (ANN) index to trade a small amount of recall for orders-of-magnitude higher query speed.

pgvector includes two built-in index access methods:

• HNSW (Hierarchical Navigable Small World): Multi-layer proximity graph. Higher recall-speed trade-off and no training step required, at the cost of slower build times and higher memory usage.
• IVFFlat (Inverted File with Flat Vectors): K-means clustering over vectors. Faster index builds and smaller index footprint, but requires data to exist before index creation and delivers lower recall per probe than HNSW.

HNSW Indexes

An HNSW index constructs a multilayer navigation graph. Unlike IVFFlat, it has no training phase, so you can create an HNSW index on an empty table before loading data.

Creating an HNSW Index

Create an index matching the distance operator used in your queries:

-- L2 distance (<->)
CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);

-- Inner product (<#>)
CREATE INDEX ON items USING hnsw (embedding vector_ip_ops);

-- Cosine distance (<=>)
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

-- L1 distance (<+>)
CREATE INDEX ON items USING hnsw (embedding vector_l1_ops);

-- Hamming distance (<~>) on bit columns
CREATE INDEX ON items USING hnsw (embedding bit_hamming_ops);

-- Jaccard distance (<%>) on bit columns
CREATE INDEX ON items USING hnsw (embedding bit_jaccard_ops);

Supported dimensionality limits for HNSW:

• vector: up to 2,000 dimensions
• halfvec: up to 4,000 dimensions
• bit: up to 64,000 dimensions
• sparsevec: up to 1,000 non-zero elements

HNSW Build Parameters

• m (default 16, range 2–100): Maximum number of bidirectional links per node per layer. Higher m improves recall on high-intrinsic-dimensionality datasets at the cost of larger index size and longer build times.
• ef_construction (default 64, range 4–1000): Size of the dynamic candidate list during graph construction. Must be $\ge 2 \times m$.

CREATE INDEX ON items USING hnsw (embedding vector_l2_ops)
WITH (m = 16, ef_construction = 64);

HNSW Query Options

Specify the size of the dynamic candidate list during search (hnsw.ef_search, default 40, range 1–1000). Higher values increase recall at the cost of latency:

SET hnsw.ef_search = 100;

-- Or scope to a single transaction:
BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;
COMMIT;

Accelerating HNSW Index Builds

HNSW construction speed drops sharply once the graph no longer fits into maintenance_work_mem. Before building an HNSW index on a large table, increase maintenance_work_mem and max_parallel_maintenance_workers:

SET maintenance_work_mem = '8GB';
SET max_parallel_maintenance_workers = 7; -- plus leader = 8 total workers

IVFFlat Indexes

An IVFFlat index partitions vectors into lists Voronoi cells using k-means clustering, and at query time inspects the closest ivfflat.probes lists.

Creating an IVFFlat Index

Choose lists based on table size:

• Up to 1 million rows: rows / 1000
• Over 1 million rows: sqrt(rows)

-- L2 distance (<->)
CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);

-- Inner product (<#>)
CREATE INDEX ON items USING ivfflat (embedding vector_ip_ops) WITH (lists = 100);

-- Cosine distance (<=>)
CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

-- Hamming distance (<~>) on bit columns
CREATE INDEX ON items USING ivfflat (embedding bit_hamming_ops) WITH (lists = 100);

Supported dimensionality limits for IVFFlat:

• vector: up to 2,000 dimensions
• halfvec: up to 4,000 dimensions
• bit: up to 64,000 dimensions

IVFFlat Query Options

Set the number of lists probed per query (ivfflat.probes, default 1). A good starting point is sqrt(lists):

BEGIN;
SET LOCAL ivfflat.probes = 10;
SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;
COMMIT;

Index Build Progress & Maintenance

Monitoring Index Build Progress

Query pg_stat_progress_create_index while CREATE INDEX is running:

SELECT
    phase,
    round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS "%"
FROM pg_stat_progress_create_index;

• HNSW phases: initializing $\rightarrow$ loading tuples
• IVFFlat phases: initializing $\rightarrow$ performing k-means $\rightarrow$ assigning tuples $\rightarrow$ loading tuples

Vacuuming HNSW Indexes

Vacuuming an HNSW index after heavy deletes can be slow because graph links must be repaired. To speed up DELETE + VACUUM cycles on HNSW tables, rebuild the index concurrently first:

REINDEX INDEX CONCURRENTLY items_embedding_idx;
VACUUM items;

4 - Quantization & Sparse Vectors

pgvector supports four vector representations so you can balance memory footprint, dimensionality limits, and recall:

Half-Precision Vectors (halfvec)

Use the halfvec type (pgvector 0.7.0+) to store 16-bit floating-point vectors, cutting table and index footprint in half compared to vector:

CREATE TABLE items (
    id bigserial PRIMARY KEY,
    embedding halfvec(3)
);

CREATE INDEX ON items USING hnsw (embedding halfvec_l2_ops);

Half-Precision Expression Indexing (Scalar Quantization)

If you want to keep full-precision vector values in the table (or index vectors between 2,001 and 4,000 dimensions, such as OpenAI text-embedding-3-large at 3,072 dimensions), build an expression index casting vector to halfvec:

CREATE TABLE items (
    id bigserial PRIMARY KEY,
    embedding vector(3072)
);

CREATE INDEX ON items USING hnsw ((embedding::halfvec(3072)) halfvec_l2_ops);

-- Query using the exact expression from the index
SELECT * FROM items
ORDER BY embedding::halfvec(3072) <-> '[1,2,3,...]'
LIMIT 5;

Binary Vectors (bit) & Binary Quantization

Use PostgreSQL’s built-in bit(n) type with pgvector’s Hamming (<~>) and Jaccard (<%>) operators to store and index binary vectors up to 64,000 bits:

CREATE TABLE items (
    id bigserial PRIMARY KEY,
    embedding bit(3)
);

INSERT INTO items (embedding) VALUES ('000'), ('111');

-- Nearest neighbors by Hamming distance
SELECT * FROM items ORDER BY embedding <~> '101' LIMIT 5;

-- Index with HNSW
CREATE INDEX ON items USING hnsw (embedding bit_hamming_ops);

Binary Quantization with Full-Precision Re-Ranking

Use binary_quantize(vector) to compress 32-bit floats to 1 bit per dimension (1 if $> 0$, 0 otherwise) in an expression index, then re-rank the top candidates using the exact vector column:

CREATE INDEX ON items USING hnsw ((binary_quantize(embedding)::bit(3)) bit_hamming_ops);

SELECT * FROM (
    SELECT * FROM items
    ORDER BY binary_quantize(embedding)::bit(3) <~> binary_quantize('[1,-2,3]')
    LIMIT 20
) AS candidates
ORDER BY embedding <=> '[1,-2,3]'
LIMIT 5;

Sparse Vectors (sparsevec)

Use sparsevec (pgvector 0.7.0+) to store vectors where most dimensions are zero (such as SPLADE, BGE-M3 sparse outputs, or BM25 term vectors generated by pg_bestmatch).

Sparse Literal Format

Format: '{index1:value1,index2:value2}/dimensions', where indices are 1-based:

CREATE TABLE items (
    id bigserial PRIMARY KEY,
    embedding sparsevec(5)
);

INSERT INTO items (embedding) VALUES ('{1:1,3:2,5:3}/5'), ('{1:4,3:5,5:6}/5');

-- Query nearest neighbors
SELECT * FROM items ORDER BY embedding <-> '{1:3,3:1,5:2}/5' LIMIT 5;

-- Create an HNSW index (up to 1,000 non-zero elements)
CREATE INDEX ON items USING hnsw (embedding sparsevec_l2_ops);

Matryoshka Sub-Vector Indexing (subvector)

For models trained with Matryoshka Representation Learning (MRL), you can index only the leading dimensions with subvector(vector, start, count) and re-rank on the full vector:

CREATE INDEX ON items USING hnsw ((subvector(embedding, 1, 512)::vector(512)) vector_cosine_ops);

SELECT * FROM (
    SELECT * FROM items
    ORDER BY subvector(embedding, 1, 512)::vector(512) <=> subvector('[1,2,3,...]'::vector, 1, 512)
    LIMIT 20
) AS candidates
ORDER BY embedding <=> '[1,2,3,...]'
LIMIT 5;

5 - Reference, Tuning & FAQ

SQL Function Reference

Vector Functions

Element-Wise Arithmetic Operators

• a + b: Element-wise addition (vector, halfvec)
• a - b: Element-wise subtraction (vector, halfvec)
• a * b: Element-wise multiplication (vector, halfvec)
• a || b: Vector concatenation (vector, halfvec)

GUC Parameters

Performance Tuning & Monitoring

1. Check Index Size

SELECT pg_size_pretty(pg_relation_size('items_embedding_idx'));

2. Inspect Query Execution Plans

Use EXPLAIN ANALYZE to confirm whether an index scan is chosen and measure execution latency:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

3. Monitor Slow Queries with pg_stat_statements

SELECT
    calls,
    round((total_plan_time + total_exec_time) / calls) AS avg_time_ms,
    round((total_plan_time + total_exec_time) / 60000) AS total_time_min,
    query
FROM pg_stat_statements
ORDER BY total_plan_time + total_exec_time DESC
LIMIT 20;

4. Measure Recall

Compare approximate index results against exact sequential scan results inside a transaction:

BEGIN;
SET LOCAL enable_indexscan = off;
-- Run exact query here to collect ground-truth IDs
COMMIT;

Frequently Asked Questions (FAQ)

Why isn’t my query using the vector index?

1. Missing ORDER BY ... LIMIT: Index scans require both an ORDER BY <operator> and a LIMIT clause.
2. Mismatched operator class: The query operator must match the index operator class (<-> requires vector_l2_ops, <#> requires vector_ip_ops, <=> requires vector_cosine_ops).
3. Small table cost estimation: On small tables, PostgreSQL’s planner may correctly determine that a sequential scan is cheaper than an index scan. To force index usage during testing, run SET LOCAL enable_seqscan = off;.

How do I index vectors with more than 2,000 dimensions?

pgvector HNSW and IVFFlat indexes cap vector columns at 2,000 dimensions because PostgreSQL index tuples cannot exceed 8 KB page limits. You have three options:

1. Install vchord: vchord’s vchordrq index supports up to 60,000 dimensions directly on vector and halfvec columns.
2. Half-precision expression indexing: Cast to halfvec ((embedding::halfvec(3072)) halfvec_l2_ops) to index up to 4,000 dimensions.
3. Binary quantization: Use (binary_quantize(embedding)::bit(N)) bit_hamming_ops to index up to 64,000 dimensions.

Can I store vectors with different dimensions in the same column?

Yes, declare the column without a dimension modifier (embedding vector), though you can only create an index on rows that share the same dimension via a partial expression index:

CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector);
CREATE INDEX ON items USING hnsw ((embedding::vector(3)) vector_l2_ops) WHERE (vector_dims(embedding) = 3);

Do vectors contain NULL or NaN / Infinity?

Vectors cannot contain NULL, NaN, or Infinity elements. A whole column value can be NULL; note that NULL vectors are not indexed in IVFFlat and are sorted last by default unless NULLS FIRST is specified.