Safe Migrations For PostgreSQL

Locking Mechanism

In Postgres, each query involves a locking mechanism to ensure data consistency and integrity. This locking is crucial because it prevents multiple transactions from simultaneously modifying the same data, which could lead to conflicts or corruption.

During migrations, DDL (Data Definition Language) queries such as ALTER TABLE can lock tables or rows to make schema changes. Many ALTER TABLE operations require an ACCESS EXCLUSIVE lock, which is the most restrictive lock mode in Postgres. An ACCESS EXCLUSIVE lock prevents all other operations on the table, including SELECT, INSERT, UPDATE, and DELETE queries. This means that while an ALTER TABLE operation is in progress, other queries that need to access or modify the table will be blocked.

Even simple migrations on small tables can lead to significant table locking. For instance, if a long-running query is already accessing the table, an ALTER TABLE statement will have to wait until the long-running query completes before it can proceed. Conversely, while the ALTER TABLE operation holds the ACCESS EXCLUSIVE lock, all other business logic queries that attempt to access the table will be delayed until the ALTER TABLE operation finishes.

For more details on Postgres’s locking mechanisms, refer to the Postgres documentation on locking.

Understanding this locking mechanism is crucial for planning migrations to minimize disruptions to business logic queries. By scheduling migrations during low-traffic periods or using techniques to reduce lock contention, you can mitigate some of the impacts. While Postgres offers mechanisms to facilitate smoother migrations, establishing an efficient process can involve numerous downtimes, months of adapting solutions, providing training, and conducting postmortems. Our solution is designed to avoid downtime by automating the understanding of locking mechanisms and applying safe query alternatives.

Operations

The examples on this page illustrate scenarios where DDL migrations and business logic queries are executed concurrently. They show how migrations can impact the performance and behavior of business logic queries.

The examples use schemas with 5-100 million records to demonstrate the effects of these operations. In real-world situations, larger datasets, more columns, and higher levels of concurrency can significantly worsen than those shown in the examples.

Here’s a sample schema used in the examples:

CREATE TABLE parent (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    uuid UUID
);
CREATE TABLE main (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    uuid UUID, parent_id integer
);
CREATE TABLE child (
    id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    uuid UUID,
    main_id integer
);

In the screenshots, you can see four panels:

Add Column Check

When we add a column with a check constraint, Postgres creates the column itself with the constraint and validates the constraint under the hood. Check constraint validation is a data-intensive read operation that takes some time, during which you cannot perform any operations on this table until the operation is finished.

Our solution detects this case and splits it into several safe queries.

Add Column Foreign Key

When we add a column with a foreign key constraint, Postgres creates the column itself with the constraint and validates the constraint under the hood. Foreign key constraint validation is a data-intensive read operation that takes some time, during which you cannot perform any operations on this table until the operation is finished. The most interesting fact about foreign key constraints is that they lock two tables.

Our solution detects this case and splits it into several safe queries.

Add Column Unique

When we add a column with a unique constraint, Postgres creates the column itself and a constraint with a unique index under the hood. Creating an index is a data-intensive read and write operation that takes some time, during which you cannot perform any operations on this table until the operation is finished.

Our solution detects this case and splits it into several safe queries.

Set Column Not Null

When we add a column with a NOT NULL constraint, Postgres creates and validates the constraint under the hood. NOT NULL constraint validation is a data-intensive read operation that takes some time, during which you cannot perform any operations on this table until the operation is finished.

Our solution detects this case and splits it into several safe queries.

Add Constraint Check

When we add a check constraint, Postgres creates and validates the constraint under the hood. Check constraint validation is a data-intensive read operation that takes some time, during which you cannot perform any operations on this table until the operation is finished.

Our solution detects this case and splits it into several safe queries.

Add Constraint Foreign Key

When we add a foreign key constraint, Postgres creates and validates the constraint under the hood. Foreign key constraint validation is a data-intensive read operation that takes some time, during which you cannot perform any operations on this table until the operation is finished. The most interesting fact about foreign key constraints is that they lock two tables.

Our solution detects this case and splits it into several safe queries.

Add Constraint Unique

When we add a unique constraint, Postgres creates a constraint with a unique index under the hood. Creating an index is a data-intensive read and write operation that takes some time, during which you cannot perform any operations on this table until the operation is finished.

Our solution detects this case and splits it into several safe queries.

Create Index

Creating an index is a data-intensive read and write operation that takes some time, during which you cannot perform write operations on this table until the operation is finished.

Our solution detects this case and adds the CONCURRENTLY keyword for index creation, making the query safe.