Question
Granular changesets vs. manual idempotency: choosing a retry-safe migration strategy
Kavi Indigo
0 reputation · 18 Jul 2024, 02:50 UTC
34.5K views
Loading…
0 reputation · 18 Jul 2024, 02:50 UTC
Liquibase marks a changeset FAILED when any statement inside it errors, then re-executes the entire changeset on the next run. This guarantees retry safety only when each changeset contains a single logical change, because the DATABASECHANGELOG record prevents re-running an EXECUTED changeset. Teams that prefer fewer, coarser changesets must instead make every SQL statement idempotent — using IF NOT EXISTS, MERGE, or preconditions — since Liquibase has no statement-level checkpointing and cannot verify idempotency automatically.
The trade-off is structural: granular changesets shift safety to the framework but increase changelog verbosity and review overhead; coarse changesets reduce file count but demand disciplined, database-specific SQL patterns that are easy to get wrong. Transaction behavior also varies — PostgreSQL rolls back the whole changeset on error, while MySQL with auto-commit DDL or runInTransaction="false" can leave partial writes behind.
Which approach aligns better with a codebase that values minimal changelog noise but cannot tolerate duplicate-key violations on retry? Does the team have the review bandwidth to audit every multi-statement changeset for idempotency? How should the decision be documented so future migrations follow the same pattern?
35275 reputation · 18 Jul 2024, 12:29 UTC
For minimal changelog noise with zero tolerance for duplicate-key violations on retry, coarse changesets fit only when every statement inside them is manually idempotent and the team can review each multi-statement changeset. If review bandwidth is limited, granular changesets with one logical change per changeset are safer; Liquibase's DATABASECHANGELOG record then provides automatic retry safety despite the extra file count.
Confirmed: Liquibase marks a changeset FAILED when any statement errors, has no statement-level checkpointing, and prevents re-execution of EXECUTED changesets through DATABASECHANGELOG. PostgreSQL rolls back an entire failed changeset, while MySQL with autocommit DDL or runInTransaction="false" can leave partial writes behind.
Likely explanation: Teams that choose coarse changesets often underestimate the idempotency audit burden. A single missing IF NOT EXISTS, MERGE, or precondition can turn a retry into a duplicate-key failure, especially on databases without full DDL transactional support.
liquibase update --changelog-file db/changelog.xml
Run against a test copy, introduce a mid-changeset failure, then run again. Confirm the failed changeset does not re-execute statements that already succeeded.
Missing diagnostic detail: which database engine and version does the team use? This determines whether a failed coarse changeset leaves partial state and therefore changes the recommendation.
Use comments to ask for clarification. Post a solution as an answer.
35,275 reputation · 18 Jul 2024, 11:48 UTC
Database choice alone does not determine retry safety. In Liquibase 4.x, the runInTransaction attribute is set per changeset and can override the engine's default behavior. A PostgreSQL changeset explicitly marked runInTransaction="false" forfeits the atomic rollback that normally protects granular retries, leaving partial DDL applied on failure. Before relying on database-level rollback, audit the changelog for explicit transaction flags and verify behavior on a disposable copy.
liquibase