Liquibase & Flyway — Interview Q&A Matrix

Parent stack: Persistence & Caching Technology: Liquibase & Flyway Levels: Junior · Architect Questions: 2

Each entry contains a Question, the Expected Depth, an Ideal Answer, and Red Flags.

Junior

Q1 — Schema migrations with Flyway and Liquibase

Question: Why use Flyway or Liquibase instead of running SQL by hand, and how do migrations work?

Expected Depth:

• Explains versioned migrations, ordering, checksums, and the schema history table.
• Knows that an applied migration should not be edited; a new migration fixes it.
• Distinguishes Flyway versioned and repeatable migrations.
• Understands Liquibase change sets, preconditions, contexts, and labels.
• Runs migrations once per deployment and avoids uncontrolled migration execution from every application instance.
• Tests migrations in CI and against production-like data before release.

Ideal Answer:

• Flyway and Liquibase store schema changes in version control and apply them in a known order. They record completed changes and checksums in a history table, so environments can be compared and reproduced.
• Treat applied migrations as immutable. If an applied migration is wrong, add a new migration that corrects it. Editing the old file causes checksum errors and makes environments inconsistent.
• A Flyway versioned migration runs once:

-- V2__add_order_status.sql
ALTER TABLE orders ADD COLUMN status text;

UPDATE orders SET status = 'PENDING' WHERE status IS NULL;

• A Flyway repeatable migration starts with R__ and runs again when its checksum changes. Use it for replaceable objects such as views, not for normal table evolution:

-- R__active_orders_view.sql
CREATE OR REPLACE VIEW active_orders AS
SELECT id, customer_id, status
FROM orders
WHERE status IN ('PENDING', 'PAID');

• Liquibase groups changes into identified change sets and can apply preconditions:

databaseChangeLog:
  - changeSet:
      id: add-order-status-index
      author: platform-team
      preConditions:
        - onFail: MARK_RAN
        - not:
            - indexExists:
                indexName: idx_orders_status
      changes:
        - createIndex:
            tableName: orders
            indexName: idx_orders_status
            columns:
              - column:
                  name: status

• Run validation and migration tests in CI. Apply the migration from one controlled deployment job or migration process. Application-startup migration can work for small systems, but many replicas starting together make ownership and failure handling less clear.
• Back up important data and define a roll-forward plan. Automatic rollback is not always possible for destructive data changes.

Red Flags:

• Edits an applied migration and uses repair only to hide the checksum mismatch.
• Runs unreviewed SQL manually in production.
• Has no schema history or version control.
• Lets every application replica run long migrations during startup without coordination.
• Assumes that every migration can be rolled back automatically.
• Tests only on an empty database and never upgrades a database with existing data.

Architect

Q2 — Zero-downtime schema migrations at scale

Question: You must add a NOT NULL column and rename another on a 500M-row table used by live traffic, with no downtime. Design the migration process.

Expected Depth:

• Uses the expand, backfill, verify, switch, and contract pattern.
• Keeps old and new application versions compatible during rolling deployment.
• Understands PostgreSQL table locks, short metadata locks, and lock_timeout.
• Backfills in small committed batches and monitors load, replication lag, and table bloat.
• Uses NOT VALID constraints, later validation, and concurrent index creation where suitable.
• Separates destructive cleanup into a later release and prefers roll-forward recovery.

Ideal Answer:

• Use expand, backfill, verify, switch, and contract. Each deployment must work with both the previous and next schema version.
• Expand: add the new column as nullable. Set a short lock timeout so the migration fails instead of waiting and blocking traffic:

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN status_v2 text;

• Deploy application code that can read either column and writes both columns. Monitor for differences between them.
• Backfill in small batches and commit after each batch. Throttle the job when database load or replica lag rises:

UPDATE orders
SET status_v2 = status
WHERE id > :start_id
  AND id <= :end_id
  AND status_v2 IS NULL;

• Verify that no rows are missing or different before adding the final constraint:

SELECT count(*)
FROM orders
WHERE status_v2 IS NULL OR status_v2 IS DISTINCT FROM status;

• Add and validate a check constraint. NOT VALID avoids checking all old rows while the constraint is added. VALIDATE CONSTRAINT scans the table with a lighter lock than a direct long-running rewrite:

ALTER TABLE orders
    ADD CONSTRAINT orders_status_v2_nn
    CHECK (status_v2 IS NOT NULL) NOT VALID;

ALTER TABLE orders
    VALIDATE CONSTRAINT orders_status_v2_nn;

ALTER TABLE orders
    ALTER COLUMN status_v2 SET NOT NULL;

• Create large indexes with CREATE INDEX CONCURRENTLY so normal writes can continue. PostgreSQL does not allow this command inside a transaction block, so configure that migration to run without a surrounding transaction:

CREATE INDEX CONCURRENTLY idx_orders_status_v2
ON orders (status_v2);

• Switch reads to the new column after verification. Stop dual writes only after every running application version uses the new column.
• Contract: drop the old column and temporary constraint in a later release. A direct rename is safe only when no old application instance, query, report, or integration still uses the old name.
• Prefer roll-forward fixes. Restoring removed data is often slower and riskier than deploying a correction.

Red Flags:

• Renames or drops the old column while an older application version is still running.
• Backfills 500 million rows in one transaction.
• Runs a lock-heavy migration with no lock_timeout, monitoring, or cancellation plan.
• Creates a large index normally during peak traffic instead of considering CONCURRENTLY.
• Adds NOT NULL before checking and backfilling existing rows.
• Calls every step rollback-safe even after destructive data removal.

Scoring Rubric