pgvector Common Commands Cheat Sheet

This quick reference guide summarizes the most common SQL syntax and operations in pgvector.

1. Extension Setup and Schema Definition

-- 1. Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- 2. Define a vector-typed column (1536 OpenAI dimension)
CREATE TABLE items (
  id BIGSERIAL PRIMARY KEY,
  embedding VECTOR(1536)
);

2. Distance Calculation Operators Reference

3. Creating HNSW Indexes

-- Build HNSW index for Cosine similarity search (Default production choice)
CREATE INDEX ON items 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Build HNSW index for L2 Euclidean search
CREATE INDEX ON items 
USING hnsw (embedding vector_l2_ops);

4. Stored Procedure RPC Template

CREATE OR REPLACE FUNCTION match_items (
  query_embedding VECTOR(1536),
  match_threshold FLOAT,
  match_count INT
)
RETURNS TABLE (
  id BIGINT,
  similarity FLOAT
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT
    items.id,
    (1 - (items.embedding <=> query_embedding)) AS similarity
  FROM items
  WHERE (1 - (items.embedding <=> query_embedding)) > match_threshold
  ORDER BY items.embedding <=> query_embedding ASC
  LIMIT match_count;
END;
$$;