PostgreSQL as a Vector Database: A Complete Guide

pgvector adds vector types, distance operators, and exact and approximate nearest-neighbor search to PostgreSQL. Its strongest use case is retrieval that also needs PostgreSQL transactions, joins, row-level security, metadata filters, backups, and operational tooling. It is not automatically the right system for every vector workload; measure recall, latency, concurrency, write cost, and failure behavior before choosing an architecture.

Install and verify the extension

Use the package or extension mechanism supported by the PostgreSQL provider and server major version. Avoid pinning a package command copied from another distribution. Enable pgvector once per database and record the installed extension version:

CREATE EXTENSION IF NOT EXISTS vector;

SELECT extversion
FROM pg_extension
WHERE extname = 'vector';

Review the pgvector changelog and provider support matrix before upgrade. An extension update and an index rebuild are different operations; rebuild only when release notes, corruption remediation, index changes, or measured benefits justify it. pgvector 0.8 improved HNSW scans, inserts, on-disk builds, and planner cost estimation, but its official changelog does not prescribe a blanket HNSW reindex.

Store embeddings with their model identity

CREATE TABLE document_chunks (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    document_id bigint NOT NULL,
    model_key text NOT NULL,
    chunk_text text NOT NULL,
    embedding vector(768) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX document_chunks_tenant_document_idx
    ON document_chunks (tenant_id, document_id);

The dimension above is illustrative. Match it to the model contract and prevent embeddings from different models or preprocessing pipelines from being compared accidentally. Store the model/version key, normalization policy, chunking version, and source identity. Generate embeddings outside SQL with bounded retries, then use parameterized inserts or COPY for bulk loads.

Start with exact search

Exact search gives a correctness baseline and can be sufficient for a filtered or modest candidate set.

SELECT id,
       document_id,
       embedding <=> $1::vector AS cosine_distance
FROM document_chunks
WHERE tenant_id = $2
  AND model_key = $3
ORDER BY embedding <=> $1::vector
LIMIT 20;

pgvector uses <-> for L2 distance, <#> for negative inner product, and <=> for cosine distance. Choose from the embedding provider's training and normalization guidance; cosine is not universally best. Keep the distance expression directly in ORDER BY with ascending order so PostgreSQL can use the matching approximate index.

Choose HNSW or IVFFlat from measured tradeoffs

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

The shown values are pgvector defaults and a starting point, not a service guarantee. Higher build or search effort can improve recall while increasing build time, insert work, memory, or query time. Build concurrently on a live table when avoiding write blocking matters, and monitor the longer build and additional I/O.

IVFFlat should be trained on representative data. An index built with too little data for its list count can return poor results. Load a stable sample first, select lists and probes through recall tests, and rebuild when the data distribution changes enough to invalidate the partitioning.

Filtering and pgvector 0.8 iterative scans

With an approximate index, PostgreSQL scans vector candidates and then applies ordinary filters. A highly selective tenant, category, or permission predicate can therefore leave fewer rows than the requested limit. pgvector 0.8 introduced iterative scans that continue through more of the approximate index until enough qualifying rows are found or a configured limit is reached.

BEGIN;
SET LOCAL hnsw.iterative_scan = strict_order;
SET LOCAL hnsw.ef_search = 100;

SELECT id, document_id, embedding <=> $1::vector AS distance
FROM document_chunks
WHERE tenant_id = $2
  AND model_key = $3
ORDER BY embedding <=> $1::vector
LIMIT 20;
COMMIT;

strict_order keeps exact distance ordering. relaxed_order can improve recall or performance but may return slightly out-of-order rows and needs the documented materialized-CTE pattern when strict presentation order is required. Tune hnsw.max_scan_tuples and hnsw.scan_mem_multiplier only after measuring the filtered workload. An ordinary index on the filter column, a partial vector index for a stable low-cardinality subset, or PostgreSQL partitioning can also help.

Half precision and smaller indexes

halfvec stores each vector component in half precision instead of the four-byte component used by vector. It can reduce table or index working-set size, but it changes numerical precision. Compare exact full-precision neighbors with half-precision or quantized candidates on a labeled evaluation set before migrating.

CREATE INDEX CONCURRENTLY document_chunks_embedding_half_hnsw_idx
ON document_chunks
USING hnsw ((embedding::halfvec(768)) halfvec_cosine_ops);

Expression indexing can keep the source vector for full-precision re-ranking. Do not publish a fixed recall-loss percentage: model, dimension, data distribution, metric, and candidate count all affect it.

Hybrid vector and full-text retrieval

PostgreSQL can retrieve vector and lexical candidates under the same tenant and permission predicates, then combine their ranks. Reciprocal Rank Fusion is one simple approach that avoids comparing scores from different scales:

WITH vector_hits AS (
  SELECT id, row_number() OVER (ORDER BY embedding <=> $1::vector) AS rank
  FROM document_chunks
  WHERE tenant_id = $2 AND model_key = $3
  ORDER BY embedding <=> $1::vector
  LIMIT 50
), lexical_hits AS (
  SELECT id,
         row_number() OVER (
           ORDER BY ts_rank_cd(to_tsvector('english', chunk_text),
                               websearch_to_tsquery('english', $4)) DESC
         ) AS rank
  FROM document_chunks
  WHERE tenant_id = $2
    AND to_tsvector('english', chunk_text) @@ websearch_to_tsquery('english', $4)
  LIMIT 50
), fused AS (
  SELECT coalesce(v.id, l.id) AS id,
         coalesce(1.0 / (60 + v.rank), 0) +
         coalesce(1.0 / (60 + l.rank), 0) AS score
  FROM vector_hits v
  FULL JOIN lexical_hits l USING (id)
)
SELECT d.id, d.document_id, d.chunk_text, fused.score
FROM fused
JOIN document_chunks d USING (id)
ORDER BY fused.score DESC
LIMIT 20;

Index a stored tsvector for a real lexical workload rather than recomputing it as shown. Calibrate candidate counts and fusion constants with relevance judgments, not intuition.

Measure recall and operations

• Create exact ground truth by temporarily disabling index scans in a transaction, then compare approximate top-k overlap or task-specific relevance.
• Measure p50/p95/p99 latency and throughput at representative concurrency, filters, dimensions, and candidate limits.
• Track table and index size, build progress, inserts and updates, dead tuples, autovacuum, WAL, replica lag, backup size, and restore time.
• Use EXPLAIN (ANALYZE, BUFFERS) safely to confirm the expected index and filter behavior.
• Test row-level security and permission predicates under approximate search; correctness of access control is separate from retrieval recall.

Choose a separate vector system when independent scaling, specialized distributed indexing, operational isolation, or features unavailable in the tested PostgreSQL design justify the added service and synchronization path. There is no universal vector-count or latency threshold that makes that decision automatically.

Official primary sources

• pgvector documentation and source
• pgvector changelog
• PostgreSQL full-text search controls
• PostgreSQL row security policies
• PostgreSQL CREATE INDEX

Working with JusDB on pgvector

JusDB helps teams design pgvector schemas, establish recall ground truth, tune filtered ANN and hybrid search, and operate vector workloads alongside PostgreSQL transactions.

Explore JusDB PostgreSQL consulting →  |  Talk to a PostgreSQL engineer