PostgreSQL & pgvector

This content was generated with the assistance of AI and should be verified against the official documentation of each tool and framework before being relied on in production.

This section’s bibliography lists the reference material consulted while preparing these pages.

Examples on this page target PostgreSQL 17 with pgvector 0.8.6 (the pgvector/pgvector:pg17 Docker image), Python 3.12 with psycopg 3 and pgvector (pgvector-python) 0.5, and langchain-postgres 0.0.18. pgvector adds vector types, distance operators and ANN indexes directly to an ordinary PostgreSQL database, so the DocsAssistant corpus lives as regular rows alongside its embeddings, searchable with plain SQL and joined against any other table in the schema.

Installing pgvector

pgvector ships as a Postgres extension, not a separate server — the pgvector/pgvector Docker image bundles it on top of the official postgres image at a matching major version:

# compose.yaml
services:
  postgres:
    image: pgvector/pgvector:pg17
    environment:
      POSTGRES_DB: docsassistant
      POSTGRES_USER: docs
      POSTGRES_PASSWORD: docs
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:

Once the container is running, the extension is enabled once per database:

CREATE EXTENSION IF NOT EXISTS vector;

See pgvector: Installation for building from source or installing on a self-managed Postgres instead of using the prebuilt image.

Vector types and their limits

pgvector adds four column types, all built around the same distance operators (below) but trading precision and dimensionality for storage:

Type Storage Notes

vector

4 bytes/dimension (float32)

The default type. Up to 16,000 dimensions can be stored, but only up to 2,000 dimensions can be indexed with hnsw/ivfflat. The DocsAssistant scenario’s chunk_embeddings.embedding column below is vector(768), well under that limit.

halfvec

2 bytes/dimension (float16)

Half the storage of vector at usually near-lossless recall. Indexable up to 4,000 dimensions. Quantization & Compression covers converting an existing vector column to halfvec in detail.

sparsevec

proportional to non-zero entries only

For sparse embeddings (learned lexical vectors such as SPLADE), stored as index/value pairs rather than a dense array. Indexable up to 1,000 non-zero elements per vector.

bit

1 bit/dimension

Binary vectors, typically produced by binary-quantizing a vector/halfvec column rather than embedded directly. Quantization & Compression shows the binary-quantization query that produces bit values from chunk_embeddings.embedding.

The three non-default types look like this against the DocsAssistant schema below — halfvec as an expression index over the existing vector(768) column (so nothing has to be re-embedded), sparsevec in a separate table for learned-sparse vectors (30,522 is the BERT vocabulary size SPLADE-style models emit), and bit produced on the fly by binary_quantize() and re-ranked against the full-precision column:

-- halfvec: half-size HNSW index over the existing vector(768) column; the ORDER BY
-- expression must match the index expression exactly for the index to be used
CREATE INDEX chunk_embeddings_hnsw_half
    ON chunk_embeddings USING hnsw ((embedding::halfvec(768)) halfvec_cosine_ops);

SELECT chunk_id
FROM chunk_embeddings
ORDER BY embedding::halfvec(768) <=> '[0.0123,-0.0871,...]'::halfvec(768)
LIMIT 5;

-- sparsevec: learned-sparse vectors, written as {index:value,...}/dimensions (indices start at 1)
CREATE TABLE chunk_sparse_embeddings (
    chunk_id  bigint PRIMARY KEY REFERENCES chunks (id) ON DELETE CASCADE,
    model     text NOT NULL,
    embedding sparsevec(30522) NOT NULL
);

CREATE INDEX chunk_sparse_embeddings_hnsw
    ON chunk_sparse_embeddings USING hnsw (embedding sparsevec_ip_ops);

SELECT chunk_id
FROM chunk_sparse_embeddings
ORDER BY embedding <#> '{1012:0.82,2045:1.31,7592:0.44}/30522'
LIMIT 5;

-- bit: binary-quantize the dense column, shortlist by Hamming distance, re-rank by cosine
CREATE INDEX chunk_embeddings_hnsw_bit
    ON chunk_embeddings USING hnsw ((binary_quantize(embedding)::bit(768)) bit_hamming_ops);

