pgvector

Postgres extension for vector similarity search — add agent memory and RAG without a separate database.

v0.8.52026-07-08SQL / CPostgreSQL

#embeddings #rag #postgres #similarity-search

22.3k Completely free Open source

Doc version 1.0 · Updated June 28, 2026

Overview

pgvector adds a vector column type and similarity operators to PostgreSQL.
If your stack already runs Postgres, it is the lowest-friction way to give an agent long-term memory or RAG retrieval — no extra service to operate.

flowchart LR
  text[text] -->|embedding model| vec[vector]
  vec --> pg[(Postgres + pgvector)]
  q[query vector] -->|<=> cosine| pg
  pg --> top[top-k rows]

The Code samples tab shows the schema/query and the index — pick from the selector to compare.

When to use it

Choose pgvector when you want vector search co-located with your relational data and prefer operating one database instead of adding a dedicated vector store.

Example

Schema · vector column

When to use

Reach for it when you want embeddings stored next to your relational data and similarity search in plain SQL.

How it flows

An embedding model turns text into a vector; the <=> (cosine distance) operator orders rows by nearest neighbour.

Structure

flowchart LR
  text[text] -->|embedding model| vec[vector]
  vec --> pg[(Postgres + pgvector)]
  q[query vector] -->|<=> cosine| pg
  pg --> top[top-k rows]

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE memories (
  id        bigserial PRIMARY KEY,
  body      text,
  embedding vector(1536)        -- match your embedding model's dimensions
);

-- Nearest-neighbour search using cosine distance (<=>):
SELECT body
FROM memories
ORDER BY embedding <=> $1
LIMIT 5;

For a runnable build, see pgvector_1 in the Implementation tab — it brings up Postgres and the app with docker compose.

When to use

Add an approximate index once a table grows large enough that an exact full scan gets slow.

How it flows

The hnsw index builds an approximate nearest-neighbour graph, trading a little recall for a big speedup.

Structure

flowchart LR
  rows[(many rows)] --> idx[HNSW index]
  q[query] --> idx
  idx --> approx[approx top-k]

-- For large tables, add an approximate index to keep queries fast.
CREATE INDEX ON memories USING hnsw (embedding vector_cosine_ops);

-- Same query as before — now answered with the index.
SELECT body FROM memories ORDER BY embedding <=> $1 LIMIT 5;

Example

pgvector — vector similarity search in Postgres

A real, runnable mini-project. Download it and run with Docker.

View samples/pgvector_1 on GitHub ↗

June 28, 2026

pgvector — vector similarity search in Postgres

A tiny pgvector demo: it creates a vector column, stores a few toy 3-dim "embeddings", and runs a nearest-neighbour query with the cosine-distance operator (<=>). Swap the toy vectors for an embedding model's output and the SQL is unchanged.

Run with Docker Compose (bundled Postgres)

The compose file starts a pgvector/pgvector Postgres and runs the app against it:

cd samples/pgvector_1
docker compose up --build

No .env is needed here — compose sets DATABASE_URL to the bundled database automatically.

Change the query word in docker-compose.yml (command: ["cat"]) — try rocket to see the neighbours flip.

Run with Docker Compose (in a devcontainer with DooD)

In a dev container that talks to the host Docker daemon (Docker-outside-of-Docker), the attached docker compose up may print nothing for the app even though it ran — the live attached stream drops output over the VM boundary. Read it back from the logs:

cd samples/pgvector_1
docker compose up --build -d
docker compose logs -f app
docker compose down -v

Run locally

Point DATABASE_URL at a Postgres that has the vector extension available:

cd samples/pgvector_1
cp .env.sample .env          # edit DATABASE_URL if needed
pip install -r requirements.txt
python app.py cat

.env is gitignored — only .env.sample is committed.

Example run

Unlike the LLM samples, this one is deterministic — the toy vectors are fixed, so the output is identical every run.

nearest to 'cat' by cosine distance:
  cat        0.0000  (query)
  kitten     0.0129
  rocket     0.8901
  airplane   0.9901

Files

.env.sample

# pgvector needs a Postgres with the `vector` extension. The bundled
# docker-compose.yml starts one for you and sets this automatically.
#
# For a local run (`python app.py`), copy this to `.env` and point it at your
# own Postgres:
#   cp .env.sample .env
DATABASE_URL=postgresql://postgres:postgres@localhost:5432/postgres

app.py

"""pgvector: vector similarity search inside Postgres.

Creates a `vector` column, stores a few toy "embeddings", and runs a
nearest-neighbour query with the cosine-distance operator (`<=>`). A real app
swaps the toy vectors for an embedding model's output — the SQL is identical.

Run (with the bundled Postgres):
    docker compose up --build
"""

import os
import sys

import psycopg
from dotenv import load_dotenv

# Load .env when running locally; docker-compose sets DATABASE_URL directly.
load_dotenv()

DSN = os.environ.get("DATABASE_URL", "postgresql://postgres:postgres@localhost:5432/postgres")

# Toy 3-dim "embeddings" — in practice these come from an embedding model.
DOCS = [
    ("cat", "[1.0, 0.1, 0.0]"),
    ("kitten", "[0.9, 0.2, 0.1]"),
    ("airplane", "[0.0, 0.1, 1.0]"),
    ("rocket", "[0.1, 0.0, 0.9]"),
]


def main() -> None:
    target = (sys.argv[1] if len(sys.argv) > 1 else "cat").lower()
    query_vec = dict(DOCS).get(target, DOCS[0][1])

    with psycopg.connect(DSN, autocommit=True) as conn:
        conn.execute("CREATE EXTENSION IF NOT EXISTS vector")
        conn.execute("DROP TABLE IF EXISTS docs")
        conn.execute("CREATE TABLE docs (id serial PRIMARY KEY, label text, embedding vector(3))")
        for label, embedding in DOCS:
            conn.execute("INSERT INTO docs (label, embedding) VALUES (%s, %s)", (label, embedding))

        rows = conn.execute(
            "SELECT label, embedding <=> %s::vector AS distance FROM docs ORDER BY distance LIMIT 4",
            (query_vec,),
        ).fetchall()

    print(f"nearest to '{target}' by cosine distance:")
    for label, distance in rows:
        marker = "  (query)" if label == target else ""
        print(f"  {label:10s} {distance:.4f}{marker}")


if __name__ == "__main__":
    main()

docker-compose.yml

# pgvector needs a Postgres with the `vector` extension. This bundles one
# (the official pgvector image) and runs the sample app against it.
#   docker compose up --build
services:
  db:
    image: pgvector/pgvector:pg16
    environment:
      POSTGRES_PASSWORD: postgres
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 2s
      timeout: 3s
      retries: 15

  app:
    build: .
    environment:
      DATABASE_URL: postgresql://postgres:postgres@db:5432/postgres
    depends_on:
      db:
        condition: service_healthy
    # The query word — try "rocket" to see the neighbours flip.
    command: ["cat"]

Dockerfile

# Minimal image for the pgvector sample app.
# Postgres (with the vector extension) runs as a separate service — see
# docker-compose.yml.
FROM python:3.13-slim

# Stream stdout/stderr unbuffered so the output appears live.
ENV PYTHONUNBUFFERED=1

WORKDIR /app

COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt

COPY app.py .

# DATABASE_URL points at your Postgres (the bundled one is set in compose):
#   docker compose up --build
ENTRYPOINT ["python", "app.py"]

Overview

When to use it

Pricing

When to use

How it flows

When to use

How it flows

Run with Docker Compose (bundled Postgres)

Run with Docker Compose (in a devcontainer with DooD)

Run locally

Example run