Schema Evolution Runbook

HeliosDB Schema Evolution Runbook

Use this runbook for online schema changes, application migrations, and large data backfills.

Pre-Change Assessment

Before changing schema, confirm:

• The change is compatible with the currently deployed application version.
• The affected tables, indexes, and views are identified.
• Backups and restore validation are current.
• The expected lock level and duration are understood.
• Rollback and cleanup steps are documented.
• Monitoring covers write errors, query latency, replication lag, and storage growth.

Prefer additive changes first. Avoid destructive changes until all readers, writers, jobs, and downstream consumers have stopped using the old shape.

Additive Change Procedure

Use additive changes for low-risk schema evolution:

1. Add the new nullable column, table, index, or view.
2. Deploy application code that can read both old and new schema.
3. Start dual writes when the application must keep two shapes synchronized.
4. Backfill historical data in bounded batches.
5. Validate row counts, checksums, and application-level invariants.
6. Switch reads to the new schema.
7. Stop writes to deprecated fields after the compatibility window.

Example:

ALTER TABLE orders ADD COLUMN fulfillment_region TEXT;

CREATE INDEX CONCURRENTLY idx_orders_fulfillment_region
  ON orders (fulfillment_region);

Backfill Procedure

Run backfills in small batches to protect serving traffic:

1. Choose a stable batch key such as primary key range or creation time.
2. Set a batch size that keeps transaction time within the maintenance objective.
3. Run one batch and inspect lock waits, replication lag, and latency.
4. Continue with throttling when resource pressure rises.
5. Persist progress so the backfill can resume safely.

Example:

UPDATE orders
SET fulfillment_region = region
WHERE fulfillment_region IS NULL
  AND id >= :batch_start
  AND id < :batch_end;

Validation

Validate the new schema before switching reads:

SELECT count(*) AS missing_region
FROM orders
WHERE fulfillment_region IS NULL;

SELECT fulfillment_region, count(*)
FROM orders
GROUP BY fulfillment_region
ORDER BY fulfillment_region;

Also confirm:

• Application error rate remains within objective.
• Replication lag returned to baseline.
• Query plans use expected indexes.
• Downstream exports or streams include the expected fields.

Rollback

Rollback depends on the migration phase:

• Before read switch: stop the backfill or dual writes and continue using the old schema.
• After read switch: switch reads back to the old schema, then stop new writes.
• After cleanup: restore from backup or replay from an audited source if removed data is required.

Do not drop old columns, indexes, tables, or views until restore coverage and application compatibility have been verified.

Cleanup

After the compatibility window:

1. Confirm no queries, jobs, or downstream consumers use the old schema.
2. Remove old application writes.
3. Archive or export deprecated data if required.
4. Drop obsolete indexes first when they are no longer used.
5. Drop old columns or tables during a planned maintenance window.
6. Run statistics refresh and query-plan validation.

ANALYZE orders;