Postgres with pgvector

Retrieval and memory exercises store embeddings next to ordinary rows, and pgvector lets Postgres do that: a vector column type, distance operators and approximate-nearest-neighbour indexes, inside the database you already know. This page runs it locally in Docker so every exercise starts from the same empty database. This page only gets it running; the retrieval lessons explain the indexes.

Before you start

• Docker and Docker Compose installed and running.
• Port 5432 free. If you already run Postgres through Homebrew or an installer, stop it or use another host port (see Troubleshooting).
• For the client code: Node.js, pnpm and TypeScript, Python with uv, or both.

1. Start Postgres with pgvector

The official pgvector/pgvector image is the postgres image with the extension already compiled in. Its tags name the Postgres major version, so you choose one; pg18 is the current release. Create a folder with this file:

services:
  db:
    image: pgvector/pgvector:pg18
    environment:
      POSTGRES_PASSWORD: postgres
      POSTGRES_DB: app
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres -d app"]
      interval: 2s
      retries: 15
 
volumes:
  pgdata:

docker compose up -d
docker compose ps

Wait until ps shows the service as healthy.

• POSTGRES_PASSWORD is required; the image refuses to start without it. postgres is fine for a database only your machine can reach.
• POSTGRES_DB creates a database called app. The user stays postgres.
• The volume is mounted at /var/lib/postgresql, not /var/lib/postgresql/data. PostgreSQL 18 images keep data in a version-specific folder below it. If you switch to a pg17 tag, mount at /var/lib/postgresql/data instead.

Put the connection string in the project's .env, and keep .env out of git:

DATABASE_URL=postgres://postgres:postgres@localhost:5432/app

2. Enable the extension and run a query

Open psql inside the container, so you don't need it installed locally:

docker compose exec db psql -U postgres -d app

pgvector is installed in the image but not enabled in your database. Enable it once per database, then try a nearest-neighbour search:

CREATE EXTENSION IF NOT EXISTS vector;
 
CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(3));
INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');
 
SELECT id, embedding <-> '[3,1,2]' AS distance
FROM items
ORDER BY embedding <-> '[3,1,2]'
LIMIT 5;

The three operators you'll meet in exercises:

Type \q to leave psql. If you have psql installed locally, psql "$DATABASE_URL" connects the same way.

3. Connect from TypeScript

Install node-postgres and the pgvector helpers:

pnpm add pg pgvector
pnpm add -D @types/pg

import pg from "pg";
import pgvector from "pgvector/pg";
 
const client = new pg.Client({ connectionString: process.env.DATABASE_URL });
await client.connect();
await client.query("CREATE EXTENSION IF NOT EXISTS vector");
await pgvector.registerTypes(client);
 
const { rows } = await client.query(
  "SELECT id, embedding FROM items ORDER BY embedding <-> $1 LIMIT 1",
  [pgvector.toSql([3, 1, 2])],
);
console.log(rows[0]);
await client.end();

node --env-file=.env src/smoke.ts

registerTypes makes vector columns come back as number arrays, and toSql turns an array into a parameter. If you prefer Postgres.js (pnpm add postgres), import pgvector from "pgvector" and pass pgvector.toSql(...) inside the sql template; it needs no type registration.

4. Connect from Python

uv add "psycopg[binary]" pgvector

import os
 
import psycopg
from pgvector import Vector
from pgvector.psycopg import register_vector
 
with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
    conn.execute("CREATE EXTENSION IF NOT EXISTS vector")
    register_vector(conn)
    row = conn.execute(
        "SELECT id, embedding FROM items ORDER BY embedding <-> %s LIMIT 1",
        (Vector([3, 1, 2]),),
    ).fetchone()
    print(row)

uv run --env-file .env python smoke.py

register_vector looks up the vector type in the database, so the extension has to exist before you call it. That's why both scripts create it first.

A hosted alternative: Neon

If you'd rather not run Docker, Neon has a free plan, and pgvector is available on every plan. Sign up with your personal account, create a project, copy its connection string into DATABASE_URL, and run CREATE EXTENSION IF NOT EXISTS vector; in the SQL editor. The code above works unchanged. Neon's string ends with SSL parameters such as sslmode=require; keep them, because Neon only accepts encrypted connections.

Check it works

docker compose exec db psql -U postgres -d app -c "SELECT extversion FROM pg_extension WHERE extname = 'vector';"

Expected output (the version is whatever the image ships, 0.8 or newer):

 extversion
------------
 0.8.7
(1 row)

Troubleshooting

password authentication failed for user "postgres" after changing the password. The password is only set when the volume is first created. Run docker compose down -v to delete the data and start again, or change it with ALTER USER in psql.

port is already allocated, or your code connects to the wrong Postgres. A local Postgres is already on 5432. Stop it, or map "5433:5432" and use port 5433 in DATABASE_URL.

type "vector" does not exist. The extension isn't enabled in this database. Run CREATE EXTENSION IF NOT EXISTS vector; while connected to the database your code uses (app, not postgres).

The container exits right after starting. Run docker compose logs db. The usual cause is a volume created by an older Postgres major version, or one mounted at /var/lib/postgresql/data with a pg18 image. Major versions can't share data files: delete the old volume with docker compose down -v, or go back to the tag that created it.

Going further

• Docker and Docker Compose, for the compose commands and cleaning up volumes
• Qdrant, a dedicated vector database for comparison
• Local models with Ollama, for free embedding models to fill the table