Django postgresql backend that apply migrations with respect to database locks.

pip install django-pg-zero-downtime-migrations

To enable zero downtime migrations for postgres just setup django backend provided by this package and add most safe settings:

DATABASES = {
    'default': {
        'ENGINE': 'django_zero_downtime_migrations.backends.postgres',
        #'ENGINE': 'django_zero_downtime_migrations.backends.postgis',
        ...
    }
}
ZERO_DOWNTIME_MIGRATIONS_LOCK_TIMEOUT = '2s'
ZERO_DOWNTIME_MIGRATIONS_STATEMENT_TIMEOUT = '2s'
ZERO_DOWNTIME_MIGRATIONS_FLEXIBLE_STATEMENT_TIMEOUT = True
ZERO_DOWNTIME_MIGRATIONS_RAISE_FOR_UNSAFE = True

NOTE: this backend brings zero downtime improvements only for migrations (schema and RunSQL operations, but not for RunPython operation), for other purpose it works the same as standard django backend.

NOTE: this package is in beta, please check your migrations SQL before applying on production and submit issue for any question.

This backend provides same result state, but different way and with additional guarantees for avoiding stuck table locks.

This backend doesn't use transactions for migrations (except RunPython operation), because not all SQL fixes can be run in transaction and it allows to avoid deadlocks for complex migration. So when your migration will down in middle of transaction you need fix it manually (instead potential downtime). For that reason good practice to make migration modules small as possible.

There are requirements for zero downtime deployment:

1. We have one database;
2. We have several instances with application - application always should be available, even you restart one of instances;
3. We have balancer before instances;
4. Our application works fine before, on and after migration - old application works fine with old and new database schema version;
5. Our application works fine before, on and after instance updating - old and new application versions work fine with new database schema version.

Flow:

1. apply migrations
2. disconnect instance form balancer, restart it and back to balancer - repeat this operation one by one for all instances

If our deployment don't satisfy zero downtime deployment rules, then we split it to smaller deployments.

Apply lock_timeout for SQL statements that require ACCESS EXCLUSIVE lock, default None:

ZERO_DOWNTIME_MIGRATIONS_LOCK_TIMEOUT = '2s'

Allowed values:

• None - current postgres setting used
• other - timeout will be applied, 0 and equivalents mean that timeout will be disabled

Apply statement_timeout for SQL statements that require ACCESS EXCLUSIVE lock, default None:

ZERO_DOWNTIME_MIGRATIONS_STATEMENT_TIMEOUT = '2s'

Allowed values:

• None - current postgres setting used
• other - timeout will be applied, 0 and equivalents mean that timeout will be disabled

Set statement_timeout to 0ms for SQL statements that require SHARE UPDATE EXCLUSIVE lock that useful in case when statement_timeout enabled globally and you try run long-running operations like index creation or constraint validation, default False:

ZERO_DOWNTIME_MIGRATIONS_FLEXIBLE_STATEMENT_TIMEOUT = True

Enabled option doesn't allow run potential unsafe migration, default False:

ZERO_DOWNTIME_MIGRATIONS_RAISE_FOR_UNSAFE = True

Define way to apply deferred sql, default True:

ZERO_DOWNTIME_DEFERRED_SQL = True

Allowed values:

• True - run deferred sql similar to default django way
• False - run deferred sql as soon as possible

Define idempotent mode, default False:

ZERO_DOWNTIME_MIGRATIONS_IDEMPOTENT_SQL = False

Allowed values:

• True - skip already applied sql migrations
• False - standard non atomic django behaviour

As this backend doesn't use transactions for migrations any failed migration can be cause of stopped process in intermediate state. To avoid manual schema manipulation idempotent mode allows to rerun failed migration after fixed issue (eg. data issue or long running CRUD queries).

NOTE: idempotent mode checks rely only on name and index and constraint valid state, so it can ignore name collisions and recommended do not use it for CI checks.

In case you using PgBouncer and expect timeouts will work as expected you need make sure that run migrations using session pool_mode.

Postgres has different locks on table level that can conflict with each other https://www.postgresql.org/docs/current/static/explicit-locking.html#LOCKING-TABLES:

