# Scalability Review

> Scope-aware review of scalability and performance readiness — database indexing and query patterns, N+1 queries, caching strategy, connection pooling, horizontal scalability, statelessness, rate limiting, pagination, background job architecture, and expected-load headroom. Thresholds adapt sharply to the project's scope tier (prototype, team, scalable). Use when the user asks about "scalability",…

- **Type:** Skill
- **Install:** `agentstack add skill-nordic-ai-production-readiness-skills-scalability-review`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [Nordic-AI](https://agentstack.voostack.com/s/nordic-ai)
- **Installs:** 0
- **Category:** [Databases](https://agentstack.voostack.com/c/databases)
- **Latest version:** 0.1.0
- **License:** Apache-2.0
- **Upstream author:** [Nordic-AI](https://github.com/Nordic-AI)
- **Source:** https://github.com/Nordic-AI/production-readiness-skills/tree/main/skills/scalability-review

## Install

```sh
agentstack add skill-nordic-ai-production-readiness-skills-scalability-review
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# Scalability Review

You review whether the application can handle its target load and grow with demand. Scalability is not a single property — it's the absence of several specific anti-patterns.

**This skill is the most scope-tier-sensitive.** A prototype should not be forced to defend horizontal scaling. A scalable-tier system must.

## Inputs

From orchestrator: `scope_tier`, `stack_summary`, `gitnexus_indexed`, entry points, and any context on expected load (QPS, data volume, tenant count, geographic distribution).

If expected load isn't known, ask — but keep it simple:

```
Rough scale targets for scaling review:
- Concurrent users (peak): ?
- Requests/sec at peak: ?
- Total data volume (e.g. rows in largest table): ?
- Tenants / accounts: ?
- Geographic spread: single region / multi-region / global?
```

## Mode detection

- **Plan mode** — produce scalability report with prioritized findings.
- **Edit mode** — apply targeted fixes. Indexes, pagination, connection pool tuning, cache additions can usually be applied. Sharding, read replicas, architectural restructuring must be proposed and discussed, not applied.

## Thresholds by tier

| Tier | DB indexing | N+1 | Caching | Stateless | Rate limiting | Pagination | Async jobs | Headroom |
|---|---|---|---|---|---|---|---|---|
| prototype | advisory | advisory | optional | advisory | optional | required on list endpoints | optional | advisory |
| team | **required** on common queries | **required** to fix | recommended where beneficial | **required** (no in-process session state) | **required** on public + auth endpoints | **required** | **required** for long-running work | 2× expected load |
| scalable | **required + reviewed regularly** | **required** | **required** — multi-tier, invalidation strategy | **required + tested** | **required + per-tenant** | **required + cursor-based for large sets** | **required + queue-backed** | 3-5× expected load, headroom monitored |

## Review surface

### 1. Database schema and indexing

- **Check for indexes on common access patterns.** Use GitNexus `mcp__gitnexus__query` if available to find all `WHERE`/`ORDER BY`/`JOIN` columns. Otherwise scan ORM queries / repository methods / SQL files.
- **For each frequently-queried table:**
  - Primary key exists and is appropriate (avoid UUID PKs on InnoDB if hot insert rate is high; use ULIDs or sortable UUIDs).
  - Foreign keys have matching indexes.
  - Composite indexes match query predicates (column order matters — most selective first).
  - Covering indexes for hot read paths where it meaningfully reduces I/O.
  - No duplicate / redundant indexes.
- **Anti-patterns:**
  - `SELECT *` in hot paths — fetches columns you don't need; blocks covering-index optimization.
  - `LIKE '%foo%'` on un-indexed text — full scan; use full-text search or GIN/GIST (Postgres).
  - `OR` across indexed + non-indexed columns causing index skip.
  - `ORDER BY RAND()` — full scan + filesort.
  - Queries without `LIMIT` returning large result sets.
- **Migrations:**
  - Adding an index on a large table without `CONCURRENTLY` (Postgres) blocks writes.
  - Adding NOT NULL columns with a default on a large table can rewrite the whole table (Postgres  1 for availability.
- Scale-up is **faster** than scale-down (avoid oscillation; gentler scale-down).
- Cooldown periods prevent flapping.
- Scaling signals are **leading** (CPU, queue depth, p95 latency), not lagging (error rate).

### 15. Expected-load headroom

- Capacity at least 2× (team) / 3-5× (scalable) expected peak — load test should prove this.
- Cross-reference test-coverage findings on stress tests.
- Failure modes at over-capacity: graceful 503s > silent degradation > timeouts.

## Severity classification

| Severity | Meaning |
|---|---|
| critical | Guaranteed failure at modest scale: N+1 on hot path, missing index on FK, single-instance stateful service. |
| high | Will degrade badly within plausible growth window: lack of pagination, sync blocking in async runtime, shared cache missing for expensive compute. |
| medium | Not immediate pain but will compound: suboptimal index, missing rate limit, missing connection pool tuning. |
| low | Nice-to-have optimizations: compression, HTTP/2, prefetch, etc. |
| info | Observations about current capacity / headroom. |

## Output format

```yaml
- id: SCALE-
  severity: ...
  category: db-index | n-plus-1 | query | pool | cache | stateless | async | rate-limit | pagination | search | concurrency | geo | payload | autoscale | headroom
  title: ...
  location: 
  description: |
    
  evidence:
    - 
  remediation:
    plan_mode: |
      
    edit_mode: |
      
  references:
    - 
  blocker_at_tier: [...]
  expected_impact: |
    
```

Dimension summary:

```markdown
## Scalability Summary

Scope tier: 
Expected load: 
Current hot paths: 

Top 3 scaling risks:
  1. ...

Indexes: 
N+1 detected: 
Cacheable but uncached: 
Paginated endpoints: 
Stateful components: 
```

## Example findings

### Example 1 — N+1 on list endpoint

```yaml
- id: SCALE-003
  severity: high
  category: n-plus-1
  title: "GET /api/projects fires N+1 queries loading owner per row"
  location: "src/routes/projects.ts:19"
  description: |
    The handler fetches projects then iterates to resolve `owner` via a
    separate lookup per row. Production logs show this endpoint issues
    1 + N queries per request, where N averages 43 and peaks at 500+
    for admin users. p99 latency of the endpoint is 1.4s in prod (SLO
    is 400ms). The fix is trivial but the gap compounds as project
    count grows — at scale, this endpoint is the single-largest source
    of DB load.
  evidence:
    - |
      // src/routes/projects.ts:19
      const projects = await db.projects.findAll({ where: { org_id } });
      for (const p of projects) {
        p.owner = await db.users.findByPk(p.owner_id);
      }
    - "Postgres pg_stat_statements: top by calls is the user-by-pk query (SELECT from users WHERE id=$1), 18M calls/day."
  remediation:
    plan_mode: |
      1. Use the ORM's eager-loading primitive:
         `include: [{ model: User, as: 'owner' }]` (Sequelize),
         `.preload(:owner)` (Ecto / Rails), `joinedload` / `selectinload`
         (SQLAlchemy).
      2. Add a test that asserts the endpoint issues bounded (≤2)
         queries.
      3. Audit sibling endpoints for the same pattern.
    edit_mode: |
      Safe. Diff adds eager-loading include + the query-count test.
  references:
    - "Martin Fowler — N+1 query problem"
  expected_impact: "p99 1400ms → ~120ms; DB load on users reduced ~95%."
  blocker_at_tier: [team, scalable]
```

### Example 2 — Missing FK index causes hot-query seq scan

```yaml
- id: SCALE-010
  severity: high
  category: db-index
  title: "messages.conversation_id has no index — every thread fetch seq-scans"
  location: "db/schema.sql:88"
  description: |
    `messages.conversation_id` has a foreign-key constraint but no
    index. The thread-fetch query `SELECT ... FROM messages WHERE
    conversation_id = $1 ORDER BY created_at DESC LIMIT 50` performs a
    sequential scan on a 62M-row table. pg_stat_statements shows this
    query contributing 22% of total DB time. An appropriate index
    drops the query from ~300ms to ~2ms and vastly reduces cache
    thrash.
  evidence:
    - |
      -- db/schema.sql:88
      CREATE TABLE messages (
        id BIGSERIAL PRIMARY KEY,
        conversation_id BIGINT NOT NULL REFERENCES conversations(id),
        ...
      );
      -- no index on conversation_id
    - "EXPLAIN ANALYZE shows Seq Scan on messages, Rows Removed by Filter: 61,998,112"
  remediation:
    plan_mode: |
      1. `CREATE INDEX CONCURRENTLY idx_messages_conv_created ON
         messages (conversation_id, created_at DESC);` — matches the
         ORDER BY so it can serve sorted reads without a filesort.
      2. Verify with EXPLAIN ANALYZE after build.
      3. Add an ORM-level contract test to prevent regression on the
         thread endpoint (query count / plan shape).
    edit_mode: |
      Safe on Postgres with CONCURRENTLY (no table lock). Confirm
      before applying — builds may take 15+ min on a 62M-row table,
      and CI that runs migrations on deploy must allow this window.
  references:
    - "Postgres — Multicolumn Indexes"
  expected_impact: "Thread fetch p99 300ms → 2ms."
  blocker_at_tier: [team, scalable]
```

### Example 3 — In-process session state prevents horizontal scaling

```yaml
- id: SCALE-022
  severity: critical
  category: stateless
  title: "Sessions stored in process memory — cannot run >1 instance"
  location: "src/auth/session.ts:8"
  description: |
    Sessions are held in a module-level `Map` in the
    application process. Any attempt to run a second instance behind
    a load balancer breaks authentication because sessions live on a
    single pod. Current deployment is 1 replica — which is also a
    single point of failure, and prevents scaling beyond a single
    node's capacity. The limit is implicit and invisible in metrics
    until the day the team tries to scale.
  evidence:
    - |
      // src/auth/session.ts:8
      const sessions = new Map();
      export function getSession(id: string) { return sessions.get(id); }
  remediation:
    plan_mode: |
      1. Move sessions to a shared store: Redis (fastest), DB
         (simplest), or signed JWTs (stateless).
      2. For Redis: use `ioredis` + a session library
         (`connect-redis`, custom wrapper). TTL matches session
         lifetime.
      3. Keep a thin in-process LRU for read-through caching, write-
         through invalidated on mutations.
      4. Update the deployment to >1 replica + enable rolling.
    edit_mode: |
      Architectural change. Requires explicit confirmation +
      coordination with ops for Redis provisioning + session
      migration (users online at cutover lose their session unless a
      dual-read strategy is used).
  references:
    - "Twelve-Factor App — VI. Processes"
  expected_impact: "Enables horizontal scale + HA."
  blocker_at_tier: [team, scalable]
```

## Edit-mode remediation

Safe:
- Adding missing indexes (use `CREATE INDEX CONCURRENTLY` for Postgres on large tables — flag to user).
- Adding `LIMIT` / pagination to list endpoints.
- Adding `prefetch_related` / `joinedload` / DataLoader for N+1.
- Adding cache-control headers.
- Adding response compression middleware.
- Tuning connection pool sizes (within conservative bounds).
- Adding rate-limit middleware with defaults.

Require confirmation:
- Migrating from in-process state to a shared store.
- Restructuring for horizontal scaling.
- Introducing a job queue or cache layer (adds dependency).
- Changing sync → async paradigm.
- Adding read replicas / sharding / caching tiers (architectural).
- Changing autoscaling policy.

## Do not

- Do not prescribe scalable-tier solutions (sharding, multi-region, event sourcing) to prototype-tier apps — complexity without justification kills small projects.
- Do not add indexes speculatively — each index has write + storage cost. Add them where queries justify them.
- Do not recommend caching as a fix for every slow query. First improve the query; cache what's still hot.
- Do not confuse "faster" with "more scalable" — some optimizations reduce latency without increasing throughput, and vice-versa.
- Do not propose microservices as a scalability fix for a monolith unless the actual bottleneck justifies it. Most "we need microservices" problems are really "we need indexes and a queue".
- Do not ignore the cost side. Scalability changes can 10× the infrastructure bill; surface the tradeoff.

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [Nordic-AI](https://github.com/Nordic-AI)
- **Source:** [Nordic-AI/production-readiness-skills](https://github.com/Nordic-AI/production-readiness-skills)
- **License:** Apache-2.0

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** yes
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-nordic-ai-production-readiness-skills-scalability-review
- Seller: https://agentstack.voostack.com/s/nordic-ai
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.