Part 053 — Database Migration Patterns

Database schema adalah kontrak runtime antara aplikasi dan data.

Mengubah schema bukan hanya menjalankan:

alter table ...

Di production, migration harus aman terhadap:

• rolling deployment;
• old app dan new app berjalan bersamaan;
• data volume besar;
• lock table;
• long-running transaction;
• index build;
• backfill;
• rollback;
• idempotency;
• replication lag;
• multiple service ownership;
• audit dan compliance.

Engineer top-tier memperlakukan database migration sebagai distributed systems problem kecil, bukan sekadar file SQL.

Part ini membahas pola migrasi database untuk Java production.

1. Core Thesis

Database migration harus memenuhi tiga prinsip:

1. Schema change must be versioned.
2. Application compatibility must be planned.
3. Data movement must be bounded, observable, and recoverable.

Migration aman bukan hanya "berhasil di staging". Migration aman berarti:

• bisa dijalankan sekali dan terlacak;
• tidak merusak old app;
• tidak merusak new app;
• tidak lock table terlalu lama;
• bisa dipantau;
• bisa dihentikan/diulang jika data job gagal;
• punya rollback/forward-fix story;
• tidak mengandalkan asumsi hidden.

2. Migration Is a Contract Change

Schema touches:

• application queries;
• JPA entities;
• jOOQ generated schema;
• MyBatis XML;
• JDBC SQL;
• read models;
• outbox/inbox;
• reports;
• ETL/data pipeline;
• stored procedures;
• indexes and constraints;
• permissions;
• replication;
• backup/restore;
• operational scripts.

Therefore migration must be reviewed with all consumers in mind.

3. Versioned Migration

Versioned migration is immutable ordered change.

Example:

V20260705_001__create_case_file.sql
V20260705_002__add_case_priority.sql
V20260705_003__create_case_dashboard_read_model.sql

Properties:

• runs once;
• recorded in schema history;
• checksum verified;
• order matters;
• should not be edited after applied to shared/prod environment.

Versioned migrations are best for schema evolution.

4. Repeatable Migration

Repeatable migration is re-applied when content changes.

Common uses:

• views;
• stored procedures;
• functions;
• grants;
• reference derived objects;
• documentation-generated metadata.

Example:

R__case_dashboard_view.sql
R__case_report_function.sql

Caution:

• repeatable migration can change runtime behavior;
• must be backward-compatible if old/new app coexist;
• function/view change can break old app if not versioned carefully.

For critical reports, consider versioned function names:

case_report_v1
case_report_v2

5. Schema History Table

Migration tools maintain table like:

flyway_schema_history
databasechangelog

It records:

• version;
• description;
• script;
• checksum;
• installed by;
• installed at;
• success/failure;
• execution time.

This is operational evidence.

Do not manually modify history except with documented repair process.

6. Checksum

Checksum proves applied migration content matches current file.

If migration file edited after applied, checksum mismatch.

Rule:

Never edit an applied versioned migration.
Create a new migration.

Exceptions are rare and require repair process, usually only in local/dev before sharing.

7. Forward-Only Migration

Production migration should usually be forward-only.

Instead of rolling back DB schema destructively, deploy a forward fix:

bad migration -> new migration fixes state/schema

Why?

• rollback can lose data;
• old data may have been written in new shape;
• app versions may already depend on new schema;
• irreversible DDL/data transformations.

Still, every migration needs rollback story:

Can we disable feature?
Can old app still run?
Can we add compatibility column back?
Can we restore backup?
Can we run forward fix?

8. Rollback vs Revert vs Forward Fix

Definitions:

Rollback

Undo migration to previous schema. Often risky.

Revert migration

A new migration that reverses or compensates prior change.

Forward fix

A new migration that fixes issue without returning exactly to old schema.

Production usually prefers:

feature flag off + forward fix

unless change is small and reversible.

9. Expand-Contract Pattern

Most important production migration pattern.

Phase:

1. Expand schema in backward-compatible way.
2. Deploy app that can use both old/new.
3. Backfill data.
4. Switch reads/writes.
5. Enforce constraints.
6. Contract old schema later.

Example: rename column status to case_status.

Bad one-step:

alter table case_file rename column status to case_status;

Old app breaks.

Safe expand-contract:

1. add case_status nullable;
2. app writes both status and case_status;
3. backfill case_status;
4. app reads case_status with fallback to status;
5. enforce not null;
6. app reads only case_status;
7. remove status later after all old versions gone.

10. Backward-Compatible Schema Change

Generally safe:

• add nullable column;
• add table not used by old app;
• add index concurrently/online if supported;
• add new view/function name;
• add non-enforced constraint if supported;
• add new enum/code value if old app tolerates;
• widen column length/type safely;
• add default if it does not rewrite/lock table heavily on your DB/version.

