AI Assisted

Introduction

Adding a new column is often considered a routine schema change. In PostgreSQL, the SQL statement itself is straightforward, and modern versions can perform many column additions as metadata-only operations. Because of this, developers frequently assume that adding a nullable column is essentially free.

The hidden cost, however, is not always the data modification. It is the lock required before PostgreSQL can modify the table definition. On a heavily trafficked table containing hundreds of millions of rows, acquiring that lock can become the most dangerous part of the migration.

The First Thing PostgreSQL Does Is Acquire an Exclusive Lock

Before PostgreSQL changes a table’s schema, it acquires an ACCESS EXCLUSIVE lock on the table. This is the strongest table-level lock in PostgreSQL and conflicts with every other lock mode.

As a result, while the lock is held:

  • SELECT queries are blocked.
  • INSERT, UPDATE, and DELETE statements are blocked.
  • Other schema changes are blocked.
  • Any transaction attempting to access the table must wait.

Even if the actual schema modification takes only a few milliseconds, PostgreSQL cannot begin the operation until it successfully acquires this lock.

The Real Problem Is Waiting for the Lock

In production systems, there is almost always at least one transaction already using the table. It may be a business transaction, a reporting query, a batch job, or an application request that unexpectedly runs for several seconds.

Suppose a transaction has been reading from the orders table for thirty seconds. When an ALTER TABLE statement is issued, PostgreSQL cannot immediately acquire the required ACCESS EXCLUSIVE lock and must wait for the existing transaction to finish.

This is where the migration becomes dangerous. While the ALTER TABLE statement is waiting, every new query that requires the table begins to queue behind it. PostgreSQL’s lock manager preserves lock ordering, meaning subsequent requests cannot bypass the pending exclusive lock request. What began as a single long-running transaction can quickly develop into a lock queue affecting hundreds or even thousands of application requests.

How a Single Long Transaction Can Cascade into an Outage

The database itself is usually not frozen, but one busy table effectively becomes unavailable. Application requests continue arriving, yet each request that needs the locked table waits for the exclusive lock to be resolved.

As waiting requests accumulate, database connections remain occupied for longer periods. Eventually, the application’s connection pool becomes exhausted because every connection is blocked waiting for the same table. Once no connections remain available, new requests begin to time out, error rates increase, and the application may appear to be completely unavailable despite PostgreSQL continuing to run normally.

This cascading failure is often far more damaging than the schema change itself.

Why Large Tables Increase the Risk

A table containing 300 million rows is not necessarily slower to add a nullable column in modern PostgreSQL. However, it is typically one of the busiest tables in the system. High request volume increases the probability that long-running transactions already exist when the migration begins, making it more difficult to obtain the required exclusive lock without disrupting production traffic.

For this reason, migration risk is often driven more by concurrency than by table size alone.

A Safer Migration Strategy

Before executing any schema change on a high-traffic table, first identify long-running transactions using PostgreSQL monitoring views such as pg_stat_activity. Schedule migrations during periods of lower traffic whenever possible, and configure an appropriate lock_timeout so that the migration fails quickly rather than waiting indefinitely for an exclusive lock.

Second, add the new column as nullable without a default value whenever possible. This minimizes the amount of work required by the database engine and reduces the likelihood of long-running locks.

Next, deploy an application version that understands both the old and new schema. New records should populate the new column, while existing records continue to function correctly even if the column is still NULL. This approach maintains backward compatibility and allows deployment to be rolled back independently of the database migration.

After the application has been writing the new column successfully, perform a gradual backfill. Rather than updating all 300 million records in a single transaction, process the data in small batches. Between batches, monitor database CPU utilization, disk I/O, replication lag, lock contention, and application latency. The objective is to spread the workload over time without degrading production performance.

Finally, once every existing row has been populated and the application no longer depends on NULL values, enforce additional constraints such as NOT NULL if required. By separating structural changes from data migration, each deployment step remains smaller, safer, and easier to recover from.

Lessons Learned

When engineers think about adding a column, they often focus on how long PostgreSQL needs to modify the table. In practice, the greater risk is usually how long PostgreSQL waits to acquire the required ACCESS EXCLUSIVE lock. A migration that executes in milliseconds can still trigger application-wide failures if it becomes blocked behind a long-running transaction on a heavily used table.

Understanding PostgreSQL’s locking behavior is therefore essential for designing safe, zero-downtime schema migrations. In production systems, managing concurrency is frequently more important than the SQL statement itself.