There Is One Threshold Between Reading and Writing

In one line

An investigation can be wrong and simply redone, but an UPDATE cannot be redone, so four things that an investigation never needed come before any write — scoping the change, a way back, a dry run, and abort criteria.

Why this was needed

Most of what an FDE does in a customer environment is reading. You read logs, query tables, follow code, and move the results into a document. In this part of the work, a wrong judgment costs time. If a hypothesis turns out wrong, you delete it and build a new one, and a miscounted number is simply counted again.

Then, at some point, this sentence always arrives: "Could you fix that for us?"

From here the nature of the work changes completely. A wrong query can be run again, but a wrong UPDATE cannot be run again. If you did not build a way to undo it beforehand, then what we did at that moment was not an investigation but an incident.

And there is one more problem. A request arrives as a sentence, not as a condition. The moment you translate the sentence "Please clean up the payments that have been stuck for a long time" into WHERE status='pending', the scope the requester pictured in their head and the scope that actually changes drift apart. The width of that gap is the incident, and until you count, neither the person who asked nor the person who received the request knows how wide it is.

How it works

The four devices that come before a write all do one of two things: they narrow this gap, or they prepare for the case where the gap could not be narrowed.

One, scoping. Put two numbers side by side — the number of rows that change when you translate the request sentence literally, and the number of rows that change when you narrow the condition. The difference between the two numbers is the risk of this change. With 45 rows and 20 rows, the risk is 25 rows, and the condition is complete only when you can write one line for each of those 25 rows explaining why it is excluded.

Two, a way back. You keep two kinds of backup, and they serve different purposes.

파일 사본   전부 잘못됐을 때 통째로 되돌린다.
            단점 — 그 사이에 들어온 남의 변경까지 함께 되돌아간다.
행 스냅샷   바꿀 행의 변경 전 값을 id 와 함께 남긴다.
            id 를 열쇠로 그 행만 되돌릴 수 있고, 무엇이 어떻게 바뀌었는지 설명할 수 있다.

With only a file copy, you can roll back but you cannot explain, and with only a row snapshot, you cannot roll back a change that alters the schema. So you keep both.

Three, a dry run. Open a transaction, run the actual UPDATE, and then roll back instead of committing. The result looks the same as counting in advance with SELECT COUNT(*), but the nature is different, because the condition you count with and the condition you change with are literally the same. If you write two separate statements, a typo hides between them, and the typo is always in whichever of the two statements is actually executed.

Four, abort criteria. Decide before you run what result will make you stop. This is covered separately in the next module.

And verification after the change is finished has one more half. The control group — counting whether the things that should not have changed are still as they were. A verification that counts only what should have changed passes without a problem even in an incident where the condition was too broad and touched the neighbors too.

What it looks like in the field

First, do not change what you cannot verify. Real data almost always contains rows that satisfy the condition but whose owner cannot be determined, such as an order with a customer_id that is not in the customer table. If you cancel such a row along with the others just because it is old, you will not be able to explain that one row later. The right move is to exclude it, write the fact of the exclusion and the reason in the plan, and ask the customer. An exclusion is not less work done; it is a judgment recorded.

Second, a change that creates a value that did not exist before spreads silently. If you put cancelled, which never existed in status before, into the column, aggregate queries and dashboards that do not know that value will quietly leave those rows out of their counts. No error is raised, so nobody notices, and a month later it surfaces as settlement figures that do not add up. That is why a change that introduces a new value is both a data change and a change that has to be announced.

Third, the execution record is not for the person who ran the change but for another person who will find this table strange two hours later. If when, what, how many rows, and what to roll back with are not in one place, that person will spend their time tracking us down.

Splitting an irreversible change into reversible ones

The most important item in a change plan is "How do we roll back if it fails?", yet for some changes there is nothing to write in that box. In that case you do not fix the plan; you split the change itself.

Dropping a column is done in three steps. Doing it in one step breaks things while the old code is still running.

1. Deploy a version of the code that stops reading the column (writes continue).
2. Wait a few days, confirm there is no reason to roll back, and then deploy a version that stops writing it.
3. Only then, drop the column.

Each step can run alongside both the version before it and the version after it, so you can roll the deployment back at any time.

Adding a column works the same way. If you apply not null from the start, every insert from the old code fails. Add the column allowing nulls, fill in the default, make the code always supply a value, and only then add the constraint. On a large table, adding the constraint first as not valid and validating it later keeps the lock time short.

A long lock does not look like a failed deployment. If a migration holds a table lock for 5 minutes, every request piles up in the meantime. The deployment ends as a "success" and only the outage remains. That is why a migration must always carry a lock limit.

set local lock_timeout = '3s';
set local statement_timeout = '30s';
alter table orders add column region text;

It is better to fail and try again when the lock cannot be acquired than to wait until you get it and stop the service.

Actually perform the rollback. The "Rollback: deploy the previous version" line in a plan has usually never been verified. If you roll back once in the development environment, you discover that the configuration file does not match, that the new format is still in the cache, or that new messages have piled up in the queue.

The change window is for people. If you deploy at 3 a.m., nobody sees the problem. The safest time is when people are awake, traffic is low, and the next day is a working day.

What you will do in the next lab

You will walk through the whole situation in which the customer has asked you to "change all pending orders to cancelled". Translated literally, the request changes 45 rows, but only 20 rows should actually change. You will find the gap of 25 rows, build a way back, demonstrate the rollback on a copy, and then finish with the real apply and the control group verification.