What You'll Get
- PostgreSQL 18 with pgvector extension pre-installed
- 1536-dimension vectors (OpenAI embedding size) ready to go
- HNSW indexing for fast similarity search
- pgAdmin4 web UI for management
- S3 backup capability with pgBackRest
- Connection pooling with pgBouncer
Launch the AMI (2 minutes)
- Go to AWS Marketplace and search for "PostgreSQL pgvector FlowOps"
- Click Continue to Subscribe then Continue to Configuration
- Select your region and click Continue to Launch
- Choose your instance type:
m5.large(8GB RAM) for productiont3.mediumfor testing
- Configure VPC/Subnet and Security Group (allow ports 22, 5432, 80)
- Click Launch
Connect and Get Credentials (1 minute)
SSH into your instance:
ssh -i your-key.pem ubuntu@<public-ip>
View your auto-generated credentials:
sudo cat /var/lib/postgresql/credentials.txt
You'll see your PostgreSQL password, pgAdmin4 URL, and connection details.
Create Your Vector Table (1 minute)
Connect to PostgreSQL:
psql -U postgres
Create a table for your RAG documents:
-- Enable pgvector
CREATE EXTENSION IF NOT EXISTS vector;
-- Create documents table with embeddings
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
content TEXT NOT NULL,
embedding vector(1536), -- OpenAI ada-002 dimensions
metadata JSONB,
created_at TIMESTAMP DEFAULT NOW()
);
-- Create HNSW index for fast similarity search
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
Query for Similar Documents (1 minute)
Find the 5 most similar documents to a query embedding:
SELECT content, metadata,
1 - (embedding <=> '[0.1, 0.2, ...]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 5;
Full Python RAG Example
Here's a complete example using OpenAI embeddings:
import openai
import psycopg2
# Connect to PostgreSQL
conn = psycopg2.connect(
host="<your-instance-ip>",
database="postgres",
user="postgres",
password="<your-password>"
)
def get_embedding(text):
"""Get embedding from OpenAI"""
response = openai.embeddings.create(
model="text-embedding-ada-002",
input=text
)
return response.data[0].embedding
def store_document(content, metadata=None):
"""Store document with its embedding"""
embedding = get_embedding(content)
with conn.cursor() as cur:
cur.execute("""
INSERT INTO documents (content, embedding, metadata)
VALUES (%s, %s, %s)
""", (content, embedding, metadata))
conn.commit()
def search_similar(query, limit=5):
"""Find similar documents"""
query_embedding = get_embedding(query)
with conn.cursor() as cur:
cur.execute("""
SELECT content, metadata,
1 - (embedding <=> %s::vector) AS similarity
FROM documents
ORDER BY embedding <=> %s::vector
LIMIT %s
""", (query_embedding, query_embedding, limit))
return cur.fetchall()
# Example usage
store_document("PostgreSQL 18 supports pgvector for AI workloads.")
store_document("RAG combines retrieval with LLM generation.")
results = search_similar("How do I build an AI search?")
for content, metadata, similarity in results:
print(f"{similarity:.3f}: {content}")
Why PostgreSQL + pgvector?
| Feature | pgvector | Dedicated Vector DB |
|---|---|---|
| Vectors + relational data | Same database | Separate system |
| SQL queries | Full SQL | Limited |
| ACID transactions | Yes | Varies |
| Operational complexity | One database | Another service to manage |
| Filtering with vectors | Native WHERE clauses | Pre/post filtering |
| Maturity | 35+ years | 2-5 years |
When to use a dedicated vector DB instead
Consider Milvus or Qdrant for billions of vectors, massive multi-tenancy, or specialized features like hybrid search with BM25. For most RAG applications with millions of vectors, pgvector is the simpler choice.
Performance Tips
Use HNSW indexes (not IVFFlat)
HNSW provides better recall and doesn't require training:
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops);
Tune for your workload
-- More accuracy, slower queries
SET hnsw.ef_search = 100;
-- Faster queries, slightly less accuracy
SET hnsw.ef_search = 40;
Use connection pooling
pgBouncer is pre-configured on port 6432:
psql -h localhost -p 6432 -U postgres
Pricing
| Software | $0.10/hour |
| EC2 (m5.large) | ~$0.096/hour |
| Total | ~$0.20/hour or ~$144/month |
Compare to managed vector databases at $0.30-1.00+/hour.