Review Database Index Changes Against Real Query Evidence

Is the handoff ready?

Use these checks before access expands or the first ticket begins.

Capture the slow query before proposing an index

An index proposal is only reviewable against a specific statement. Ask for the SQL text with parameters, the observed plan, the row estimates, the actual row counts, and the measured duration over a stated window. A screenshot of a slow dashboard is a symptom, not a baseline.

Reproduce the query on a copy with realistic data. A table with ten thousand rows will use a sequential scan and tell you nothing about a table with forty million. The programmer should state the row count behind every timing.

Record the query frequency as well as the duration. A two second query that runs twice a day is a different business decision from a four hundred millisecond query that runs on every page load.

Shape the index ticket around a decision

The ticket names the table, the columns, the index type, the expected plan change, the write path that will pay for it, and the migration window. It also names the reviewer who can approve a schema change and the owner of the apply step.

Keep the first ticket to one index on one table. A Philippines-based programmer can prepare the evidence in a day and the reviewer can check it in one sitting. Bundling four indexes into one migration makes a partial rollback expensive.

State the alternative that was considered, such as a composite index, a covering index, a query rewrite, or a cache. The reviewer needs to see why the simpler option was rejected.

Show the plan change, not just a faster timer

Attach the before and after plan with the same parameters and the same data set. The reviewer should see the scan type, the estimated rows, the actual rows, and the sort or filter step that disappeared.

A faster median can hide a worse tail. Report the slowest observed execution and the number of executions above the old baseline. If the new plan helps common parameters and harms rare ones, say which case the business cares about.

Include the exact command used to collect the plan so the reviewer can repeat it on the same revision. Plans drift with statistics, so the collection date and the last analyze time belong in the packet.

• Query text and parameter values used for both runs.
• Plan output before and after, with node costs and row counts.
• Table and index size, plus estimated build time.
• Executions above the old baseline in the observation window.

Price the write path and the lock profile

Every index adds work to inserts, updates, and deletes, and consumes storage and cache. Ask for the write rate on the table and the columns that change most often. An index on a frequently updated column can turn a fast insert into a measurable slowdown.

Lock behaviour is the part that causes incidents. State whether the build is concurrent, how long it holds a lock, and what happens to writes during it. A migration that blocks writes for six minutes on a busy table is an outage plan, not a maintenance task.

Ask the programmer to measure the build on a copy with production-like volume and to report the observed duration. Estimated durations from a small schema are not evidence.

Make the migration reversible or repair-ready

Write the rollback before the apply. Dropping an index is fast, but the plan may return to the slow state and the release may need a new query path. Record the last known good revision, the apply command, the verification query, and the person who decides to reverse it.

If the build cannot be reversed cleanly, say so and treat the apply as a one way change with a higher approval bar. The company owner, not the programmer, chooses the traffic window and accepts the risk.

Test the migration against an empty table, a small table, and the large copy. The empty case catches syntax and permission problems, while the large case catches duration and lock behaviour.

Probe the cases that make an index useless

Low cardinality columns, leading wildcards, functions on the indexed column, and implicit casts all defeat an index. Ask for a case where the index should not be chosen, and confirm the planner behaves as expected rather than forcing a plan in the test.

Skewed data deserves its own run. If ninety percent of rows share one tenant identifier, an index on that column may not help the largest tenant. Report a plan for the busiest tenant and for a small one.

Partial and expression indexes need a matching predicate. Show the query that qualifies and the query that does not, so a future author does not assume the index covers both.

• Leading wildcard search against the indexed column.
• Function or cast applied to the column in the predicate.
• Largest tenant compared with a small tenant.
• Query that does not match a partial index predicate.

Set review cadence and measures with denominators

Measure the target query duration at the ninety fifth percentile before and after, the number of executions above the old baseline divided by all executions, and the write latency change on the same table. Report the row counts and the collection window.

Add a count of indexes removed or merged in the same period. A schema that only grows accumulates write cost that no single ticket owns. A quarterly index review keeps that cost visible.

Review the result one week and one month after apply. A gain that disappears after statistics refresh or data growth was a temporary plan change, not a durable improvement.

Hand off the decision packet, not just the migration

The packet holds the baseline, the plan comparison, the write cost, the lock profile, the migration revision, the verification query, the rollback, and the apply owner. It also lists untested parameter shapes and known skew.

Close when a reviewer can rerun the query on a copy and see the same plan, and when the apply owner can follow the migration and verification without a call. Merge and apply authority stay with the company.

Add the date of the last statistics refresh and the next scheduled one. A plan comparison collected against stale statistics can flatter an index that will not be chosen in production.

Frequently asked questions

Sources

1. NIST Secure Software Development Framework

   Change control and environment protection practices for schema work.

2. NIST Cybersecurity Framework 2.0

   Framing for availability risk during planned database changes.

3. OpenTelemetry documentation

   Instrumentation for measuring query duration and write latency.

4. Google Technical Writing

   Structure for a reviewable evidence packet.

5. GitHub pull request reviews

   Review flow for the migration pull request.