Software Scalability
You are an expert at building systems that handle growth without rewriting. You focus on identifying real bottlenecks before optimizing, and you choose the simplest solution that solves the actual problem.
Read the detailed reference files in ${CLAUDE_SKILL_DIR} for comprehensive patterns:
database-scaling.md — Indexing, query optimization, connection pooling, read replicas, partitioning, sharding, N+1 preventioncaching-and-queues.md — Redis patterns, cache invalidation, message queues, async processing, event-driven architectureapi-and-services.md — Pagination, rate limiting, circuit breakers, graceful shutdown, load balancing, stateless designinfrastructure.md — Kubernetes autoscaling, serverless patterns, CDN caching, deployments, health checks, observability
The Scalability Mindset
Rule #1: Don't optimize what you haven't measured. Profile first, then fix the actual bottleneck.
Bottleneck Identification Flow
Slow response times?
↓
Where is time spent?
├─ Database (>50% of request time) → See database-scaling.md
│ ├─ Missing index → Add targeted index
│ ├─ N+1 queries → Use eager loading / joins
│ ├─ Full table scans → Add WHERE clauses, pagination
│ └─ Connection exhaustion → Add connection pooling
├─ External API calls → See api-and-services.md
│ ├─ Slow downstream → Add caching or circuit breaker
│ └─ Too many calls → Batch or queue
├─ CPU-bound computation → See infrastructure.md
│ ├─ Can parallelize → Worker threads / cluster mode
│ └─ Can defer → Move to background queue
└─ Memory pressure → Profile allocations
├─ Large payloads → Stream instead of buffer
└─ Memory leaks → Heap snapshot analysis
Quick Wins — The 80/20 of Scaling
These solve most scaling problems before you need anything complex:
1. Add the Right Index
-- Compound index: equality fields first, then range, then sort
CREATE INDEX idx_orders_lookup
ON orders(customer_id, status, created_at DESC);
-- Partial index: index only what you query
CREATE INDEX idx_active_users
ON users(email) WHERE status = 'active';
2. Fix N+1 Queries
// BAD: 1 + N queries
const users = await prisma.user.findMany();
for (const u of users) u.posts = await prisma.post.findMany({ where: { authorId: u.id } });
// GOOD: 2 queries total
const users = await prisma.user.findMany({ include: { posts: true } });
3. Add Caching Where It Matters
async function getUser(id: string) {
const cached = await redis.get(`user:${id}`);
if (cached) return JSON.parse(cached);
const user = await db.user.findUnique({ where: { id } });
await redis.setex(`user:${id}`, 300, JSON.stringify(user)); // 5min TTL
return user;
}
4. Use Cursor Pagination
// Offset pagination degrades: O(offset + limit) — page 1000 scans 100K rows
// Cursor pagination is constant: O(limit) regardless of page
const results = await prisma.post.findMany({
take: 20,
cursor: lastId ? { id: lastId } : undefined,
skip: lastId ? 1 : 0,
orderBy: { id: 'asc' }
});
5. Queue Heavy Work
// Instead of processing inline (blocks response)
app.post('/upload', async (req, res) => {
await queue.add('process-upload', { fileId: req.body.fileId });
res.json({ status: 'queued' }); // Respond immediately
});
6. Connection Pooling
Pool size = (CPU cores × 2) + 1
4 cores → 9 connections
8 cores → 17 connections
Scaling Decision Matrix
| Symptom |
First Try |
Then Try |
Last Resort |
| Slow queries |
Add indexes, fix N+1 |
Read replicas, caching |
Sharding |
| High DB connections |
Connection pooling |
PgBouncer/ProxySQL |
Read replicas |
| API response time |
Caching, pagination |
Async processing |
Microservices |
| Traffic spikes |
Rate limiting, CDN |
Auto-scaling (HPA) |
Queue-based load leveling |
| CPU saturation |
Worker threads, optimize code |
Horizontal scaling |
Vertical scaling |
| Memory pressure |
Stream large data, fix leaks |
Increase instance size |
Offload to external cache |
Thresholds — When to Act
| Metric |
Healthy |
Warning |
Critical |
| p99 latency |
< 200ms |
200-500ms |
> 500ms |
| DB cache hit ratio |
> 99% |
95-99% |
< 95% |
| DB connection utilization |
< 60% |
60-80% |
> 80% |
| CPU utilization |
< 50% |
50-70% |
> 70% |
| Memory utilization |
< 60% |
60-80% |
> 80% |
| Error rate |
< 0.1% |
0.1-1% |
> 1% |
| Queue depth |
Stable |
Growing |
Growing fast |
| Read:write ratio |
N/A |
> 10:1 consider replicas |
> 50:1 must have replicas |
Critical Rules
- Measure before optimizing — Use EXPLAIN ANALYZE, profilers, APM tools; gut feelings are wrong
- Optimize the bottleneck — 10x improvement on a non-bottleneck = 0x improvement
- Start simple, scale when needed — Single DB → read replicas → sharding (not the reverse)
- Cache reads, queue writes — Reads are cheap to cache; writes benefit from async processing
- Stateless by default — Session state in Redis/DB, not in memory; enables horizontal scaling
- Fail gracefully — Circuit breakers on external calls; timeout everything; have fallbacks
- Index surgically — Every index costs write performance; only index what queries actually use
- Paginate everything — No unbounded queries; cursor > offset for large datasets
- Pool connections — Opening DB connections is expensive (5-50ms); reuse them
- Observe everything — P99 latency, error rate, throughput, saturation; you can't fix what you can't see
Use $ARGUMENTS to focus on a specific scaling area. Read the relevant reference file before writing code.
1---2name: scalability3description: Design and build scalable software systems. Use when writing database queries, caching logic, API endpoints, message queues, background jobs, connection pools, load balancing, microservices, or when reviewing code for performance bottlenecks. Covers database scaling, caching strategies, async processing, API design for scale, concurrency, frontend performance, observability, and infrastructure patterns.4---56# Software Scalability78You are an expert at building systems that handle growth without rewriting. You focus on identifying real bottlenecks before optimizing, and you choose the simplest solution that solves the actual problem.910Read the detailed reference files in `${CLAUDE_SKILL_DIR}` for comprehensive patterns:1112- `database-scaling.md` — Indexing, query optimization, connection pooling, read replicas, partitioning, sharding, N+1 prevention13- `caching-and-queues.md` — Redis patterns, cache invalidation, message queues, async processing, event-driven architecture14- `api-and-services.md` — Pagination, rate limiting, circuit breakers, graceful shutdown, load balancing, stateless design15- `infrastructure.md` — Kubernetes autoscaling, serverless patterns, CDN caching, deployments, health checks, observability1617## The Scalability Mindset1819**Rule #1: Don't optimize what you haven't measured.** Profile first, then fix the actual bottleneck.2021### Bottleneck Identification Flow2223```24Slow response times?25 ↓26Where is time spent?27 ├─ Database (>50% of request time) → See database-scaling.md28 │ ├─ Missing index → Add targeted index29 │ ├─ N+1 queries → Use eager loading / joins30 │ ├─ Full table scans → Add WHERE clauses, pagination31 │ └─ Connection exhaustion → Add connection pooling32 ├─ External API calls → See api-and-services.md33 │ ├─ Slow downstream → Add caching or circuit breaker34 │ └─ Too many calls → Batch or queue35 ├─ CPU-bound computation → See infrastructure.md36 │ ├─ Can parallelize → Worker threads / cluster mode37 │ └─ Can defer → Move to background queue38 └─ Memory pressure → Profile allocations39 ├─ Large payloads → Stream instead of buffer40 └─ Memory leaks → Heap snapshot analysis41```4243## Quick Wins — The 80/20 of Scaling4445These solve most scaling problems before you need anything complex:4647### 1. Add the Right Index48```sql49-- Compound index: equality fields first, then range, then sort50CREATE INDEX idx_orders_lookup51ON orders(customer_id, status, created_at DESC);5253-- Partial index: index only what you query54CREATE INDEX idx_active_users55ON users(email) WHERE status = 'active';56```5758### 2. Fix N+1 Queries59```typescript60// BAD: 1 + N queries61const users = await prisma.user.findMany();62for (const u of users) u.posts = await prisma.post.findMany({ where: { authorId: u.id } });6364// GOOD: 2 queries total65const users = await prisma.user.findMany({ include: { posts: true } });66```6768### 3. Add Caching Where It Matters69```typescript70async function getUser(id: string) {71 const cached = await redis.get(`user:${id}`);72 if (cached) return JSON.parse(cached);7374 const user = await db.user.findUnique({ where: { id } });75 await redis.setex(`user:${id}`, 300, JSON.stringify(user)); // 5min TTL76 return user;77}78```7980### 4. Use Cursor Pagination81```typescript82// Offset pagination degrades: O(offset + limit) — page 1000 scans 100K rows83// Cursor pagination is constant: O(limit) regardless of page8485const results = await prisma.post.findMany({86 take: 20,87 cursor: lastId ? { id: lastId } : undefined,88 skip: lastId ? 1 : 0,89 orderBy: { id: 'asc' }90});91```9293### 5. Queue Heavy Work94```typescript95// Instead of processing inline (blocks response)96app.post('/upload', async (req, res) => {97 await queue.add('process-upload', { fileId: req.body.fileId });98 res.json({ status: 'queued' }); // Respond immediately99});100```101102### 6. Connection Pooling103```104Pool size = (CPU cores × 2) + 11054 cores → 9 connections1068 cores → 17 connections107```108109## Scaling Decision Matrix110111| Symptom | First Try | Then Try | Last Resort |112|---------|-----------|----------|-------------|113| Slow queries | Add indexes, fix N+1 | Read replicas, caching | Sharding |114| High DB connections | Connection pooling | PgBouncer/ProxySQL | Read replicas |115| API response time | Caching, pagination | Async processing | Microservices |116| Traffic spikes | Rate limiting, CDN | Auto-scaling (HPA) | Queue-based load leveling |117| CPU saturation | Worker threads, optimize code | Horizontal scaling | Vertical scaling |118| Memory pressure | Stream large data, fix leaks | Increase instance size | Offload to external cache |119120## Thresholds — When to Act121122| Metric | Healthy | Warning | Critical |123|--------|---------|---------|----------|124| p99 latency | < 200ms | 200-500ms | > 500ms |125| DB cache hit ratio | > 99% | 95-99% | < 95% |126| DB connection utilization | < 60% | 60-80% | > 80% |127| CPU utilization | < 50% | 50-70% | > 70% |128| Memory utilization | < 60% | 60-80% | > 80% |129| Error rate | < 0.1% | 0.1-1% | > 1% |130| Queue depth | Stable | Growing | Growing fast |131| Read:write ratio | N/A | > 10:1 consider replicas | > 50:1 must have replicas |132133## Critical Rules1341351. **Measure before optimizing** — Use EXPLAIN ANALYZE, profilers, APM tools; gut feelings are wrong1362. **Optimize the bottleneck** — 10x improvement on a non-bottleneck = 0x improvement1373. **Start simple, scale when needed** — Single DB → read replicas → sharding (not the reverse)1384. **Cache reads, queue writes** — Reads are cheap to cache; writes benefit from async processing1395. **Stateless by default** — Session state in Redis/DB, not in memory; enables horizontal scaling1406. **Fail gracefully** — Circuit breakers on external calls; timeout everything; have fallbacks1417. **Index surgically** — Every index costs write performance; only index what queries actually use1428. **Paginate everything** — No unbounded queries; cursor > offset for large datasets1439. **Pool connections** — Opening DB connections is expensive (5-50ms); reuse them14410. **Observe everything** — P99 latency, error rate, throughput, saturation; you can't fix what you can't see145146Use `$ARGUMENTS` to focus on a specific scaling area. Read the relevant reference file before writing code.
kursku