Schema Evolution and Backward Compatibility

Marlow Books is an online bookshop run by four people that exists only in this course, and at twenty past two on the morning of Sunday 4 October it emailed a two person poetry press to say the shop owed it $128,400 for the year. The true figure was $1,284.00.

Twenty six days earlier the founder had done something every engineer reading this would have applauded. Lesson 028 found a penny bug in the shop's own JSON: a book at $8.20 goes out as the number 8.2, a client multiplies by a hundred and truncates, and the customer is charged 819 pennies for an 820 penny book. That lesson's advice was to hold money as a string or an integer count of the smallest unit. So on Tuesday 8 September, at twenty past eleven at night, the founder held money as an integer count of the smallest unit.

ALTER TABLE books
  ALTER COLUMN price TYPE integer USING (price * 100)::integer;

ALTER TABLE order_lines
  ALTER COLUMN unit_price TYPE integer USING (unit_price * 100)::integer;

Twenty past eleven because lesson 003 established that every Marlow deploy takes the site down for about ninety seconds, and because rewriting a 240 megabyte table and every index on it is not a lunchtime job. He changed the checkout, the price import, the admin form, the page template and the JSON response in the same release, and flushed Redis afterwards, which lesson 009 priced as free at a quiet hour and unsurvivable in Christmas week.

For twenty six days the shop was perfect. Prices rendered, orders took, pennies were pennies everywhere a customer could see.

Then the publisher payout job woke up.

You have met this job before, and most of what the shop knows about safe background work came from fixing it. It pulls every order line for the year, groups by publisher, renders a CSV and emails it. Lesson 011 moved its read onto the replica. Lesson 018 gave it a job_runs row and a watcher that fires at 06:00 if no run has finished. Lesson 019 split it so each publisher's CSV lands in object storage under a key of (publisher, month) and a sent_at is stamped before the send. It runs at two in the morning on the first Sunday, in the same slot lesson 005 once caught it running in twice over.

On Sunday 4 October it ran clean. It started, it finished, it wrote one file per publisher, it stamped each row once, it sent each email once. The watcher had nothing to say at 06:00. Every guard this shop has built for that job did exactly what it was built to do, and every number in every CSV was a hundred times too large.

The job sums unit_price. Those are pennies now. The script formats the total to two decimal places, and an integer formats to two decimal places perfectly happily, so 128400 came out as 128400.00 and nothing anywhere raised anything.

It had last run on Sunday 6 September, two days before the migration, and nobody had opened it since the year it was written.

The first reply came at 09:14 from the poetry press, who were pleased. The second, at 09:40, was from a publisher who had already sent the statement on to their bookkeeper. No money moved, because the job states what is owed and a human pays it. The cost was a Sunday of apologies.

That migration was reviewed. The review covered every file the migration touched, which is precisely the set of files that were not the problem.

A schema is a contract with a reader who is not in the room

A schema is the agreed shape of a piece of data: which fields exist, what type each one holds, which are required, and what each one means. It can live in a CREATE TABLE, in an Avro or Protobuf definition, in a JSON Schema file, or nowhere at all.

Lesson 016 gave it the sentence everything here stands on: the schema is the only code every writer runs. The other half is true too. It is the only contract every reader has already signed, including the readers that are asleep.

Two words sort the rest, and they are easiest to keep straight if you say them from the new code's point of view.

A change is backward compatible when the new code can read data the old code wrote. It reaches backwards into your own history and copes.

A change is forward compatible when the old code can read data the new code writes. The old code copes with a future nobody told it about.

Most changes are one and not the other, which is the whole reason this is hard. Adding a field is backward compatible without effort and forward compatible only if your old readers ignore what they do not recognise, which lesson 028 established is a property of their code rather than yours. Removing a field is backward compatible for the same easy reason, since the new code never looks at it again, and forward compatible only if the old readers can do without it. The two commonest changes land on the same side of that pair.

Now sort the changes you can make to a stored column by what each costs the engine and who finds out.