Potentially unsafe:

• drop column;
• rename column/table;
• change type;
• add not-null without backfill;
• add unique constraint on dirty data;
• add FK that scans/locks huge table;
• change enum value semantics;
• change view used by old app;
• tighten check constraint;
• remove index used by old app.

11. Deployment Ordering

For code + schema change:

schema expand -> app deploy -> data backfill -> read switch -> schema contract

Never deploy app that depends on missing schema.

Never contract schema while old app still uses it.

Rolling deployment means old and new app versions overlap.

Migration design must support overlap.

12. Old App / New App Compatibility Matrix

For each migration, test:

This matrix prevents rolling deploy outages.

13. Add Nullable Column Pattern

Migration:

alter table case_file
add column priority text;

App:

Priority priority = row.priority() == null
        ? Priority.NORMAL
        : Priority.fromDbCode(row.priority());

Later:

update case_file
set priority = 'NORMAL'
where priority is null;

Then:

alter table case_file
alter column priority set not null;

Better for large table: backfill in chunks, then enforce.

14. Add Not Null Column Safely

Bad:

alter table case_file
add column priority text not null default 'NORMAL';

Depending DB/version/table size, may rewrite/lock or be okay. Know your database.

Safer general pattern:

1. add nullable column;
2. app writes value for new rows;
3. backfill old rows in chunks;
4. verify no nulls;
5. add not null constraint.

15. Backfill Pattern

Backfill should be:

• chunked;
• resumable;
• idempotent;
• throttled;
• observable;
• safe to rerun;
• separated from schema DDL when large;
• not one huge transaction.

Example:

update case_file
set priority = 'NORMAL'
where priority is null
  and id > :last_id
order by id
limit :chunk_size;

Actual syntax varies by DB.

Store progress:

migration_backfill_progress(job_name, last_id, updated_at)

or use query that repeatedly updates remaining rows.

16. Idempotent Backfill

Backfill update should be safe to run multiple times.

Good:

update case_file
set priority = 'NORMAL'
where priority is null;

Bad:

update account
set balance = balance + 100
where ...

Non-idempotent update needs marker or deterministic recompute.

17. Chunk Cursor

Use stable cursor:

• primary key;
• created_at + id;
• sequence;
• dedicated migration cursor.

Avoid offset for large backfill.

Offset gets slower and can skip/duplicate under changes.

18. Throttling Backfill

Backfill can compete with OLTP.

Controls:

• chunk size;
• sleep between chunks;
• max rows/sec;
• run off-peak;
• statement timeout;
• lock timeout;
• pause/kill switch;
• monitor replication lag;
• monitor DB CPU/IO.

Backfill is production workload.

19. Online Index Build

Adding index to large table can lock writes depending database.

Use database-specific online/concurrent option if supported.

PostgreSQL example:

create index concurrently ix_case_file_tenant_status_updated
on case_file(tenant_id, status, updated_at desc, id desc);

Cautions:

• cannot run inside transaction block for PostgreSQL;
• failure leaves invalid index needing cleanup;
• migration tool transaction settings matter;
• still consumes IO/CPU.

Know your DB.

20. Unique Constraint Addition

Before adding unique constraint:

1. find duplicates;
2. decide cleanup;
3. prevent new duplicates if possible;
4. add unique index/constraint;
5. update error translation.

Example duplicate check:

select tenant_id, case_number, count(*)
from case_file
group by tenant_id, case_number
having count(*) > 1;

Do not add unique constraint blindly.

21. Foreign Key Addition

Adding FK to large table can scan/lock.

Pattern:

• ensure data valid;
• add FK in non-valid/not validated mode if DB supports;
• validate separately;
• add index on child FK;
• monitor locks.

PostgreSQL supports NOT VALID then VALIDATE CONSTRAINT.

Portability varies.

22. Check Constraint Addition

Similar:

1. add constraint not valid if supported;
2. validate data;
3. validate constraint;
4. update application validation/error mapping.

Check constraints are excellent for invariant, but migration must handle existing dirty data.

23. Column Rename Pattern

Unsafe direct rename breaks old app.

Safe pattern:

1. add new column;
2. dual write;
3. backfill;
4. dual read/fallback;
5. switch read to new;
6. stop writing old;
7. drop old later.

For views/API, create compatibility view if needed.

24. Table Split Pattern

Example: split case_file huge JSON/detail into case_detail.

Steps:

1. create new table;
2. app writes both old and new;
3. backfill detail table;
4. reads prefer new with fallback;
5. verify parity;
6. switch reads;
7. remove old columns later.

Data parity checks are important.

25. Table Merge Pattern

Merging tables also requires compatibility.

Often use:

• new table;
• dual write;
• backfill;
• read fallback;
• switch;
• cleanup.

Avoid destructive merge in one migration.

