Pgvector - 101

Solutions

• Pgvector - 101
  • What is pgvector
  • Installation
  • Enabling the extension
  • Creating a Table with a Vector Column
  • Insert the data
  • Running a query
  • Choosing a Distance Metric
  • Adding an Index for Performance
  • Filtering

What is pgvector

pgvector is an open-source PostgreSQL extension that adds native vector search to your existing database. Rather than moving your embeddings to a dedicated vector store, pgvector keeps them alongside your relational data, preserving PostgreSQL’s transactional guarantees, JOIN semantics, point-in-time recovery, and the full SQL query language.

This extension adds:

1. Vector data type for storing embeddings.
2. SQL distance operators for ordering query results by similarity.
3. Two index types — HNSW and IVFFlat — for accelerating nearest-neighbor lookups at scale.

Installation

1. For linux: sudo apt install postgresql-18-pgvector
2. For Windows: Installation for Windows

Enabling the extension

  CREATE EXTENSION IF NOT EXISTS vector;

Creating a Table with a Vector Column

  CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    category    TEXT,
    description TEXT,
    price       NUMERIC(8,2),
    embedding   vector(1536)
  );

The vector(1536) column holds one embedding per row. That number must match the output dimension of your model; adjust it accordingly if you use a different one.

Insert the data

  INSERT INTO products (name, category, description, embedding) VALUES
  ('Merrell Moab 3 GTX',      'Footwear',  'Waterproof hiking boot for all-day trail comfort',        '[0.82, 0.15, 0.44]'),
  ('Salomon Speedcross 6',    'Footwear',  'Aggressive trail runner for muddy and technical terrain', '[0.79, 0.21, 0.38]'),
  ('Black Diamond Spot 400',  'Lighting',  'Rechargeable headlamp with 400 lumens and waterproofing', '[0.11, 0.88, 0.22]'),
  ('Petzl ACTIK CORE',        'Lighting',  'Lightweight headlamp for hiking and camping',             '[0.09, 0.91, 0.19]'),
  ('Osprey Atmos AG 65',      'Backpacks', 'Anti-gravity backpack for multi-day backcountry trips',   '[0.55, 0.30, 0.77]'),
  ('Gregory Baltoro 75',      'Backpacks', 'High-volume pack for extended wilderness expeditions',    '[0.58, 0.28, 0.81]');

Running a query

  SELECT
    name,
    category,
    description,
    embedding <-> '[0.80, 0.19, 0.40]' AS distance
  FROM products
  ORDER BY distance
  LIMIT 3;

Choosing a Distance Metric

Common metrics (ordered by how it used):

• L2 distance treats vectors as points in space and measures the geometric distance between them. It works best when vector magnitude carries meaningful information.
• Cosine distance is 1 - cosine similarity and measures the angle between vectors rather than their length. This makes it the preferred choice for text embeddings generated by language models.
• Inner product is useful in recommendation systems, where embeddings are trained so that dot products directly represent similarity. pgvector returns the negative inner product, so you should negate the result to get the actual similarity score.
• L1 distance weights each dimension equally without squaring the differences, giving it mild robustness to outliers compared to L2.
• Hamming and Jaccard apply only to binary vectors, used for memory-efficient quantized representations.

Most LLM-based embedding APIs produce normalized or near-normalized vectors, where semantic meaning is encoded in direction, not magnitude. Because of this, cosine distance generally delivers more accurate semantic rankings for search and retrieval workloads.

Adding an Index for Performance

Without an index, every similarity query performs a full sequential scan: PostgreSQL computes the distance between the query vector and every row in the table. That is acceptable at ten thousand rows. At a million rows, query latency becomes a serious problem.

Index types on pgvector:

• Hierarchical Navigable Small Worlds (HNSW) constructs a multi-layer graph where each node connects to a bounded number of neighbors across multiple levels of resolution. HNSW gives the best speed-to-recall ratio of the two options, but constructing the graph requires more memory and takes longer than IVFFlat.
• Inverted File Flat (IVFFlat) partitions the vector space into a fixed number of clusters during index construction, then at query time searches only the clusters closest to the query vector. It builds faster and uses less memory, but those cluster boundaries are fixed at build time.

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

where:

• m = the maximum connections per node in the graph
• ef_construction = controls the size of the candidate list during graph construction.

The operator class in your index must match the distance operator in your queries. Always verify with EXPLAIN that the index is being used when needed.

Filtering

Similarity search becomes more useful when combined with ordinary SQL filters. pgvector integrates directly with PostgreSQL’s query planner, so you can combine vector ordering with WHERE clauses, JOINs, and aggregations without learning a separate query language.

  SELECT
    name,
    category,
    embedding <-> '[0.80, 0.19, 0.40]' AS distance
  FROM gear
  WHERE category = 'Footwear'
  ORDER BY distance
  LIMIT 2;