SQL migrations in PostgreSQL, part 1

How do you update an attribute value in all table rows? How do you add a primary or a unique key to a table? How do you split a table in two? How do you…

If you accept to factor in some downtime while you’re performing migrations, then the answers to these questions are easy. But what if you need to carry out migrations on the fly, without stopping the database, and without interfering with other people’s work?

In this article, we try to give practical answers to these and other questions related to schema and data migrations in PostgreSQL.

It’s a lot of information, so we’re splitting the article in two:

• Base migrations
• Approaches to updating large tables

At the end of the article, you can find a cheat sheet for the entire piece.

Contents

Task definition
Adding a column
Adding a column with a default value
Deleting a column
Creating an index
Creating an index for a partitioned table
Creating a NOT NULL constraint
Creating a foreign key
Creating a unique constraint
Creating a primary key
A quick cheat sheet with migrations

Task definition

Let’s suppose that we’re running an application that works with a database. In a minimal configuration, it may consist of two nodes: the application, and the database.

In such a configuration, application updates often lead to downtime, which we can use also to update the database. Time is the crucial factor here: the migration should take as little time as possible to minimize service downtime.

As the application grows in size and complexity, it may become necessary to release without the downtime and to use multiple application servers. There can be as many servers as we want, and they run different versions of the code. In this case, it is essential to ensure backward compatibility.

The application keeps growing, and a single database is no longer enough to store all the data. Now it’s time to scale the database as well by sharding it. It’s very difficult to apply migrations to multiple databases synchronously; at some point, they may have different data schemas. Therefore, we’ll work in a mixed environment, where application servers may have different code, and databases may have different data schemas.

This is the kind of configuration that we’re focusing on in this article. We’ll look at some of the most popular migrations that developers work on, from simple to more complex ones.

Basically, we want to perform SQL migrations with minimum impact on the application, i.e., we want to change the data or the data schema while the application keeps running, and without affecting users.

Adding a column

ALTER TABLE my_table ADD COLUMN new_column INTEGER -- fast and safe

Probably, anyone who works with databases has written such a migration. As for PostgreSQL, this migration is cheap and safe. Even though the command acquires the highest-level lock (AccessExclusive), it executes fast: it only adds the new column metadata, without overwriting the table. In most cases, the process doesn’t cause any noticeable ripples. However, trouble may be brewing if long transactions are running on the table while the migration is taking place. To understand the nature of the problem, let’s take a quick look at how locks work in PostgreSQL. This is important when reviewing most of the following migrations.

Let’s suppose that we have a large table, and that we want to perform a SELECT query on all the data it holds. Depending on the sizes of the database and the table, it can take from several seconds to some minutes.

When the transaction starts executing, the weakest AccessShare lock is acquired to prevent changes to the table structure.

At the same time, another transaction starts executing; it tries to run an ALTER TABLE query on the same table. As we’ve seen, the ALTER TABLE command acquires an AccessExclusive lock that is incompatible with any other locks. Therefore, this transaction is queued.

Queued queries are executed based on their order in the queue, regardless of them acquiring blocking or non-blocking locks. For instance, if a few SELECT queries start executing after the ALTER TABLE query, they will be queued even though they do not conflict with the currently running SELECT query. As a result, the ALTER TABLE query acts as a bottleneck for the application, until it is executed.

What can we do in this situation? We can limit the locking time with the SET lock_timeout command: run it before the ALTER TABLE query. The LOCAL keyword applies the setting only to the current transaction. Without this keyword, the setting applies to the current session.

SET LOCAL lock_timeout TO '100ms'

If the query SET lock_timeout refers to can’t acquire a lock within 100 milliseconds, it fails. Then we can either run it again, expecting it to execute correctly; or we can try to find out why it’s taking so long when it shouldn’t. What matters here is that we don’t negatively impact the application. Another option is using SET statement_timeout, which sets the timeout for the whole query execution, including locking time.

Usually, it’s good practice to set a timeout for any command that acquires a strict lock.

Adding a column with a default value

-- fast and safe since PostgreSQL 11 
ALTER TABLE my_table ADD COLUMN new_column INTEGER DEFAULT 42

Executing this command in PostgreSQL before version 11 overwrites all table rows. If it runs on a large table, it can take a long time. And since the command acquires a strict lock (AccessExclusive), any other queries on the table are blocked.

Since PostgreSQL 11, this operation has become much cheaper. Now the default value is saved to the pg_attribute catalog. When a SELECT query executes, all empty values in the column are replaced on the fly with the value from the pg_attribute. Then, when rows in the table are overwritten due to any modifications, the default value is also written to those rows.

Moreover, since version 11 you can also create a new column, and mark it as NOT NULL right away:

