optimize-database

Database Optimization

Optimize any database technology without assuming MongoDB, PostgreSQL, MySQL, an ORM, or a particular hosting provider. Inspect evidence before making recommendations.

First: detect the data layer

Search the repository for database drivers, ORM/query-builder packages, connection configuration, schemas/models, migrations, indexes, constraints, repositories, raw queries, transactions, seeds, caches, search systems, background jobs, analytics workloads, tests, deployment configuration, and monitoring.

Identify:

• database engines, versions, extensions, topology, hosting, and environment;
• drivers, ORM/query layer, versions, pooling, retry, and timeout configuration;
• schemas, relationships, constraints, indexes, migrations, and ownership;
• high-traffic read/write paths, data volume/growth, concurrency, latency, and throughput;
• replicas, partitions/shards, caches, queues, backup/restore, retention, and observability.

If a data layer exists, explain its current design, healthy choices, measured or evidenced problems, and an ordered safe improvement plan. If none exists, design the smallest suitable persistence approach from the product's consistency, query, scale, security, and operational requirements.

Do not upgrade a database, driver, or ORM silently. Check release notes, compatibility, migration requirements, and rollback before changing versions.

Mandatory full audit

Check every applicable area, even when the request mentions only one slow query:

1. Correctness and integrity — types, nullability, uniqueness, foreign keys/references, validation, invariants, transactions, isolation, idempotency, and consistency.
2. Indexes — missing, unused, duplicate, overlapping, invalid, low-selectivity, oversized, write-heavy, foreign-key, compound order, covering, partial, expression, text, geospatial, TTL, and unique indexes.
3. Queries and plans — scans, selectivity, cardinality estimates, join strategy, sort/group operations, temporary work, N+1 calls, repeated queries, over-fetching, application-side filtering, and plan regressions.
4. Access patterns — real filters, sorts, joins/population, aggregations, writes, hot keys/rows, batch operations, and read/write amplification.
5. Pagination and limits — stable ordering, maximum limits, cursor/keyset strategy, large offsets, unbounded exports, and count-query cost.
6. Schema and modeling — normalization/denormalization, relationship ownership, document growth, row width, large fields, enums, temporal/history data, and multi-tenant boundaries.
7. Concurrency — transactions, isolation level, locks, deadlocks, optimistic/pessimistic control, race conditions, long-running work, and connection starvation.
8. Connections — pool sizing, leaks, timeouts, retries, backoff, prepared statements, proxy/serverless behavior, and graceful shutdown.
9. Caching — need, keys, TTL, invalidation, consistency, stampede protection, negative caching, memory limits, and failure fallback.
10. Migrations — backward compatibility, locks, table rewrites, online index creation, backfills, expand/contract rollout, validation, rollback, and deployment order.
11. Operations — slow-query logs, metrics, tracing, alerts, vacuum/analyze or equivalent maintenance, statistics, fragmentation/bloat, storage, and cost.
12. Reliability — backups, restore tests, point-in-time recovery, replication, failover, disaster recovery, RPO/RTO, and reconciliation.
13. Growth — retention, archival, partitioning/sharding, replicas, materialized views, queues, warehouse/search separation, capacity forecasts, and scaling thresholds.
14. Security and privacy — least privilege, credential storage/rotation, encryption, network access, tenant isolation, sensitive fields, audit logs, deletion/retention, and injection risks.