Database Migrations Without Downtime

The Challenge

Schema changes need to happen live.

Old and new code coexist during deploy.

Data volume: hours or days for large tables.

Rollback: harder than code.

Types of Migrations

Additive: new tables, columns. Usually safe.

Modifying: change existing column. Risky.

Removing: drop column, table. Very risky.

Data: move/transform data. Can be huge.

From our workA first product live in 4 weeksSign-up, payments and the core feature, ready for real users. FutureSelf is one of the products we built.How it works →

Additive Migrations – Safe

Add nullable column.

Add new table.

Add index (concurrently in Postgres).

Add function/procedure.

Deploy new code that can use or ignore.

Modifying Columns

Renaming: use temporary alias.

Type changes: often need whole new column.

Nullable to not null: fill data first.

Multi-step approach usually needed.

Multi-Phase Approach

Phase 1: Add new column/table alongside old.

Phase 2: Write to both old and new.

Phase 3: Backfill data.

Phase 4: Read from new, write to both.

Phase 5: Stop writing to old.

Phase 6: Remove old.

Backfilling Data

Large tables: batch process.

Small batches: 1000-10000 rows.

Sleep between batches to avoid load.

Track progress: resume-able jobs.

Monitor: don’t crash production.

Index Creation

Postgres: CREATE INDEX CONCURRENTLY.

MySQL: online DDL.

Large tables: hours to build.

Impact: increased load during creation.

Dropping Columns

Never drop immediately.

Deploy code that doesn’t use column.

Wait: verify nothing uses it.

Then drop in later deployment.

Blue-Green with DB

Shared DB: normal deployment.

Separate DBs: complex sync.

Usually: keep one DB, careful with schema.

Migration Tools

Rails: ActiveRecord migrations.

Node: Knex, Prisma.

Python: Alembic, Django.

Language-agnostic: Flyway, Liquibase.

Version control migrations like code.

Testing Migrations

Test on production-size data.

Time the migration.

Estimate downtime if any.

Rollback plan tested.

Staging environment mirrors production data structure.

Handling Failures

Migration takes too long: pause, resume.

Migration fails partway: rollback carefully.

Data corruption: restore from backup.

Communication: users see status.

Common Mistakes

Renaming column and expecting rollback: not simple.

Dropping index during peak hours.

Not testing on production-size data.

Skipping the multi-phase approach.

Long transactions holding locks.

PostgreSQL Specific

Concurrent index creation.

pg_repack for reorganization.

Table partitioning for large tables.

VACUUM for cleanup.

Explain plans for query performance.

Our Process

Every migration: reviewed.

Multi-phase when needed.

Backup before major migration.

Monitored during execution.

Rollback plan documented.

Based on Real Projects

This guide is based on our work with:

• Nasca
• Platforma365

Further Reading

If this guide helped you, you might also want to read our comprehensive guide on Custom SaaS Development.