-- fast and safe since PostgreSQL 11
ALTER TABLE my_table ADD COLUMN new_column INTEGER DEFAULT 42 NOT NULL

What if PostgreSQL is older than version 11?

You can split the migration in several steps. First, create a new column with no restrictions and no default values. This is both cheap and fast. In the same transaction, modify this column by adding a default value.

ALTER TABLE my_table ADD COLUMN new_column INTEGER; 
ALTER TABLE my_table ALTER COLUMN new_column SET DEFAULT 42;

Splitting one command into two may look awkward at first. However, the point is that the mechanism to create a new column with a default value immediately affects all existing records in the table, while setting a value for an existing column (even if it has just been created, as in this case) affects only new records.

After running these commands, we only need to update the values that were already in the table. In a nutshell:

UPDATE my_table set new_column = 42 -- not safe for a large table

However, running UPDATE like this may backfire: updating a large table would lock the entire table for a long time. In the second part of this article (there will be a link later on), we’ll examine some strategies to update large tables in PostgreSQL; for now, let’s assume that we somehow updated the data, and now both old and new data have the required default value.

Deleting a column

ALTER TABLE my_table DROP COLUMN new_column -- fast and safe

The underlying logic here is the same as when adding a column: table data is not modified right away, we change only the metadata. When carrying out a deletion, the column is marked as deleted, and it’s not possible to run queries on it. This is why deleting a column in PostgreSQL doesn’t release disk space, unless you also run VACUUM FULL: old data remains stored in the table, but it becomes inaccessible. The space it takes frees up gradually, as data in table rows gets overwritten.

The migration in itself is quite simple, but sometimes mistakes can occur on the application side. To avoid that, carry out a few simple preliminary steps before deleting a column.

• Remove any constraints (NOT NULL, CHECK, …) applied to the column:
  ALTER TABLE my_table ALTER COLUMN new_column DROP NOT NULL
• Then, ensure back-end compatibility. Make sure that the column isn’t being used anywhere. For example, in Hibernate you need to tag the field with the @Transient annotation. We use JOOQ, which features a special <excludes> tag to add fields to exceptions:
  <excludes>my_table.new_column</excludes>
  You should also carefully inspect all SELECT * queries, as frameworks may map all columns to the structure in the code (and vice versa); you may again encounter the problem of referring to a column that doesn’t exist.

After applying the changes to all application servers, it’s safe to delete the column.

Creating an index

CREATE INDEX my_table_index ON my_table (name) -- not safe, it locks the table

If you work with PostgreSQL, you probably know that this command locks the entire table. However, since version 8.2 the special keyword CONCURRENTLY enables creating an index in non-blocking mode.

CREATE INDEX CONCURRENTLY my_table_index ON my_table (name) -- safe

The execution is slower, but it doesn’t interfere with concurrent queries.

There’s a little twist to this command. It may fail; for example, when creating a unique index in a table containing duplicate values. The index is created, but it’s marked as invalid, and it’s not available for querying. You can check the status of the index with the following query:

SELECT pg_index.indisvalid     
  FROM pg_class, pg_index 
WHERE pg_index.indexrelid = pg_class.oid     
  AND pg_class.relname = 'my_table_index'

In such a case, delete the old index, fix the values in the table, and then rebuild the index.

DROP INDEX CONCURRENTLY my_table_index 
UPDATE my_table ... 
CREATE INDEX CONCURRENTLY my_table_index ON my_table (name)

REINDEX

The REINDEX command deserves a special mention, since it’s designed exactly to recreate the index. In the versions before 12, it operates in blocking mode only, which makes it difficult to use. From PostgreSQL 12, the command supports the CONCURRENTLY keyword, which makes its usage more convenient.

REINDEX INDEX CONCURRENTLY my_table_index -- since PostgreSQL 12

It’s important to point out that since REINDEX does the same thing as CREATE INDEX CONCURRENTLY + SWAP + DROP INDEX CONCURRENTLY, it may also end up failing and leaving behind invalid indexes.

• If an error occurs while creating a new index, _ccnew is appended to the index name. The recommended way to fix it is to delete the index and to run REINDEX again.
• If _ccold is appended to the index name, it means that an error occurred while deleting the old index. In this case, delete this index, as the new index was built successfully.

REINDEX CONCURRENTLY for tables, schemas, and such works slightly differently: invalid indexes are ignored to avoid generating even more of them.

You can learn more about the REINDEX mechanism in the PostgreSQL official documentation.

Creating an index for a partitioned table

Now let’s look at creating indexes for partitioned tables. There are two types of partitioning in PostgreSQL: table inheritance, and declarative partitioning, introduced in version 10. Let’s review both using a simple example.

Let’s suppose that we want to partition a table by date, where each partition contains the equivalent of one year of data.