In prose, because the spoken version skips the table. Adding a column is free: Postgres records it in the catalogue and never goes near the table's pages, and since version 11 that holds even with a constant default, which before 11 rewrote every row. Widening a type rewrites the whole table. Renaming a column is a catalogue edit that finishes before you let go of the return key. And changing what a column means while leaving its name alone is free or a rewrite depending on the type, and nobody finds out at all.

Read the last two columns together, because nothing links them.

What a migration costs and who it breaks are two independent columns, and only one of them turns up in a plan. A rename is free and breaks every reader in the building at once. A reinterpretation is free or rewrites the whole table depending on the type, and breaks nobody. Cost is the signal almost every migration gets reviewed on, and it says nothing whatever about the column that hurts.

Marlow's September is that in one night. The type change was a rewrite, so it was treated as a risky migration: a late slot, a careful release, a flush afterwards. Every bit of that care went into the ninety seconds of downtime, because the downtime was the only risk with a number attached to it.

Lesson 028's table ranks these changes too, and ranks them differently: there, renaming a field is a flat no, because it breaks every caller at the same moment. Both rankings are right, and what separates them is who pays for a loud failure. At a service boundary your callers are software you cannot read, cannot upgrade and sometimes cannot drive to, so a loud break is your outage happening inside somebody else's program. Inside a store your own building writes to, a loud break is a stack trace with one of your file names in it.

So the ranking flips when you cross your own front door. Outside it, prefer the change nobody notices. Inside it, prefer the change everybody notices, and then do the work of finding out who they all are.

Run Marlow's September through that. Out of everything available the founder picked the one row nobody finds out about: same column, same name, new meaning. Suppose instead he had added price_pennies and unit_price_pennies, backfilled them and dropped the originals. That is more work and about the same bytes on disk, since either way every row gets written once, so nothing in it was cheaper. At 02:00:03 on Sunday 4 October the payout job would have died on column "unit_price" does not exist, written no CSV, stamped no sent_at and sent no email, because lesson 019 put the send last. At 06:00 lesson 018's watcher would have found no finished run and buzzed a phone. The founder would have fixed a short script over breakfast and nobody outside the shop would ever have known.

Same data, same month, more effort. One version produces a crash at three seconds past two in the morning. The other produces a confident statement for every publisher the shop owes money to.

Which side you deploy first

Which brings the real artefact into view.

A compatibility property sounds like a fact about a change. It is not. It is a claim about which side you may deploy first, and it resolves into four cases.

Backward but not forward, which is the usual one: new readers understand both shapes, old readers choke on the new one. Deploy every reader first, wait, then the writer. Forward but not backward: the writer first, wait, then the readers. Both: any order, go home early. Neither: there is no order that works, and that is what the next section exists for.

The wait in the middle is where the thinking is. It is however long the old shape can still turn up in front of a reader, and that number is a property of where your data sits rather than of your code.

A queue holds the old shape until it drains. Lesson 018's Friday of Christmas week is this case at its purest: a field went into the confirmation payload in producer and consumer together, forty messages written by the previous version were still on the queue because the email provider had been sluggish, and the new consumer raised a KeyError on the first of them and took eleven minutes of the shop's evening with it. Lesson 018's verdict is the one to keep: a deploy that is safe because the queue is empty is a deploy that is unsafe on your worst day. Lesson 038 found the shop making the identical change in April and getting away with it, because four workers leave the queue empty on an ordinary Tuesday. Same code, same team, opposite outcome, and the variables were the week of the year and the email provider's mood. A compatibility window you cannot measure is one your worst dependency sets for you.

A log holds the old shape for its retention, whatever anybody reads. Galewatch, the telemetry service in this course that collects a reading every two seconds from each of its customers' wind turbines, keeps seven days on the stream lesson 037 built. Lesson 037 called retention how long you are allowed to be wrong for. Pointed at schemas it says something kinder: seven days is how far back your new code has to be able to read, and it is the only compatibility window here that is written down in a file somebody can open.

Then the third venue, where it stops being seven days. Lesson 051 spent a section on reprocessing: re-running a computation over a window you have already computed, because the inputs changed underneath you. Replay a topic from offset zero and today's code is a reader of every schema that topic has ever carried. A year of records written under three schemas is three readers' worth of work in one deployment, and two of those shapes are ones nobody has looked at in months.

