pgvector

pgvector is a Postgres extension, not a separate database. You get vector similarity search without leaving Postgres — same transactions, same Row-Level Security, same backup tooling.

CREATE EXTENSION IF NOT EXISTS vector;

Info

The word CREATE is misleading. This does not install software. The shared library (vector.so) must already be present on disk (via shared_preload_libraries or the OS package manager). CREATE EXTENSION simply registers the extension into the current database’s catalog — it creates the types, operators, and index access methods in pg_catalog.

The vector type

A fixed-dimension column storing a float32 array:

embedding vector(384)

The dimension (384 above) must match the embedding model’s output exactly. A 384-dim model (e.g. all-MiniLM-L6-v2) produces 384 floats per embedding; storing them in a vector(768) column will fail at insert time.

Internally pgvector stores vectors as a contiguous array of float4 values, so a vector(384) column uses $384\times 4=1536$ bytes per row plus a small header.

Distance operators

pgvector defines three distance operators:

Operator	Distance	Formula	Order
<->	L2 (Euclidean)	$\sqrt{{\sum }_i(a_i-b_i{)}^2}$	smaller = closer
<=>	Cosine distance	$1-\frac{a\cdot b}{\Vert a\Vert \Vert b\Vert }$	smaller = closer
<#>	Negative inner product	$-(a\cdot b)$	smaller = higher similarity

When to use each

• Cosine (<=>) — the default choice for text embeddings. Text embedding models typically produce normalized vectors, so direction matters more than magnitude. Two chunks about the same topic point in the same direction regardless of document length. L2 would penalize longer documents unfairly.
• L2 (<->) — useful when absolute distances matter (e.g. image feature vectors, geospatial embeddings).
• Inner product (<#>) — when vectors are already normalized, inner product and cosine are equivalent. The negative sign exists so that ORDER BY ... ASC returns the most similar first, consistent with the other operators.

Index types

Without an index, pgvector performs exact (brute-force) kNN (k-Nearest Neighbours) search — $O(N)$ per query, comparing the query vector against every row. Two ANN (Approximate Nearest Neighbour) index types trade a small recall loss for dramatically faster queries. See Vector Search and Vector Databases for the underlying ANN algorithm theory.

IVFFlat (Inverted File Flat)

IVFFlat partitions vectors into clusters using k-means — an algorithm that assigns each vector to the nearest of $k$ centroids, then recomputes centroids as the mean of their cluster, repeating until stable — (the “IVF” — Inverted File — part), then at query time searches only the nearest clusters rather than all rows. “Flat” means the vectors inside each cluster are stored as-is (uncompressed), so distance computation within a cluster is exact.

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

Parameters:

• lists — number of k-means partitions. Rule of thumb: $\sqrt{N}$ for under 1M rows, $N/1000$ for larger datasets.
• probes (query time) — how many lists to search. Higher probes = better recall, slower queries. Set via SET ivfflat.probes = 10;.

Warning

IVFFlat requires training data — the table must already contain representative rows when the index is created. Building the index on an empty table produces useless clusters. After bulk inserts, run VACUUM to update the index statistics.

HNSW

Builds a hierarchical navigable small world graph. Each vector is a node connected to its nearest neighbors across multiple layers.

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

Parameters:

• m — max connections per node (default 16). Higher = better recall but more memory and slower builds.
• ef_construction — search width during build (default 64). Higher = better index quality but slower construction.
• ef_search (query time) — search width at query time. Set via SET hnsw.ef_search = 100;. Higher = better recall.

Tip

HNSW is generally preferred over IVFFlat because:

• No training step — works on empty tables, no VACUUM dance after bulk loads
• Better recall at any scale
• Incrementally maintained as rows are inserted

The trade-off is that HNSW indexes use more memory and are slower to build than IVFFlat.

Operator classes

The index must use the operator class matching the distance operator in your queries:

Operator	Operator class
<->	vector_l2_ops
<=>	vector_cosine_ops
<#>	vector_ip_ops

If you create an index with vector_cosine_ops but query with <-> (L2), Postgres will not use the index — it falls back to a sequential scan with no warning.

Practical patterns

No native Rust type in sqlx

pgvector has no native Rust type in the sqlx driver (sqlx is a Rust async SQL toolkit with compile-time checked queries). Vectors must be passed as a formatted string and cast at the SQL level:

let embedding_str = format!(
    "[{}]",
    chunk.embedding.iter()
        .map(ToString::to_string)
        .collect::<Vec<_>>()
        .join(",")
);
 
// sea-query (a Rust SQL query builder library) — inject as parameterized custom expression
Expr::cust_with_values("?::vector", [embedding_str.into()])

The ::vector cast tells Postgres to parse the string literal into the vector type. Using Expr::cust_with_values keeps this safe from SQL injection.

Cosine similarity as a score

To return a similarity score (1.0 = identical, 0.0 = orthogonal), invert the cosine distance:

SELECT *, 1.0 - (embedding <=> $1::vector) AS score
FROM content_chunks
ORDER BY embedding <=> $1::vector ASC
LIMIT 20;

This pattern is useful for the vector leg of hybrid search (combined with BM25 via Reciprocal Rank Fusion).

Gotchas

Warning

Dimension mismatch is a runtime error. Inserting a 768-dim vector into a vector(384) column fails at execution, not at planning. There is no compile-time safety net.

• Index/operator class mismatch — an index built with vector_cosine_ops is invisible to queries using <->. Always verify the operator class matches.
• IVFFlat needs VACUUM — after bulk inserts, new rows are not in any cluster until VACUUM runs. Queries silently miss them.
• Exact vs approximate results — index scans return approximate results. For higher recall, tune SET hnsw.ef_search (HNSW) or SET ivfflat.probes (IVFFlat). For exact results on small tables, drop the index or use SET enable_indexscan = off.
• Memory — HNSW indexes live in memory. For large tables, monitor pg_relation_size() on the index and ensure maintenance_work_mem is large enough during index creation.

See also

• Vector Search and Vector Databases — ANN algorithm theory (LSH, IVF, HNSW, ScaNN)
• Full-Text Search and ParadeDB — BM25 keyword search, hybrid retrieval
• Reciprocal Rank Fusion — merging vector and keyword ranked lists
• Index Types — full Postgres index type guide
• Extensions and Grants — how to install and grant access to extensions