Migrations And Schema Management

Why this matters

Schema changes break production if done carelessly. As a Backend Engineer you will:

• Ship features that need new columns, tables, or indexes.
• Fix performance with new indexes and constraints.
• Rename or remove legacy fields without downtime.
• Migrate data types and backfill safely on large tables.

Good migration habits prevent outages, lockups, and data loss.

Concept explained simply

A migration is a small, versioned step that changes the database structure or data. You store steps as code so the schema can be reproduced and reviewed.

Mental model

Think of your schema as a public API contract. Clients (your app, jobs, analytics) depend on it. Safe changes follow this pattern:

• Additive first (add new pieces alongside old).
• Backfill data slowly.
• Switch reads/writes to the new shape.
• Remove old pieces later.

Production-safe rules

Worked examples

Example 1: Add a non-nullable column to a large table

Goal: Add users.timezone TEXT NOT NULL DEFAULT 'UTC' without downtime.

1. Add column as nullable (fast):

   ALTER TABLE users ADD COLUMN timezone TEXT;

2. Set default for future inserts (fast):

   ALTER TABLE users ALTER COLUMN timezone SET DEFAULT 'UTC';

3. Backfill in batches (application job or scheduled task):

   -- Pseudocode SQL loop; implement in app/job
   UPDATE users SET timezone = 'UTC'
   WHERE timezone IS NULL
     AND id BETWEEN :min AND :max;
   -- Repeat for ranges, sleep between batches

4. Ensure all rows are filled (sanity check):

   SELECT COUNT(*) FROM users WHERE timezone IS NULL;

5. Add NOT NULL constraint (now safe):

   ALTER TABLE users ALTER COLUMN timezone SET NOT NULL;

Example 2: Rename a column without breaking reads

Goal: Rename customers.full_name to customers.name with zero downtime.

1. Add new column:

   ALTER TABLE customers ADD COLUMN name TEXT;

2. Dual-write in app: when saving, write to both full_name and name.
3. Backfill existing rows:

   UPDATE customers SET name = full_name WHERE name IS NULL;

4. Switch reads to name in code. Monitor errors and metrics.
5. Stop writing full_name. Keep dual-read fallback briefly if needed.
6. After a safe window, drop old column:

   ALTER TABLE customers DROP COLUMN full_name;

Example 3: Drop a column safely

Goal: Remove orders.legacy_code used by old code paths.

1. Verify no reads/writes in code. Remove usages and deploy.
2. Deploy a guard: log or alert if any reference occurs.
3. Take a backup/snapshot if required by policy.
4. Drop the column:

   ALTER TABLE orders DROP COLUMN legacy_code;

Example 4: Create an index without long locks

Goal: Add index on events(user_id, created_at) used by a hot query.

-- Prefer online/concurrent index creation where supported
-- Example syntax (database-specific):
CREATE INDEX CONCURRENTLY idx_events_user_created
ON events (user_id, created_at);

Monitor impact and query plans. Avoid building multiple heavy indexes simultaneously.

Exercises you will do here

These match the exercise tasks below. Do them in a dev database or a small local instance.

• Exercise 1: Plan and write a safe migration to add a unique email to users.
• Exercise 2: Safely change a column type from INTEGER to BIGINT.

Tip: Use small batches for backfills and measure execution time.

Common mistakes and self-check

• Adding NOT NULL with default on huge tables directly. Self-check: Will this rewrite the table? Can I add nullable + backfill first?
• Renaming columns directly. Self-check: Do all services use the new name? Did I dual-write and backfill?
• Building non-concurrent indexes on hot tables. Self-check: Does my index creation block writes?
• Skipping validations. Self-check: Did I run COUNT checks before enforcing constraints?
• Dropping too early. Self-check: Are logs, dashboards, and jobs updated and quiet?

Practical projects

• Blue-green column rename: Pick a busy table, add a new column, dual-write, backfill, switch reads, and drop the old column after a safe window.
• Online indexing: Identify a slow query and add an index using an online/concurrent method. Compare query timings before/after.
• Constraint hardening: Start with a nullable column, backfill, add NOT NULL and a CHECK constraint after validation.

Who this is for

• Backend Engineers shipping features on relational databases.
• Platform/Infra engineers building deployment pipelines for schema changes.
• Data-minded developers maintaining long-lived systems.

Prerequisites

• Comfortable with basic SQL (CREATE TABLE, ALTER TABLE, INSERT/UPDATE/DELETE).
• Familiar with your app framework's migration tool (any is fine).
• Basic understanding of transactions and locks.

Learning path

1. Read the concepts and worked examples above.
2. Complete Exercises 1–2 (below).
3. Run the Quick Test at the end. The test is available to everyone; only logged-in users get saved progress.
4. Do one Practical project this week to reinforce habits.

Next steps

• Adopt an additive-first checklist for every schema change.
• Automate backfills with batch jobs and clear metrics.
• Document a company playbook for zero-downtime patterns.

Mini challenge

Your product needs to enforce uniqueness on accounts.handle, but duplicates exist. Sketch a three-step plan to clean data, add a unique index safely, and prevent regressions. Keep each step deployable within minutes.