VisaSpark
Sign in
advanced23 min

Migrations, Schema Evolution, and Operational Safety

Changing a live schema safely with ordered, reversible migrations, testing database behavior deliberately, and the backup/recovery discipline that makes every other guarantee in this course matter.

What you'll learn

  • Explain why schema migrations must be ordered, sequential, and (ideally) reversible
  • Identify a schema change that's unsafe to apply directly to a live, populated table
  • Describe the operational checklist a real schema change should pass before deployment

Prerequisites

Explanation

A migration is a small, ordered, version-controlled script that changes a database schema incrementally — 001_create_learner.sql, 002_add_course_price.sql, 003_add_enrollment_unique_constraint.sql — applied in strict sequential order, tracked in a dedicated table (schema_migrations, recording which migrations have already run) so every environment (a developer's machine, staging, production) reaches the exact same schema state through the exact same ordered sequence of changes, never through an untracked, ad-hoc ALTER TABLE run once by hand and never recorded anywhere. This discipline is what makes a schema change reproducible and auditable — anyone can read the migration history and know exactly what changed, when, and in what order, rather than reverse-engineering a live schema's current, undocumented state.

Not every schema change is safe to apply directly to a live, populated table, and recognizing which ones aren't is a genuinely important operational skill. Adding a NOT NULL column to a table with existing rows fails outright unless a DEFAULT is also provided (existing rows would otherwise have no value for the new required column) — and even with a default, rewriting every existing row to backfill that default can, on a very large table, hold a lock long enough to cause real, visible application downtime, which is why large-table migrations sometimes need a more careful, multi-step approach (add the column nullable first, backfill in batches, then add the NOT NULL constraint once every row has a value). Dropping a column outright is straightforwardly destructive and irreversible — the data is genuinely gone the moment the migration commits — unlike most additive changes, which can usually be undone by a corresponding reverse migration.

Testing database behavior deliberately means going beyond "the application seems to work" and specifically verifying: that constraints actually reject the invalid data they're meant to reject (does inserting a negative price genuinely fail?), that a migration applies cleanly to a copy of realistic data (not just an empty test database), and that a migration's reverse operation (if one exists) actually restores the prior state correctly. Seed data — a separate, deliberately-maintained set of realistic sample rows — supports exactly this kind of testing, and should never be confused with production data or committed with anything resembling real user information.

Backup and recovery is the operational safety net every other guarantee in this course ultimately depends on: even a perfectly-designed, fully-normalized, properly-constrained, correctly-indexed schema doesn't protect against hardware failure, a catastrophic operator mistake (a DROP TABLE run against the wrong database), or a bug that corrupts data faster than anyone notices. At minimum, a real production PostgreSQL setup needs regular, automated backups, a tested restore procedure (a backup that has never actually been restored and verified is not a real safety net, only an assumption that it would work), and a documented recovery time expectation — this lesson's guided local lab has you draft exactly this checklist for a real, if small, schema.

Example

A real, ordered migration sequence and the specific unsafe change it deliberately avoids -- shown for reading, since this platform's SQLite sandbox has no migration-tracking mechanism to demonstrate this against.

-- migrations/001_create_learner.sql
CREATE TABLE learner (
    id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

-- migrations/002_add_learner_display_name.sql
-- UNSAFE if run directly on a table with existing rows and no DEFAULT:
--   ALTER TABLE learner ADD COLUMN display_name TEXT NOT NULL;  -- FAILS: existing rows have no value
-- SAFE version, providing a default so existing rows get a valid starting value:
ALTER TABLE learner ADD COLUMN display_name TEXT NOT NULL DEFAULT 'Unnamed Learner';

-- migrations/003_add_enrollment_unique_constraint.sql
ALTER TABLE enrollment ADD CONSTRAINT uq_learner_course UNIQUE (learner_id, course_id);

-- A dedicated tracking table records exactly which migrations have run, in order:
CREATE TABLE IF NOT EXISTS schema_migrations (
    version    TEXT PRIMARY KEY,
    applied_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- INSERT INTO schema_migrations (version) VALUES ('003_add_enrollment_unique_constraint');

Guided exercise

Guided exercise

Write isSafeToAddNotNullColumn(hasExistingRows, hasDefault) modeling the ALTER TABLE ADD COLUMN ... NOT NULL rule: return true if the table has NO existing rows (nothing to backfill) OR a default is provided; return false only if there ARE existing rows AND no default (this would fail).

Checks: existing rows with a default is safe · existing rows with no default is unsafe · an empty table is safe regardless of a default

Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.

Loading editor…

Stuck? Get a hint.

Independent exercise

Independent exercise

Write validateMigrationOrder(appliedVersions, newMigrationVersion) modeling the sequential-migration rule: migration filenames sort as strings (e.g. '001_...', '002_...'). Return true only if newMigrationVersion is ALPHABETICALLY GREATER than every version already in appliedVersions (it must come strictly after everything already applied -- no gaps backward, no re-running an old one).

Checks: accepts a migration that correctly comes after everything applied · rejects re-applying an already-applied migration · rejects a migration that would apply out of sequence · accepts the first migration when no history exists yet

Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.

Loading editor…

Stuck? Get a hint.

Guided local lab

Analyze Queries and Implement Safe Roles and Migrations

Runs on your computer
This lab runs on your own computer, in your own terminal and editor — not in your browser. VisaSparkSchools does not execute, run, or verify these commands for you. Follow the verification steps yourself to confirm your result.

Write a real, ordered migration sequence for a schema change, set up a genuinely least-privileged role, and draft an operational backup/recovery checklist — the capstone of this course's PostgreSQL-specific hands-on work.

Required tools

  • PostgreSQL server (16 or newer)
  • psql (matching your server version)
  • A terminal (any)

Setup

  1. Continue from the learning_platform_lab database used in this module's earlier labs (or recreate it from schema.sql/seed.sql/inventory.sql if starting fresh).
  2. Create a migrations/ folder with numbered .sql files, and a schema_migrations tracking table as shown in this lesson's explanation.

Project structure

learning-platform-lab/
  migrations/
    001_create_schema_migrations_table.sql
    002_add_learner_display_name.sql
  roles.sql
  operational-checklist.md

Starter files

migrations/001_create_schema_migrations_table.sql

-- TODO: CREATE TABLE IF NOT EXISTS schema_migrations (version TEXT PRIMARY KEY,
--   applied_at TIMESTAMPTZ NOT NULL DEFAULT now())
-- TODO: record this migration itself in the table after creating it

migrations/002_add_learner_display_name.sql

-- TODO: safely add a display_name column to learner (NOT NULL, with a DEFAULT,
-- since the learner table may already have rows from earlier labs)
-- TODO: record this migration in schema_migrations

roles.sql

-- TODO: CREATE ROLE reporting_readonly LOGIN PASSWORD '...';
-- TODO: GRANT SELECT on exactly the tables a reporting/analytics use case needs
--   (learner, course, enrollment) -- nothing else, and no INSERT/UPDATE/DELETE at all.

operational-checklist.md

# Backup and recovery checklist

TODO: fill in each item based on this lesson's explanation and your own research
against the PostgreSQL documentation.

- [ ] How is this database backed up, and how often?
- [ ] Has the restore procedure actually been tested, not just assumed to work?
- [ ] What is the acceptable data-loss window (how much data could be lost between
      the last backup and a failure)?
- [ ] What is the expected recovery time if the primary database is lost entirely?
- [ ] Who is responsible for verifying backups remain valid over time?

Requirements

  • migrations/001 and migrations/002 apply cleanly, in order, to the existing database.
  • 002_add_learner_display_name.sql safely adds a NOT NULL column to a table that may already contain rows, using a DEFAULT.
  • Both migrations record themselves in schema_migrations after applying.
  • roles.sql creates a role with ONLY SELECT access on exactly the tables a reporting use case needs — verified by attempting (and having rejected) an INSERT as that role.
  • operational-checklist.md is filled in with real, specific answers, not placeholder text.

Commands to run

  • Apply each migration in order

    psql -d learning_platform_lab -f migrations/001_create_schema_migrations_table.sql

  • Apply the second migration

    psql -d learning_platform_lab -f migrations/002_add_learner_display_name.sql

  • Create the least-privileged role

    psql -d learning_platform_lab -f roles.sql

  • Verify the role's access is genuinely restricted

    psql -d learning_platform_lab -U reporting_readonly -c "INSERT INTO learner (email) VALUES ('test@example.com');"

Expected behavior

Both migrations apply with no errors, and `SELECT * FROM schema_migrations;` lists both versions with a timestamp. The reporting_readonly role can successfully run a SELECT against learner/course/enrollment, but the INSERT verification command fails with a permission-denied error, confirming the role's privileges are genuinely restricted to read-only.

Verify it yourself

  • psql -d learning_platform_lab -c "SELECT * FROM schema_migrations;"

    Expected: Lists both migration versions with applied_at timestamps

  • psql -d learning_platform_lab -c "\d learner"

    Expected: Shows the new display_name column as NOT NULL with a default

  • psql -d learning_platform_lab -U reporting_readonly -c "SELECT * FROM learner;"

    Expected: Succeeds, returning learner rows

  • psql -d learning_platform_lab -U reporting_readonly -c "INSERT INTO learner (email) VALUES ('test@example.com');"

    Expected: ERROR: permission denied for table learner

Troubleshooting

  • `ERROR: column "display_name" contains null values` when adding the NOT NULL column — The ALTER TABLE statement is missing a DEFAULT value — without one, existing rows have nothing to populate the new required column with.
  • reporting_readonly can successfully INSERT — Check roles.sql only GRANTed SELECT, not ALL PRIVILEGES or INSERT/UPDATE/DELETE — the goal is a role that structurally cannot write, not one that merely isn't expected to.
  • `psql: error: connection to server ... failed: FATAL: role "reporting_readonly" does not exist` — Confirm roles.sql actually ran successfully before attempting to connect as that role — check for an earlier error in its output.

Stuck? Get a hint.

Extension challenge

Write a third migration, 003_add_learner_email_index.sql, that adds an index on learner(email) (the column already used in WHERE clauses via the UNIQUE constraint's implicit index -- research whether PostgreSQL's UNIQUE constraint already creates this index, and document what you find in your migration's own comment).

When you've verified this locally, use the "Mark lesson complete" button below to record your progress.

Common mistakes

  • Running an ALTER TABLE by hand directly against production, with no corresponding migration file committed anywhere -- this makes the schema's actual current state undocumented and unreproducible on any other environment.
  • Adding a NOT NULL column to a populated table with no DEFAULT -- this fails immediately with a clear error, but the underlying mistake (not considering existing rows) is worth recognizing before attempting the migration, not just after the error.
  • Treating an untested backup as a real safety net -- a backup that has never been restored and verified is only an assumption that recovery would work, not a demonstrated, reliable guarantee.

Knowledge check

Knowledge check

1. Why must migrations be applied in a strict, tracked, sequential order rather than as untracked, ad-hoc schema changes?
2. Why does adding a NOT NULL column to a table with existing rows fail without a DEFAULT value?
3. Why is an untested backup not a reliable safety net?

Takeaway

Schema changes belong in ordered, tracked migrations, not ad-hoc live edits — and some changes (adding a required column to a populated table, dropping a column) need deliberate care or a multi-step approach to apply safely; a backup that's never been tested with a real restore is an assumption, not a guarantee.

Summary

Migrations are small, ordered, tracked scripts (recorded in a schema_migrations table) that make schema changes reproducible and auditable. Adding a NOT NULL column to a populated table needs a DEFAULT to avoid failing outright. Testing database behavior means verifying constraints actually reject invalid data and migrations apply/reverse cleanly. Backups are only a real safety net once their restore procedure has actually been tested.

References

Your notes

Notes save automatically.

Finished this lesson?

Mark it complete to track your progress and schedule a future review.