Requiring DBA Approval for High-Risk Migrations

For a while every migration needed a DBA’s approval. The queue grew to three days, developers started batching unrelated schema changes into single pull requests to save waiting time, and the DBAs — reviewing forty migrations a week, most of them ADD COLUMN ... NULL — began approving on sight. Then someone removed the rule, and within a month a validated foreign key on a 300 GB table took the checkout flow down for nine minutes. Neither extreme works. The useful gate is risk-based: most migrations are routine and should flow through automated checks alone, while the few that can lock a hot table, rewrite data, or remove structure get a focused human review from someone who owns the database. This guide classifies migration risk automatically, routes the high-risk ones to approval, and keeps the process fast enough that nobody routes around it. It belongs to Migration Pipeline Gating.

Risk-Based Approval Flow Pipeline. Lint and dry run apply to every migration. A risk classifier scores the migration from its statements and the target tables' sizes. Low-risk migrations merge with standard review. High-risk migrations require approval from the database owners group, with a checklist, before merge and before the deploy step runs. Risk-Based Approval Flow Lint + dry run every migration Risk classifier statements × table size risk high? Standard review low-risk path Deploy step approval checked again route to DBA review fail
Automation handles every migration; humans spend their attention only on the ones that can hurt.

Symptom / Error Signatures

The approval process needs redesign when you see either failure mode:

  • Over-gating: a long queue of migrations waiting for DBA review; routine changes delayed by days; approvals granted without comments; developers bundling or hiding schema changes to avoid the queue.
  • Under-gating: incidents from migrations that a database specialist would have caught — blocking index builds, validated constraints on large tables, type rewrites, drops of columns still read — with no record of who assessed the risk.

A healthy process shows up as a small, steady number of high-risk reviews per week, fast turnaround on them, and no incidents from migrations that were classified low-risk.

Root Cause Analysis

Migration risk is driven by two factors that can be measured automatically: what the statements do (their lock mode, whether they scan or rewrite, whether they are destructive) and what they do it to (table size and traffic). A nullable ADD COLUMN on any table is low risk; CREATE INDEX CONCURRENTLY on a large table is moderate; a validated ADD FOREIGN KEY or ALTER COLUMN TYPE on a large, hot table is high; any DROP of structure still used by running code is high regardless of size. Linting already classifies statements, as described in Migration Linting & Static Analysis; adding table sizes turns that into a risk score.

Statement class Small table Large or hot table
additive, metadata-only (ADD COLUMN nullable) low low
online build (CREATE INDEX CONCURRENTLY, VALIDATE) low medium
blocking lock or scan (SET NOT NULL, validated FK) medium high
rewrite (ALTER TYPE, volatile default) medium high
destructive (DROP COLUMN/TABLE, rename) high high
data migration (large UPDATE/DELETE) medium high

Approval should also be about something specific. A reviewer asked to “approve this migration” skims; a reviewer asked “this statement takes ACCESS EXCLUSIVE on orders (180 GB, 4k writes/s); confirm the lock is instant and a lock_timeout is set” can answer precisely.

Risk Score Matrix Matrix of statement classes against table size bands, giving low, medium or high risk, which determines whether DBA approval is required. Risk Score Matrix Statement class < 1 GB 1–50 GB > 50 GB or hot metadata-only additive low low low online build / validate low medium medium blocking lock or scan medium high high table rewrite medium high high destructive / rename high high high
Only the high cells route to database owners; everything else relies on automated gates and normal code review.

Immediate Mitigation

1. Export table sizes for the classifier. CI should not query production directly; a scheduled job exports sizes and hot-table flags to a file or service the pipeline can read.

-- PostgreSQL · read-only · scheduled export of table sizes and write activity
SELECT relname, pg_total_relation_size(relid) AS bytes,
       n_tup_ins + n_tup_upd + n_tup_del AS writes_since_reset
FROM pg_stat_user_tables
ORDER BY bytes DESC;

