Database Performance Audit Template

 

Use this checklist to review database health, spot performance bottlenecks, and ensure long-term scalability. Each item can be marked as:

✅ Yes | ⚠️ Needs Attention | ❌ No

📌 Section 1: Query Optimization

Item	Status	Notes
Are queries using indexes on WHERE and JOIN conditions?
Are queries using SELECT * unnecessarily?
Are expensive joins or subqueries optimized or refactored?
Are EXPLAIN plans used to analyze slow queries?
Are there long-running queries that can be batched or rewritten?

📌 Section 2: Indexing Strategy

Item	Status	Notes
Are indexes created on frequently queried columns (e.g., user_id, timestamps)?
Are unused or duplicate indexes removed?
Are compound (multi-column) indexes used where appropriate?
Is the index hit rate monitored and acceptable?
Are write-heavy tables optimized to avoid over-indexing?

📌 Section 3: Data Modeling & Schema Design

Item	Status	Notes
Is data normalized (or denormalized, if necessary for reads)?
Are data types optimized for storage and performance?
Are foreign keys and constraints appropriately defined?
Are ENUMs or lookups used for repetitive values?
Is table partitioning used for very large tables?

📌 Section 4: Caching & Materialization

Item	Status	Notes
Is caching implemented for read-heavy queries (Redis, Memcached, etc.)?
Are frequently accessed views materialized?
Is query result caching available and enabled (if supported by DB)?
Is app-layer caching used for static dropdowns, configs, etc.?

📌 Section 5: Maintenance & Monitoring

Item	Status	Notes
Are slow query logs enabled and reviewed regularly?
Is database load and memory usage monitored (e.g., via Grafana, CloudWatch, Datadog)?
Are vacuuming/analyze jobs (for PostgreSQL) scheduled regularly?
Is there a regular backup and restore testing plan?
Are schema changes tested in staging before production?

📌 Section 6: Scaling & Infrastructure

Item	Status	Notes
Is read/write splitting (read replicas) used where appropriate?
Are queries balanced across replicas in high-volume environments?
Is connection pooling properly configured?
Is horizontal scaling (sharding) planned for large data volumes?
Are appropriate cloud database instance types (or managed DBs) used?

✅ Summary Section

Area	Score (Out of 5)	Notes/Action Points
Query Optimization
Indexing
Schema & Data Modeling
Caching & Materialization
Maintenance & Monitoring
Scaling & Infrastructure

🔁 Recommended Audit Frequency:

• Monthly for fast-changing production systems

• Quarterly for stable environments

• Before/After major product or schema changes