Online schema migrations
rkarthik007 opened this issue · 0 comments
rkarthik007 commented
Jira Link: DB-511
This issue tracks which DDL operations can be safely run as a part of schema migrations concurrently with foreground operations.
Design Docs
✅ Design Doc for a generic online schema change framework
✅ Design doc for adding indexes and backfilling it
DDL Operations
Safe for online ALTER - definitions:
- Unsafe: Concurrent DDL + DML may result in a constraint violation / data inconsistency.
- Safe, not online (unrelated object): No constraint violation. However, concurrent transactions that affect unrelated objects will get aborted.
- Safe, not online (same object): No constraint violation. However, concurrent transactions that affect the same object will get aborted.
- Online: No constraint violation, concurrent transactions on same object are not aborted.
Safe for online ALTER? |
Feature | Supported by YSQL? | GitHub Issue | Notes |
|---|---|---|---|---|
| Safe, not online (same object) | Add a column | Yes | - | |
| Safe, not online (same object) | Drop an existing column | Yes | - | |
| Safe, not online (same object) | Add a (nullable) column with a non-volatile default value | Yes | - | - |
| Safe, not online (same object) | Add a column that is non-nullable with a non-volatile default value | Yes | - | - |
| - | Add a column with a volatile default value | No | #19563 | - |
| Safe, not online (same object) | Change default value of a column | Yes | - | |
| Unsafe | Add a column that is non-nullable | Yes | #12106 | Concurrent DML against same table can result in constraint violation. |
| Unsafe | Change the type of a column | Yes | - | - |
| Online | Create index concurrently (default) | Yes | #7376 | Default behavior. Tracking a specific defect. |
| Unsafe | Create index non-concurrently | Yes | ||
| Unsafe* | Drop index | Yes | #7376 | *During the drop index operation, another session may attempt to perform an index scan and see incomplete data. |
| Online | Add a unique constraint during CREATE TABLE | Yes | ||
| Unsafe | Add a unique constraint with ALTER TABLE | Yes | #7376 | Same as create index non-concurrently |
| Unsafe | Add non-unique constraints (CHECK, NOT NULL, etc) | Yes | #1395 | Concurrent DML may result in constraint violations |
| Safe, not online (unrelated object) | Drop constraint | Yes | - | For drop non-unique constraint, there may be a small delay (RPC heartbeat) where another session may still enforce the constraint because its catalog cache has not yet refreshed. |
| Unsafe | Truncate table | Yes | #8528 | Truncate table is non-transactional |
Note: Schema changes involving indexes and constraints are separately tracked by #448 given they are substantial.
Integration with schema migration frameworks
There have been requests to integrate with the following migration frameworks:
- Liquibase
- Flyway
- Native migrations in ORMs (Hibernate/Spring, Django, Gorm, etc)