When partitioning through inheritance, the schema looks like this:

Parent table:

CREATE TABLE my_table (
    ...
    reg_date   date not null
)

Partitions for the years 2020 and 2021:

CREATE TABLE my_table_y2020 (
CHECK ( reg_date >= DATE '2020-01-01' AND reg_date < DATE '2021-01-01' ))
INHERITS (my_table);

CREATE TABLE my_table_y2021 (
CHECK ( reg_date >= DATE '2021-01-01' AND reg_date < DATE '2022-01-01' ))
INHERITS (my_table);

Partition field indexes for each partition:

CREATE INDEX ON my_table_y2020 (reg_date);
CREATE INDEX ON my_table_y2021 (reg_date);

For now, let’s leave out creating a trigger/rule to insert data in the table.

The most important thing here is that each partition is basically an almost independent table, and it’s processed separately. Therefore, creating new indexes is the same as creating for regular tables:

CREATE INDEX CONCURRENTLY my_table_y2020_index ON my_table_y2020 (name); 
CREATE INDEX CONCURRENTLY my_table_y2021_index ON my_table_y2021 (name);

Now let’s look at declarative partitioning:

CREATE TABLE my_table (...) PARTITION BY RANGE (reg_date); 
CREATE TABLE my_table_y2020 PARTITION OF my_table FOR VALUES FROM ('2020-01-01') TO ('2020-12-31'); 
CREATE TABLE my_table_y2021 PARTITION OF my_table FOR VALUES FROM ('2021-01-01') TO ('2021-12-31');

Index creation varies, based on the PostgreSQL version in use. In version 10, indexes are created individually, just like in the previous approach. Creating new indexes for existing tables works in the same way.

In version 11, declarative partitioning is improved, and tables are processed together. Creating an index on the parent table automatically results in creating indexes for all existing and new partitions that will be created in the future:

-- since PG 11 it’s convenient for a new (empty) partitioned table
CREATE INDEX ON my_table (reg_date)

It’s convenient when creating a partitioned table, but it’s not viable when creating a new index for an existing table, because this command acquires a strict lock on the table while the indexes are being created.

CREATE INDEX ON my_table (name) -- it locks the table

Unfortunately, CREATE INDEX does not support the CONCURRENTLY keyword for partitioned tables. To bypass this restriction, and to perform a migration without locks, you can do the following:

1. Create an index for the parent table with the ONLY option:
CREATE INDEX my_table_index ON ONLY my_table (name)
This generates an empty invalid index without creating indexes for the partitions.

2. Create indexes for each partition:
CREATE INDEX CONCURRENTLY my_table_y2020_index ON my_table_y2020 (name);
CREATE INDEX CONCURRENTLY my_table_y2021_index ON my_table_y2021 (name);

3. Attach partition indexes to the parent table index:
ALTER INDEX my_table_index ATTACH PARTITION my_table_y2020_index;
ALTER INDEX my_table_index ATTACH PARTITION my_table_y2021_index;
Once all indexes are attached, the index of the parent table automatically becomes valid.

Constraints

Now let’s go over constraints: NOT NULL, foreign, unique, and primary keys.

Creating a NOT NULL constraint

ALTER TABLE my_table ALTER COLUMN name SET NOT NULL -- it locks the table

Creating a constraint this way causes the entire table to be examined: all rows are checked for the NOT NULL condition. If it’s a large table, it can take a long time. This query acquires a strict lock, which blocks all concurrent queries until the initial query has been completed.

What options do we have? PostgreSQL features another type of constraint: CHECK. This constraint can help us. The constraint checks for any boolean conditions composed of columns of a row. In our case, the condition is trivial: CHECK (name IS NOT NULL). Most importantly, the CHECK constraint supports the NOT VALID option:

ALTER TABLE my_table ADD CONSTRAINT chk_name_not_null      
  CHECK (name IS NOT NULL) NOT VALID -- safe since PostgreSQL 9.2

This constraint applies only to newly added and to modified records — existing ones are not affected, so it doesn’t examine the whole table.

To ensure that existing records also meet the constraint, it is necessary to validate it (of course, after updating the data in the table):

ALTER TABLE my_table VALIDATE CONSTRAINT chk_name_not_null

This command iterates through table rows and checks that all records are not-null. But unlike the usual NOT NULL constraint, the lock that this command acquires is not very strict (ShareUpdateExclusive); it doesn’t block insert, update, and delete operations.

Creating a foreign key

ALTER TABLE my_table ADD CONSTRAINT fk_group      
  FOREIGN KEY (group_id) REFERENCES groups(id) -- locks both the tables

When a foreign key is added, all the entries in the child table are scanned for a value from the parent table. If it’s a large table, the scan can take a long time, and both tables are locked while the operation is in progress.

