For whom and when this change is needed
The administrator of the location system faces this decision when the measurement of the heartbeat cost shows that queries read thousands of rows per request. An example measurement: about 3,700 rows read per heartbeat dropped to about 2,000 after adding an index on the pair of transmitter identifiers. Similarly, the employee location history gained from a composite index on employee and time.
A schema change on a live system is feasible but requires a plan. The location system receives heartbeats without a break, so every table lock means lost pulses or errors on the device side.
Stage 1: measurement and justification of the change
Before you touch the schema, measure the cost of the query in rows read and record the result. Without a starting value you cannot demonstrate the effect, and an auditor or manager will not understand why production was altered. Identify the query that causes the full scan, and check whether the existing index starts with the right column.
Record the proposed index definition: the table name, the list of columns in order and the justification for that order. The order of columns in a composite index decides whether the database will use it.
Rows read per heartbeat before and after the index
Stage 2: preparation and a trial on a copy
Take a backup and verify that it can be restored. Make a trial of creating the index on a copy of a table of similar size to estimate the time and the load. The result of the trial is the basis for choosing the window: the longer the trial takes, the more important it is to choose a period with lower traffic.
In the plan, record the rollback criterion: after exceeding what time or error level the change is stopped, and who makes that decision.
Stage 3: execution on the live system
Create the index in a mode that does not block writes, if the database engine in use allows it. For a table of several dozen million records in the described case, the operation took a few minutes. During the operation, watch two indicators: the number of 5xx errors on the API side and the latency of accepting heartbeats.
Monitoring of errors should be continuous, not only after the operation ends. If the error rate rises, abort the operation according to the criterion in the plan.
Execution window
Choose a period with lower traffic, for example the break between shifts. The heartbeat generates from half to over 90% of API traffic, so even a single minute of increased latency affects many devices at once.
Stage 4: verification of the effect
After completion, repeat the measurement of the cost in rows read and compare it with the starting value. Also check the query plan to make sure the database uses the new index. Record the result: it is evidence for an audit and a justification for further changes.
While doing this, check the level of the diagnostic log: an entry written on every heartbeat at warning level produced about 250 thousand lines per day and was lowered to debug.
Pitfalls we have seen
The most common pitfall is an index created with the wrong column order: the database ignores it and the cost of the query stays unchanged. The second is creating the index at the peak hour, which increases the latency of accepting pulses. The third is the lack of a rollback plan.
The last pitfall concerns hosts: the schema change must be consistent across all hosts that share the same backend. Inconsistent host versions after deployment mean that one host uses the new index and another does not.
- Wrong column order in a composite index.
- Execution during the peak of heartbeat traffic.
- No criterion and procedure for rollback.
- Inconsistent host versions after deployment.
Checklist
Video
Frequently asked questions
Does adding an index to a live table always block the system?
No. In the described case, an index on a table of several dozen million records was created within a few minutes, without a lock and without 5xx errors. This requires the right mode and window.
How do you know the index worked?
From the repeated measurement of the query cost in rows read, and from the query plan, which shows the use of the new index.
What should you do if 5xx errors rise during the operation?
Stop the operation according to the rollback criterion set in advance, and return to it in a window with lower traffic.
Are you planning a database schema change?
We will go through the change plan, the execution window and the rollback criteria with your IT team, and show how to measure the effect.
Let's talk