Optimize Database Performance for SaaS

This agent analyzes and optimizes multi-tenant database performance for SaaS platforms, identifying bottlenecks, recommending optimizations, and implementing performance improvements. It starts with database schema analysis: reading schema files, migration scripts, and ORM models, identifying the multi-tenancy pattern in use (shared database, database per tenant, schema per tenant), mapping tenant-isolation and data-partitioning strategies, and documenting table relationships and normalization issues.

Query performance assessment analyzes slow query logs and execution plans, identifies N+1 queries, missing indexes, and inefficient joins, reviews ORM-generated queries for optimization, and examines tenant-specific query patterns and cross-tenant data access. Index strategy optimization analyzes existing indexes for redundancy, recommends composite indexes for multi-tenant query patterns, designs tenant-aware indexing (tenant_id as the leading column), and weighs index-maintenance overhead against performance gains.

Resource allocation analysis monitors connection-pooling efficiency and tenant resource usage, memory allocation and buffer-pool utilization, partition-pruning effectiveness for time-series data, and read-replica load distribution. Implementation planning prioritizes optimizations by impact versus complexity, creates migration scripts for index changes, designs an A/B testing framework for performance improvements, and plans a deployment strategy that minimizes tenant impact.

The output is a Performance Optimization Report: an executive summary (baseline metrics, top 3 opportunities with expected impact, timeline), schema analysis, query optimization (top 10 slow queries with fixes, ORM pattern improvements, tenant-isolation query efficiency), index strategy (additions/removals, composite designs, maintenance impact), and a phased implementation plan (Phase 1 quick wins under 1 week, Phase 2 medium-impact changes 1-4 weeks, Phase 3 architectural improvements 1-3 months). It also produces SQL implementation files for index creation, query rewrites, and partition management. Guidelines throughout: tenant isolation first, measure performance before and after every change, implement incrementally, monitor CPU/memory/I/O impact, account for tenant-size variability, ensure every change is safely rollback-able, and document all changes.

Use this agent when a multi-tenant SaaS database is showing performance bottlenecks - slow queries, index bloat, or resource contention across tenants - and needs a structured optimization plan that respects tenant isolation. It is specifically built around multi-tenant architecture patterns. It is not the right fit for single-tenant databases with no tenant-isolation concerns, where a general query-optimization pass is simpler and sufficient.

Input: database schema, migration scripts, ORM models, and slow query logs/execution plans.

Output: a Performance Optimization Report plus SQL implementation files for indexes, query rewrites, and partitioning. Example tenant-aware index pattern the agent recommends:

-- Tenant-aware composite index pattern
CREATE INDEX CONCURRENTLY idx_table_tenant_lookup
ON table_name (tenant_id, frequently_queried_column, created_at)
WHERE active = true;

Analyzes database schema, migrations, and ORM models directly, and uses database-specific tooling such as pg_stat_statements-style query statistics to identify expensive tenant queries.

Backend and database engineers on multi-tenant SaaS platforms diagnosing performance bottlenecks, and platform teams needing a tenant-isolation-respecting, phased plan for index and query optimization.