How to Deploy an App with a Vector Database

Vector search powers semantic search, recommendations, deduplication, and RAG. The deployment question is which vector store to run and how to wire it into your app. This guide focuses on the pragmatic and increasingly popular approach, PostgreSQL with the pgvector extension, while being fair about when a dedicated vector database is the better call.

pgvector vs a dedicated vector database

There's a genuine tradeoff here:

For most applications, especially ones that already use Postgres for relational data, pgvector wins on simplicity: your vectors live next to the rows they describe, you can filter with normal SQL WHERE clauses, and there's one backup and one connection pool. Reach for a dedicated vector DB when you're at very large scale, need advanced hybrid search, or want to scale vector workloads independently of your relational database.

Setting up pgvector

Enable the extension and create a table with a vector column. The dimension must match your embedding model (1536 for OpenAI text-embedding-3-small, 384 for many sentence-transformers):

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id BIGSERIAL PRIMARY KEY,
  content TEXT NOT NULL,
  category TEXT,
  embedding vector(1536)
);

Indexing for speed

Without an index, similarity search scans every row, fine for thousands of rows, painful for millions. pgvector supports HNSW (better recall, slower build) and IVFFlat (faster build, needs tuning). HNSW is usually the right default:

CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops);

Match the operator class to your distance metric: vector_cosine_ops for cosine, vector_l2_ops for Euclidean, vector_ip_ops for inner product. Use the same metric at query time that you indexed with, or the index won't be used.

Generating and storing embeddings

Embeddings come from a model, hosted (OpenAI, Cohere) or self-run (sentence-transformers). Generate once on insert:

emb = embed("Some document text")  # returns a 1536-dim list
cur.execute(
    "INSERT INTO documents (content, category, embedding) VALUES (%s, %s, %s)",
    (text, category, emb),
)

Cache embeddings for repeated inputs to avoid paying for the same text twice.

Querying by similarity

The distance operators do the work. <=> is cosine distance:

SELECT content, 1 - (embedding <=> $1) AS similarity
FROM documents
WHERE category = 'docs'
ORDER BY embedding <=> $1
LIMIT 10;

Notice the WHERE category = 'docs', this is pgvector's superpower: combine semantic similarity with ordinary relational filters in one query. A dedicated vector DB makes this harder.

Wiring it into your app

Your application connects to Postgres normally and runs these queries. Nothing exotic, the vector column is just another column. In Python with psycopg, register the vector type adapter so lists round-trip cleanly:

from pgvector.psycopg import register_vector
register_vector(conn)

Deploying on PandaStack

1. 1Provision a managed PostgreSQL database (14.x or 16.x). PandaStack wires it to your app and injects DATABASE_URL.
2. 2Enable the vector extension and create your schema (run the SQL via a migration or a one-off job).
3. 3Deploy your app as a container app, connected to your Git repo. Build runs in an ephemeral Job pod with rootless BuildKit and deploys via Helm.
4. 4Add embedding-model API keys as environment variables if you use a hosted embedder.
5. 5Tail live logs to confirm the app connected and queries are hitting the HNSW index (check EXPLAIN shows an index scan).

Keeping vectors in the managed Postgres means one auto-wired database serves both your relational and vector needs, with scheduled and manual backups covering both.

Sizing and the free tier

Vector indexes consume memory and storage. The free-tier database is small, perfect for prototyping semantic search over a modest dataset or a demo, but plan to size up for production corpora. HNSW indexes in particular benefit from memory, so a memory-optimized tier helps as your vector count grows into the millions. The app itself is light and fits the free tier, including scale-to-zero for low-traffic services.

Common pitfalls

• Dimension mismatch between your model and the column definition, the insert fails.
• Querying with a different metric than the index was built with, the planner skips the index.
• No index on large tables, queries do full scans.
• Undersized database for a big index, retrieval slows and memory pressure mounts.

References

• pgvector: https://github.com/pgvector/pgvector
• pgvector HNSW indexing: https://github.com/pgvector/pgvector#hnsw
• PostgreSQL extensions: https://www.postgresql.org/docs/current/sql-createextension.html
• OpenAI embeddings: https://platform.openai.com/docs/guides/embeddings
• Qdrant (dedicated alternative): https://qdrant.tech/documentation/

For most apps, pgvector in your managed Postgres is the simplest way to add vector search, one database, full SQL, semantic plus relational filtering. Provision a managed PostgreSQL with pgvector and deploy your app on PandaStack's free tier: https://dashboard.pandastack.io