pgvector Basics

pgvector adds a vector type and distance operators to PostgreSQL for embedding storage and similarity search. Install the extension, pick a distance metric, and understand dimension limits before building RAG indexes.

Recipe

CREATE EXTENSION IF NOT EXISTS vector WITH SCHEMA extensions VERSION '0.8.0';
 
CREATE TABLE embeddings (
  id        bigserial PRIMARY KEY,
  label     text NOT NULL,
  embedding extensions.vector(3)  -- dimension fixed at DDL time
);
 
INSERT INTO embeddings (label, embedding) VALUES
  ('postgres', '[1,2,3]'),
  ('vectors',  '[2,3,4]');
 
-- Distance operators (use matching index opclass)
SELECT label,
       embedding <-> '[1,2,3]'::extensions.vector AS l2_distance,
       embedding <=> '[1,2,3]'::extensions.vector AS cosine_distance,
       embedding <#> '[1,2,3]'::extensions.vector AS inner_product_neg
FROM embeddings
ORDER BY embedding <=> '[1,2,3]'::extensions.vector
LIMIT 5;

When to reach for this: Semantic search, RAG retrieval, recommendations, or deduplication where embeddings already exist from an upstream model.

Working Example

CREATE TABLE documents (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  title       text,
  embedding   extensions.vector(1536)  -- OpenAI text-embedding-3-small
);
 
INSERT INTO documents (title, embedding)
VALUES (
  'Vacuum and bloat',
  (SELECT array_agg(random())::float[]::extensions.vector FROM generate_series(1, 1536))
);
 
-- Exact nearest neighbor (no index; fine for small tables)
SELECT id, title, embedding <=> (
  SELECT embedding FROM documents LIMIT 1
) AS distance
FROM documents
ORDER BY distance
LIMIT 10;

What this demonstrates:

• Dimension is part of the type (vector(1536))
• Vectors insert as string literals '[...]' or casts from arrays
• <=> is cosine distance for normalized embeddings
• Exact scan is the baseline before IVFFlat or HNSW indexes

Deep Dive

Distance Operators

Rule: Query operator must match index opclass. Cosine-indexed column with L2 operator returns wrong ordering or no index.

Type Limits

• Max dimensions: 2000 for vector type in pgvector 0.8+ (check release notes for your pin).
• Storage: 4 * dimensions bytes per vector (float32) plus heap overhead.
• Half-precision halfvec (when enabled in your build) reduces storage for large corpora.

SELECT typname, typlen FROM pg_type WHERE typname = 'vector';
 
-- Validate dimension at insert
ALTER TABLE documents
  ADD CONSTRAINT embedding_dim CHECK (vector_dims(embedding) = 1536);

Functions

SELECT vector_dims(embedding), vector_norm(embedding) FROM documents LIMIT 1;
 
-- Element-wise arithmetic
SELECT embedding + '[0.1,0.2,0.3]'::extensions.vector FROM embeddings WHERE id = 1;

When Postgres vs Dedicated Vector DB

Postgres + pgvector wins when:

• Data already lives in Postgres with transactional updates
• Corpus fits one instance with HNSW tuning
• Team cannot operate another distributed system

Consider dedicated vector stores when billion-scale, sub-10ms at huge QPS, or advanced filtering DSL is mandatory and proven costly in SQL.

Gotchas

• Wrong opclass - HNSW built with vector_l2_ops but queries use <=>. Fix: Rebuild index with vector_cosine_ops to match queries.
• Dimension mismatch - Model change 1536 to 3072 without migration. Fix: New column or table; re-embed everything; version embedding model in metadata.
• Unnormalized cosine - Cosine distance on zero-norm vectors is undefined behavior. Fix: CHECK (vector_norm(embedding) > 0) or normalize at insert in app.
• String building embeddings in SQL - Injection and parse errors. Fix: Bind parameters from application driver.
• No index on large table - Seq scan sorts millions of rows per query. Fix: IVFFlat or HNSW after baseline exact search tests.
• Extension not in search_path - type vector does not exist. Fix: Schema-qualify extensions.vector or add schema to search_path for app role only.

Alternatives

FAQs

Related

• IVFFlat vs HNSW Indexes - ANN index trade-offs
• RAG Storage Schema - chunk tables
• Hybrid Search - keyword + vector
• Extensions Basics - install and pin version
• pgvector Best Practices - recall benchmarking

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PostGIS 3.5+, pgbouncer 1.x, and Patroni 3.x.