Deploy a Vector Search API with FastAPI, pgvector and Postgres

Vector search stopped being exotic the moment every product needed "semantic search" or a RAG pipeline. The good news: for most workloads you do not need a dedicated vector database. Postgres with the [pgvector](https://github.com/pgvector/pgvector) extension handles millions of embeddings with an HNSW index, and it keeps your vectors in the same database as the rest of your data — same backups, same transactions, same access control.

Here's the full path: a FastAPI service that ingests documents, embeds them, and serves top-k similarity search — built, containerized, and deployed with a managed Postgres attached.

The stack

• FastAPI + uvicorn — the API layer
• psycopg 3 + pgvector — Postgres driver and vector type support
• OpenAI text-embedding-3-small — 1536-dimension embeddings (swap in any embedding provider; only the dimension in the schema changes)

requirements.txt:

fastapi
uvicorn[standard]
psycopg[binary]
psycopg-pool
pgvector
numpy
openai

Schema and index

One table, one index. Put this in migrate.py so schema setup is a repeatable, explicit step rather than something buried in app startup:

import os
import psycopg

DDL = """
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE IF NOT EXISTS documents (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  content    TEXT NOT NULL,
  embedding  VECTOR(1536) NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS documents_embedding_idx
  ON documents USING hnsw (embedding vector_cosine_ops);
"""

url = os.environ["DATABASE_URL"].replace("postgres://", "postgresql://", 1)

with psycopg.connect(url) as conn:
    ok = conn.execute(
        "SELECT 1 FROM pg_available_extensions WHERE name = 'vector'"
    ).fetchone()
    if not ok:
        raise SystemExit("pgvector is not available on this Postgres instance")
    conn.execute(DDL)
    print("migration complete")

Two details worth pausing on:

• The pg_available_extensions check fails loudly if the extension isn't shipped with your Postgres build, instead of letting CREATE EXTENSION produce a confusing error mid-deploy. Most modern managed Postgres offerings include pgvector, but verify — don't assume.
• HNSW vs IVFFlat. HNSW builds slower and uses more memory, but query recall is better and — critically — you can create the index on an empty table. IVFFlat needs representative data before indexing. For a new service, HNSW is the right default.

The postgres:// → postgresql:// rewrite matters because SQLAlchemy and some drivers reject the short scheme, and connection strings arrive in both forms depending on the platform. One replace() removes a whole category of boot failures.

The API

main.py, complete:

import os
import numpy as np
from fastapi import FastAPI
from pydantic import BaseModel
from openai import OpenAI
from psycopg_pool import ConnectionPool
from pgvector.psycopg import register_vector

DATABASE_URL = os.environ["DATABASE_URL"].replace(
    "postgres://", "postgresql://", 1
)

pool = ConnectionPool(
    DATABASE_URL,
    min_size=1,
    max_size=5,
    configure=lambda conn: register_vector(conn),
)

ai = OpenAI()  # reads OPENAI_API_KEY
app = FastAPI(title="vector-search-api")


def embed(text: str) -> np.ndarray:
    r = ai.embeddings.create(model="text-embedding-3-small", input=text)
    return np.array(r.data[0].embedding)


class DocumentIn(BaseModel):
    content: str


class SearchIn(BaseModel):
    query: str
    k: int = 5


@app.post("/documents")
def add_document(doc: DocumentIn):
    vec = embed(doc.content)
    with pool.connection() as conn:
        row = conn.execute(
            "INSERT INTO documents (content, embedding) "
            "VALUES (%s, %s) RETURNING id",
            (doc.content, vec),
        ).fetchone()
    return {"id": row[0]}


@app.post("/search")
def search(q: SearchIn):
    vec = embed(q.query)
    with pool.connection() as conn:
        rows = conn.execute(
            "SELECT id, content, 1 - (embedding <=> %s) AS score "
            "FROM documents ORDER BY embedding <=> %s LIMIT %s",
            (vec, vec, q.k),
        ).fetchall()
    return [
        {"id": r[0], "content": r[1], "score": float(r[2])} for r in rows
    ]


@app.get("/healthz")
def healthz():
    with pool.connection() as conn:
        conn.execute("SELECT 1")
    return {"ok": True}

The non-obvious parts:

• register_vector is applied per-connection via the pool's configure hook. Without it, psycopg doesn't know how to serialize numpy arrays into the vector type and you get an adapter error on the first insert.
• <=> is pgvector's cosine *distance* operator (0 = identical). 1 - distance converts it to a similarity score, which is what API consumers expect.
• The ORDER BY embedding <=> %s form is what lets Postgres use the HNSW index. Wrap the expression in a function or reorder it and you silently fall back to a sequential scan — fine at 1,000 rows, catastrophic at 5 million.
• max_size=5 on the pool is deliberate, and we'll come back to it.

Run it locally:

export DATABASE_URL=postgres://user:pass@localhost:5432/vectors
export OPENAI_API_KEY=sk-...
python migrate.py
uvicorn main:app --reload

The Dockerfile

FROM python:3.12-slim
WORKDIR /app
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt
COPY . .
EXPOSE 8000
CMD ["sh", "-c", "uvicorn main:app --host 0.0.0.0 --port ${PORT:-8000}"]

Two conventions that make this portable across platforms: bind to 0.0.0.0 (containers routed through an ingress will never reach 127.0.0.1), and read the port from $PORT with a fallback, since most platforms tell you which port to listen on via that variable.

Deploying on PandaStack

1. 1Provision the database. Create a managed PostgreSQL instance (14.x or 16.x) from the dashboard. Backups run daily on top of manual snapshots, retained 7–30 days depending on plan.
2. 2Connect the repo. Add your repository as a container app. With the Dockerfile above, the build runs as-is — rootless BuildKit in an ephemeral Kubernetes Job pod, no host Docker socket involved. Build logs stream live, so when a pip install fails you see it in real time rather than after a timeout.
3. 3Wiring is automatic. Because the database is attached to the app, DATABASE_URL is injected into the container's environment. The only variable you add by hand is OPENAI_API_KEY, set in the app's environment settings.
4. 4Run the migration as a one-off command (python migrate.py) before the service takes traffic — not inside app startup, where two replicas rolling out can race each other on CREATE INDEX.
5. 5Go live. git push. Subsequent pushes rebuild and redeploy; the deployment history gives you rollbacks when an embedding-model change goes sideways.

Production gotchas specific to vector workloads

Connection limits are real. A free-tier PandaStack database allows 50 connections (300 on Pro, 1,000 on Premium). Vector queries hold connections longer than typical CRUD because similarity search is compute-heavy, so a runaway pool exhausts the limit faster than you'd expect. max_size=5 per replica is a sane start; multiply by replica count before raising it.

HNSW index builds take memory. Building the index over an existing large table can exceed maintenance_work_mem and slow to a crawl. Create the index while the table is empty (as migrate.py does) and let it grow incrementally — inserts into an HNSW index are cheap; bulk-indexing 10M rows after the fact is not.

Batch your backfills. Embedding APIs bill per token and rate-limit per minute. If you're importing an existing corpus, embed in batches of 100–500 texts per request rather than one call per row — it's an order of magnitude fewer round trips.

Cold starts on free tier. Idle free-tier apps scale to zero and wake on the next request. For a demo or internal tool that's a fine trade for $0/mo; for a latency-sensitive search endpoint, a paid tier keeps the service warm.

That's the whole system: one table, one index, ~80 lines of Python, and a database you never had to copy credentials for. If you want to see the git push → build → live loop with the Postgres auto-wired, try it at https://pandastack.io.