There is a tool for this, and its default does not cover the case. A schema registry is a service that holds the schemas for a set of streams and can refuse to register a new version; Confluent's defaults to BACKWARD, which checks a new schema against the previous version only. The transitive form, BACKWARD_TRANSITIVE, checks it against all of them. The gap between those two words stays invisible for years, because a stream that always reads near the end never meets a schema older than the last one. It becomes visible the first morning somebody rewinds, which is the morning they can least afford it.

And a fourth venue, the one that caught Marlow, where the window has nothing to do with the data. A reader that is asleep carries the old shape in its code rather than in your store. The payout job's compatibility window is not the age of an order line. It is twenty eight days, or thirty five when the first Sundays fall that way, because that is how often it wakes up, and a reader that runs twelve times a year is a reader you cannot smoke out by waiting.

Lesson 050 listed the shared causes that stop a second region buying you what you hoped: the deploy that goes to both, the schema migration that goes to both, the DNS record in front of both, and the control plane. The migration is the one on that list that looks impossible to stage, since both regions read the same shapes out of the same tables. It is stageable, with one extra rule: every step has to be correct against its own neighbour. Ship two steps in one window and you have put a region on step four beside a region on step two, which is a pair nobody designed and nobody tested. One step per window, and the window is the longer of your two deploy lags.

Expand and contract, and the five deploys nobody finishes

Lesson 028 named the move and promised it to today: expand and contract, sometimes parallel change. Widen the world so both shapes are legal, move everybody across, then narrow it again. 028 did it to a field in a JSON response, where the wait was ten minutes of edge expiry. Here it is on stored data, where the wait runs to weeks.

Marlow's pennies, done properly, is five deploys.

Add the new column. ALTER TABLE books ADD COLUMN price_pennies integer; Catalogue only, instant, invisible to everything in the shop.

Backfill it. The expensive step, and lesson 043 priced its shape already: in Postgres an update is an insert, so writing all 1.2 million rows writes 1.2 million new row versions. The table goes from 240 megabytes to about 480 until autovacuum catches up, and the write ahead log carries most of that again, so call it 480 megabytes moved for the four point eight megabytes of actual news that 1.2 million four byte integers weigh. A hundred to one counting everything the engine writes, or fifty on the log alone, which is how lesson 043 got seventeen hundred and fifty to one on the 06:00 import, because for once every page it dirties is a page it needed. Do it in batches of ten thousand with a pause, so nothing holds one transaction open across the whole table.

Make both columns true at once, in the table. Not in the application.

ALTER TABLE books
  ADD CONSTRAINT price_agrees
  CHECK (price_pennies IS NOT NULL
         AND price_pennies = (price * 100)::integer) NOT VALID;

ALTER TABLE books VALIDATE CONSTRAINT price_agrees;

NOT VALID applies the constraint to new writes and skips the scan of what is already there; the VALIDATE afterwards does that scan under a lock that blocks neither readers nor writers. The IS NOT NULL is load bearing, because a CHECK passes on NULL: without it an INSERT naming only price slips straight through, and an insert is exactly the writer that never mentions the new column. Now lesson 016's sentence is doing work rather than sitting in a recap. A dual write written into the application holds for the paths you remembered. A dual write written into the table holds for the founder's own psql session at eleven at night, which lesson 009 counted as the fifth write path precisely because no code review reaches it. The writer you forgot announces itself by name, in your own terminal, before it does any damage.

Move the readers, then find the ones you do not know about, which is the step with no clean answer. Lesson 028's rule was that traffic tells you how much and never who. A database gets closer than an API can, because every reader has to name the column in a statement and Postgres keeps the normalised text of those statements in pg_stat_statements, which you can grep for unit_price. Two things eat the technique. The view holds a bounded number of statements and empties on restart, so a month of history is really a claim about a month of uptime. And SELECT * names no columns at all, so a reader written in a hurry is invisible to the one tool you have. SELECT * is the reason you cannot tell who reads a column. A monthly job appears in that view at most once a month, so a view reset last Tuesday has never seen it.

