Documentation
Database & Caching Layer
PostgreSQL high-availability, PgBouncer, Redis caching, and cache warming.
6. Database Layer
PostgreSQL High Availability Setup
Rendering diagram…
Connection Flow with PgBouncer
Rendering diagram…
PostgreSQL max_connections = 100 (50 for app, 20 for admin, 20 for monitoring, 10 spare)
Without PgBouncer at 100k users
❌ Each request opens a new connection → 1000+ concurrent connections ❌ PostgreSQL crashes under connection pressure
With PgBouncer at 100k users
✅ 50 persistent connections handle all 1000 RPS ✅ PostgreSQL is happy and fast
Database Provider Comparison
| Feature | AWS RDS | GCP Cloud SQL | Hostinger MySQL | Hetzner (Self) | Heroku Postgres |
|---|---|---|---|---|---|
| PostgreSQL version | 16 | 16 | ❌ MySQL only | 16 (self-managed) | 15 |
| PITR | ✅ 35 days | ✅ 7 days | ❌ | Manual | ✅ 4 days |
| Read replicas | ✅ | ✅ | ❌ | Manual | ✅ (Premium) |
| Auto-failover | ✅ ~30s | ✅ ~60s | ❌ | Manual | ✅ |
| Connection pooling | PgBouncer addon | Built-in proxy | Limited | Self | Built-in |
| Encryption at rest | ✅ | ✅ | ✅ | Manual | ✅ |
| VPC isolation | ✅ | ✅ | ❌ shared | ✅ private net | ✅ |
| Monthly cost (4vCPU/32GB) | ~$350 | ~$300 | ~$50 (limited) | ~$120 | ~$400 |
7. Caching Layer
Redis Cache Architecture
Topology: Redis Sentinel (3 nodes: 1 primary + 2 replicas), or Redis Cluster for >50GB cache.
Cache key namespaces:
| Key pattern | TTL | Purpose |
|---|---|---|
user:{id} | 300s | user profile |
query:{collection}:{hash} | 60s | query results |
ratelimit:{ip}:{endpoint} | 120s | rate state |
session:{token_hash} | 604800s | 7d refresh |
otp:{email} | 600s | 10min OTP |
embedding:{text_hash} | 86400s | embeddings |
pubsub:{collection} | none | realtime channel |
Rendering diagram…
Cache sizing at 100k users:
- Active users (10k concurrent) × avg session data (2KB) = 20MB
- Hot query results × avg result (10KB) = ~500MB
- Rate limit keys (100k IPs) × 64 bytes = 6.4MB
- Total recommended: 4-8GB Redis instance
Cache Warming Strategy
Cold start problem: first request always hits the DB. Solution: predictive warming.
Startup:
- Load top 100 most-queried collections into cache
- Pre-warm user sessions from active refresh tokens
- Pre-load AI embedding models into memory
Rolling deploys:
- New pod comes up → subscribes to Redis PubSub channels
- Serves requests immediately (cache shared across pods)
- No thundering herd (Redis absorbs load)