2. Classify each migration in CI. Combine the linter’s findings with sizes to produce a risk level, and post it on the pull request with the specific reasons.

# Python · scripts/migration_risk.py · reads lint findings (JSON) and table sizes (CSV)
# WARNING: classification is advisory for low/medium; "high" must block until approved.
import csv, json, sys
sizes = {r["relname"]: int(r["bytes"]) for r in csv.DictReader(open("table_sizes.csv"))}
HIGH_RULES = {"changing-column-type", "ban-drop-column", "renaming-column", "renaming-table",
              "adding-foreign-key-constraint", "constraint-missing-not-valid", "setting-not-nullable-field"}
risk, reasons = "low", []
for f in json.load(open("lint.json")):
    big = sizes.get(f.get("table", ""), 0) > 50 * 1024**3
    if f["rule"] in {"ban-drop-column", "renaming-column", "renaming-table"} or (f["rule"] in HIGH_RULES and big):
        risk = "high"; reasons.append(f"{f['rule']} on {f.get('table')}")
    elif f["rule"] in HIGH_RULES and risk == "low":
        risk = "medium"; reasons.append(f"{f['rule']} on {f.get('table')}")
print(json.dumps({"risk": risk, "reasons": reasons}))
sys.exit(2 if risk == "high" else 0)

3. Require database-owner approval for high risk. With GitHub, a CI step can add a migration-risk:high label and request review from a team; branch protection requires that team’s approval via CODEOWNERS on migration paths, or a status check that passes only when an approval from the group exists.

Permanent Fix / Long-Term Pattern

Design the approval to be fast and focused. Publish a short checklist that high-risk reviews answer — lock mode and expected duration, lock_timeout present, backfill batched, running code compatible, rollback path, timing relative to traffic — and ask the author to fill it in the pull request so the reviewer verifies rather than investigates. Staff the review with a rotation and a response-time expectation measured in hours. Re-check the approval at deploy time: the pipeline’s migration step should refuse to run a high-risk migration whose approval is missing or predates later changes to the file.

# YAML · CODEOWNERS-style routing (conceptual) · migrations need database owners when labelled high-risk
# .github/CODEOWNERS
/db/migrations/ @org/database-owners
# branch protection: require review from code owners; CI adds the label and fails
# the "migration-risk" check until a member of @org/database-owners approves.

Review the classifier’s accuracy quarterly: every migration-related incident should be checked against the risk it was assigned. Incidents from “low” migrations mean a rule or size threshold is missing; high-risk reviews that never find anything mean a rule is too broad. Pair this gate with the automatic checks it relies on — gating migrations on estimated lock duration and blocking deploys on failed migration dry runs — so reviewers see measured data, not guesses.

Review Load Before and After Risk Routing Bar chart of migrations per week needing DBA review. Before: all 40 migrations. After risk routing: 4 high-risk migrations. Median approval time before: 3 days; after: 3 hours. Review Load Before and After Risk Routing reviews / week, all migrations 40 reviews / week, high-risk only 4 DBA reviews per week (illustrative)
Routing by risk cut the review queue by an order of magnitude and made the remaining reviews meaningful.

Verification Checklist

Frequently Asked Questions

Why not require DBA approval for every migration? Because the queue becomes long and the reviews become rubber stamps. Automated checks handle routine migrations well; human attention is most valuable on the few changes that can lock hot tables, rewrite data or remove structure.

What makes a migration high risk? A blocking lock, scan or rewrite on a large or hot table, any destructive change or rename, and large data migrations. The combination of statement class and table size captures most of it.

Who should approve high-risk migrations? People accountable for database reliability — DBAs, platform or SRE engineers — organised as a group with a rotation, so approval does not depend on one person’s availability.

Should the deploy step re-check approval? Yes. A migration file can change after approval; the deploy step should verify that the approved version is the one being run, or require re-approval.

Can the classifier use production data directly? It is better to export sizes and activity from production on a schedule and let CI read the export. That keeps CI isolated from production credentials while still giving the classifier current numbers.

Related Articles