26. Enum / Code Value Migration

Adding enum/code value can break old app if it cannot parse unknown code.

Pattern:

1. deploy old app tolerant to unknown/new code or fallback;
2. add DB code/constraint allows value;
3. deploy writer producing new value;
4. monitor;
5. later make new value mandatory if needed.

Java enum switch must handle unknown code deliberately.

27. Data Type Change

Changing column type is risky.

Pattern:

1. add new column with new type;
2. dual write;
3. backfill with conversion;
4. validate conversion;
5. read switch;
6. drop old.

Direct alter column type on large table may lock/rewrite and may fail for dirty data.

28. Default Value

Default affects new rows.

It does not always backfill old rows.

Understand DB behavior.

Pattern:

alter table case_file
alter column priority set default 'NORMAL';

Then backfill old rows separately.

Do not assume default means no null historical data.

29. View Migration

For view used by app:

• changing view columns can break old app;
• create versioned view if incompatible;
• deploy app to use new view;
• drop old view later.

Example:

create view case_dashboard_v2 as ...

Avoid replacing case_dashboard incompatibly during rolling deploy.

30. Stored Procedure / Function Migration

Same as API versioning.

If signature changes:

• create new function name/version;
• deploy caller;
• drop old later.

Do not replace function with incompatible signature while old app may call it.

31. Reference Data Migration

Reference/seed data is part of schema contract.

Patterns:

• versioned migration inserts/updates specific code rows;
• idempotent upsert for reference code;
• never delete code still referenced by historical rows;
• add effective date/status if deprecating;
• app tolerates unknown/disabled codes.

Reference data changes can break enum mapping.

32. Permissions/Grants Migration

If app DB user needs permission on new table/view/function, migration must include grants.

Rolling deploy failure can occur when schema exists but app user lacks permission.

Test with same DB user as production.

33. Migration Lock

Migration tools often use lock to prevent concurrent migration.

But DDL itself can also lock application tables.

Understand:

• migration tool lock;
• database DDL locks;
• lock timeout;
• statement timeout;
• transaction wrapping.

Never run two migration processes accidentally.

34. Transactional DDL

Some databases support transactional DDL; others do not or only partially.

Migration tool transaction mode must match SQL.

Example PostgreSQL CREATE INDEX CONCURRENTLY cannot run inside transaction block.

Use migration tool features to disable transaction for that migration when needed.

35. Migration Failure

If migration fails:

• schema may be partially changed if DDL non-transactional;
• history table may record failed entry;
• migration tool may block future runs;
• manual cleanup/repair needed.

Runbook:

1. identify failed migration;
2. inspect actual schema;
3. clean partial artifacts;
4. repair history if needed;
5. create forward fix if shared environment;
6. rerun migration;
7. document incident.

36. Migration in CI

CI should:

• create empty DB;
• apply all migrations;
• run integration tests;
• optionally apply migrations from previous release to current;
• run codegen if using jOOQ;
• verify repeatable migrations;
• test rollback/compatibility where feasible.

This catches syntax/order issues early.

37. Migration From Previous Production Snapshot

Most bugs happen from old prod schema + real data.

Test:

restore anonymized/sampled prod schema/data
apply migrations
run smoke tests

For large systems, use staging with realistic data volume.

38. Shadow / Dry Run

Some migration tools/DBs allow dry-run SQL.

Useful but not enough.

You still need actual apply test because locks/data constraints/plans depend DB.

39. Migration and jOOQ Codegen

Workflow:

apply migrations -> generate jOOQ schema -> compile app

If migration removes column still used in code, compile fails.

This is strong safety.

But rolling compatibility still must be designed. Compile safety does not prove old app compatibility.

40. Migration and JPA

If entity expects non-null field before migration/backfill complete, app may fail.

During expand phase, entity/model should tolerate nullable or default.

Be careful with:

@Column(nullable = false)
private String priority;

while DB column still nullable during migration.

Application invariants and migration phases must align.

41. Migration and MyBatis/JDBC

String SQL does not compile against schema.

Integration tests are essential.

If column renamed/dropped, MyBatis XML may fail only at runtime unless tested.

42. Migration and Read Models

Read model schema change requires:

• projector compatibility;
• consumer compatibility;
• rebuild/backfill plan;
• version/source_version handling;
• old/new read model side by side if needed.

For high-traffic read model, create read_model_v2, backfill/rebuild, switch consumers, drop v1 later.

43. Migration and Outbox

Outbox payload schema evolves.

Migration may add event type/version.

Pattern:

• event payload has version;
• consumers tolerate old/new;
• migration does not rewrite all old events unless necessary;
• outbox table indexes support new publisher query.

Never break old unprocessed outbox events.

44. Migration and Audit

Audit/history tables are often append-only.

Avoid destructive schema changes.

Add columns nullable.