Contract. Drop the constraint, drop the old column. Nobody does this. Not doing it costs you two columns that must agree forever, a check evaluated on every write to the busiest table in the shop, and a schema that lies to the next person about which column is real. Doing it costs you a bare DROP COLUMN, which is instant, irreversible, and breaks loudly, which by this lesson's own ranking is the good kind of break and is still a break. You earn the right to run it by having done the four steps before it, and not otherwise.

Five deploys and six weeks of calendar, for a change a migration tool writes in one line. Almost nobody reaches step five, and the archaeology of any codebase over five years old is mostly abandoned expand and contracts: a _v2 column, a legacy_ prefix, a field that has been nullable since 2019 and has never once been null. I have left two of those behind me. Both are still there.

Galewatch has the other half of the problem, and it is the half that looks free.

Lesson 034 added received_at to a table holding a year of readings, eight bytes a row, 428 megabytes a day across the fleet. On disk that is the cheapest row in the table above: a catalogue edit against a year of them on nineteen machines, finished instantly on each. The question nobody asks is what the new column means on a row written in May.

It means NULL, and NULL now carries two different facts in one value. It is "we measured and there was nothing" and it is "the code that wrote this row had never heard of the question", and no query can tell them apart. Lesson 051 took the ninety ninth percentile of received_at minus recorded_at at one farm over the week of 1 August and got 20,952 seconds. That number is sound because the column arrived in June and the window sits after it. Widen the window back to March and it answers from the June rows alone, because NULL minus anything is NULL, a quantile skips them without comment, and nothing on the screen says how many it skipped. Ask only about March and you get NULL, which is the one honest output here and the easiest to mistake for a measured zero.

The temptation is to backfill a default and make the gap go away. Resist it, and the reason is one line: a received_at backfilled to equal recorded_at asserts zero transport time for a year of history, and lesson 051's watermark is a quantile of exactly that subtraction. The fiction would not sit quietly in a corner. It would come back as the product's freshness number.

When you add a column, the value you backfill is a claim about history, and for a fact you were not recording, NULL is usually the only honest one going. Then put the date the column arrived somewhere a query can reach, because every aggregate over it has a cutoff and nobody will remember it in a year.

Where the rule gets teeth

Back to that registry, because the mechanism is worth a paragraph. A producer registers a schema and gets an id, writes the id into each record, and the consumer fetches the schema by id rather than guessing from the bytes. That is plumbing. What matters is the refusal, which moves the check out of a code review and into the write, and is the same move as putting that CHECK constraint in Marlow's table, one layer up. A compatibility rule you cannot enforce is a style guide.

Avro's version of the rule is worth carrying, because it is a decision that pays years later: a field you gave a default to can be added or removed in either direction, and a field with no default can be added or removed in one direction only: add it and only the old readers cope, remove it and only the new ones do. The default you type today is the permission slip for deleting that field in three years.

Then the limit.

A registry checks the shape. It has nothing whatever to say about the meaning. Marlow's migration would have passed every compatibility check in this section without a murmur, because price: integer to price: integer is not a change at all: same type, same name, and the only thing that moved was a factor of a hundred that exists in no definition file anywhere. Lesson 028 said shape breaks loudly and meaning breaks silently, and called that the wrong way round. Every tool here is a machine for the loud half. A compatibility check cannot catch a unit change, because a unit is not part of the shape, and units are where the money is.

Which leaves lesson 016's question, handed here explicitly: what about a store with no schema at all. 016's answer was that there is always a schema, and the only question is whether it sits in a table definition or in the application, where it is really one schema per version of the code that ever wrote a document, all of them still in the store. Here is the second half. What the table buys is not safety, it is knowability: the set of shapes in your store is a file in your repository with a date on it. With documents the set of shapes is whatever your code has ever been, there is no file, and the only way to find out is to read all of it. So the if in the reader that copes with the old shape is never deleted, because the only evidence that deleting it is safe is a scan nobody will schedule.

Marlow owns one of these and has never called it one. Lesson 009's book: record in Redis is a small JSON document holding a title, a price and a stock count, written by three code paths, read by two, with its shape defined nowhere. On the night of the migration those records held dollars and the new code was about to read them as pennies. The founder flushed it, which is the one advantage a cache has over a database in this entire lesson: you are allowed to delete all of it, and no table will ever offer you that.

