Adding Vector Search to PostgreSQL with pgvector

Everybody uses AI for almost everything these days. If you’ve been paying attention to the backend space recently, you’ve probably heard of RAG (Retrieval-Augmented Generation) and the sudden explosion of dedicated “vector databases.”

These databases are fantastic for giving LLMs long-term memory or building semantic search engines. But for many of us, introducing a completely new database technology into our stack is a massive operational headache.

What if you could keep your mature, battle-tested, existing PostgreSQL database and just add vector search to it?

In this post, we’ll try to demystify vector search by solving a real(ish) problem using pgvector, an open-source extension for Postgres.

Let’s find an hypotetical problem

Imagine we have an e-commerce catalog. Traditional SQL LIKE queries only work if the user types exact keywords. If a user searches for “I need something to wear when it’s freezing outside”, a standard SQL query will return zero results because the word “freezing” isn’t in our product descriptions.

Our goal is to build a semantic search engine that understands the meaning of the search query and matches it against the meaning of our products.

Our setup

To keep things simple, we’ll use Docker to run our Postgres database and Python to build our small semantic search engine.

First, let’s create our docker-compose.yml file to spin up a Postgres database with the pgvector extension pre-installed:

# docker-compose.yml
services:
  db:
    image: pgvector/pgvector:pg16
    ports:
      - "5432:5432"
    environment:
      POSTGRES_USER: user
      POSTGRES_PASSWORD: password
      POSTGRES_DB: vectordb

Run this with docker-compose up -d.

Next, we’ll need a few Python libraries. We’ll use sentence-transformers (which runs a lightweight AI model locally on your machine to generate embeddings) and psycopg2 to talk to Postgres.

# requirements.txt
psycopg2-binary
pgvector
sentence-transformers

Install them in your virtual environment:

python -m venv venv
source venv/bin/activate # or venv\Scripts\activate on Windows
pip install -r requirements.txt

Generating embeddings and storing them in PGVector

Here is our working demo. We will create a table, insert some products, convert their descriptions into vectors (embeddings), and then perform a semantic search.

# demo.py
import psycopg2
from pgvector.psycopg2 import register_vector
from sentence_transformers import SentenceTransformer

# 1. Setup Postgres Connection
conn = psycopg2.connect(dbname='vectordb', user='user', password='password', host='localhost')
conn.autocommit = True
cur = conn.cursor()

# 2. Enable pgvector extension and create table
cur.execute('CREATE EXTENSION IF NOT EXISTS vector')
register_vector(conn)
cur.execute('DROP TABLE IF EXISTS products')

# sentence-transformers 'all-MiniLM-L6-v2' outputs 384-dimensional vectors
cur.execute('CREATE TABLE products (id serial PRIMARY KEY, name text, description text, embedding vector(384))')

# 3. Load the local AI model
model = SentenceTransformer('all-MiniLM-L6-v2')

# 4. Insert some products
products = [
    ("Cozy Wool Sweater", "A warm, knitted winter sweater made of 100% wool."),
    ("Running Shoes", "Lightweight sneakers perfect for jogging or marathons."),
    ("Coffee Mug", "Ceramic mug that keeps your coffee hot for hours."),
    ("Winter Coat", "Heavy duty jacket with thermal insulation for snowy weather.")
]

print("Generating embeddings and inserting into database...")
for name, desc in products:
    # Generate the vector embedding for the description
    embedding = model.encode(desc)
    # Insert into PostgreSQL
    cur.execute('INSERT INTO products (name, description, embedding) VALUES (%s, %s, %s)', (name, desc, embedding))

# 5. Search using natural language
query = "I need something to wear when it's freezing outside"
query_embedding = model.encode(query)

print(f"\nSearching for: '{query}'")

# Use the <=> operator for cosine distance
cur.execute("""
    SELECT name, description, embedding <=> %s AS distance 
    FROM products 
    ORDER BY distance LIMIT 2
""", (query_embedding,))

results = cur.fetchall()
for row in results:
    print(f"Product: {row[0]} | Distance: {row[2]:.4f}")

cur.close()
conn.close()

1. Setup Connection: We connect to our local Docker database. Nothing too fancy here.
2. Enable pgvector: We execute CREATE EXTENSION vector. Notice our table schema: embedding vector(384). We are telling Postgres that this column will hold an array of exactly 384 floating-point numbers.
3. Load the Model: We load all-MiniLM-L6-v2. It’s a tiny, fast open-source model that converts text into a 384-dimensional vector. The first time you run this, it will take a few seconds to download.
4. Insert Data: For each product, we ask the model to encode() the description into a vector, and we save both the raw text and the vector to the database.
5. Semantic Search: This is where the magic happens. We encode the user’s natural language query into a vector. Then, we use pgvector‘s custom <=> operator in our SQL query to calculate the “Cosine Distance” between the query vector and every product vector in the database, ordering by the closest match!

Running the code returns:

Generating embeddings and inserting into database...

Searching for: 'I need something to wear when it's freezing outside'
Product: Winter Coat | Distance: 0.5841
Product: Cozy Wool Sweater | Distance: 0.6542

Note: A lower distance means the texts are more semantically similar.

Notice how it successfully found the “Winter Coat” and “Cozy Wool Sweater” even though the words “freezing”, “wear”, or “outside” do not appear in those descriptions!

Why is pgvector even needed?

One of the things that may be bothering you at this point is: why do we need an extension at all? Can’t we just store arrays of numbers in standard Postgres?

You can store arrays in Postgres, but standard SQL doesn’t know how to efficiently calculate spatial geometry across 384 dimensions.

A decent analogy is mapping coordinates. If you have the latitude and longitude of two cities, you can’t just do a simple A = B check to see if they are close; you need a formula to calculate the physical distance between them on a sphere.

Vectors are just coordinates in a high-dimensional space. The AI model places similar concepts closer together in this space. pgvector provides the highly optimized mathematical operators (like <=> for Cosine distance or <-> for Euclidean distance) required to calculate how “close” two concepts are to each other in milliseconds.

A Quick note on Indexing

We skipped one very important step for the sake of simplicity: Indexing.

Because our dataset only has four rows, performing an exact nearest neighbor search (comparing our query to every single row in the database) is instantaneous. However, if you have millions of rows, this linear scan will crush your database.

For production use cases, pgvector supports creating HNSW or IVFFlat indexes, which allow the database to find approximate nearest neighbors incredibly fast, without scanning the entire table. We will cover vector indexing strategies in a future article.

And that should be it. Cheers!