If report needs new audit field, old rows may not have it.

Mapping should handle null/unknown.

45. Migration and Backup

Before risky migration:

• ensure backup exists;
• restore process tested;
• know RTO/RPO;
• know whether point-in-time recovery available;
• know who can execute restore;
• know data loss impact.

Backup is not migration strategy, but is safety net.

46. Migration Deployment Runbook

For risky migration:

1. announce window if needed;
2. verify backups;
3. pause heavy jobs if needed;
4. apply schema expand;
5. deploy compatible app;
6. run backfill with metrics;
7. verify data parity;
8. switch feature/read path;
9. monitor;
10. contract later.

Runbook must include abort/stop conditions.

47. Migration Observability

Metrics/logs:

migration.applied{name, duration}
migration.failed{name}
backfill.rows_processed{job}
backfill.lag{job}
backfill.errors{job}
constraint.validation.duration{name}
index.build.duration{name}
db.lock_wait during migration
replication.lag during migration

Long migrations need progress visibility.

48. Data Parity Check

During dual write/backfill:

select count(*)
from case_file
where case_status is distinct from status;

or domain-specific equivalence.

For large table, sample/chunk.

Parity check before read switch.

49. Feature Flag for Migration

Feature flag can control:

• start writing new column;
• read from new column;
• enable new query/index path;
• enable new read model consumer.

Feature flag helps rollback code path without rolling back schema.

But avoid permanent dual-path complexity.

50. Contract Phase

Contract means remove old schema/path after safe period.

Checklist before drop:

• all app instances new version;
• no old jobs/scripts use old column;
• reports updated;
• data parity verified;
• backups available;
• monitoring no access to old column;
• code search clean;
• migration reviewed.

Dropping old schema too early breaks hidden consumers.

51. Anti-Pattern: One Big Migration

Combining schema change, backfill, constraint, index, and cleanup in one file is risky.

Split into phases.

52. Anti-Pattern: Editing Applied Migration

Create new migration.

53. Anti-Pattern: Rename/Drop During Rolling Deploy

Old app breaks.

Use expand-contract.

54. Anti-Pattern: Backfill Huge Table in One Transaction

Locks, logs, replication lag, rollback disaster.

Chunk and throttle.

55. Anti-Pattern: Adding Constraint Without Checking Dirty Data

Migration fails or locks too long.

Validate data first.

56. Anti-Pattern: Assuming Migration Tool Equals Zero Downtime

Tool orders scripts. It does not design compatibility for you.

57. Production Migration Checklist

• Migration is versioned and immutable.
• Backward compatibility reviewed.
• Old/new app compatibility matrix checked.
• Expand-contract phases defined.
• Data backfill chunked/resumable if large.
• Lock/statement timeout considered.
• Online index strategy reviewed.
• Constraints named and dirty data checked.
• Error translation updated.
• App model tolerates transition state.
• jOOQ codegen/JPA/MyBatis/JDBC tests updated.
• Read model/outbox/audit impact reviewed.
• Rollback/forward-fix story exists.
• Backup/restore readiness known for risky migration.
• Observability/runbook exists.
• Contract cleanup scheduled later.

58. Mini Lab

You need to change case_file.priority from nullable text to required enum-like code and add dashboard filter.

Design:

1. Migration to add column if missing.
2. App write path change.
3. Backfill job.
4. Constraint/check addition.
5. Index addition.
6. Query/read model change.
7. Old/new app compatibility.
8. Error mapping.
9. Monitoring.
10. Contract cleanup.

Write exact phase order and what can be rolled back/feature-flagged.

59. Summary

Database migration is production engineering.

You must master:

• versioned migration;
• repeatable migration;
• schema history;
• checksum;
• forward-only mindset;
• expand-contract;
• compatibility matrix;
• nullable column pattern;
• safe not-null;
• backfill chunking;
• online index;
• constraint validation;
• rename/table split/type change patterns;
• enum/code evolution;
• view/procedure versioning;
• reference data;
• grants;
• migration locks;
• transactional DDL caveats;
• failure repair;
• CI/staging/prod-data tests;
• tool integration;
• read model/outbox/audit impact;
• runbook/observability;
• contract cleanup.

Part berikutnya membahas Flyway Production Workflow: naming, baseline, repair, callbacks, Java migration, multi-env migration safety, and how to operate Flyway in real deployments.

60. References

• Flyway Documentation: https://documentation.red-gate.com/fd
• Liquibase Documentation: https://docs.liquibase.com/
• PostgreSQL ALTER TABLE: https://www.postgresql.org/docs/current/sql-altertable.html
• PostgreSQL CREATE INDEX: https://www.postgresql.org/docs/current/sql-createindex.html
• PostgreSQL Constraints: https://www.postgresql.org/docs/current/ddl-constraints.html