SELECT chunk_id
FROM (
    SELECT chunk_id, embedding
    FROM chunk_embeddings
    ORDER BY binary_quantize(embedding)::bit(768) <~> binary_quantize('[0.0123,-0.0871,...]'::vector(768))
    LIMIT 20
) candidates
ORDER BY embedding <=> '[0.0123,-0.0871,...]'::vector(768)
LIMIT 5;

See pgvector: Half-precision indexing, pgvector: Sparse vectors and pgvector: Binary quantization for these three examples, and pgvector: README for the full type reference.

Distance operators

Every pgvector type supports a matching set of operators, used directly in ORDER BY to rank rows by similarity:

Operator Distance Typical use

<→

Euclidean (L2)

Raw-magnitude embeddings where absolute distance matters.

<#>

Negative inner product

Cheapest to compute; equivalent to cosine ranking once every vector is unit-normalized.

<⇒

Cosine distance

The DocsAssistant scenario’s default — nomic-embed-text embeddings are compared this way throughout Generating Embeddings in Practice.

<+>

L1 (taxicab/Manhattan)

Occasionally preferred for sparse or robustness-sensitive comparisons.

<~>

Hamming

bit columns only — counts differing bits, the natural distance after binary quantization.

<%>

Jaccard

bit columns only — set-similarity distance, sometimes preferred over Hamming for sparse binary patterns.

 
SELECT chunk_id, embedding <=> '[0.0123,-0.0871,...]'::vector(768) AS distance
FROM chunk_embeddings
ORDER BY distance
LIMIT 5;

See pgvector: Getting started for the operator reference.

HNSW vs. IVFFlat

pgvector offers the same two index families ANN Indexes: Flat, IVF, HNSW and Beyond introduces in general terms, using the parameter names the earlier page’s cross-engine table already maps to M / ef_construction / ef_search:

  • HNSW — USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64) builds the graph at creation time. m bounds edges per node and ef_construction the build-time candidate list, exactly as ANN Indexes: Flat, IVF, HNSW and Beyond's parameter table describes them. There is no training step: rows can be inserted before, during or after index creation, and recall/latency at query time is tuned per session with SET hnsw.ef_search = 100; — no rebuild required.

  • IVFFlat — USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100) partitions vectors into lists clusters via k-means, then a query only scans the ivfflat.probes nearest clusters (SET ivfflat.probes = 10;). Unlike HNSW, IVFFlat’s clusters are chosen from whatever data is present at build time, so it should be built after the table is loaded, not before — an index built on an empty or small table produces poor cluster centroids that keep giving low recall even after more rows are inserted.

Every pgvector operator has a matching operator class to select at index-creation time, and each column type has its own set — a vector_*_ops class cannot be used on a halfvec or sparsevec column (the CREATE INDEX fails). Not every class is available for both index types either:

Column type HNSW operator classes IVFFlat operator classes

vector

vector_l2_ops, vector_ip_ops, vector_cosine_ops, vector_l1_ops

vector_l2_ops, vector_ip_ops, vector_cosine_ops

halfvec

halfvec_l2_ops, halfvec_ip_ops, halfvec_cosine_ops, halfvec_l1_ops

halfvec_l2_ops, halfvec_ip_ops, halfvec_cosine_ops

sparsevec

sparsevec_l2_ops, sparsevec_ip_ops, sparsevec_cosine_ops, sparsevec_l1_ops

not supported

bit

bit_hamming_ops, bit_jaccard_ops

bit_hamming_ops

The L1 (*_l1_ops) and Jaccard (bit_jaccard_ops) classes are HNSW-only. Beyond matching the column type, the operator class chosen must also match the operator used in ORDER BY (vector_cosine_ops for <⇒, vector_l2_ops for <→, and so on), or the planner silently ignores the index and falls back to a sequential scan.

-- HNSW, matching the DocsAssistant chunk_embeddings index below
CREATE INDEX chunk_embeddings_hnsw
    ON chunk_embeddings USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- IVFFlat alternative on the same column, built after the table is loaded
