Database Migrations

Flyway and Liquibase for versioned schema changes without downtime surprises.

Intermediate10 min read

Read these first

Connection Pooling

Why this matters

Manual ALTER TABLE in production causes environment drift — staging passes, production fails mysteriously.
Zero-downtime deployments require migration strategies that avoid locking entire tables during peak checkout.
Flyway and Liquibase integrate with Spring Boot CI pipelines — migrations run before new code serves traffic.
Interview DevOps questions ask how you add a column to a 50-million-row table without downtime.

Clustered (PK)

Table rows (sorted by PK)

Row 1id=1

Row 2id=2

Row 3id=3

Non-clustered (email)

email index→ row pointer

Table heapunordered rows

A clustered index sorts table rows by key. Non-clustered indexes point to row locations.

Why versioned migrations

Schema is code. VaultCommerce stores migrations in src/main/resources/db/migration/:

Java

db/migration/
  V1__create_customers.sql
  V2__create_products.sql
  V3__create_orders.sql
  V4__add_orders_shipping_address.sql

Flyway tracks applied versions in flyway_schema_history. On startup (or CI deploy step), only pending scripts run — never twice, never out of order.

Java

-- V4__add_orders_shipping_address.sql
ALTER TABLE orders
  ADD COLUMN shipping_address_json JSONB;

UPDATE orders SET shipping_address_json = '{}'::jsonb
WHERE shipping_address_json IS NULL;

ALTER TABLE orders
  ALTER COLUMN shipping_address_json SET NOT NULL;

Flyway with Spring Boot

Java

spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: true

Add flyway-core and flyway-database-postgresql to pom.xml. VaultCommerce runs Flyway in the deploy pipeline before rolling out new pods — old code tolerates new schema (additive changes), new code requires new schema.

Flyway naming rules

Versioned — V{version}__{description}.sql — runs once in version order.
Repeatable — R__{description}.sql — re-runs when checksum changes (views, grants).
Undo — Commercial Flyway undo migrations; open-source relies on forward-fix scripts.

Safe migration patterns

Expand-contract for zero downtime

Expand — Add new column as nullable; deploy code that writes both old and new.
Migrate — Backfill data in batches: UPDATE ... WHERE id BETWEEN ? AND ?.
Contract — Deploy code reading new column only; drop old column in later migration.

Java

-- Step 1: add nullable column (fast, no rewrite in Postgres 11+)
ALTER TABLE products ADD COLUMN price_cents INTEGER;

-- Step 2: backfill in application or batch job
UPDATE products SET price_cents = (price_dollars * 100)::INTEGER
WHERE price_cents IS NULL AND id BETWEEN 1 AND 10000;

-- Step 3: enforce NOT NULL after backfill complete
ALTER TABLE products ALTER COLUMN price_cents SET NOT NULL;

Operations that lock tables

Aspect	Low risk (Postgres)	High risk
Add nullable column	Metadata only (PG 11+)	—
CREATE INDEX	Use CONCURRENTLY	Blocking index build
SET NOT NULL	After backfill	Full table scan + lock
DROP COLUMN	After code stop reading	Breaking deploy order

Add nullable column
Low risk (Postgres)Metadata only (PG 11+)
High risk—
CREATE INDEX
Low risk (Postgres)Use CONCURRENTLY
High riskBlocking index build
SET NOT NULL
Low risk (Postgres)After backfill
High riskFull table scan + lock
DROP COLUMN
Low risk (Postgres)After code stop reading
High riskBreaking deploy order

Java

CREATE INDEX CONCURRENTLY idx_orders_tracking
ON orders(tracking_number)
WHERE tracking_number IS NOT NULL;

CONCURRENTLY takes longer but allows reads and writes during build — mandatory for VaultCommerce's orders table during business hours.

Liquibase is an alternative using XML or YAML changelogs — VaultCommerce chose Flyway for plain SQL diffs in code review. Open-source Flyway has no automatic down migrations; use forward-fix scripts, snapshots before major changes, and staging clones at production scale.

Quick recall

Everything you need if you only revisit this box.

Versioned migrations keep schema identical across environments; Flyway tracks applied scripts.
Name files V{n}__description.sql; run in CI before deploying application code.
Expand-contract pattern enables zero-downtime column additions and renames.
Use CREATE INDEX CONCURRENTLY on large VaultCommerce tables to avoid write locks.
Prefer forward-fix migrations and snapshots over destructive rollback in production.

Test yourself

Answer these before moving on — recall is what makes it stick.