Questions: SQL: ALTER TABLE and Schema Modification

Question 1

Your team wants to add a NOT NULL column with a DEFAULT value to a 50-million-row orders table during peak business hours. Which outcome should you expect?

• A. The operation completes instantly because adding a default is a metadata-only change
• B. The operation rewrites every row to store the default value, locking the table for potentially minutes and blocking all reads and writes
• C. The operation fails immediately because NOT NULL columns cannot have defaults
• D. The operation succeeds but existing rows get NULL instead of the default value

Answer: (B)

Adding a column with a DEFAULT value on many older or certain database configurations requires rewriting every existing row to store the default, taking an exclusive lock for the duration. On a 50-million-row table this can block all queries for minutes. Modern PostgreSQL (v11+) handles this specific case more efficiently for volatile defaults, but behavior varies by engine and version. The practical lesson is: always test ALTER TABLE operations on production-sized data, and plan schema changes for maintenance windows when they involve full rewrites.

Question 2

An ALTER TABLE command attempts to change a column from VARCHAR(100) to VARCHAR(50). Under what condition will this operation fail?

• A. Always — data type changes require dropping and recreating the table
• B. Never — the database silently truncates values that exceed the new length
• C. Only if the column has a foreign key referencing another table
• D. If any existing row contains a value longer than 50 characters in that column

Answer: (D)

Narrowing a data type is a constraint tightening: the database must verify that all existing data fits the new type. If even one row has a 51-character value, the ALTER TABLE will fail with a constraint violation — it does not silently truncate. This is the general principle: any ALTER TABLE that imposes a new constraint (NOT NULL, shorter length, type change, foreign key) will fail if current data violates that constraint. The fix is either to clean the data first or to widen rather than narrow.

Question 3

An ALTER TABLE operation that adds a nullable column with no default value is always a fast, metadata-only change regardless of table size.

• T. True
• F. False

Answer: True

Adding a nullable column with no default does not need to touch existing rows — the database records the new column in the catalog and uses NULL as the implicit value for existing rows without actually rewriting storage. This is one of the few ALTER TABLE operations that is genuinely instant even on billion-row tables. The contrast with adding a NOT NULL column with a default (which may require rewriting rows) is an important practical distinction for production deployments.

Question 4

Dropping a column with ALTER TABLE DROP COLUMN is a reversible operation because the data remains in the table's storage pages until the next VACUUM.

• T. True
• F. False

Answer: False

From the application and SQL perspective, DROP COLUMN is irreversible. While some databases mark the column as invisible rather than immediately reclaiming storage space, the column is logically deleted and cannot be accessed or recovered through SQL. You cannot un-drop a column — once committed, the schema change and the data are gone (short of restoring from a backup). This makes dropping columns among the more dangerous ALTER TABLE operations, and naming your backup state before running it is essential practice.

Question 5

Why do some ALTER TABLE operations lock the table for seconds or minutes on large tables, while others complete almost instantly? What determines which category an operation falls into?

Model answer: The key distinction is whether the operation requires reading and rewriting existing rows (a table rewrite) or only updating the database catalog metadata. Operations like adding a nullable column with no default, renaming a column, or dropping a constraint are metadata-only — the database records the change in system tables and returns immediately. Operations like adding a NOT NULL constraint, adding a column with a default, changing a data type, or adding an index that must be built require scanning and sometimes rewriting every row, holding an exclusive lock for the duration.

Understanding this distinction is critical for production database management. The rule of thumb: anything that validates or transforms existing data requires a scan; anything that only changes how future writes are interpreted is metadata-only. Many production teams use tools like pg_repack or online schema change utilities precisely to avoid the table lock during large rewrites.