Each test is 5 questions with varying difficulty.
AI Prep covers AI Agents, Generative AI, ML Fundamentals, NLP & LLMs and a lot more, with adaptive tests and daily challenges. Fully offline on Android. Free to try, one-time unlock for lifetime access.
Database migrations are the programmatic process of managing schema evolution in relational databases. In 2026, as systems move toward continuous deployment and high-availability requirements, the ability to safely modify database schemas without service interruption is a critical engineering competency. Roles such as Backend Engineers, Data Engineers, and DevOps Engineers are frequently tested on their ability to handle schema versioning, state consistency, and rollback strategies. Interviewers ask about this topic to assess a candidate's understanding of production safety, transactional integrity, and the operational risks associated with altering live data structures. At a junior level, candidates are expected to understand the basic workflow of migration tools like Alembic or Flyway. At a senior level, the focus shifts to designing zero-downtime migration strategies, handling large-scale data migrations, managing multi-tenant schema isolation, and ensuring backward compatibility during rolling deployments.
Database migrations are the primary mechanism for evolving application state. In production environments, an incorrectly executed migration—such as a blocking 'ALTER TABLE' operation on a multi-terabyte table—can cause immediate service outages, leading to significant revenue loss. Modern engineering teams rely on migration tools to treat database changes as code, ensuring that schema modifications are version-controlled, reproducible, and auditable. This topic is a high-signal interview area because it forces candidates to demonstrate 'production-first' thinking. A strong candidate will discuss the implications of lock contention, the necessity of idempotent scripts, and the importance of decoupling schema changes from application code deployments. Conversely, a weak candidate often ignores the operational reality of database locks or the complexity of rolling back changes in a distributed system. As of 2026, the rise of serverless and highly distributed databases makes understanding migration state management even more vital, as teams must navigate schema changes across multiple read replicas and global clusters without violating consistency guarantees.
Migration tools operate by maintaining a metadata table within the target database that tracks the history of applied scripts. When a command is issued, the tool compares the local migration files against the metadata table, identifies pending scripts, and executes them in order. In frameworks like Alembic, this involves an abstraction layer that generates SQL from Python model definitions, while Flyway operates directly on raw SQL or Java-based migration files.
The engine reads the repository, queries the metadata table for the current version, calculates the delta, and executes the SQL scripts sequentially.
[Migration Files]
↓
[Migration Tool Engine]
↓
[Metadata Table Check]
↓
[Transaction Wrapper]
↓
[Database Driver]
↓
[Target Schema]
↓
[Update Metadata]Expand schema to support new code, deploy code, then contract (remove) old schema elements.
Trade-offs: Increases complexity but enables zero-downtime deployments.
Using migration scripts to initialize outbox tables for reliable event sourcing.
Trade-offs: Ensures consistency but adds overhead to migration scripts.
Using database-specific syntax (e.g., 'IF NOT EXISTS') to ensure scripts are safe to run repeatedly.
Trade-offs: Reduces risk of failure but makes scripts database-specific.
| Reliability | Use transactional migrations where possible and implement automated health checks post-migration. |
| Scalability | Use asynchronous migration runners for large datasets to avoid blocking the main application startup. |
| Performance | Avoid heavy DDL on high-traffic tables; use 'online' schema change tools (e.g., gh-ost or pt-online-schema-change). |
| Cost | Minimize downtime-related costs by ensuring migrations are backward compatible and reversible. |
| Security | Restrict migration tool database credentials to the minimum necessary permissions (principle of least privilege). |
| Monitoring | Monitor migration execution time and success rates in CI/CD pipelines; alert on failed migrations. |
Alembic is a Python-specific migration tool designed for SQLAlchemy, offering features like auto-generation of migrations from models. Flyway is a language-agnostic tool that primarily uses SQL scripts or Java code to version databases, making it more suitable for polyglot environments where multiple services interact with the same database.
Manual changes lead to 'schema drift,' where the database state deviates from the version-controlled migration history. This makes deployments non-reproducible, complicates environment synchronization, and increases the risk of human error during critical updates.
Zero-downtime migrations rely on the Expand-Contract pattern. You add new schema elements in a backward-compatible way, deploy code that supports both old and new schemas, and then remove the old schema elements in a subsequent deployment once the application is fully migrated.
The version table acts as the source of truth for the migration tool. It records which migration scripts have been successfully applied to the database, allowing the tool to determine which scripts are pending and preventing the accidental re-execution of already applied changes.
No. While PostgreSQL supports transactional DDL, allowing you to roll back a failed migration, MySQL does not. In MySQL, DDL statements cause an implicit commit, meaning a failed migration might leave the database in a partially updated state that requires manual intervention.
Large data migrations should be broken into small, manageable batches to prevent transaction log overflow and long-held locks. Using background processes or specialized online schema change tools can help move data without blocking application traffic.
It is generally discouraged. Using ORM models in migrations creates a dependency on the application code state at the time of the migration. If the ORM model changes in the future, the migration script may fail or produce incorrect results when run against a new database instance.
Schema drift occurs when the actual state of the database schema does not match the state defined in your migration history or version control. This often happens due to manual hotfixes or failed, partially-applied migrations that were not properly reconciled.
In PostgreSQL, a standard 'CREATE INDEX' statement acquires an exclusive lock on the table, preventing writes. 'CREATE INDEX CONCURRENTLY' allows the index to be built without blocking writes, which is essential for maintaining availability in production environments.
Rollbacks are handled by 'down' migration scripts or by reverting to a previous version using the migration tool's rollback command. For non-transactional databases, rollbacks often require manual intervention to undo the partial changes applied before the failure.
AI Prep covers AI Agents, Generative AI, ML Fundamentals, NLP & LLMs and a lot more, with adaptive tests and daily challenges. Fully offline on Android. Free to try, one-time unlock for lifetime access.