pgvector cheat sheet: vector types, distance operators, index operator classes and the settings that matter

#Enable and check the extension

CREATE EXTENSION IF NOT EXISTS vector;          -- the extension is called "vector", not "pgvector"
SELECT extversion FROM pg_extension WHERE extname = 'vector';
SELECT default_version FROM pg_available_extensions WHERE name = 'vector';   -- what an upgrade would give you
ALTER EXTENSION vector UPDATE;                  -- after the server package is upgraded

Note

On managed services the extension must usually be allow-listed first (Azure Flexible Server: add VECTOR to the azure.extensions server parameter). RDS, Aurora and Cloud SQL ship it as a supported extension; the available version depends on the engine minor version.

#Column types

Always declare the dimension (vector(1536), not vector). A column without a fixed dimension can't be indexed.

CREATE TABLE doc_chunks (
  id         bigserial PRIMARY KEY,
  doc_id     bigint NOT NULL,
  tenant_id  int    NOT NULL,
  content    text   NOT NULL,
  embedding  vector(1536) NOT NULL
);

#Distance operators and matching operator classes

The operator in ORDER BY must match the operator class of the index, or the index is ignored.

For halfvec and sparsevec the opclasses are halfvec_cosine_ops, sparsevec_l2_ops and so on.

Tip

If your embedding model returns normalised vectors (OpenAI, most sentence-transformers models), inner product and cosine give the same ranking and inner product is slightly cheaper.

#Indexes

-- HNSW: best recall/latency, no training step, can be built on an empty table
CREATE INDEX CONCURRENTLY doc_chunks_emb_hnsw
  ON doc_chunks USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);          -- these are the defaults

-- IVFFlat: faster build, less memory, build AFTER loading data (it trains on existing rows)
CREATE INDEX CONCURRENTLY doc_chunks_emb_ivf
  ON doc_chunks USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 1000);                          -- rule of thumb: rows/1000 up to 1M rows, sqrt(rows) above

-- Index a halfvec expression without changing the column
CREATE INDEX ON doc_chunks USING hnsw ((embedding::halfvec(1536)) halfvec_cosine_ops);

Build settings for big tables:

SET maintenance_work_mem = '8GB';               -- graph must fit, or the build slows sharply
SET max_parallel_maintenance_workers = 7;       -- plus the leader process

#Query patterns

-- Top 10 nearest; the parameter must be a vector of the same dimension
SELECT id, content, 1 - (embedding <=> $1) AS cosine_similarity
FROM doc_chunks
ORDER BY embedding <=> $1
LIMIT 10;

-- With a filter
SELECT id FROM doc_chunks
WHERE tenant_id = 42
ORDER BY embedding <=> $1
LIMIT 10;

-- Halfvec expression index: the ORDER BY must use the same expression
SELECT id FROM doc_chunks
ORDER BY embedding::halfvec(1536) <=> $1::halfvec(1536)
LIMIT 10;

#Query-time settings

Set them per transaction so one query doesn't change the whole session:

BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT ... ORDER BY embedding <=> $1 LIMIT 20;
COMMIT;

#Inspect what you have

-- Vector indexes, their size and definition
SELECT c.relname AS index, am.amname, pg_size_pretty(pg_relation_size(c.oid)) AS size,
       pg_get_indexdef(c.oid) AS definition
FROM pg_class c JOIN pg_am am ON am.oid = c.relam
WHERE am.amname IN ('hnsw', 'ivfflat');

-- Is the index used? idx_scan should grow with traffic
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes WHERE indexrelname LIKE '%emb%';

-- Build progress
SELECT phase, round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS pct
FROM pg_stat_progress_create_index;

#Useful functions

#Prevent mistakes next time

• Pin the dimension in the column type and check vector_dims() in ingestion tests.
• Keep one distance metric per column and write it down next to the index definition.
• Use SET LOCAL for query tuning, not database-wide defaults you'll forget about.
• Check ALTER EXTENSION vector UPDATE after every PostgreSQL minor upgrade on managed services.