# PostgreSQL 16 migration study reveals silent schema drift from merged branches

> **Key takeaway:** Defensive migration idioms like IF NOT EXISTS can mask incompatible schema changes, causing identical migration sets to produce divergent database schemas across environments with no error or warning.

**Published:** 2026-11-09T04:02:36.297051+00:00 
**Source:** Planet PostgreSQL 
**Category:** databases-storage 
**Canonical URL:** https://fosswire.org/news/postgresql-16-migration-study-reveals-silent-schema-drift-fr.html 

## Summary
A practical study on PostgreSQL 16 demonstrates how Git-mergeable migration files can produce two distinct failure modes when semantically incompatible schema changes collide. The "loud" failure occurs when migration order determines whether a rename or column addition succeeds, potentially passing staging while breaking production. The more dangerous "silent" failure arises when two branches create indexes with identical names but different definitions using IF NOT EXISTS guards, causing one environment to index raw columns while another indexes transformed expressions, with both reporting success and recording both migrations as applied.

The research emphasizes that PostgreSQL's transactional DDL provides critical protection against partial migration states, automatically rolling back all changes when any statement fails within a transaction. However, this safeguard is explicitly unavailable for CREATE INDEX CONCURRENTLY, the operation most commonly used on large production tables. The author identifies ephemeral database-per-branch workflows as the most reliable prevention strategy, since they surface conflicts at merge time rather than deferring resolution to apply time.

## Migration collision mechanics and PostgreSQL DDL behavior
- **Loud failure mode:** Branch A adding a column to `users` and Branch B renaming `users` to `accounts` merge cleanly in Git but produce `ERROR: relation "users" does not exist` when the rename executes before the column addition, halting the migration run mid-stream with subsequent migrations unapplied.
- **Silent failure mode:** Two `CREATE INDEX IF NOT EXISTS idx_users_email` statements with different definitions (`email` versus `lower(email)`) both report success with exit code zero, but the resulting schema depends entirely on application order, creating undetected performance divergence across environments.
- **Transactional DDL protection:** PostgreSQL 16 wraps multi-statement migrations in transactions, rolling back all changes on failure and leaving the database in its pre-migration state, unlike MySQL where implicit commits after each DDL statement make half-applied migrations the baseline behavior.
- **CONCURRENTLY limitation:** `CREATE INDEX CONCURRENTLY` cannot execute inside a transaction, explicitly removing transactional protection from the long-running, high-risk operations most likely to fail on production datasets.
- **IF NOT EXISTS semantics:** The guard checks only object name existence, not structural equivalence, meaning it silently skips definitionally incompatible objects without comparing column expressions, index types, or constraints.

## Why it matters
For DevOps teams and database administrators running PostgreSQL in CI/CD pipelines, this research exposes a fundamental gap between version control guarantees and schema integrity that standard migration tools do not bridge. The findings directly challenge the assumption that staging-success validates production deploys, and provide concrete justification for ephemeral database-per-branch strategies in tools like Neon, Flyway, or custom automation. PostgreSQL's transactional DDL advantage over MySQL is also clarified, helping teams make informed platform choices and understand exactly where that protection ends.