Skip to main content

PG.L4.EXPAND_WITHOUT_CONTRACT — A column was added and back-filled, and the one it replaces never dropped

  • Category: safety
  • Level: 4
  • Confidence: heuristic
  • Downtime class: online
  • Stability: stable
  • Suites: lint
  • Applies to: PostgreSQL 18

This rule is not telling you that you did something wrong. Adding a column and filling it from the data already in the table is the expand step of an expand/contract deploy, and it is the correct way to change a column without a maintenance window: for a while both columns are written, so the old code and the new code can run side by side.

It is also only half of the pattern, and this rule is the account of the other half.

What an unfinished expand leaves behind​

Two things are true from the moment the back-fill returns:

  • Every reader that knows the new column is correct.
  • The old column stops being maintained the moment the last deploy moves over — and it is still there, still readable, still joinable, holding whatever it held at that moment.

Nothing breaks, nothing is slow, and no error is raised. That is precisely why the contract step is the one that gets forgotten: there is no symptom to notice. What is left is a table where two columns hold one fact, one of them quietly stale, and nothing in the schema says which is authoritative. The next person to write a query picks one — and has no way to know which.

The second half​

ALTER TABLE orders DROP COLUMN total;

It is a catalog operation, not a rewrite; the space comes back on a later rewrite. The reason it belongs in its own migration is not cost, it is ordering: it is only safe once every deployed reader has stopped using the old column, and that is a fact about your deploy, not about your schema.

Flagged​

// Migration 1 — expand, and back-fill from the column being replaced:
DB::statement('ALTER TABLE orders ADD COLUMN total_cents bigint');
DB::statement('UPDATE orders SET total_cents = total * 100');
// …and no migration ever drops `total`. The table now carries both, and the older one
// stops being written the moment the last deploy moves over — silently.

Preferred​

// Migration 1 — expand and back-fill, unchanged. This half was always right:
DB::statement('ALTER TABLE orders ADD COLUMN total_cents bigint');
DB::statement('UPDATE orders SET total_cents = total * 100');

// Migration 2, AFTER every deployed reader uses the new column — contract:
DB::statement('ALTER TABLE orders DROP COLUMN total');

What makes this a finding, and what does not​

The back-fill is the evidence. "The column this one replaces" is not a fact a linter can read — it is a decision, and it does not appear in the statement being judged. So the rule does not guess it. It asks for something that IS in the SQL: a column was added, and this same run wrote to it using the rows that were already there.

A column added for genuinely new data is never written from existing rows, so it is not reported. That is the whole reason this rule is narrow enough to leave switched on.

Two consequences follow, and both are deliberate:

  • A back-fill in a queued job is invisible. If the data move happens outside the migration, this rule sees an ordinary column addition and says nothing. It would rather stay silent than guess which additions were replacements.
  • The replaced column is never named. It sits inside the expression on the right of the assignment, and reading it out would mean parsing expressions in the layer that exists to be free of grammar. The finding names the column that was added, and the table it sits on.

Why the whole run, and what that costs on the fast path​

The contract step belongs in a later migration — that separation is the pattern, not a mistake. So the question is asked of every migration in the run, and a run that drops a column from the same table answers it: no finding.

On the single-file fast path (sqlens:lint --file=…) the run is one file, so a contract step elsewhere is out of view and a finished sequence can read as unfinished. That is the fast path's standing limit — it sees what it was given — and it is why this rule reports heuristic confidence rather than certainty.

Why it is level 4 and carries no security severity​

Level 4 is the backward-compatibility band — the axis on which a table holding two columns for one fact genuinely is a gap. There is no security question here at all, so the rule declares no security severity and cannot breach that gate.

What makes one open end more urgent than another is its age, and age is not a property of a migration. It is what the debt account adds later, from the ledger.

Sources​

  • PostgreSQL 18 — ALTER TABLE — ADD COLUMN with no default, or a non-volatile one, is a catalog change rather than a rewrite, which is what makes the expand half cheap enough to leave in place while readers migrate; DROP COLUMN is likewise a catalog operation, with the space reclaimed on a later rewrite

The fix material this rule carries​

A finding from this rule carries machine-readable fix material, using this sequence:

  • expand_contract — Add the new shape, move readers and writers across, remove the old one — over three deploys.

The payload is material for you or an agent to apply. SQLens writes no migration and runs no DDL. See the remediation payload for every field, the placeholder semantics, and the version rules a consumer has to follow.