Database Performance Report Processing

Use this skill to analyze database observability reports and create prioritized optimization plans. Covers PostgreSQL performance triage heuristics specific to Drizzle ORM codebases.

Triage Workflow

• Parse Report — Extract data from the pasted observability report

• Analyze Issues — Identify critical performance problems

• Priority Ranking — Sort issues by impact (high/medium/low)

• Generate Action Plan — Create specific tasks with code snippets

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 Indexes Index usage <50% with high sequential scans Add composite index in packages/db/src/schema.ts

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 <10 uses Remove from schema.ts (verify not PK/UNIQUE first)

Duplicate Indexes Multiple indexes covering same columns Remove redundant index

Low Priority (Monitor)

Issue Detection Rule Action

Cache Hit Ratio Below 99% Consider increasing shared_buffers

Report Format Expected

Table Statistics:

High Sequential Scans:

Unused/Rarely Used Indexes:

Foreign Keys Without Indexes:

Cache Hit Ratio:

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 <10 uses

• Redundant indexes (covered by composite indexes)

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

Database Performance Analysis

Critical Issues (Immediate Action Required)

1. Bloated Tables (Dead Rows >10%)

• table_name (X% dead rows, Y MB)
  • Action: Run VACUUM via /app/admin/observability UI
  • Impact: Reclaim ~Z MB disk space, improve query speed

2. Missing Indexes (Index Usage <50%)

• table_name table (X% index usage, Nk sequential scans)
  • Action: Add composite index in schema.ts
  • Code:

    export const tableName = pgTable(
      "table_name",
      {
        /* fields */
      },
      (table) => [index("idx_table_lookup").on(table.column1, table.column2)],
    );
    

Medium Priority (Plan for Next Sprint)

3. Unused Indexes (Remove ONLY if Safe)

• idx_name (N uses, X MB) - verify not a PK/UNIQUE first
  • Action: Remove from schema.ts, generate migration
  • Impact: Faster INSERT/UPDATE on affected table

Low Priority (Monitor)

4. Cache Hit Ratio (X%)

• Slightly below optimal (target: >99%)
• Action: Consider increasing shared_buffers in PostgreSQL config

Workflow

1. Add indexes to packages/db/src/schema.ts
2. Generate migration: bun run db:generate
3. Review migration SQL in packages/db/drizzle/
4. Apply: bun run db:migrate
5. Run VACUUM on bloated tables via /app/admin/observability UI
6. Monitor Sentry for query performance improvements
7. Re-run report after 24-48h to verify impact

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 disk space/performance improvements

Related Skills

• performance-optimization — Effect parallel patterns, N+1 fixes, batch operations

• drizzle-database — Schema patterns, migrations, relations

• production-troubleshooting — Kubernetes performance debugging