A one-line SQL command should take a fraction of a second. On a table with 500 million rows and a dozen active connections, that same command can hold an exclusive lock for hours, blocking every read and write until it finishes. The operation looks simple. The mechanics are not.
1. The Lock That Freezes Everything
Most databases, including PostgreSQL and MySQL, require an ACCESS EXCLUSIVE lock to alter a table’s structure. This is the most aggressive lock available. It blocks every other operation, including reads, until it’s released. Dropping a column is a structural change, so it demands this lock.
The problem compounds when you have long-running transactions. Before your ALTER TABLE can acquire its lock, it has to wait for every existing transaction touching that table to finish. Meanwhile, every new query that arrives after your ALTER also waits. Within seconds, you have a queue. Within minutes, connection pools exhaust, timeouts cascade, and your application is effectively down.
This is why engineers who have been through it once treat schema changes with the same care as a production deployment.
2. The Data Doesn’t Actually Disappear Immediately
Dropping a column does not instantly reclaim disk space. In PostgreSQL, the column is marked as invisible to new queries, but the data sits in existing rows on disk until those rows are rewritten. That rewrite happens during a VACUUM FULL or when rows are updated individually over time. On a heavily written table, this self-healing process happens organically. On a mostly-read table, the dead column bytes can linger for months.
MySQL with InnoDB used to handle this even more aggressively, requiring a full table rebuild for any column removal. Older versions would copy the entire table to a temporary file, row by row, before swapping it in. On a 200GB table, that’s a multi-hour operation with the original table locked for writes the entire time.
Modern MySQL (5.6 and later) introduced Online DDL, which reduces but does not eliminate this cost. The details of what requires a rebuild versus what doesn’t fill several pages of the MySQL documentation.
3. Indexes Don’t Know the Column Is Gone
If the column you’re dropping is part of an index, the database must drop that index first or simultaneously. Index maintenance is not cheap. A B-tree index on a 500-million-row table can itself take tens of minutes to drop because the database has to update internal bookkeeping and free pages in an orderly sequence.
Composite indexes create a subtler problem. If your column appears as the second or third field in a multi-column index, the database may need to rebuild that index entirely, not just trim it. Developers often forget these dependencies exist because the index was built years ago by someone who has since left.
Foreign keys are worse. If another table has a foreign key referencing the column you’re dropping, many databases will refuse the operation outright until you explicitly drop the constraint. Discovering this at 2 AM during a maintenance window is a specific kind of misery.
4. Replication Lag Multiplies the Damage
In any setup with read replicas, a long-running DDL statement doesn’t just block the primary. It replicates. The same operation, with the same blocking behavior, runs on every replica, sequentially in most configurations. If your primary takes 90 minutes, your replicas can fall 90 minutes behind.
Applications that route reads to replicas will start serving stale data. Monitoring systems will alert. On-call engineers will wake up. The domino effect from one dropped column can span an entire infrastructure.
This is why high-availability teams at companies running large databases keep explicit runbooks for schema changes, separate from general deployment procedures. The operational surface area is genuinely different.
5. The Safe Way Is Deliberately Slow
The standard industry approach to dropping columns on large tables is to do almost nothing right away. Step one: stop writing to the column in application code. Step two: remove it from all read paths. Step three: wait. Let the column become genuinely unused, which you can verify through query logging. Step four, often weeks later: drop the column during a low-traffic window, with a lock timeout set so the operation aborts rather than queue indefinitely if it can’t acquire the lock promptly.
Tools like gh-ost (GitHub’s online schema change tool) and pt-online-schema-change from Percona exist precisely because this problem is widespread enough to warrant dedicated infrastructure. They work by creating a shadow copy of the table, applying changes there while syncing writes, then doing a fast table swap. The lock window shrinks from hours to seconds. GitHub built gh-ost internally because the alternatives weren’t reliable enough at their scale.
6. The Column You Drop Today Was Load-Bearing Yesterday
Data infrastructure accumulates dependencies the way old buildings accumulate load-bearing walls. The column you want to remove might be unused in your main application and still queried daily by a BI tool, a data pipeline, or a scheduled report that nobody updated when the feature was deprecated. These consumers don’t show up in your application code review.
Searching for column references in a single codebase is straightforward. Finding every place a column is used across microservices, third-party integrations, data warehouse sync jobs, and internal analytics queries is genuinely hard. Teams have dropped columns and broken dashboards that finance depended on, because the connection between the two was never documented.
The lesson most engineering teams arrive at eventually: treat column removal like removing a production dependency. Audit broadly, communicate widely, and build in more lead time than you think you need. The database will forgive you for being cautious. It won’t forgive you for being hasty.