Lets split this lock to migration and business logic operations.

• Migration operations work synchronously in one thread and cover schema migrations (data migrations conflict with business logic operations same as business logic conflict concurrently).
• Business logic operations work concurrently.

*: CREATE SEQUENCE, DROP SEQUENCE, CREATE TABLE, DROP TABLE shouldn't have conflicts, because your business logic shouldn't yet operate with created tables and shouldn't already operate with deleted tables.

**: Not all ALTER TABLE operations take ACCESS EXCLUSIVE lock, but all current django's migrations take it https://github.com/django/django/blob/master/django/db/backends/base/schema.py, https://github.com/django/django/blob/master/django/db/backends/postgresql/schema.py and https://www.postgresql.org/docs/current/static/sql-altertable.html.

***: Django doesn't have VALIDATE CONSTRAINT logic, but we will use it for some cases.

So you can find that all django schema changes for exist table conflicts with business logic, but fortunately they are safe or has safe alternative in general.

As business logic mostly works with table rows it's also important to understand lock conflicts on row level https://www.postgresql.org/docs/current/static/explicit-locking.html#LOCKING-ROWS:

Main point there is if you have two transactions that update one row, then second transaction will wait until first will be completed. So for business logic and data migrations better to avoid updates for whole table and use batch operations instead.

NOTE: batch operations also can work faster because postgres can use more optimal execution plan with indexes for small data range.

Found same diagram in interesting article http://pankrat.github.io/2015/django-migrations-without-downtimes/.

In this diagram we can extract several metrics:

1. operation time - time spent changing schema, in the case of long running operations on many rows tables like CREATE INDEX or ALTER TABLE ADD COLUMN SET DEFAULT, so you need a safe equivalent.
2. waiting time - your migration will wait until all transactions complete, so there is issue for long running operations/transactions like analytic, so you need avoid it or disable during migration.
3. queries per second + execution time and connections pool - if executing many queries, especially long running ones, they can consume all available database connections until the lock is released, so you need different optimizations there: run migrations when least busy, decrease query count and execution time, split data.
4. too many operations in one transaction - you have issues in all previous points for one operation so if you have many operations in one transaction then you have more likelihood to get this issue, so you need avoid too many simultaneous operations in a single transaction (or even not run it in a transaction at all but being careful when an operation fails).

Postgres has two settings to dealing with waiting time and operation time presented in diagram: lock_timeout and statement_timeout.

SET lock_timeout TO '2s' allow you to avoid downtime when you have long running query/transaction before run migration (https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-LOCK-TIMEOUT).

SET statement_timeout TO '2s' allow you to avoid downtime when you have long running migration query (https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-STATEMENT-TIMEOUT).

There no downtime issues for deadlocks, but too many operations in one transaction can take most conflicted lock and release it only after transaction commit or rollback. So it's a good idea to avoid ACCESS EXCLUSIVE lock operations and long time operations in one transaction. Deadlocks also can make you migration stuck on production deployment when different tables will be locked, for example, for FOREIGN KEY that take ACCESS EXCLUSIVE lock for two tables.

Postgres store values of different types different ways. If you try to convert one type to another and it stored different way then postgres will rewrite all values. Fortunately some types stored same way and postgres need to do nothing to change type, but in some cases postgres need to check that all values have same with new type limitations, for example string length.

Regarding documentation https://www.postgresql.org/docs/current/static/mvcc-intro.html data consistency in postgres is maintained by using a multiversion model. This means that each SQL statement sees a snapshot of data. It has advantage for adding and deleting columns without any indexes, constrains and defaults do not change exist data, new version of data will be created on INSERT and UPDATE, delete just mark you record expired. All garbage will be collected later by VACUUM or AUTO VACUUM.

Any schema changes can be processed with creation of new table and copy data to it, but it can take significant time.

*: main point with migration on production without downtime that your old and new code should correctly work before and after migration, lets look this point closely in Dealing with logic that should work before and after migration section.

**: postgres will check that all items in column NOT NULL that take time, lets look this point closely in Dealing with NOT NULL constraint section.

