A platform’s database is not a document that gets rewritten. It is a structure that is holding weight while people are standing on it, and the tables carrying player balances, round history and settled transactions are the load-bearing walls. Changing their shape is therefore a question about what the platform can promise in the minutes the change is running, not only about what the new shape should be.
That is why schema work on a regulated platform looks different from schema work on a website. The platform is expected to settle rounds, honour limits and answer questions about a past transaction while a column is being added, a constraint is being enforced and several million rows are being rewritten in place. The engineering discipline below is about doing that deliberately: knowing which statement form takes which lock, staging the change so no single step is both large and irreversible, and holding evidence that the data on the far side is the same data.
The lock is the change, not the statement
Every schema operation is really a locking decision with a syntax attached, and the platform’s availability is decided by the lock rather than by how long the statement takes to plan.
PostgreSQL states the default bluntly in its ALTER TABLE documentation: “An ACCESS EXCLUSIVE lock is acquired unless explicitly noted.” The same page adds the sentence that catches teams out, because it applies to a statement that looks like one change and behaves like several: “When multiple subcommands are given, the lock acquired will be the strictest one required by any subcommand.” Combining a cheap metadata change with an expensive one in a single statement does not average the cost; it inherits the worst lock in the list and holds it for the whole command.
The documentation also notes the cases where a lighter lock is enough. Adding a foreign key is one: ADD FOREIGN KEY “requires only a SHARE ROW EXCLUSIVE lock”, though it also takes one on the referenced table. Validating a constraint that was already added as NOT VALID is another, and it is the reason the staged approach below works at all.
So the first practical rule is a reading habit rather than a technique: before running anything, look up the lock level of the form being used, and split a statement that mixes a fast subcommand with a slow one.
Which edits rewrite the table and which only touch metadata
The second question is whether the engine can change the definition without rewriting the stored rows. MySQL’s online DDL documentation makes the distinction explicit and worth copying into the change plan, because the difference is measured in seconds versus hours on a large table.
Its documentation describes three ways an operation can behave. An instant operation “only modify[ies] metadata in the data dictionary”, with a possible brief exclusive metadata lock during execution and concurrent DML permitted. An in-place operation changes the table without a full copy but may still need to reorganise data. A copying operation rewrites the table.
| Change | How the engine classifies it | What it costs the platform |
|---|---|---|
| Adding a secondary index | In place, no table rebuild, concurrent DML permitted | Index build time, plus the write path it competes with |
Adding the first FULLTEXT index |
In place, but concurrent DML is not permitted | A write-blocking step, not a background one |
| Adding a primary key | In place, rebuilds the table; the manual calls the data reorganisation substantial and expensive | A high-volume rewrite with concurrent DML permitted |
| Dropping a primary key | Rebuilds the table and does not permit concurrent DML | A write outage for the duration |
| Changing an index type | Instant possible, metadata only | Practically free, so do it early and separately |
Two details in the same documentation are easy to miss and both matter to a change plan. The first is that a LOCK clause is an assertion, not a preference: if it asks for a less restrictive level “than is permitted for a particular DDL operation, the statement fails with an error”. That failure is the useful outcome, because it arrives before the change rather than during it. The second is about when an index is actually usable: a newly created secondary index “contains only the committed data in the table at the time the CREATE INDEX … statement finishes executing”, which is why an index belongs at the end of the sequence rather than at the beginning.
PostgreSQL’s equivalent habit is unlocked by the same reasoning with different vocabulary. A form that rewrites the table under an ACCESS EXCLUSIVE lock is scheduled like a migration; a form that takes SHARE UPDATE EXCLUSIVE is a routine change. The change plan should say which one it is in writing, because the two get different windows, different approvals and different rollback stories.
Decompose the change: expand, backfill, contract
The way to avoid a single large irreversible step is to split the change into three that are individually small, which is the shape most experienced teams already use even when they do not name it.
Expand. Add the new column, table or index without asking the database to prove anything about existing rows. PostgreSQL’s ALTER TABLE documentation explains the mechanism plainly: an added constraint normally causes a scan of the table to verify that all existing rows satisfy it, but “if the NOT VALID option is used, this potentially-lengthy scan is skipped”. The constraint still applies to everything written afterwards, so the platform stops accumulating new violations immediately, while the historical rows stay unproven.
Backfill. Move the data in batches, in a separate process, at a rate the platform chooses. The section below treats this as the workload it is.
Contract. Once the historical rows are proven valid and the reading path has moved, validate the constraint, then drop what is no longer needed — and drop it in a later change, not in the same one.
The validation step is the one that makes the sequence affordable. The same documentation states that the validation “does not need to lock out concurrent updates, since it knows that other transactions will be enforcing the constraint for rows that they insert or update; only pre-existing rows need to be checked. Hence, validation acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered.” A foreign key adds a ROW SHARE lock on the referenced table.
The same staging applies to a NOT NULL column, where the behaviour is documented in more detail than a team usually assumes. SET NOT NULL is “ordinarily checked during the ALTER TABLE by scanning the entire table, unless NOT VALID is specified”; the scan is skipped entirely “if a valid CHECK constraint exists (and is not dropped in the same command) which proves no NULL can exist”; and if a not-null constraint was already added as not valid, SET NOT NULL validates it instead of re-checking the world. In practice that means: add the check as NOT VALID, validate it, and only then set the column not null — three small steps whose total cost is one scan at a level that permits writes.
Build the index without stopping writes — and price the concurrency
Indexes are where a schema change most often becomes an outage, because a plain index build locks out writes for the duration and a large table can take hours. Every major engine now offers a way to avoid that, and each one trades availability for total work.
PostgreSQL’s CREATE INDEX documentation describes the option and its cost together: CONCURRENTLY builds the index “without taking any locks that prevent concurrent inserts, updates, or deletes on the table; whereas a standard index build locks out writes (but not reads) on the table until it’s done”. It then says what the platform buys with its time budget: the concurrent build performs two scans, “must wait for all existing transactions that could potentially modify or use the index to terminate”, and therefore “requires more total work than a standard index build and takes significantly longer to complete”.
Three documented behaviours belong in the runbook, because they are the ones that surprise people:
- A concurrent build can fail and leave something behind. The index is first entered as an “invalid” index; if a problem such as a deadlock arises while scanning, the command “will fail but leave behind an ‘invalid’ index”. That index is ignored for querying because it may be incomplete, but it “will still consume update overhead” on every write. The documented recovery is to drop it and run the concurrent build again.
- A unique index starts enforcing before it is usable. Uniqueness is enforced against other transactions from the second scan onward, so a violation “could be reported in other queries prior to the index becoming available for use, or even in cases where the index build eventually fails”, and an invalid unique index keeps enforcing afterwards. Deploying the constraint and the code that satisfies it in the wrong order turns this into visible errors on live traffic.
- A concurrent build is not composable. Only one concurrent index build can run on a table at a time, the table cannot have its schema modified while the build runs, and
CREATE INDEX CONCURRENTLYcannot run inside a transaction block. On a partitioned table, concurrent builds are not supported directly; the documented alternative is to build the index concurrently on each partition and then attach the partitioned index, which is a metadata-only operation.
The last constraint is the one that usually forces a coordinated deployment of a later release: if the index cannot be created in the same migration transaction as the rest of the schema, the build becomes a separate operational step with its own monitoring.
A backfill is a workload, not a script
Rewriting historical rows is the part of a schema change that actually spends the platform’s capacity, and the temptation is to treat it as a one-off script that runs to completion. On a live gambling platform it is a competing workload with a return path.
The open-source pattern most teams borrow is worth reading for its design choices rather than its command line. GitHub’s gh-ost describes the shape of an online change: it creates a ghost table in the likeness of the original, migrates that table while empty, copies data from the original into it “slowly and incrementally”, propagates ongoing changes at the same time, and then, “at the right time”, replaces the original with the ghost table. Two of its design decisions are the transferable ones. It deliberately avoids triggers and reads the binary log instead, because, in its authors’ words, triggers are the source of “many limitations and risks” on a busy write path. And its throttle is a real pause rather than a slower pace: when it throttles, “it truly ceases writes on master: no row copies and no ongoing events processing”, returning the database to its original workload.
That last property is what a backfill needs in a regulated platform: an off switch that works immediately, because the trigger for using it is usually a signal from outside the backfill — settlement latency rising, replication lag climbing, or a support queue filling up with failed wagers. A backfill that can only be slowed by restarting it is not one an operator can safely leave running during peak hours.
The properties worth writing into the plan:
- Bounded batches with a committed progress marker. The job must be able to resume from where it stopped without rewriting rows it already processed, and it must be able to stop between batches, not only between runs.
- Idempotent writes. A backfill that runs twice must leave the same result, because the most likely failure is not a wrong value but a repeated step after an interrupted one.
- Throttling driven by the platform’s own health signal, not by a fixed sleep. Replication lag, write latency and settlement queue depth are the signals that say whether the extra load is free right now.
- A completion condition that is checkable, such as a count of rows still awaiting the new value, rather than a log line saying the script finished.
- A deliberate order between the backfill and the cut-over. If the new column becomes the source of truth while the backfill is still running, the platform has two writers and one reconciliation problem.
Time limits belong to the change
A schema statement that waits is worse than one that fails, because a change waiting for a lock is holding resources and remains an unfinished step in a sequence nobody is watching any more. The timeouts are how the platform decides that in advance.
PostgreSQL documents lock_timeout as a session or statement setting that aborts “any statement that waits longer than the specified amount of time while attempting to acquire a lock on a table, index, row, or other database object”, and notes that the limit “applies separately to each lock acquisition attempt”. It is disabled by default, and the documentation explicitly advises against setting it in the server configuration file “because it would affect all sessions” — which is exactly why it belongs in the change tooling, scoped to the connection that is running the migration. It also notes the ordering trap: if statement_timeout is set to the same or a smaller value, it will always trigger first, so a lock timeout chosen to protect the platform can be masked by a statement timeout chosen for a different purpose.
The related setting is the one that protects against the migration that stalls without failing. idle_in_transaction_session_timeout terminates any session left idle inside an open transaction, and the documentation gives the operational reason: it can be used to ensure that idle sessions “do not hold locks for an unreasonable amount of time”. A change process that opens a transaction, performs a step and then waits on something else is holding whatever it has locked, and on a platform the cost is paid by the players whose round cannot be settled.
The change plan should therefore state, per step: which session settings it runs under, what it does when it times out, and whether the failure leaves the database in a state the next step can resume from. A timeout that leaves an invalid index or an unvalidated constraint is a checkpoint, not a disaster, provided the plan says so before it happens.
Verification is a comparison, not a green tick
A schema change is finished when the data proves it is finished. The evidence is a comparison between two states of the same question, taken at two moments, and it has to be reproducible by someone who was not on the change.
| What is compared | Why it is the one that matters |
|---|---|
| Row counts per table and per partition | Cheap, and it catches the truncated batch and the duplicated insert |
| Sums and counts over the money columns, grouped the way the ledger groups them | The comparison a finance or compliance reviewer will ask for, and the one that catches a backfill that miscounted |
| A checksum over a defined key set, before and after | Turns “we believe it ran” into a value that can be recomputed |
| Open rounds and unsettled transactions at the boundary | The state most likely to be half-migrated, because it was in flight when the change ran |
| The set of rows the new constraint rejects | A count of violations is a progress measure and a risk measure at once |
| Reads through the new path versus the old path on the same rows | Detects the mapping error that leaves both paths individually valid |
Two habits make this affordable. The first is the reconciliation window: the change is followed by a period in which the ledger is compared the way it is compared after any other settlement event, which is where the wallet reconciliation discipline already applies. The second is the old path staying readable until the comparison has passed, which is the technical content of “expand and contract” and the reason a drop column is scheduled in a later change.
None of these comparisons proves that a round can still be interpreted the way it was interpreted when it was played. That property is worth stating separately, because a schema change can quietly break it: the deterministic replay discipline exists precisely because a past round sometimes has to be reconstructed from the data as it was, and a change that adds, renames or reinterprets a column is a change to that record.
Decide the rollback before the first statement runs
Every change plan has a point past which reversing is more expensive than going forward. Naming that point in advance is the difference between a decision and an improvisation at three in the morning.
The reversible part is usually larger than teams assume, because of how staged constraints behave. An added NOT VALID constraint can be dropped. A backfill can be halted and restarted. A newly built index can be dropped — and dropping an index, unlike building one, is a metadata change that permits concurrent DML. The irreversible part is narrow and identifiable: dropping a column or a table, and any change that has already been read and written through by the new code path.
That gives a change plan a shape rather than a checklist:
- Expand and backfill are reversible and can be abandoned at any point, leaving extra columns and an extra index that cost space but change nothing.
- The cut-over is the reversible-with-effort step: the code can be rolled back, but only while the old path is still being maintained.
- Contract is the point of no return and belongs in a separate, later change with its own approval, its own window and its own verification — after the observation period that the plan defines, not after the change window ends.
The same discipline determines what an incident looks like. A failed change with an unreverted schema is a different incident from a failed change that was abandoned cleanly, and the incident response plan is where the difference between the two is written, along with who is allowed to call the rollback.
Rehearse against a copy of the real size
A schema change rehearsed on an empty table proves almost nothing, because the thing being tested is how the operation behaves against the data’s actual shape.
The rehearsal needs three properties. Size: a copy of the table at production volume, because the difference between an instant metadata change and a rewriting one only appears when there are rows to rewrite. Concurrency: writes happening while the change runs, because a lock that is invisible on an idle database is the entire problem on a busy one. Injection: a deliberately induced failure — a statement killed mid-backfill, a constraint that should reject a row, an index build that fails — because the recovery path is the part of the plan nobody has tested.
That is what the non-production estate is for, and the non-production environment requirements are the right home for it. A rehearsal can also borrow the restore path: a change rehearsed against a restored copy of the production database tests both the change and the restorability of the backup it came from, which is the same evidence the backup and restore verification discipline asks for.
For a change to a table that another team owns — a supplier integration table, a reporting extract, a table a partner reads — the rehearsal is also the moment to confirm that the change is compatible with their reads. The platform migration and cutover guide covers the wider version of that problem: a platform move is a sequence of coordinated changes, and the schema is one of the things being coordinated rather than an implementation detail of the move.
Turn it into acceptance evidence
The pack a reviewer, an auditor or a buyer should be able to read is short, and every item in it is checkable rather than asserted:
- The change plan, naming for each step the statement form, the lock level the engine documents for it, and whether the operation rewrites the table.
- The decomposition, showing that no single step both rewrites a large table and is irreversible.
- The backfill specification: batch size, progress marker, resumption behaviour, throttle signal and completion condition.
- The session settings each step runs under, including the lock and idle-transaction timeouts and what happens when they fire.
- The comparison evidence: the counts, sums and checksums taken before and after, with the queries that produced them.
- The rehearsal record, including the injected failure and the observed recovery.
- The named rollback point, the steps that remain reversible, and who is authorised to call the rollback.
- The schedule for the contract step, kept separate from the change that made it possible.
The work this describes is mostly preparation, which is why it is so often compressed into the change window itself, where it is both more expensive and more visible to players. The habit it belongs to — specifying behaviour before building it, and keeping the specification current when the system changes — is the same one behind the platform development discipline this site applies elsewhere. For a database, the specification is unusual in one respect: it has to remain true while several million rows are being rewritten underneath it, which is the only reason the sequence is worth writing down before it is run.
Questions a platform team asks
Can we just schedule the migration for a quiet hour?
You can, and it is still worth decomposing the statement. A quiet hour reduces the chance that someone is mid-round when the lock is taken; it does not change how long a rewrite takes, and on a table large enough to matter the rewrite is longer than any quiet hour the platform has. The two mitigations are separate: the window reduces the number of people affected, and the staged change reduces what they are affected by. A change that relies on the window alone fails the first time the table grows past it.
Is CREATE INDEX CONCURRENTLY safe to run at any time?
It is designed not to block concurrent inserts, updates or deletes, and it is still a heavier operation than a plain build, because it performs two scans and waits for transactions that could touch the index. Two of its documented behaviours decide whether it is safe in a particular hour: it can fail and leave an invalid index that consumes update overhead until it is dropped, and a unique index enforces uniqueness from its second scan onward, which means it can surface violations on live traffic before it is usable. So the answer is a sequence rather than a yes: build it while the data is known good, watch for the invalid state, and treat the index as a separate operational step from the rest of the change.
What actually has to be true before we drop the old column?
Three things, in order. The new path must be the only writer. The comparison over the money and round data must have passed across a full settlement cycle, not just a spot check. And a defined observation period must have elapsed with no consumer still reading the old column — including the reporting extract, the partner feed and the one dashboard nobody remembered. Until all three hold, the old column is cheap insurance: it occupies space, and it is also the only remaining copy of the data in the shape the old code understood.
How do we know the backfill is really finished?
Do not use the absence of errors or a log line. Define a completion condition the database can answer, such as the count of rows still holding the previous value or still failing the new constraint, and require that count to reach zero and stay there through one full write cycle after the cut-over. Then keep the same count as a monitor for a defined period, because a code path that writes the old value can be reintroduced by a rollback of an unrelated release, and a quiet backfill is not the same thing as a backfill that stays done.
What is the smallest useful first step if none of this exists yet?
Write down, for the next change you have to make anyway, the four facts the documentation already tells you: the statement form, the lock it takes, whether it rewrites the table, and the point past which the change is no longer reversible. That is an afternoon of reading and it changes the plan immediately, because the two decisions that cause most of the damage — combining a fast subcommand with a slow one in a single statement, and discovering the rollback point after the fact — are both decided at the moment the plan is written.
