Database Performance Report Processing

Analyze database observability reports and produce prioritized optimization plans for Drizzle ORM codebases.

Triage Workflow

Parse report — Extract structured data from the pasted observability report.
Validate report completeness — Identify missing sections before making recommendations.
Analyze issues — Detect high-impact problems with evidence-based rules.
Rank priorities — Sort issues by impact (high/medium/low).
Generate action plan — Propose specific, safe tasks with schema-level guidance.

Incomplete Report Handling

Ask for missing sections instead of guessing when required data is absent.
Request only the missing parts needed for the blocked recommendation.
Mark recommendations as conditional when partial data is available.

Required sections for full triage:

Table Statistics
High Sequential Scans
Unused/Rarely Used Indexes
Foreign Keys Without Indexes
Cache Hit Ratio

Issue Detection Heuristics

Critical Issues (Immediate Action)

| Issue | Detection Rule | Action | | ---------------------------- | -------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------- | | Bloated Tables | Dead rows >10% in Table Statistics | VACUUM via /app/admin/observability UI | | Missing/Weak Index Coverage | Index usage <50% with high sequential scans and known query filter/sort patterns | Propose an index strategy in packages/db/src/schema.ts only after validating query patterns | | Foreign Keys Without Indexes | FK columns without corresponding indexes | Add index in schema.ts |

Medium Priority (Next Sprint)

| Issue | Detection Rule | Action | | ----------------- | ------------------------------------------------------------------ | ------------------------------------------------------------------------------ | | Unused Indexes | Non-unique indexes with low usage across a defined workload window | Remove from schema.ts only after validating real query paths and safety checks | | Duplicate Indexes | Multiple indexes covering same columns | Remove redundant index |

Low Priority (Monitor)

| Issue | Detection Rule | Action | | --------------- | -------------- | ----------------------------------------------------------------------------------------------------------------- | | Cache Hit Ratio | Below 99% | Investigate workload and cache behavior first; escalate shared_buffers tuning as optional infra-level follow-up |

Report Format Expected

Table Statistics:
| table_name | total_size | dead_rows | dead_ratio_pct |
|------------|------------|-----------|----------------|
| users      | 2048 MB    | 15000     | 12.5           |

High Sequential Scans:
| table_name | sequential_scans | index_usage_pct |
|------------|------------------|-----------------|
| sessions   | 45000            | 25              |

Unused/Rarely Used Indexes:
| index_name           | times_used | index_size |
|---------------------|------------|------------|
| idx_old_feature     | 2          | 5 MB       |

Foreign Keys Without Indexes:
| table_name | column_name | suggested_index |
|------------|-------------|-----------------|
| posts      | user_id     | CREATE INDEX... |

Cache Hit Ratio:
| cache_hit_ratio_pct |
|---------------------|
| 97.5                |

Index Safety Rules

NEVER remove these indexes:

Primary keys (*_pkey)
Unique constraints (*_unique)
Business logic constraints (e.g., organizations_slug_unique)

SAFE to remove:

Non-unique indexes with consistently low usage over a representative workload window (for example 7-30 days, depending on traffic patterns)
Redundant indexes (covered by composite indexes)

Removal guards (required):

Validate workload window coverage before deciding an index is unused.
Validate query-path usage from real application paths (API endpoints, background jobs, cron flows) before removal.
Verify no hidden dependency through constraints, migrations, or operational workflows.
Keep recommendation conditional if query-path validation data is missing.

Example of safe removal:

// oauth_consents has composite unique(userId, clientId)
// So single-column index on clientId alone is redundant
index("oauth_consents_client_id_idx"); // SAFE TO REMOVE

Action Plan Template

Load and follow references/action-plan-template.md for the full output structure and wording.

Important Rules

Reference actual file paths (packages/db/src/schema.ts)
Provide exact code snippets for schema changes
Prioritize by impact (dead rows > missing indexes > unused indexes)
NEVER suggest ad-hoc SQL — schema.ts is source of truth
Include workflow steps at the end
Estimate improvements only when the report provides enough data to support a defensible estimate
Treat shared_buffers tuning as optional and escalation-only infrastructure guidance

Related Skills

performance-optimization — Effect parallel patterns, N+1 fixes, batch operations
drizzle-database — Schema patterns, migrations, relations
production-troubleshooting — Kubernetes performance debugging