Scalability Review

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

- 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:

## 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

- 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

- 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

- 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
• Source: Nordic-AI/production-readiness-skills
• License: Apache-2.0

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