Table of contents
- Quick read
- Readiness check
- Capture the query and the plan before adding an index
- Measure the current read and write cost
- Choose the index shape and write the name down
- Check the migration and rollback path
- Watch write amplification and storage
- Set the verification evidence the ticket must carry
- Review the index set on a schedule
- Role brief
- Frequently asked questions
- Sources
Is the handoff ready?
Use these checks before access expands or the first ticket begins.
Query evidence
Ready: Attach the query, parameters, before plan, and data-set date to the ticket.
Needs work: The lane has no written record for this check.
Read and write cost
Ready: Record read percentiles and write latency on the busiest table hour.
Needs work: The lane has no written record for this check.
Index shape
Ready: Name the column order, index type, and the queries expected to use it.
Needs work: The lane has no written record for this check.
Rollback
Ready: Keep a tested drop statement, the old plan, and the rollback timing.
Needs work: The lane has no written record for this check.
Capture the query and the plan before adding an index
Start the ticket with the exact query, the parameter values, and the screen or job that runs it. Record the database engine and version, because the planner behavior changes between releases. Save the current execution plan and the time it takes on a production-sized data set. An index added to a query nobody captured is a guess that will be hard to review.
Record the row counts for the tables in the plan and the selectivity of the filter columns. Note whether the query sorts, groups, or joins on the same columns. Capture the plan again after the index so the reviewer can compare the two. The before and after plans are the evidence that the change did what the ticket claims.
Measure the current read and write cost
Measure the read latency of the slow query on a representative data set before the change. Record the percentile that matters, such as the median, the ninety-fifth, and the slowest observed run. Note the frequency of the query so a small gain on a rare report does not outrank a daily page. A fast query that runs twice a month is not the same win as a slow checkout read.
Measure the write cost on every table the index will touch. Record the insert, update, and delete rate and the current write latency during the busiest hour. Note the batch jobs that rewrite the same rows. An index that speeds one report can slow every write on a busy table, and the review must see both sides.
Choose the index shape and write the name down
Choose the column order from the filter and sort pattern in the plan, not from the order the columns appear in the ticket. Record why each leading column is included and which queries share the index. Name the index with the table, the purpose, and a short suffix that fits the team convention. A name that describes the purpose survives a review better than an auto-generated string.
Decide whether a partial, covering, or expression index is justified and record the trade-off. Note the queries that would not use it and the ones that would lose a better plan. Keep the index count visible so the reviewer can see whether the table is accumulating redundant copies. Every index has a maintenance cost even when it looks free in a single query test.
Check the migration and rollback path
Write the migration with the lock behavior of the chosen engine in mind. Record whether the index can be built without blocking writes and how long the build takes at production size. Note the migration window and the person who will watch it. A blocking build during peak traffic turns a performance ticket into an outage.
Write the rollback statement and test that it removes the index cleanly. Record the time to roll back and any dependent query that would regress. Keep the old plan and the old metric with the rollback note. A reviewer should be able to reverse the change without reconstructing the reason from chat.
Watch write amplification and storage
Record the expected storage growth and the disk headroom on the target database. Note the backup size change and any retention policy that depends on it. Check the write latency during the first busy hour after the deploy. A storage surprise on a small managed instance can block the next migration.
Watch the cache hit ratio and the buffer pool behavior after the index lands. Note whether the new index evicts a hotter page and shifts latency elsewhere. Record the queries that became slower after the change, not only the one that became faster. The review is complete only when the rest of the workload is checked.
Set the verification evidence the ticket must carry
Require the after plan, the read timing, and the write timing in the ticket before it closes. Record the data set used for the measurement and the date it was taken. Note the query frequency and the expected monthly saving. Evidence with no data set and no date cannot be repeated by the next reviewer.
Ask the reviewer to run one query that should use the index and one that should not. Record both plans and confirm the planner chose as expected. Note any hint, statistic update, or configuration change that the plan depends on. A plan that only works after a manual analyze is a fragile result.
Review the index set on a schedule
Review the table index list each quarter and compare it with the queries the application actually runs. Record the indexes with no observed use and the ones that duplicate a longer index. Remove the dead indexes with the same migration discipline used to add them. An index register that is never pruned becomes a tax on every write.
Review the rule when the engine version, the data volume, or the query mix changes. Record the date of the last review and the owner who signed it. Add the new slow query to the next cycle rather than to an emergency change. A predictable review keeps the database from accumulating one-off decisions.
Copy-ready brief
Put the operating agreement in writing.
Save the before and after execution plans with the data set and date.
Show insert, update, and delete latency before and after the index.
Record the lock mode, build time, and the window owner.
List unused indexes and schedule removal with the same review.
Frequently asked questions
Should every slow query get an index?
No. Some slow queries need a rewrite, a statistic update, or a smaller result set before an index helps.
Who approves the production migration?
The client technical owner approves the window and the rollback plan, while the delivery lead owns the evidence.
How long should the measurement run?
Measure at production size during a normal busy period and record the percentiles, not a single best run.
What happens to an index nobody uses?
List it in the quarterly review and remove it with a tested migration after the owner confirms no query depends on it.
Sources
- PostgreSQL index documentation
How indexes are chosen, maintained, and costed by the planner.
- PostgreSQL CREATE INDEX
Concurrent build behavior and lock considerations.
- MySQL EXPLAIN output
Reading access type, key choice, and row estimates.
- Use The Index, Luke
Practical guidance on index column order and query patterns.
