Learning center

Introduce soft delete while the old release still reads the table

Adding a deleted_at column changes what every existing query means, not only the new ones. The release that is still running does not filter on it, so it shows soft-deleted rows as live, and it deletes rows outright, removing the record soft delete was meant to keep. Ship a release that filters before the release that soft-deletes, and convert the old release's deletes with a trigger.

The old release meets a soft-deleted row#

Customer 7 has two addresses, and the newer one is the default. Release 2026.11.4 soft-deletes it and makes the older one the default. We ran this on PostgreSQL 16.15:

ALTER TABLE addresses ADD COLUMN deleted_at timestamptz;

-- 2026.11.4
UPDATE addresses SET deleted_at = now(), is_default = false WHERE id = 2;
UPDATE addresses SET is_default = true WHERE id = 1;

-- 2026.11.3 lists the customer's addresses
SELECT id, line1, is_default FROM addresses WHERE customer_id = 7 ORDER BY id;
idline1is_default
1Alte Str. 1t
2Neue Str. 2f

The old release still offers the deleted address at checkout, and counts it: count(*) returned 2 where the customer sees 1. Unique constraints, totals and "is this the last address" checks in the old release all count the deleted row.

The old release deletes for real#

When the customer then deletes address 1 on an old-release pod:

DELETE FROM addresses WHERE id = 1;

the row is gone. Only address 2 remains, soft-deleted. If the feature exists to keep deleted addresses for order history or audit, it lost one on its first day.

A BEFORE DELETE trigger turns the old release's delete into a soft delete:

CREATE FUNCTION addresses_soft_delete() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  UPDATE addresses SET deleted_at = now() WHERE id = OLD.id AND deleted_at IS NULL;
  RETURN NULL;
END $$;

CREATE TRIGGER addresses_soft_delete BEFORE DELETE ON addresses
  FOR EACH ROW EXECUTE FUNCTION addresses_soft_delete();

After it, the same DELETE left address 1 in place with deleted_at set. Returning NULL from a BEFORE row trigger skips the operation for that row, as the trigger behavior chapter describes, so the statement reports DELETE 0. An old release that checks the affected row count and treats 0 as "not found" will show an error to the customer even though the delete took effect. Check what yours does with it before relying on the trigger.

Release order#

  1. Add deleted_at and ship a release that filters deleted_at IS NULL everywhere it reads the table, while nothing sets the column yet. Every row is live, so this release behaves exactly like the old one.
  2. Once that release is the rollback target, add the delete trigger and ship the release that soft-deletes.

A rollback from step 2 lands on the step 1 release, which filters correctly. A rollback past step 1 shows soft-deleted rows again, which is the rollback limit.

The compatibility test#

After step 2's migration, soft-delete a row with the new release, then run the step 1 release's list, count and delete paths. Pass when the deleted row appears in none of them and the old release's delete leaves the row with deleted_at set. The other mixed-release checks are in moving data while two application releases share one database.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading