Your Postgres Is Already a Vector Database: A pgvector Quickstart

pgvector turns any PostgreSQL instance into a vector database with one extension -- store embeddings alongside your relational data and query nearest neighbors with the <=> cosine operator.

What you'll be able to do after this:

• Add a vector column to any Postgres table and store embedding outputs directly alongside relational data
• Run semantic similarity queries using the <=> operator and retrieve the top-K nearest neighbors in SQL
• Add an HNSW index that keeps similarity queries fast past one million rows

Resource: pgvector — Supabase Docs + GitHub pgvector/pgvector — Supabase is the easiest setup path (one click in the dashboard); the GitHub README covers raw Postgres install.

Install (once per database)

CREATE EXTENSION IF NOT EXISTS vector;

On Supabase: Database → Extensions → search "vector" → enable. On a local Postgres, install the OS package first (apt-get install postgresql-15-pgvector on Ubuntu).

Create a table with a vector column

CREATE TABLE documents (
  id     SERIAL PRIMARY KEY,
  content TEXT,
  embedding vector(1536)   -- 1536 dims = text-embedding-3-small; match your model
);

Insert embeddings from Python

import psycopg2
from psycopg2.extras import execute_values

conn = psycopg2.connect("postgresql://postgres:pass@localhost/mydb")
cur = conn.cursor()

# embedding is a list of floats from your embedding model
rows = [("pgvector stores any embedding", [0.12, -0.04, 0.33, ...]),
        ("vector search in Postgres", [0.22, 0.01, 0.28, ...])]
execute_values(
    cur,
    "INSERT INTO documents (content, embedding) VALUES %s",
    rows
)
conn.commit()

Query nearest neighbors

-- Find the 5 most similar rows to a query vector
SELECT content,
       1 - (embedding <=> '[0.15, -0.02, 0.30, ...]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[0.15, -0.02, 0.30, ...]'::vector
LIMIT 5;

The three distance operators:

• <=> — cosine distance (standard for text embeddings; values near 0 = very similar)
• <-> — L2 / Euclidean distance
• <#> — negative inner product (use when embeddings are L2-normalized)

Add an HNSW index for production scale

Without an index, every query does a full table scan — fine at 10,000 rows, painful at 1,000,000.

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

HNSW (Hierarchical Navigable Small World) builds a graph-based approximate nearest-neighbor index. At 1M rows: indexed query ~2 ms vs. full-scan ~800 ms. Use vector_cosine_ops when your distance operator is <=>.

IVFFlat is the alternative: lower memory cost, faster build, slightly worse recall. Choose it when HNSW's memory overhead (roughly 128 bytes per vector × dimensions × rows) is prohibitive.

When to use pgvector vs. a dedicated vector DB

Situation	Reach for
Already running Postgres; < ~5M vectors	pgvector
Need ACID transactions and JOINs with relational data	pgvector
Hundreds of millions of vectors or multi-tenant isolation	Qdrant / Pinecone / Weaviate
Built-in hybrid (keyword + vector) search + filtering	Qdrant or Weaviate

pgvector is the right default for any project that already has a Postgres instance. You add one extension and one column type — no new infrastructure to operate.

Sources: pgvector — Supabase Docs · GitHub pgvector/pgvector · pgvector similarity search best practices — Instaclustr · Storing OpenAI embeddings in Postgres with pgvector — Supabase blog