The real legacy is your database schema

Conversations about legacy systems are almost always about code. Which language, which framework, which version. That is the layer where it hurts, and it is the layer that is easiest to replace.

The layer that stays is a different one. A team can rebuild the entire application in two years, change the language and swap the framework. The database schema survives all of that, because everything that is not in the repository hangs off it: reports, exports, an interface to a partner, a script in the finance department that has run on the first of every month since 2017.

This article is about how to judge a schema that has grown over time, which legacy traits are worth treating and which you carry along, and in what order to proceed without stopping the business.

Why the schema outlives the code

Code has exactly one user: the runtime. A schema has any number of them, and you do not know most of them.

That is not a metaphor. In every grown system I have seen there was at least one access to the database that did not go through the application. A reporting tool with its own read-only account. A second system that reads a table overnight. A spreadsheet where somebody set up a connection years ago. A script on an employee's laptop.

Each of those accesses is a contract nobody signed and nobody can cancel, because nobody knows it exists. As long as you only change code, those users notice nothing. The moment you rename a column, they all notice at once, and you hear about it from the finance department.

From that follows the first rule, and it is inconvenient: a schema is treated differently from code. In code, renaming is cheap, because the compiler or a search finds every place. In a schema, the search only finds the places that are in the repository.

The four legacy traits that are almost always there

Grown schemas differ less than you would think. Four patterns show up in nearly all of them, and they cost very different amounts.

One: no foreign keys. The relationship exists, but only in people's heads and in the code. You notice the consequence at the first migration: there are order lines without orders, addresses without customers, payments against deleted invoices. Not many, but enough that every conversion needs special handling.

-- How many orphans are there really? Measure once before every migration.
SELECT COUNT(*) AS order_lines_without_order
FROM order_line l
LEFT JOIN orders o ON o.id = l.order_id
WHERE o.id IS NULL;

Two: columns that mean two things. A status with values 0 to 9, of which 7 and 8 mean the same thing, because somebody once needed a new case. Or a text field note that has also carried the return number since 2019, recognizable by a prefix. Columns like that are the reason nobody can rebuild the report.

Three: everything is text. Amounts as VARCHAR, dates as CHAR(10), booleans as 'Y' and 'N', occasionally as 'yes'. That is the most expensive trait, because it cannot be fixed locally: every place that reads the value has its own conversion, and they do not all agree.

Four: nothing is ever deleted. A deleted column that half the queries respect and the other half do not. That is the trait that quietly produces wrong numbers: two reports about the same thing return different results, and both are right from where they stand.

What you measure before changing anything

Before a decision is made there have to be numbers. Three queries provide the basis, and together they take half an hour.

What is how big, and where does the history sit? That decides whether a conversion fits into a maintenance window or has to run in batches.

SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024) AS mb,
       table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_length + index_length DESC
LIMIT 20;

Which tables are still written to at all? In a ten-year-old schema, twenty per cent of the tables are regularly dead. Those do not need modernizing, they need archiving.

SELECT table_name, update_time, create_time
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_type = 'BASE TABLE'
ORDER BY update_time IS NULL, update_time ASC;

Depending on the storage engine the timestamp is imprecise, and it is still enough: a table last touched in 2021 is a candidate, and the rest is settled in conversation.

Who connects, and from where? That is the most important of the three questions and the only one that is not in the schema. The database user list is the start:

SELECT user, host FROM mysql.user ORDER BY user;

Every user that is not the application is one of the unwritten contracts above. For each one, note who owns it and what it reads. Where the slow query log is running, the answer is already there.

The rule: extend, never rename

When the users of a schema are unknown, you can still change it. Just not in one step. The pattern is called expand and contract, and it amounts to old and new existing side by side for a while.

For a column whose meaning changes, it looks like this:

1. Expand. The new column appears, properly typed. Nothing reads it.
2. Write to both. From now on the code writes to both columns. Reading still happens from the old one. From here on, everything arriving is present in both forms.
3. Backfill. The existing rows are converted in batches, outside the deployment, with a way to stop. Then: count whether both columns say the same thing.
4. Switch. Reading moves to the new column, writing continues to both. This is the point at which an unknown user who needs the old column shows up, and it happens before that column disappears.
5. Contract. Only after a period in which nothing turned up does writing to the old column stop, and only after that is it dropped.

The step everybody wants to shorten is the wait between 4 and 5. It has to be longer than the longest reporting period in the building. If somebody runs an analysis once a quarter, two weeks is too short, and you find out in the quarter after.

Between steps 3 and 4 there is a check worth doing, because it finds the one case that gets expensive later:

-- Where do the old and the new column disagree?
SELECT id, status_old, status_new
FROM orders
WHERE status_new IS DISTINCT FROM derived(status_old)
LIMIT 50;

How such an extension goes live without losing data on the way is covered in Dual write and backfill.

The users you do not know about

Since those users are the actual reason for all this effort, it pays to go looking for them rather than waiting. Three routes work.

The connection log. Record for one week which users connect from which addresses. That is the most complete list you can get, and it regularly contains addresses nobody can place.

The column nobody reads. Conversely, before dropping a column you can check whether it is still queried at all. Where the query log is running, a search over a week of it is enough; otherwise the intermediate step helps: set the column to NULL for two weeks instead of dropping it. Complaints then arrive with names attached.

The view as a bridge. When an unknown user reads a table that is meant to disappear, a view can take its place, serving the same columns out of the new structure. That costs work once and takes the time pressure out of the conversion, because the foreign access keeps working.

Those views are explicitly meant as a transition and need an end date attached, or in five years they are the next legacy trait.

When you leave the schema alone

Not every legacy trait is worth treating, and a modernization that starts at the schema and ends there has helped nobody. Three cases I leave standing.

The table is dead. It is read, not written, and only by one report. Then it does not need cleaning up, it needs archiving, and the question stops being technical and becomes one of retention.

The trait costs nothing. A boolean as 'Y' and 'N' is ugly. If it is read in three places and all three use the same conversion, it is a blemish, not a risk. Where the same value is interpreted differently in twenty places, it is a bug waiting for its day. The difference is not in the schema, it is in the number of readers.

The system has an end date. If the platform is being replaced in eighteen months, every schema change is money in a product that is on its way out. When that is the right conclusion is covered in its own article.

A version jump of the database adds legacy of its own: MySQL 5.7 to 8.0.

The order that has proved itself

Finally, the sequence I follow when a schema is seriously in the way.

1. Count, do not guess. Size, last writes, user list. Half an hour, and after that everybody is talking about the same numbers.
2. Establish integrity before changing structure. Find the orphans, decide what happens to them, then add the foreign keys that are missing. It is unspectacular and it prevents most later surprises.
3. Take on the one column that causes the most confusion. Not all of them, one. With the full five-step route, waiting period included. That is the run on which the team learns the procedure.
4. Only then talk about moving. A different database version, a managed service, a split: those are projects of their own, and they get considerably easier once integrity is in place.

The item most often missing from that list is the second. It has no visible benefit, it changes no behaviour, and it is the reason the steps after it become plannable. How a step-by-step modernization works overall is covered on its own page.

This article belongs to a series about systems that already exist. The retrospective orders every article in it by situation.