Cutting database CPU by 83% without upgrading the instance

// the challenge

A fintech customer had a transactions table on a large RDS MariaDB instance that was struggling under write load. In the worst hours, CPU sat pegged at 100%. The standard answer is to resize to a bigger instance and pay more, forever. But the write load itself was suspicious: every insert had to update every index on the table, and nobody had checked in years whether anything still read those indexes. An index no query uses is pure cost — it slows down every write and serves nothing.

// our approach

• Pulled index usage statistics from the database and checked each index against the application code, looking for indexes with zero reads and zero references anywhere in the codebase.
• Marked each candidate invisible first (`ALTER TABLE ... ALTER INDEX ... IGNORED`) instead of dropping it — the database keeps maintaining the index but the query planner stops using it, so the change is a reversible dry run.
• Watched query performance and error rates through the invisible phase; dropped the indexes only after nothing regressed.
• Measured before and after with CloudWatch and Performance Insights, so the numbers here are measurements, not estimates.

// the outcome

Average CPU fell from 13.4% to 2.3%, p95 CPU from 31.7% to 3.3%, and database load dropped 58% — all on the same instance, with zero hardware changes. The worst hours changed the most: hours that used to run pegged at 100% now peak at 11.7%. The spikes are gone, the instance went from maxed out to mostly idle, and the next expensive resize is years away.