Getting Started with pgvector

This post was written by an engineer at QueryPlane. QueryPlane is an app builder for your database: bring your own postgres db and you can create interactive applications to share with other developers, coworkers or even your customers. If you’re interested in trying it out, get started here.

---

Vector databases are having a moment. With the explosion of AI applications, everyone needs a place to store embeddings. But before you spin up yet another managed service, consider this: your PostgreSQL database can do vector search too.

pgvector is a PostgreSQL extension that adds vector similarity search, created by Andrew Kane. It’s not a toy—companies like Supabase and Render use it in production.

This guide will get you from zero to semantic search in about 30 minutes.

In this post, we’ll cover:

• Why pgvector - Benefits of vector search in Postgres
• Installation - Docker and cloud provider setup
• Creating vector tables - Storing embeddings with the right dimensions
• Generating embeddings - Python example with OpenAI
• Querying similar documents - Distance operators and search patterns
• Adding indexes - IVFFlat vs HNSW for performance

Why pgvector?

• No new infrastructure - Your vectors live in the same database as your data
• ACID transactions - Vector operations participate in regular transactions
• JOIN with your data - Combine vector search with traditional SQL queries
• Familiar tooling - Use your existing PostgreSQL clients, ORMs, and monitoring
• Scale with Postgres - Partitioning, sharding, and read replicas all work

As your needs grow, fundamental database scaling concepts—partitioning, connection pooling, read replicas—apply to vector workloads too. You’re not locked into a specialized system with its own scaling model.

Installation

Local development (Docker)

The easiest way to get started:

docker run -d \
  --name postgres-pgvector \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  pgvector/pgvector:pg16

Cloud providers

Most managed Postgres providers now support pgvector:

• Supabase: Enabled by default
• Neon: Enabled by default
• AWS RDS: Available as an extension
• Google Cloud SQL: Available as an extension
• Azure Flexible Server: Available as an extension

Enable the extension

Once you have a pgvector-enabled Postgres instance:

CREATE EXTENSION IF NOT EXISTS vector;

Creating your first vector table

Let’s build a simple document search system. First, create a table to store documents and their embeddings:

CREATE TABLE documents (
  id BIGSERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  embedding vector(1536),  -- OpenAI ada-002 dimension
  created_at TIMESTAMPTZ DEFAULT NOW()
);

The vector(1536) type stores a 1536-dimensional vector. The dimension must match your embedding model:

• OpenAI text-embedding-ada-002: 1536 dimensions
• OpenAI text-embedding-3-small: 1536 dimensions
• OpenAI text-embedding-3-large: 3072 dimensions
• Cohere embed-english-v3.0: 1024 dimensions
• Local models vary (384-4096 common)

Generating embeddings

You need to generate embeddings outside of Postgres. Here’s a simple Python example using OpenAI:

import openai
import psycopg2

client = openai.OpenAI()

def get_embedding(text: str) -> list[float]:
    response = client.embeddings.create(
        model="text-embedding-ada-002",
        input=text
    )
    return response.data[0].embedding

# Connect to Postgres
conn = psycopg2.connect("postgresql://postgres:postgres@localhost/postgres")
cur = conn.cursor()

# Insert a document with its embedding
title = "Introduction to PostgreSQL"
content = "PostgreSQL is a powerful, open source relational database..."
embedding = get_embedding(content)

cur.execute(
    "INSERT INTO documents (title, content, embedding) VALUES (%s, %s, %s)",
    (title, content, embedding)
)
conn.commit()

For production, you’d want to batch these operations and handle rate limits. Check out Supabase’s vecs library for a more robust Python client.

Querying similar documents

pgvector supports three distance metrics:

Operator	Distance	Use case
<->	L2 (Euclidean)	General purpose
<#>	Inner product	When vectors are normalized
<=>	Cosine	Text embeddings (most common)

For text embeddings, cosine distance (<=>) is typically best:

-- Find 5 most similar documents to a query
SELECT
  id,
  title,
  1 - (embedding <=> $1::vector) AS similarity
FROM documents
ORDER BY embedding <=> $1::vector
LIMIT 5;

The $1 would be the embedding of your search query. In Python:

def search_documents(query: str, limit: int = 5):
    query_embedding = get_embedding(query)

    cur.execute("""
        SELECT id, title, content, 1 - (embedding <=> %s::vector) AS similarity
        FROM documents
        ORDER BY embedding <=> %s::vector
        LIMIT %s
    """, (query_embedding, query_embedding, limit))

    return cur.fetchall()

# Search for relevant documents
results = search_documents("How do I set up a database?")
for doc_id, title, content, similarity in results:
    print(f"{title} (similarity: {similarity:.3f})")

Adding indexes for performance

Without an index, pgvector does an exact nearest-neighbor search (scanning every row). This is fine for small tables but doesn’t scale.

pgvector offers two index types:

IVFFlat (Inverted File with Flat compression)

Faster to build, good for datasets under 1M vectors:

CREATE INDEX ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

The lists parameter controls how many clusters to create. Rule of thumb:

• Under 1M rows: lists = rows / 1000
• Over 1M rows: lists = sqrt(rows)

Important: IVFFlat requires data to build the index. Create it after inserting your initial data.

HNSW (Hierarchical Navigable Small World)

More accurate, better for larger datasets, but slower to build:

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

Parameters:

• m: Max connections per node (default 16, higher = more accurate but slower)
• ef_construction: Size of candidate list during build (default 64)

HNSW can be created on an empty table and updated incrementally. The algorithm is based on research by Yury Malkov—see the original HNSW paper for the theory.

Which index to choose?

Factor	IVFFlat	HNSW
Build speed	Faster	Slower
Query speed	Fast	Faster
Accuracy	Good	Better
Memory	Lower	Higher
Incremental updates	Poor (needs rebuild)	Good

Recommendation: Start with HNSW unless you have a specific reason not to.

Wrapping up

You now have everything you need to add vector search to your PostgreSQL database. pgvector gives you semantic search without the operational complexity of a separate vector database—your embeddings live alongside your data, participate in transactions, and scale with familiar Postgres patterns.

To recap what we covered:

• Install pgvector via Docker or your cloud provider
• Store embeddings in vector(N) columns matching your model’s dimensions
• Query with cosine distance (<=>) for text embeddings
• Add an HNSW index for production performance

As your dataset grows, there’s more to explore: tuning accuracy parameters, half-precision vectors for storage savings, and pgvectorscale for tens of millions of vectors.

Before you ship to production, make sure to read Common pgvector Pitfalls—it’ll save you from the mistakes we’ve seen trip up most teams.