Safe Operations For High Volume PostgreSQL

Add a new column (safe)

In general, adding a new column is a safe operation that doesn't lock the table. However, there are cases where certain options of the command lock the table. These cases are quite common when working on an evolving product.

Add a new column with a non-nullable constraint / default value (unsafe)

This requires each row of the table to be updated so the default column value can be stored. The entire table is locked during this operation, depending on the size of the table, it can take a while and everything else comes to a halt. The solution is to add the new nullable column without a default value, backfill the desired value in batches, then add the non-nullable constraint/default value with an alter statement.

One big good news is that from PostgreSQL 11, adding a new column with default value will be painless, as it should have been :)

Add a default value to an existing column. (safe)

If the column is nullable previously, the default value won't alter those existing null rows. If the column is non-nullable, all rows are guaranteed to store some value. Either way, the operation does not block the table and is safe to execute.

Change the type of a column. (unsafe)

Strictly speaking, this operation locks the table and is therefore unsafe. If the underlining datatype is not changed, like increasing the length of varchar, then the table isn't locked. But if the change requires a rewrite/re-cast, each row of the table is to be updated and the table is locked for the same reason as adding a new column with a default value. The solution is to add a new column, backfill the new column, and get rid of the old one.

Add a Foreign Key (unsafe)

To protect data integrity, an AccessExclusiveLock is placed on both tables, grinds every read and write to a halt (PostgreSQL 9.6+ reportedly allows read). The solution is to take advantage of Postgres' ability to introduce invalid constraints. The procedure is to first create an invalid Foreign Key constraint by specifying NOT VALID in the ALTER TABLE statement, and then validate it in a separate VALIDATE CONSTRAINT statement. The second validation only requires a RowShareLock that doesn't block reads nor writes. Do note that if there is a reference to non-existing rows, the operation won't be completed and you have to take care of integrity on your own.

Add an index (unsafe)

By nature, the table being indexed is locked against writes and the entire index is built in a single scan of the table. Read transactions can still be done meanwhile.

For a production environment, it is always better to build the index without locking writes. CREATE INDEX comes with the CONCURRENTLY option for this purpose. Though building index is still extra work for the database and this is reflected on extra CPU and I/O load. This might still slow other operations, we notice an increased queue depth when we add index concurrently on one of the biggest tables in the system. Because our infrastructure is in the cloud, with a minimal budget, we can upsize the database a couple of sizes larger than normal. The extra power makes adding indices (still with the CONCURRENTLY option) a lot more comfortable and productive.

Add a column with a unique constraint (unsafe)

This operation will lock the table because it requires a scan for uniqueness. The solution is to add the column, add a unique index concurrently, and then add the constraint onto the table (ADD CONSTRAINT UNIQUE USING INDEX).

Drop a column / a constraint (safe)

This is safe and quick. If the operation appears to take time, the cause is more likely because the table/index was in use rather than the operation time itself. A quick check on pg_stat_activity should confirm that.

Rename an entity (safe)

The entity can be a table (or an index, sequence, or view) or a column in a table. This operation has no effect on stored data and therefore also isn't affected by the size of the data.

Turn on MultiAZ (unsafe)

This is particular to AWS RDS. AWS RDS MultiAZ setup is different from a typical read replica in nature, one is synchronous replication, the other is asynchronous. As such, naively turning on MultiAZ on a (high volume) write-intensive DB will lead to every single write operation being synchronously replicated to the standby replica. It would severely impact the performance of the master because initially the data on the slave machine would not satisfy many integrity constraints and result in either locks or scans on the master for missing data.

It is recommended to take advantage of the asynchronous nature of read replication to get around:

1. Create a Multi-AZ Read Replica of your Single AZ DB Instance and enable automated backups.
2. Once the read replica is synchronized, promote the read replica to be a standalone DB Instance.
3. Redirect your application to the newly promoted read replica.
4. Rename the source and the new instance to preserve the endpoint (optional).