Optimize Database Performance for SaaS

Database Performance Optimizer is an autonomous agent for diagnosing and improving multi-tenant database performance in SaaS platforms. It reviews schema files, migrations, ORM models, slow query logs, and execution plans to identify bottlenecks and recommend changes. Its scope includes tenant isolation patterns, query shape, indexes, resource allocation, and deployment planning.

The workflow begins with the schema and tenancy model, such as a shared database, separate database per tenant, or schema per tenant. It maps tenant isolation and partitioning, table relationships, and possible normalization issues. Query analysis then looks for N+1 behavior, missing indexes, inefficient joins, ORM-generated patterns, tenant-specific access, and cross-tenant queries. Index review considers redundant or unused indexes, tenant-aware composite indexes, and maintenance overhead.

Use this agent when a SaaS database has performance issues or needs a structured assessment of queries, indexing, connection pools, memory, partitioning, or read replica usage. It can prepare an implementation plan that weighs expected impact against complexity and stages changes to reduce tenant impact.

Tenant isolation is a first-order constraint: an optimization must preserve boundaries between customers. The agent should measure before and after changes, account for different tenant sizes, monitor CPU, memory, and I/O, and preserve a rollback path. Do not treat a proposed index or query rewrite as safe without checking it against the actual schema, workload, and database engine. The output is analysis and implementation guidance grounded in the available project evidence.

Inputs include schema definitions, migration scripts, ORM models, slow query logs, execution plans, index definitions, connection pool settings, and workload or tenant usage information. The agent can use Read, Glob, Grep, Bash, and WebSearch according to its front matter. It uses those materials to identify bottlenecks and prioritize changes.

The performance report includes an executive summary with baseline metrics and the top three optimization opportunities, schema and tenancy analysis, query recommendations, an index strategy, and an implementation plan. The plan divides work into quick wins, medium-impact changes, and longer architectural improvements. SQL deliverables may cover index creation, query rewrites, and partition management. The source also includes templates for tenant-aware composite indexes and queries against pg_stat_statements.

The agent is designed for SaaS database and ORM environments, and its query template uses PostgreSQL's pg_stat_statements. It refers to database-specific tools for logs and execution plans, connection pools, read replicas, and time-series partitioning. It can prepare migration scripts, but the actual database engine and rollout process must be taken from project context.

This agent is for database engineers, backend developers, and platform teams operating multi-tenant SaaS systems. It is useful when several candidate improvements need to be compared while preserving tenant isolation, documenting impact, and planning safe rollouts. Before/after baselines, incremental changes, resource monitoring, backups, and clear documentation are part of its working guidance.

CREATE INDEX CONCURRENTLY idx_table_tenant_lookup 
ON table_name (tenant_id, frequently_queried_column, created_at)
WHERE active = true;