pgvector

Overview

pgvector is an open-source PostgreSQL extension that adds vector similarity search capabilities directly to your PostgreSQL database. It enables storing, indexing, and querying vector embeddings, making it perfect for AI applications like semantic search, recommendation systems, and retrieval-augmented generation (RAG). With pgvector, you can perform efficient nearest neighbor searches using various distance metrics right in your SQL queries.

System Requirements

Since pgvector is a PostgreSQL extension, you need:

• PostgreSQL: Version 12 or higher
• CPU: 2+ cores recommended for decent performance
• RAM: Depends on your vector dimension and dataset size, 4GB+ recommended
• Storage: Depends on your dataset size
• OS: Any platform that supports PostgreSQL (Linux, macOS, Windows)

Installation Guide

Installing pgvector on a PostgreSQL Server

Linux (Ubuntu/Debian)

1. Ensure PostgreSQL development files are installed:

   sudo apt update
   sudo apt install postgresql-server-dev-all build-essential git
   

2. Clone and install pgvector:

   git clone --branch v0.7.0 https://github.com/pgvector/pgvector.git
   cd pgvector
   make
   sudo make install
   

macOS (with Homebrew)

brew install pgvector

Windows

1. Install PostgreSQL with the development toolkit

2. Clone the pgvector repository

3. Open the Visual Studio command prompt

4. Navigate to the pgvector directory and run:

   set PG_CONFIG=C:\Program Files\PostgreSQL\{version}\bin\pg_config.exe
   nmake /f Makefile.win
   nmake /f Makefile.win install
   

Using pgvector with a Managed Database Service

Many managed PostgreSQL providers now include pgvector as a built-in extension:

• Supabase: pgvector is pre-installed
• Amazon RDS: Available as an extension
• Azure Database for PostgreSQL: Available as an extension
• Neon: Available as an extension
• Timescale: Available as an extension

Enabling the Extension

Once installed, connect to your database and enable the extension:

CREATE EXTENSION vector;

Using pgvector

Creating Tables with Vector Columns

Create a table with a vector column by specifying the dimensions:

-- Create a table with a 3-dimensional vector
CREATE TABLE items (
  id SERIAL PRIMARY KEY,
  name TEXT,
  embedding VECTOR(3)
);

-- Or for typical AI embeddings (e.g., OpenAI's text-embedding-3-small)
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  content TEXT,
  embedding VECTOR(1536)
);

Inserting Vectors

-- Insert a 3-dimensional vector
INSERT INTO items (name, embedding) VALUES ('item1', '[1,2,3]');

-- Insert vectors generated from an AI embedding model
INSERT INTO documents (content, embedding)
VALUES ('Sample document text', '[0.1, 0.2, ..., 0.5]');

Performing Similarity Searches

pgvector supports three distance operators:

• <-> L2 distance (Euclidean)
• <#> Negative inner product
• <=> Cosine distance

-- Find the 5 most similar items using L2 distance
SELECT name, embedding <-> '[1,2,3]' AS distance
FROM items
ORDER BY distance
LIMIT 5;

-- Find similar documents using cosine similarity
SELECT content, embedding <=> (
  SELECT embedding FROM documents WHERE id = 1
) AS distance
FROM documents
WHERE id != 1
ORDER BY distance
LIMIT 5;

Creating Indexes for Performance

pgvector offers different index types for efficient similarity search:

IVFFlat Index (good for larger datasets)

-- Create an IVFFlat index (first populate your table with data)
CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);

HNSW Index (best for recall and query speed)

-- Create an HNSW index (Hierarchical Navigable Small World)
CREATE INDEX ON items USING hnsw (embedding vector_l2_ops) WITH (m = 16, ef_construction = 64);

Practical Exercise: Building a Simple Semantic Search System

Let’s create a simple semantic search system that can find semantically similar text:

Step 1: Set Up the Database

-- Create the extension
CREATE EXTENSION vector;

-- Create a table for storing documents and their embeddings
CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  embedding VECTOR(1536)  -- For OpenAI's text-embedding-3-small
);

-- Add an HNSW index for faster searches
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

Step 2: Generate and Store Embeddings

Use a Python script to generate embeddings and store them:

import psycopg2
import openai

# Connect to PostgreSQL
conn = psycopg2.connect("dbname=your_db user=your_user")
cur = conn.cursor()

# Example documents
documents = [
    {"title": "Vector Search", "content": "Vector search uses embeddings to find similar items."},
    {"title": "PostgreSQL", "content": "PostgreSQL is an advanced open-source database."},
    {"title": "Machine Learning", "content": "Machine learning helps computers learn patterns."}
]

# Generate embeddings and store documents
for doc in documents:
    # Get embedding from OpenAI
    response = openai.embeddings.create(
        model="text-embedding-3-small",
        input=doc["content"]
    )
    embedding = response.data[0].embedding

    # Store document with embedding
    cur.execute(
        "INSERT INTO documents (title, content, embedding) VALUES (%s, %s, %s)",
        (doc["title"], doc["content"], embedding)
    )

conn.commit()
cur.close()
conn.close()

Step 3: Perform Semantic Search

def semantic_search(query, limit=5):
    # Get embedding for the query
    response = openai.embeddings.create(
        model="text-embedding-3-small",
        input=query
    )
    query_embedding = response.data[0].embedding

    # Connect to PostgreSQL
    conn = psycopg2.connect("dbname=your_db user=your_user")
    cur = conn.cursor()

    # Search for similar documents
    cur.execute(
        """
        SELECT title, content, embedding <=> %s AS distance
        FROM documents
        ORDER BY distance
        LIMIT %s
        """,
        (query_embedding, limit)
    )

    results = cur.fetchall()
    cur.close()
    conn.close()

    return results

# Example search
results = semantic_search("How do databases store information?")
for title, content, distance in results:
    print(f"{title} (distance: {distance:.4f})")
    print(f"Content: {content}\n")

Common Use Cases

pgvector enables numerous AI applications within your existing PostgreSQL database:

• Semantic Search: Find documents by meaning rather than keywords
• Recommendation Systems: Recommend similar products, articles, or content
• Retrieval Augmented Generation (RAG): Enhance LLM outputs with relevant context
• Image Similarity: Find visually similar images using vector representations
• Anomaly Detection: Identify unusual patterns in data
• Natural Language Processing: Support various NLP tasks with vector operations

Resources

Official Documentation

• pgvector GitHub Repository
• pgvector README

Tutorials and Guides

• Supabase pgvector Guide
• PostgreSQL Vector Search on Azure
• DataCamp pgvector Tutorial

Community Support

• GitHub Issues
• Stack Overflow pgvector Questions

Limitations and Considerations

• Maximum vector dimensions: 16,000 (can be extended using half-precision indexing or binary quantization)
• Performance depends on PostgreSQL configuration, index choice, and vector dimensions
• Creating indexes on large datasets can take time
• Consider scaling options (vertical scaling, partitioning) for very large vector datasets