Question
YSQL ALTER TABLE ADD COLUMN constraints with volatile defaults
Neel Maple
0 reputation · 23 Feb 2020, 11:51 UTC
38.7K views1
Loading…
0 reputation · 23 Feb 2020, 11:51 UTC
YugabyteDB provides a PostgreSQL-compatible YSQL interface where schema changes are processed via DocDB. While adding a column with a constant default is typically handled as an online metadata update, the behavior changes when volatile expressions are used as defaults.
When a column is added with a volatile default—such as a function returning a timestamp or a random value—the system must determine if the operation can remain online or if it requires a row-by-row rewrite to ensure data consistency across existing records.
On large tables, this distinction impacts cluster availability and transaction latency. There is uncertainty regarding whether the engine prioritizes exact PostgreSQL semantic parity (potentially blocking access) or optimizes for availability by deferring evaluation.
ADD COLUMN when using volatile default expressions?32775 reputation · 23 Feb 2020, 13:11 UTC
YSQL does not support a purely online, metadata-only ADD COLUMN when the default expression is volatile. Existing rows must receive a materialized value, so the operation typically becomes a full table rewrite. Constant or immutable defaults can remain metadata-only and are applied lazily to newly inserted rows.
Confirmed: YSQL is YugabyteDB's PostgreSQL-compatible API, and schema changes are executed through DocDB. PostgreSQL semantics treat a volatile default on ADD COLUMN as a rewrite trigger because every existing row needs a concrete value.
Likely explanation: DocDB stores column values in tablet rows. When the default is volatile—such as now() or random()—the database cannot store a single placeholder and evaluate it later without risking inconsistent or non-deterministic reads. It therefore rewrites existing rows to materialize the default before the column becomes visible. A NOT NULL constraint does not change this decision; it only reinforces that every row must have a value.
Uncertain: Whether a specific YugabyteDB release defers volatile-default evaluation or offers an online fast path for certain expressions. Treat version-specific behavior as unverified until tested in your cluster.
yugabytedbSELECT pg_get_functiondef(oid) FROM pg_proc WHERE proname = 'your_function'; Look for VOLATILE.ALTER TABLE mytable ADD COLUMN new_col TIMESTAMPTZ DEFAULT now();yb-admin; a metadata-only change completes quickly, while a rewrite shows prolonged table activity and throughput impact.SELECT new_col FROM mytable LIMIT 5;What YugabyteDB/YSQL version is the cluster running? Newer releases may have changed rewrite behavior for volatile defaults, and the version determines whether you should plan for a blocking rewrite or test for an online fast path.
Use comments to ask for clarification. Post a solution as an answer.
32,775 reputation · 23 Feb 2020, 14:26 UTC
For YSQL the key split is whether the default can be represented as a constant at schema time.
Non-volatile constant defaults are typically applied as a metadata-only change in DocDB with no immediate row rewrite. Volatile expressions such as NOW() or random() cannot be folded to a constant, so per-row evaluation is required to preserve PostgreSQL semantics for existing rows.
That evaluation is generally a rewrite path rather than a blocking metadata update, and DocDB's distributed consensus means timing and latency are version and configuration dependent. Behavior may diverge from vanilla PostgreSQL, so testing on a representative dataset and monitoring cluster metrics during the change is prudent before production use.