Fortunately, foreign keys in PostgreSQL also support the NOT VALID option; therefore, we can use the same approach that we previously discussed with CHECK. Let’s create a non-valid foreign key:

ALTER TABLE my_table ADD CONSTRAINT fk_group      
  FOREIGN KEY (group_id) REFERENCES groups(id) NOT VALID

Then, after updating the data, let’s perform validation:

ALTER TABLE my_table VALIDATE CONSTRAINT fk_group_id

Creating a unique constraint

ALTER TABLE my_table ADD CONSTRAINT uk_my_table UNIQUE (id) -- it locks the table

As with the previously discussed constraints, this command acquires a strict lock while it checks all rows in the table against the specified constraint — in this case, uniqueness.

The implementation of unique constraints in PostgreSQL is built on unique indexes. In other words, when a constraint is created, a corresponding unique index with the same name is also created to serve the constraint. You can find the corresponding index of the constraint with the following query:

SELECT conindid index_oid, conindid::regclass index_name
  FROM pg_constraint
WHERE conname = 'uk_my_table_id'

Most of the time required to create a constraint is spent on the index, while its subsequent binding to the constraint is relatively fast. If you already have a unique index, you can bind it manually by adding a constraint with the USING INDEX keywords:

ALTER TABLE my_table ADD CONSTRAINT uk_my_table_id UNIQUE
  USING INDEX uk_my_table_id -- fast since PostgreSQL 9.1

The basic idea is simple: let’s create a unique index CONCURRENTLY, as we discussed earlier, and then use it to create a unique constraint.

At this point, you may wonder why we should create a constraint at all, if the index does exactly what we want it to do — ensure that values are unique. If we exclude partial indexes from the comparison, then from some point of view the result is really almost identical. The only difference we could point out is that constraints can be deferrable, while indexes can’t. In the documentation about older versions of PostgreSQL (up to and including 9.4), there was a footnote stating that the preferred way to create a uniqueness constraint was to explicitly create an ALTER TABLE … ADD CONSTRAINT, while the use of indexes should be considered as an implementation detail. However, in more recent versions this footnote is no longer there.

Creating a primary key

Besides uniqueness, a primary key also imposes a NOT NULL constraint. If the column initially had this restriction, it’s not difficult to turn it into a primary key — just create a unique index CONCURRENTLY, and then create a primary key:

ALTER TABLE my_table ADD CONSTRAINT uk_my_table_id PRIMARY KEY
  USING INDEX uk_my_table_id -- if id is NOT NULL

The column must have an “honest” NOT NULL constraint. The CHECK workaround discussed earlier won’t work.

If the column doesn’t have the required constraint, and if we’re using PostgreSQL versions before 11, we can’t create a primary key without locking the table.

But if we’re on PostgreSQL 11 or later, we can solve the problem by creating a new column to replace the existing one. Let’s go through it step by step.

Create a new column that is not-null by default, and that has a default value:

ALTER TABLE my_table ADD COLUMN new_id INTEGER NOT NULL DEFAULT -1 
-- safe since PostgreSQL 11

Set up data synchronization of the old and the new columns with a trigger:

CREATE FUNCTION on_insert_or_update() RETURNS TRIGGER AS
$$
BEGIN
    NEW.new_id = NEW.id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg BEFORE INSERT OR UPDATE ON my_table
FOR EACH ROW EXECUTE PROCEDURE on_insert_or_update();

Then, update the data of the rows that were not affected by the trigger:

-- don’t do that on a large table
UPDATE my_table SET new_id = id WHERE new_id = -1

The update query above is very straightforward, and it’s better not to use it on a large table because it keeps the table locked for a long time.

As mentioned earlier, in the second part of this article we’ll discuss approaches to update large tables. For now, let’s assume that the data is updated, and that all we need to do is to swap the columns.

ALTER TABLE my_table RENAME COLUMN id TO old_id; 
ALTER TABLE my_table RENAME COLUMN new_id TO id; 
ALTER TABLE my_table RENAME COLUMN old_id TO new_id;

DDL commands in PostgreSQL are transactional, which means that you can rename, add, and delete columns without a concurrent transaction knowing about it during its execution.

After swapping the columns, all we need to do is create an index and clean up: delete the trigger, the function, and the old column.

A quick cheat sheet with migrations

It’s advisable to invoke a lock timeout before any queries that acquire a strict lock (almost all ALTER TABLE … queries):

SET LOCAL lock_timeout TO '100ms'

In the second article, we’ll discuss approaches to update large tables. Enjoy easy migrations everyone!

Join our team!

Would you like to be an Engineer at Miro? Check out opportunities to join the Engineering team.