CREATE INDEX chunk_embeddings_ivfflat
    ON chunk_embeddings USING ivfflat (embedding vector_cosine_ops)
    WITH (lists = 100);

See pgvector: HNSW and pgvector: IVFFlat for both index types and their operator classes.

Iterative scans for filtered search

A WHERE clause combined with an ORDER BY …​ LIMIT vector search can starve HNSW/IVFFlat of enough in-range candidates, returning fewer rows than LIMIT asked for — Metadata Filtering covers this failure mode and the overfetch-then-filter pattern in general. pgvector’s own fix is an iterative scan: SET hnsw.iterative_scan = relaxed_order; (or strict_order to preserve exact distance ordering at a higher cost) tells the index to keep walking past its normal candidate budget until enough filtered rows are found, bounded by SET hnsw.max_scan_tuples = 20000; so a highly selective filter cannot scan the whole table. IVFFlat has the equivalent ivfflat.iterative_scan. See Metadata Filtering for the full pre-/post-/in-graph-filter comparison this setting belongs to.

Build-time settings and bulk loads

An HNSW or IVFFlat build is memory-bound: raising maintenance_work_mem (a session or server setting, e.g. SET maintenance_work_mem = '1GB';) before CREATE INDEX lets the build keep more of the graph or cluster state in memory instead of spilling to disk, which is usually the single biggest factor in build time. pgvector’s HNSW build also supports parallel workers, controlled by the standard max_parallel_maintenance_workers setting, so a build can use several CPU cores at once.

For a bulk load of the DocsAssistant corpus (or any large COPY/batch insert), dropping the vector index first, loading every row, and recreating the index afterward is faster than inserting into a live HNSW or IVFFlat index row by row — each insert into a live HNSW index pays the same graph-wiring cost a build amortizes in bulk, and a live IVFFlat index cannot be reasoned about until the load is done anyway:

DROP INDEX IF EXISTS chunk_embeddings_hnsw;
-- ... COPY or batch INSERT the corpus ...
SET maintenance_work_mem = '1GB';
CREATE INDEX chunk_embeddings_hnsw
    ON chunk_embeddings USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

See pgvector: Index build time for tuning build speed further.

Confirming the index is used

EXPLAIN (ANALYZE) shows whether a query actually took the vector index or fell back to a sequential scan — the latter is the usual symptom of a mismatched operator class, a missing ORDER BY, or a LIMIT large enough that the planner decides a full scan is cheaper:

EXPLAIN (ANALYZE)
SELECT chunk_id FROM chunk_embeddings
ORDER BY embedding <=> '[0.0123,-0.0871,...]'::vector(768)
LIMIT 5;

A plan reading Index Scan using chunk_embeddings_hnsw (rather than Seq Scan on chunk_embeddings) confirms the index is in play. See PostgreSQL: EXPLAIN for reading the rest of the plan.

The DocsAssistant schema

This is the canonical, hand-written DocsAssistant schema every other page’s pgvector examples build on: documents holds one row per source file, chunks holds the chunked, full-text-indexed sections of a document, and chunk_embeddings holds each chunk’s vector, kept in a separate table so a chunk can be re-embedded (or embedded by more than one model) without rewriting chunks itself.

