Skip to content

Store — pgvector

Same SQL, same Python, three managed-Postgres flavors. Move between clouds without rewriting the storage layer.

Official docs verified 2026-08-08

All three managed Postgres services have pgvector in their extension allowlist — enable it once per database with CREATE EXTENSION.

Schema

Dimension must match the embedding model. Default 1536 here matches Azure OpenAI text-embedding-3-small; adjust for other embedders (Gemini 3072, Titan 1024).

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE IF NOT EXISTS chiron_chunks (
    id           text PRIMARY KEY,
    source_uri   text,
    text         text NOT NULL,
    embedding    vector(1536) NOT NULL,
    created_at   timestamptz NOT NULL DEFAULT now()
);

-- HNSW is the current recommended default (fast, high recall).
-- Requires pgvector >= 0.5.0 and matching Postgres cluster.
CREATE INDEX IF NOT EXISTS chiron_chunks_embedding_hnsw
    ON chiron_chunks USING hnsw (embedding vector_cosine_ops);

Python client

import os
import psycopg
from pgvector.psycopg import register_vector

conn = psycopg.connect(os.environ["DATABASE_URL"])   # postgresql://user:pass@host/db
register_vector(conn)


def upsert_chunks(rows: list[tuple[str, str, str, list[float]]]) -> None:
    """rows = [(id, source_uri, text, embedding), ...]"""
    with conn.cursor() as cur:
        cur.executemany(
            """
            INSERT INTO chiron_chunks (id, source_uri, text, embedding)
            VALUES (%s, %s, %s, %s)
            ON CONFLICT (id) DO UPDATE SET
                text = EXCLUDED.text,
                embedding = EXCLUDED.embedding
            """,
            rows,
        )
        conn.commit()


def search_similar(query_vec: list[float], k: int = 5) -> list[dict]:
    """Cosine-distance nearest-neighbors — HNSW picks the index automatically."""
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT id, source_uri, text,
                   1 - (embedding <=> %s) AS similarity
            FROM chiron_chunks
            ORDER BY embedding <=> %s
            LIMIT %s
            """,
            (query_vec, query_vec, k),
        )
        cols = [c.name for c in cur.description]
        return [dict(zip(cols, row)) for row in cur.fetchall()]

<=> is pgvector's cosine-distance operator (see the pgvector README). 1 - distance converts to similarity in [0, 1].

Managed Postgres per cloud

Azure Database for PostgreSQL Flexible Server exposes pgvector as an allowlisted extension. The Terraform module in examples/rag/terraform/azure/ provisions a azurerm_postgresql_flexible_server and pins azure.extensions = "VECTOR" on the server parameters so CREATE EXTENSION vector works.

az postgres flexible-server parameter set \
  --resource-group myrg --server-name mypg \
  --name azure.extensions --value VECTOR

Cloud SQL for PostgreSQL supports pgvector out of the box on Postgres 15+. The Terraform module provisions a google_sql_database_instance and the client CREATE EXTENSION vector runs at first connect. No cluster-level flag needed.

RDS for PostgreSQL supports pgvector on Postgres 15.5+ (see RDS release notes). The Terraform module provisions an aws_db_instance with engine_version = "16.4"; the client runs CREATE EXTENSION vector at first connect.

Same schema on the laptop

To iterate without a cloud round-trip:

docker run --rm -d --name pg \
  -e POSTGRES_PASSWORD=chiron \
  -p 5432:5432 \
  pgvector/pgvector:pg16

DATABASE_URL=postgresql://postgres:chiron@127.0.0.1/postgres \
  python -m examples.rag.service.ingest my-docs/

Portable schema plus a managed backend on every cloud is the whole reason this second path exists.