***: postgres will have same behaviour when you skip ALTER TABLE ADD CONSTRAINT UNIQUE USING INDEX and still unclear difference with CONCURRENTLY except difference in locks, lets look this point closely in Dealing with UNIQUE constraint.

****: lets look this point closely in Dealing with ALTER TABLE ALTER COLUMN TYPE section.

*****: if you check migration on CI with python manage.py makemigrations --check you can't drop column in code without migration creation, so in this case you can be useful back migration flow: apply code on all instances and then migrate database

Migrations: CREATE SEQUENCE, DROP SEQUENCE, CREATE TABLE, DROP TABLE, ALTER TABLE ADD COLUMN, ALTER TABLE DROP COLUMN.

This migrations are pretty safe, because your logic doesn't work with this data before migration

Migrations: ALTER TABLE RENAME TO.

Standard django's approach does not allow to operate simultaneously for old and new code with old and new table name, hopefully next workaround allows to rename table by splitting migration to few steps:

1. provide code changes but replace standard migration with SeparateDatabaseAndState sql operation that in transaction rename table and create updatable view that has old table name
   • old code can work with updatable view by old name
   • new code can work with table by new name
2. after new code deployment old code is not used anymore, so we can drop view
   • new code can work with renamed table

Migrations: ALTER TABLE RENAME COLUMN.

Standard django's approach does not allow to operate simultaneously for old and new code with old and new column name, hopefully next workaround allows to rename column by splitting migration to few steps:

1. provide code changes but replace standard migration with SeparateDatabaseAndState sql operation that in transaction rename column, rename table to temporary and create updatable view that has old table name with both old and new columns
   • old code can work with new updatable view and use old column
   • new code can work with new updatable view and use new column
2. after new code deployment old code is not used anymore, so in transaction we can drop view and rename table back
   • new code can work with renamed column

Migrations: ALTER TABLE SET TABLESPACE, ALTER TABLE ADD CONSTRAINT EXCLUDE.

For this migration too hard implement logic that will work correctly for all instances, so there are two ways to dealing with it:

1. create new table, copy exist data, drop old table
2. downtime

Migrations: ALTER TABLE ADD COLUMN SET DEFAULT.

Standard django's behaviour for creation column with default is populate all values with default. Django don't use database defaults permanently, so when you add new column with default django will create column with default and drop this default at once, eg. new default will come from django code. In this case you can have a gap when migration applied by not all instances has updated and at this moment new rows in table will be without default and probably you need update nullable values after that. So to avoid this case best way is avoid creation column with default and split column creation (with default for new rows) and data population to two migrations (with deployments).

Postgres check that all column items NOT NULL when you applying NOT NULL constraint, for postgres 12 and newest it doesn't make this check if appropriate CHECK CONSTRAINT exists, but for older versions you can't defer this check as for NOT VALID. Fortunately we have some hacks and alternatives there for old postgres versions.

1. Run migrations when load minimal to avoid negative affect of locking.
2. SET statement_timeout and try to set NOT NULL constraint for small tables.
3. Use CHECK (column IS NOT NULL) constraint instead that support NOT VALID option with next VALIDATE CONSTRAINT, see article for details https://medium.com/doctolib-engineering/adding-a-not-null-constraint-on-pg-faster-with-minimal-locking-38b2c00c4d1c. There are additionally can be applied NOT NULL constraint via direct pg_catalog.pg_attribute attnotnull update, but it require superuser permissions.

Postgres has two approaches for uniqueness: CREATE UNIQUE INDEX and ALTER TABLE ADD CONSTRAINT UNIQUE - both use unique index inside. Difference that we can find that we cannot apply DROP INDEX CONCURRENTLY for constraint. However it still unclear what difference for DROP INDEX and DROP INDEX CONCURRENTLY except difference in locks, but as we seen before both marked as safe - we don't spend time in DROP INDEX, just wait for lock. So as django use constraint for uniqueness we also have a hacks to use constraint safely.

Next operations are safe:

1. varchar(LESS) to varchar(MORE) where LESS < MORE
2. varchar(ANY) to text
3. numeric(LESS, SAME) to numeric(MORE, SAME) where LESS < MORE and SAME == SAME

For other operations propose to create new column and copy data to it. Eg. some types can be also safe, but you should check yourself.