CREATE TABLE documents (
    id            bigserial PRIMARY KEY,
    source_path   text UNIQUE NOT NULL,
    title         text,
    component     text,
    content_hash  text,
    updated_at    timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX documents_component_idx ON documents (component);

CREATE TABLE chunks (
    id            bigserial PRIMARY KEY,
    document_id   bigint NOT NULL REFERENCES documents (id) ON DELETE CASCADE,
    chunk_index   int NOT NULL,
    section_path  text,
    content       text NOT NULL,
    token_count   int,
    content_hash  text,
    tsv           tsvector GENERATED ALWAYS AS (to_tsvector('english', content)) STORED,
    CONSTRAINT chunks_document_chunk_key UNIQUE (document_id, chunk_index)
);

CREATE INDEX chunks_tsv_gin ON chunks USING gin (tsv);

CREATE TABLE chunk_embeddings (
    chunk_id  bigint PRIMARY KEY REFERENCES chunks (id) ON DELETE CASCADE,
    model     text NOT NULL,
    embedding vector(768) NOT NULL
);

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

chunks.tsv is a GENERATED ALWAYS …​ STORED column: Postgres recomputes it from content on every insert or update, so chunks_tsv_gin always reflects current text without the application maintaining it by hand — Hybrid Search & Re-ranking uses this exact column for its ts_rank_cd full-text side of hybrid search. documents_component_idx, chunks_tsv_gin and chunk_embeddings_hnsw together are the three indexes the shared scenario fixes for this schema.

The chunks_document_chunk_key constraint makes (document_id, chunk_index) the natural key of a chunk: PostgreSQL backs it with a unique B-tree index, and it is the conflict target every INSERT …​ ON CONFLICT (document_id, chunk_index) upsert on the other pages relies on — including ingest.py and the incremental update in Production Considerations. Without it, such an upsert fails with "there is no unique or exclusion constraint matching the ON CONFLICT specification". See PostgreSQL: Unique constraints.

Metadata indexes: B-tree, GIN and trigram

Vector search rarely runs alone: retrieval filters on metadata, and users look documents up by name. Each access pattern wants a different index type. documents_component_idx is a plain B-tree for equality filters such as WHERE component = 'vector-rag', chunks_tsv_gin is a GIN index for full-text search, and a trigram index from the bundled pg_trgm extension (part of the standard PostgreSQL contrib modules shipped in the pgvector/pgvector image) covers substring and typo-tolerant matching on titles or paths, which neither of the other two can serve:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX documents_title_trgm ON documents USING gin (title gin_trgm_ops);

-- substring match, served by the trigram index
SELECT id, title FROM documents WHERE title ILIKE '%pgvector%';

-- typo-tolerant match ("pgvectr" still finds "pgvector basics")
SELECT id, title, similarity(title, 'pgvectr basics') AS score
FROM documents
WHERE title % 'pgvectr basics'
ORDER BY score DESC
LIMIT 5;

See PostgreSQL: pg_trgm for the trigram operators, gin_trgm_ops/gist_trgm_ops, and the pg_trgm.similarity_threshold setting behind %.

Hybrid SQL with tsvector

Because chunks.tsv and chunk_embeddings.embedding live in the same database, a hybrid query can run the keyword search and the vector search side by side and fuse their rankings in a single SQL statement. The query below uses Reciprocal Rank Fusion (with the conventional k = 60) over the top 20 of each list; $1 is the query embedding and $2 the raw query text:

WITH semantic AS (
    SELECT ce.chunk_id, rank() OVER (ORDER BY ce.embedding <=> $1) AS rank
    FROM chunk_embeddings ce
    ORDER BY ce.embedding <=> $1
    LIMIT 20
),
keyword AS (
    SELECT c.id AS chunk_id, rank() OVER (ORDER BY ts_rank_cd(c.tsv, q) DESC) AS rank
    FROM chunks c, websearch_to_tsquery('english', $2) AS q
    WHERE c.tsv @@ q
    ORDER BY ts_rank_cd(c.tsv, q) DESC
    LIMIT 20
)
SELECT c.id, c.section_path, c.content,
       COALESCE(1.0 / (60 + s.rank), 0.0) + COALESCE(1.0 / (60 + k.rank), 0.0) AS rrf_score
FROM semantic s
FULL OUTER JOIN keyword k ON k.chunk_id = s.chunk_id
JOIN chunks c ON c.id = COALESCE(s.chunk_id, k.chunk_id)
ORDER BY rrf_score DESC
LIMIT 5;

The keyword side reads the stored tsv column, so it is served by chunks_tsv_gin. The same query also works on a table without a stored column by computing the vector inline — WHERE to_tsvector('english', c.content) @@ q — but then every candidate row is re-parsed at query time and the GIN index is only used if an expression index on exactly to_tsvector('english', content) exists, which is why this schema stores it. Hybrid Search & Re-ranking covers the fusion methods (RRF vs. weighted score fusion) in depth. See pgvector: Hybrid search and PostgreSQL: Controlling text search for the full-text functions used here.

Multilevel embeddings: document and chunk

A single flat table of chunk embeddings scales less well as the corpus grows, because every query walks the same HNSW graph regardless of how unrelated most chunks are to the question. A document-level embedding — one vector per document, usually the mean or a dedicated summary embedding — lets a query first narrow down to the most relevant few documents, then search only those documents' chunks:

CREATE TABLE document_embeddings (
    document_id bigint PRIMARY KEY REFERENCES documents (id) ON DELETE CASCADE,
    model       text NOT NULL,
    embedding   vector(768) NOT NULL
);

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

-- stage 1: nearest documents
WITH top_documents AS (
    SELECT document_id
    FROM document_embeddings
    ORDER BY embedding <=> $1
    LIMIT 20
)
-- stage 2: nearest chunks, restricted to those documents
SELECT c.id, c.content, ce.embedding <=> $1 AS distance
FROM chunks c
JOIN chunk_embeddings ce ON ce.chunk_id = c.id
JOIN top_documents td ON td.document_id = c.document_id
ORDER BY distance
LIMIT 5;

This is the same pre-filtering idea Metadata Filtering covers for structured metadata, applied instead along a document/chunk hierarchy.

A processing queue table

Ingesting the corpus — reading a source file, chunking it, embedding each chunk — is naturally asynchronous work, so a small queue table tracks what still needs (re-)processing independently of the documents/chunks tables themselves:

CREATE TABLE ingest_queue (
    id           bigserial PRIMARY KEY,
    source_path  text NOT NULL,
    status       text NOT NULL DEFAULT 'pending',
    enqueued_at  timestamptz NOT NULL DEFAULT now(),
    processed_at timestamptz
);

CREATE INDEX ingest_queue_status_idx ON ingest_queue (status);

A worker opens a transaction, claims a row with SELECT …​ WHERE status = 'pending' ORDER BY enqueued_at LIMIT 1 FOR UPDATE SKIP LOCKED, processes the file (chunk, embed, upsert into documents/chunks/chunk_embeddings), updates status to done or failed with processed_at = now(), and only then commits. FOR UPDATE SKIP LOCKED lets several worker processes pull from the same queue without double-processing a row — but only while the row lock is held, so the claim, the processing and the status update must all run in that one transaction (in autocommit mode the lock is released as soon as the SELECT returns, and two workers can claim the same row). ingest.py implements exactly this loop. See PostgreSQL: SELECT …​ FOR UPDATE.

erDiagram DOCUMENTS ||--o{ CHUNKS : "has" DOCUMENTS ||--o| DOCUMENT_EMBEDDINGS : "summarized by" CHUNKS ||--|| CHUNK_EMBEDDINGS : "embedded as" DOCUMENTS { bigserial id PK text source_path UK text title text component text content_hash timestamptz updated_at } CHUNKS { bigserial id PK bigint document_id FK int chunk_index "unique per document_id" text section_path text content int token_count text content_hash tsvector tsv } CHUNK_EMBEDDINGS { bigint chunk_id PK, FK text model vector embedding } DOCUMENT_EMBEDDINGS { bigint document_id PK, FK text model vector embedding } INGEST_QUEUE { bigserial id PK text source_path text status timestamptz enqueued_at timestamptz processed_at }

Framework-managed vector stores can live in the same docsassistant database without touching this hand-written schema: Spring AI’s PgVectorStore creates and owns its own docs_vector_store table, and LangChain’s PGVector owns its own tables under the docsassistant collection name — both are covered in In LangChain and Spring AI below, and neither reuses chunk_embeddings.

Connecting with psycopg

pgvector.psycopg.register_vector registers pgvector’s types with a psycopg 3 connection. From then on a pgvector.Vector (or a NumPy ndarray, if NumPy is installed) is sent as a vector parameter, and a fetched vector column comes back as a pgvector.Vector — call .to_list() or .to_numpy() on it (pgvector-python releases before 0.5 returned a NumPy array directly). A plain Python list[float] is not adapted: psycopg sends it as double precision[]. That happens to work in an INSERT, because PostgreSQL applies pgvector’s assignment cast to the target column, but in an expression such as embedding <⇒ %s it fails with "operator does not exist: vector <⇒ double precision[]". Wrap every embedding in Vector(…​) (or cast the placeholder explicitly, %s::vector) to avoid the mismatch:

import psycopg
from pgvector import Vector
from pgvector.psycopg import register_vector

conn = psycopg.connect(
    "host=localhost dbname=docsassistant user=docs password=docs",
    autocommit=True,
)
register_vector(conn)

# insert one chunk's embedding
chunk_id = 1
embedding = Vector([0.0123, -0.0871, 0.0456])  # a real 768-dim vector in practice
conn.execute(
    "INSERT INTO chunk_embeddings (chunk_id, model, embedding) VALUES (%s, %s, %s)"
    " ON CONFLICT (chunk_id) DO UPDATE SET embedding = EXCLUDED.embedding",
    (chunk_id, "nomic-embed-text", embedding),
)

# k-nearest-neighbor query
query_embedding = Vector([0.0110, -0.0850, 0.0470])
rows = conn.execute(
    "SELECT chunk_id, embedding <=> %s AS distance"
    " FROM chunk_embeddings ORDER BY distance LIMIT 5",
    (query_embedding,),
).fetchall()

# reading a vector column back returns a pgvector.Vector
stored = conn.execute(
    "SELECT embedding FROM chunk_embeddings WHERE chunk_id = %s", (chunk_id,)
).fetchone()[0]
values = stored.to_list()  # or stored.to_numpy()

See pgvector-python: psycopg 3 for the full client setup, including connection pools (register_vector in the pool’s configure callback) and async connections (register_vector_async).

In LangChain and Spring AI

LangChain

langchain-postgres’s `PGVector store wraps this same pgvector extension behind LangChain’s standard vector-store interface, managing its own tables under a named collection (docsassistant, per the shared scenario) rather than the hand-written chunk_embeddings table above:

from langchain_postgres import PGVector
from langchain_ollama import OllamaEmbeddings

vector_store = PGVector(
    embeddings=OllamaEmbeddings(model="nomic-embed-text"),
    collection_name="docsassistant",
    connection="postgresql+psycopg://docs:docs@localhost:5432/docsassistant",
    use_jsonb=True,
)

results = vector_store.similarity_search("What does ef_search control?", k=5)

See LangChain vector stores and pgvector-python: LangChain for the integration’s own connection and indexing options.

Spring AI

Spring AI’s PgVectorStore gives the same VectorStore abstraction over a Spring Boot data source, creating and managing its own docs_vector_store table (configured via spring.ai.vectorstore.pgvector.* properties) rather than the chunk_embeddings table defined above — see Spring AI vector store API for the full setup.

Managed pgvector on the clouds

All three major clouds offer first-party managed Postgres with pgvector preinstalled, so most teams never run the compose.yaml above in production: AWS RDS for PostgreSQL and Aurora PostgreSQL both support the vector extension out of the box; Google Cloud offers it on Cloud SQL for PostgreSQL and on AlloyDB, where AlloyDB additionally provides its own ScaNN index as a faster alternative to HNSW/IVFFlat; and Azure Database for PostgreSQL Flexible Server supports pgvector plus the GA pg_diskann extension for disk-resident Vamana-style search at larger scale. For deploying PostgreSQL flexible server on Azure with pgvector enabled via the extensions allowlist, see PostgreSQL and MySQL on Azure. For RDS for PostgreSQL and Aurora PostgreSQL on AWS, see Amazon RDS and Amazon Aurora. For Cloud SQL for PostgreSQL and AlloyDB on Google Cloud, see Cloud SQL and AlloyDB for PostgreSQL. Comparing Vector Databases compares these managed offerings against purpose-built engines in more depth, including cloud availability and pricing. As a rough scale guide: pgvector on a well-tuned managed instance comfortably serves a corpus from thousands up to tens of millions of chunks; once a single Postgres primary’s HNSW build starts competing too heavily with OLTP traffic for CPU and memory, or the working set no longer fits comfortably in RAM, that is the signal to consider a dedicated vector engine or a disk-resident index such as pg_diskann instead of scaling the same table further.

Related pages

References