self hosted vector search PostgreSQL

If you want a self-hosted vector search setup using PostgreSQL, the usual choice is:

Use PostgreSQL + pgvector

pgvector is a PostgreSQL extension that adds vector data types and similarity search.

Why this is a good option

• Runs on your own infrastructure
• Simple if you already use Postgres
• Supports approximate and exact vector similarity search
• Works well for moderate-scale semantic search, embeddings, RAG, recommendations, etc.

---

Basic setup

1. Install PostgreSQL

Use your usual self-hosting method:

• Docker
• VM/bare metal
• Kubernetes
• Managed-but-self-controlled Postgres

2. Install pgvector

If using Docker, simplest is an image that includes it, or install the extension package on your server.

Example SQL:

CREATE EXTENSION IF NOT EXISTS vector;

---

Example table

CREATE TABLE documents (
    id bigserial PRIMARY KEY,
    content text,
    embedding vector(1536)
);

1536 is just an example dimension — match it to your embedding model.

---

Insert vectors

INSERT INTO documents (content, embedding)
VALUES (
  'hello world',
  '[0.12, 0.34, ...]'
);

---

Search by similarity

Cosine distance example:

SELECT id, content
FROM documents
ORDER BY embedding <=> '[0.11, 0.33, ...]'
LIMIT 10;

Operators:

• <=> cosine distance
• <-> Euclidean distance
• <#> negative inner product

---

Add an index for performance

HNSW index

Good for high-quality approximate nearest-neighbor search:

CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops);

IVFFlat index

Another option, often used for large datasets:

CREATE INDEX ON documents
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

---

When PostgreSQL is enough

Postgres + pgvector is a great fit if:

• You have up to millions of vectors
• You want operational simplicity
• You need metadata filters plus vector search in one query
• You don’t need a dedicated vector database

---

When to consider a dedicated vector DB instead

Consider alternatives if you need:

• Very large-scale ANN search
• Very high QPS
• Low-latency distributed vector search
• Advanced vector-native features

Examples:

• Qdrant
• Milvus
• Weaviate
• OpenSearch vector search

---

Common stack for self-hosted RAG

• PostgreSQL + pgvector for embeddings
• Application backend in Python/Node/Go
• Embedding model via OpenAI/local models
• Full-text search with Postgres tsvector if needed

---

If you want, I can also give you:

1. a Docker Compose example for Postgres + pgvector,
2. a Python example for inserting/searching vectors, or
3. a production setup guide for self-hosted deployment.