Stagefront, the ticketing service in this course where two hundred thousand people press the same button at ten in the morning, sits at the other end of that. A seat row cannot be flushed and cannot be rebuilt from anywhere. There is no quick version of expand and contract there, and no survivable version of skipping it.

So: almost every team writes the migration and almost none write down the deploy order it requires, and the order is the artefact. The migration file is the part a tool generates. The order is the part only a person knows, and it lives in that person's head for exactly as long as they work there. Four lines in the pull request hold it: what reads this, what writes this, which side goes first, how long the wait is. I have seen that written down twice. I have never regretted writing it.

Recap

A schema is the only code every writer runs and the only contract every reader has already signed. Lesson 016 published the first half. The second half is the one that bites, because some of those readers are asleep.

Backward compatible means the new code can read the old data; forward compatible means the old code can read the new data. Say them from the new code's point of view and they stop sliding around. Most changes are one and not the other.

What a migration costs and who it breaks are two independent columns. A rename is free and breaks every reader at once; a reinterpretation breaks nobody at any price. Cost is the signal a migration gets reviewed on, and Marlow's bought a careful window for the ninety seconds of downtime, which was the only risk with a number on it.

Inside your own store, prefer the change everybody notices. Lesson 028 ranks it the other way because its callers cannot be upgraded, so a loud break lands inside somebody else's program. Marlow's quietest available change bought a statement per publisher with every number a hundred times too large; a rename would have bought one crashed job at three seconds past two in the morning.

A compatibility property is a claim about which side you may deploy first. Backward and not forward means readers first. Forward and not backward means the writer first. Neither means there is no order at all, which is what expand and contract is for.

The wait between the two deploys is set by where the old shape lives. A queue holds it until it drains, which your worst dependency decides. A log holds it for its retention, the only one of these numbers written down in a file. A replay from offset zero holds it forever, and a registry's default BACKWARD check does not cover that. A sleeping job holds it in its own code for the length of its schedule.

Expand and contract is five deploys: add, backfill, agree, move the readers, drop. Put the agreement in the table rather than in the application and the writer you forgot announces itself by name. Finding the readers is the step with no clean answer, and SELECT * is why you cannot tell who reads a column.

When you add a column, the value you backfill is a claim about history. NULL is usually the only honest one, and it quietly means two things: nothing was there, and nobody was asking yet. A plausible default would have put a fiction straight into lesson 051's watermark.

A compatibility check cannot catch a unit change, because a unit is not part of the shape. Every tool here checks the shape, and the meaning is where the money is.

Check your understanding

1. Marlow's founder, having had that Sunday, wants a rule that any column holding money must end in _pennies. Say what that rule would have prevented in September, what it does not prevent, and what you would put beside it to cover the gap.

2. Lesson 028 had you write the deploy order for a renamed field in a cached JSON response, where an edge expiry set the wait. Do it now for a required field added to a message on a queue, with the consumer owned by another team who deploy on Thursdays: give the order, say what you would measure to choose the wait, and say what changes when the queue is a log with fourteen days of retention.

3. A colleague proposes backfilling Galewatch's received_at for the year of rows written before the column existed, setting it equal to recorded_at so that no query has to special case NULL. Make the strongest case for them, then say which earlier lesson's number the change corrupts and what you would ship instead.

4. A topic has run on the registry's default BACKWARD setting for three years and twelve schema versions, and somebody needs to reprocess it from offset zero next week. Say what you would check first, what you expect to find, and what the options are once you have found it.

5. You have run the first four steps of an expand and contract, and the old column has had no reader in pg_stat_statements for six weeks. Say what would still stop you dropping it, how you would close that gap, and what you would do if closing it is not possible.

6. Stagefront wants a seat_class column on its seat table, written from the next release, with the previous release still serving in the other region for a day. Say what makes that safe or unsafe, which of lesson 050's shared causes it touches, and what you would want in place before the next on sale.

Next lesson

053 Secrets, Keys and Rotation. Rotating a credential is today's procedure with a deadline attached: two keys valid at once, every reader taught before the writer moves, and a contract step somebody actually has to do.