Last active
December 13, 2019 16:33
-
-
Save siviae/0d7313d843565f9838f7b099a399ce6c to your computer and use it in GitHub Desktop.
PostgreSQL: add NOT NULL column faster with minimal locking
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/bin/sh | |
| # Inspired by https://medium.com/doctolib/adding-a-not-null-constraint-on-pg-faster-with-minimal-locking-38b2c00c4d1c | |
| if [ "$#" -ne 5 ]; then | |
| echo "This script generates safe not-null column addition SQL script for large tables" | |
| echo "Usage: $0 <table_name> <column_name> <column_type> <id_column_name> <default_value> > script.sql" >&2 | |
| exit 1 | |
| fi | |
| table_name=$1 | |
| column_name=$2 | |
| column_type=$3 | |
| id_column_name=$4 | |
| default_value=$5 | |
| cat <<EOF | |
| --create indexes and constraints for fast search and correctness | |
| ALTER TABLE ${table_name} | |
| ADD COLUMN ${column_name} ${column_type} DEFAULT $default_value; | |
| ALTER TABLE ${table_name} | |
| ADD CONSTRAINT chk_${column_name}_not_null CHECK (${column_name} IS NOT NULL) NOT VALID; | |
| CREATE INDEX CONCURRENTLY idx_${table_name}_null_${column_name} ON $table_name (${column_name} NULLS FIRST); | |
| --batch update column with default value | |
| DO | |
| \$\$ | |
| BEGIN | |
| WHILE (SELECT exists(select 1 from ${table_name} where ${table_name}.${column_name} IS NULL)) | |
| LOOP | |
| UPDATE ${table_name} | |
| SET ${column_name} = ${default_value} | |
| WHERE ${id_column_name} IN ( | |
| SELECT ${id_column_name} | |
| FROM ${table_name} | |
| WHERE ${table_name}.${column_name} IS NULL | |
| LIMIT 1000 | |
| ); | |
| COMMIT; | |
| END LOOP; | |
| END; | |
| \$\$; | |
| --set not null | |
| ALTER TABLE ${table_name} | |
| VALIDATE CONSTRAINT chk_${column_name}_not_null; | |
| ALTER TABLE ${table_name} | |
| ALTER COLUMN ${column_name} SET NOT NULL; | |
| -- clean up | |
| ALTER TABLE ${table_name} | |
| DROP CONSTRAINT chk_${column_name}_not_null; | |
| DROP INDEX CONCURRENTLY idx_${table_name}_null_${column_name}; | |
| EOF |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment