Implementing Schema Evolution
1. Adding Columns Safely
| Step | Detail |
|---|---|
| NULLable first | ADD COLUMN without DEFAULT — instant in PG 11+ |
| Backfill in batches | UPDATE WHERE ... LIMIT |
| Add NOT NULL after | Once backfill complete |
| Default-with-volatile | Avoid full table rewrite |
2. Removing Deprecated Columns
Deprecation Workflow
- Stop writes from app (release N)
- Stop reads (release N+1)
- Verify no production usage
- DROP COLUMN (release N+2)
3. Renaming Columns Without Downtime
Expand-Contract Rename
- ADD new column
- App writes both
- Backfill new from old
- App reads new
- App writes only new
- DROP old column
4. Changing Column Data Types
| Approach | Detail |
|---|---|
| ALTER TYPE compatible | VARCHAR → TEXT — no rewrite |
| USING clause | ALTER COLUMN ... TYPE int USING col::int |
| Shadow column | Add new col, dual-write, switch reads |
| pg_repack / gh-ost / pt-osc | Online schema change tools |
5. Adding Indexes Online
| DB | Command |
|---|---|
| Postgres | CREATE INDEX CONCURRENTLY |
| MySQL 8 | ALTER TABLE ... ADD INDEX ... ALGORITHM=INPLACE, LOCK=NONE |
| SQL Server | WITH (ONLINE = ON) |
| Oracle | CREATE INDEX ... ONLINE |
6. Modifying Constraints Safely
| Operation | Pattern |
|---|---|
| Add CHECK | ADD ... NOT VALID, then VALIDATE CONSTRAINT |
| Add FK | NOT VALID + VALIDATE — avoids full lock |
| Add NOT NULL | Backfill, add CHECK NOT VALID, validate, then SET NOT NULL |
| Drop constraint | Generally fast (metadata only) |
7. Handling Backward Compatibility
| Practice | Detail |
|---|---|
| Tolerant reader | App ignores unknown fields |
| Additive changes | Add new; never repurpose existing |
| Version flag | schema_version + branching code |
| Views as facade | Stable contract over evolving tables |
8. Implementing Forward Compatibility
| Practice | Detail |
|---|---|
| Reserve unknown fields | JSONB extra blob |
| Use enums carefully | Prefer lookup tables |
| Schema registry | Avro / Protobuf compat checks |
| Don't enforce strict shape | If clients may be ahead |
9. Documenting Schema Changes
| Artifact | Detail |
|---|---|
| Migration files | Ordered, version-controlled (Flyway/Liquibase) |
| CHANGELOG | Human-readable per release |
| Schema diff | migra, schemaspy, dbml |
| ADRs | Why this change |
10. Testing Schema Changes
| Test | Detail |
|---|---|
| CI migration apply | From scratch + from prod snapshot |
| Rollback path | Down-migration tested |
| Load on staging | Verify lock time / perf impact |
| Shadow run | Apply against prod clone |