Home
Tutorial

Deploy PostgreSQL with pgvector for RAG in 5 Minutes

Build AI-powered semantic search without the complexity of dedicated vector databases. PostgreSQL + pgvector gives you vectors AND relational data in one place.

Get the AMI on AWS Marketplace

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
1

Launch the AMI (2 minutes)

  1. Go to AWS Marketplace and search for "PostgreSQL pgvector FlowOps"
  2. Click Continue to Subscribe then Continue to Configuration
  3. Select your region and click Continue to Launch
  4. Choose your instance type:
    • m5.large (8GB RAM) for production
    • t3.medium for testing
  5. Configure VPC/Subnet and Security Group (allow ports 22, 5432, 80)
  6. Click Launch
2

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.

3

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);
4

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.

Next Steps

Ready to build your RAG application?

Get a production-ready PostgreSQL + pgvector instance running in minutes.

Launch on AWS Marketplace