Answer.

Background

Changing a table schema has become relevant with the widespread adoption of Agile methodologies. Projects evolve, requirements change — over time, there is always a need to add/modify/remove columns. Such changes are particularly risky in production databases.

Problem

Modifying the structure can lead to:

long-term locks
loss or incorrect migration of old data
violation of external constraints, triggers, or application logic

Changing large tables (millions of rows) that are actively used by other services is especially complicated.

Solution

Proper work through ALTER TABLE — phased changes, data backup, testing on a staging environment, and limiting downtime. Use of transactions, incremental migration, and backups before significant changes. In high-load databases, "online" ALTER algorithms are often utilized.

Sample code:

-- Adding a new column with a default value
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'new';

-- Gradually filling the new columns
UPDATE orders SET status = CASE WHEN shipped_at IS NOT NULL THEN 'shipped' ELSE 'pending' END;

Key features:

It’s preferable to create new columns first, then gradually transfer data
Large operations should be carried out outside peak hours
Always perform backups and automated testing

Tricky Questions.

Is ALTER TABLE executed atomically?

Most often no: changing a table can take a long time. In case of failure, part of the changes may roll back, but part may remain pending. Therefore, transactional protection for DDL commands is implemented only by some databases (e.g., PostgreSQL).

Can you change a column type from INTEGER to VARCHAR painlessly?

Not always: if there are old data in the column that do not match the new format, or related objects (indexes, triggers, keys), the database may not allow the type change, or the data may be corrupted.

Does ALTER TABLE always impose an exclusive lock on the entire table?

It depends on the database: in MySQL and older versions of SQL Server, any ALTER operation often completely locks the table until completion, but modern databases support "online DDL," reducing lock time.

Common Mistakes and Anti-Patterns

Changing structure without backup
Migrating large tables without testing on a staging environment
Renaming columns without checking for dependencies (e.g., foreign keys, procedures)
Mass ALTER during peak load hours

Real-life Example

Negative Case

A DevOps engineer made mass changes to three important tables via ALTER TABLE and removed old columns. Did not consider that these columns were bound by foreign keys and triggers. During the ALTER operation, the database was busy for 20 minutes — during this time, services "went down" due to the absence of required fields.

Pros:

Changes were implemented according to specifications

Cons:

Loss of functionality for some services
Business downtime of nearly half an hour
Labor-intensive restoration of dependencies and return of deleted data

Positive Case

An analyst planned the addition of a column in several stages: first, they created a column with a default value, loaded a test load on copies, and only then made the actual ALTER at night, notifying all developers about the upcoming migration window.