Coexisting Schemas and Safe Contraction
In one line
When you change a schema, do not look only at whether the new code succeeds; put into the compatibility contract which columns the old code that is still alive reads and writes.
Why this was needed
You are fixing the name badge system of the alien festival. The customer says the name name is ambiguous and asks you to change it to display_name. It seems that renaming the column in one go would finish the job, but old programs remain on the tablets at the venue. While the new program is being deployed, the batch printer and jobs that are being retried also read the old column. Even though no data has disappeared, badge printing can stop with an error that the column cannot be found.
This problem also arises in a Kubernetes rolling update. A new Pod becoming Ready does not mean that all the old consumers have terminated. Even if the screen connects, waiting jobs, other services, analytics queries, and the operator's tools are separate. If you judge the success of a DB change only by the normal response of a single new Pod, the verification scope is too narrow.
This lesson is a change that moves a value of literally the same name. It does not cover lossy conversions that split or translate a name. If the values on the two sides diverge, it does not automatically decide which side to discard. It is also run only on a disposable lab DB and does not change the schema of the LabHub production DB.
How it works
1. Expanding means keeping the old contract
You leave the existing name NOT NULL as it is and add a nullable display_name. The new column of the existing rows holds NULL. If you fill this with an empty string or "unknown", it becomes hard to tell a real name from a not-yet-migrated state. PostgreSQL's ALTER TABLE needs a different lock for each kind of operation, and even adding a column can wait for a running read transaction. Check the explanation of each form in the ALTER TABLE official documentation.
Do not leave the lock wait unlimited; apply a short lock limit. If it fails, preserve the existing schema and clean up the open transaction. The guess that "it is a simple DDL, so it will finish quickly" breaks in front of a connection with a long SELECT open. The lab keeps a real old read connection alive to reproduce this wait. The limit value of 500ms is a contract of the small teaching DB and not a recommended value for every production environment.
2. There are three kinds of clients, not two
| Client | Expression it reads | After the old column is removed |
|---|---|---|
| Old version | name | Column-not-found error |
| Transition version | COALESCE(display_name, name) | Also a column-not-found error |
| Final version | display_name | Works with the new column alone |
The transition code is needed to read rows that have not been moved yet. But even if COALESCE does not select the old value at run time, the SQL still references a column called name. You cannot drop the old column leaving the transition code as it is just because the new column is all filled in. You need one more release that moves to the final version after the backfill and the constraint validation.
The final version can be tested even before the old column is removed. At that time the old version, the transition version, and the final version briefly coexist. Only when you check this coexistence interval with real SQL can you judge whether the later contraction satisfies both data preservation and client compatibility.
3. Connect both writes, but do not hide contradictions
If you only add the new column and then the old app changes name, display_name falls behind. This lab uses a BEFORE INSERT OR UPDATE trigger as a compatibility bridge. If the old version changes only name, it copies to the new column, and if the final version changes only display_name, it copies to the old column. Changing both to the same new value is allowed, but changing them to different values is rejected.
To know which field changed, you have to compare NEW and OLD including NULL. If you use only an ordinary equality comparison, the judgment can drop out at NULL. Read the NEW, OLD, and TG_OP and the row return rules in the PL/pgSQL trigger official documentation, and distinguish the case of automatically filling a field that was not given from the case of explicitly trying to clear it to NULL.
On an INSERT, if only one field is present, it fills the other. On an UPDATE, an existing row whose value did not change but whose new column is still NULL can have the old value filled into the new column. On the other hand, a write that clears an existing name to NULL is rejected. This rule is not a general rule for all data conversion but this customer's contract for keeping the two representations of the same name equivalent.
A trigger is temporary complexity. If it stays for a long time, it becomes hard to know which code is the real source, and the extra writes may not be visible from the application logs alone. You design the conditions for removing it and the verification together from the time you install it. Do not think that the trigger does user authentication or per-customer permissions for you.
4. A backfill is based on the current row, not on a value read in the past
The backfill targets are the rows with display_name IS NULL for the explicitly given IDs. The name on the right side of the UPDATE is also read as the current row inside the DB. If the app first fetches the old name with a SELECT and later does the UPDATE, it can overwrite a name that another owner changed in between with the past value.
In the lab, you open the old app's name-change transaction to hold the lock and start the backfill in a separate process. After observing that the backfill is actually waiting, you commit the old app. A correct backfill re-evaluates the current NULL condition after the lock is released and does not touch rows that have already been moved. Read the Read Committed official explanation together with the time order of this row. A wrong answer that unconditionally writes the old value fetched earlier is also checked in the same situation.
5. Separate protecting new writes from verifying all the past
CHECK(display_name IS NOT NULL) NOT VALID applies the condition to new writes without immediately checking all the existing not-yet-migrated rows. You must prepare the compatibility trigger first so that even new inserts from the old app satisfy this rule. After the backfill ends, you check the existing rows too with VALIDATE CONSTRAINT, and finally strengthen the column itself to NOT NULL.
You must not read NOT VALID as "nothing has been checked yet". Conversely, you must not believe that the validation of existing data is finished just because a constraint name exists. In the lab, you check whether the full validation fails before the backfill, and whether convalidated and attnotnull actually change after the backfill. You do not look only at whether the word VALIDATE is in the statement.
What it looks like in the field
If you remove the compatibility feature first and a later DDL fails
If removing the trigger, removing the function, and removing the old column are separate commits, then after a failure midway, two names remain but the feature that connects writes to both can be gone. This contraction is one transaction. If an exception or a client termination in the middle happens before the commit, even the compatibility bridge must be restored. If only the response was lost after the commit, you have to observe the real schema with a new connection.
Check which section psycopg's transaction context commits. If there is already an outer transaction, the exit of the inner context may not be a real commit. This lesson makes it a contract to start each migration function on an IDLE connection with autocommit=True. Look at the transaction management documentation and the position of the hooks together.
A "retirement approval of True" does not prove that no real consumers remain
The two approval values in the lab are inputs saying that the retirement of the old-version and transition-version consumers was confirmed externally. To prove that fact in production, you would have to investigate the running versions, scheduled jobs, retry queues, and even the tools that read the DB directly. A simple flag or the observation that there were no errors for a few days cannot prove that no consumers remain.
What an FDE needs to do is to confirm this consumer map with the customer and agree on when to stop or roll back. It is an author-designed case tied to the skills of understanding customer problems and implementing solutions in the Palantir FDE job posting we checked, and it does not mean that the company requires this trigger pattern or guarantees hiring.
What you will do in the next lab
Preserving the badge's name and ID, you move through adding a nullable column, a transition read, two-way writes, a backfill limited by ID, constraint validation, an approved contraction, and the final client. If the values of two wrongly migrated columns differ, it rejects the contraction even if the counts are the same. You check a real DDL lock wait, a concurrent backfill, and process termination at three points, and see for yourself why the old SQL and the transition SQL fail after the contraction.
This lab does not prove server power failure durability or large-scale online migration performance. The goal is to verify the compatibility relationship of the client, the schema, and the transaction on a prepared small DB.