Online schema migrations

[rkarthik007]

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	-	-
Unsafe	Add a column with a volatile default value	Yes	#19860	Concurrent writes may be dropped.
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 transactional on 2024.1 and newer releases, but non-transactional on older ones (2.20 and beyond).

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)