[](https://pypi.org/project/django-pg-zero-downtime-migrations/)
[](https://raw.githubusercontent.com/tbicr/django-pg-zero-downtime-migrations/master/LICENSE)
[](https://pypistats.org/packages/django-pg-zero-downtime-migrations)
[](https://github.com/tbicr/django-pg-zero-downtime-migrations/commits/master)
[](https://github.com/tbicr/django-pg-zero-downtime-migrations/actions)
# django-pg-zero-downtime-migrations
Django postgresql backend that apply migrations with respect to database locks.
## Installation
pip install django-pg-zero-downtime-migrations
## Usage
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.
### Differences with standard django backend
This backend provides same result state (except of `ZERO_DOWNTIME_MIGRATIONS_KEEP_DEFAULT=True` usage for django < 5.0), 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 the middle of migration file operations you need to fix db state manually (instead potential downtime). For that reason good practice to make migration modules small as possible. Also `ZERO_DOWNTIME_MIGRATIONS_IDEMPOTENT_SQL=True` allows to automate manual db state fixing.
### Deployment flow
There are requirements for zero downtime deployment:
1. We have one database;
1. We have several instances with application - application always should be available, even you restart one of instances;
1. We have balancer before instances;
1. Our application works fine before, on and after migration - old application works fine with old and new database schema version;
1. 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
1. 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.
### Settings
#### ZERO_DOWNTIME_MIGRATIONS_LOCK_TIMEOUT
Apply [`lock_timeout`](https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-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
#### ZERO_DOWNTIME_MIGRATIONS_STATEMENT_TIMEOUT
Apply [`statement_timeout`](https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-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
#### ZERO_DOWNTIME_MIGRATIONS_FLEXIBLE_STATEMENT_TIMEOUT
Set [`statement_timeout`](https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-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
#### ZERO_DOWNTIME_MIGRATIONS_RAISE_FOR_UNSAFE
Enabled option doesn't allow run potential unsafe migration, default `False`:
ZERO_DOWNTIME_MIGRATIONS_RAISE_FOR_UNSAFE = True
#### ZERO_DOWNTIME_DEFERRED_SQL
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
#### ZERO_DOWNTIME_MIGRATIONS_IDEMPOTENT_SQL
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.
#### ZERO_DOWNTIME_MIGRATIONS_EXPLICIT_CONSTRAINTS_DROP
Define way to drop foreign key, unique constraints and indexes before drop table or column, default `True`:
ZERO_DOWNTIME_MIGRATIONS_EXPLICIT_CONSTRAINTS_DROP = True
Allowed values:
- `True` - before dropping table drop all foreign keys related to this table and before dropping column drop all foreign keys related to this column, unique constraints on this column and indexes used this column.
- `False` - standard django behaviour that will drop constraints with `CASCADE` mode (some constraints can be dropped explicitly too).
Explicitly dropping constraints and indexes before dropping tables or columns allows for splitting schema-only changes with an `ACCESS EXCLUSIVE` lock and the deletion of physical files, which can take significant time and cause downtime.
#### ZERO_DOWNTIME_MIGRATIONS_KEEP_DEFAULT
Define way keep or drop code defaults on database level when adding new column, default `False`:
ZERO_DOWNTIME_MIGRATIONS_KEEP_DEFAULT = False
Allowed values:
- `True` - after adding column with code default this default will not be dropped, this option allows to use `ALTER TABLE ADD COLUMN SET DEFAULT NOT NULL` as safe operation that much more simple and efficient than creating column without default on database level and populating column next
- `False` - after adding column with code default this default will be dropped, this is standard django behaviour
> _NOTE:_ this option works only for django < 5.0, in django 5.0+ explicit [`db_default`](https://docs.djangoproject.com/en/dev/ref/models/fields/#db-default) should be used instead.
#### PgBouncer and timeouts
In case you using [PgBouncer](https://www.pgbouncer.org/) and expect timeouts will work as expected you need make sure that run migrations using [session pool_mode](https://www.pgbouncer.org/config.html#pool_mode) or use direct database connection.
## How it works
### Postgres table level locks
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:
| | `ACCESS SHARE` | `ROW SHARE` | `ROW EXCLUSIVE` | `SHARE UPDATE EXCLUSIVE` | `SHARE` | `SHARE ROW EXCLUSIVE` | `EXCLUSIVE` | `ACCESS EXCLUSIVE` |
| ------------------------ | :------------: | :---------: | :-------------: | :----------------------: | :-----: | :-------------------: | :---------: | :----------------: |
| `ACCESS SHARE` | | | | | | | | X |
| `ROW SHARE` | | | | | | | X | X |
| `ROW EXCLUSIVE` | | | | | X | X | X | X |
| `SHARE UPDATE EXCLUSIVE` | | | | X | X | X | X | X |
| `SHARE` | | | X | X | | X | X | X |
| `SHARE ROW EXCLUSIVE` | | | X | X | X | X | X | X |
| `EXCLUSIVE` | | X | X | X | X | X | X | X |
| `ACCESS EXCLUSIVE` | X | X | X | X | X | X | X | X |
### Migration and business logic locks
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.
#### Migration locks
| lock | operations |
| ------------------------ | ----------------------------------------------------------------------------------------------------- |
| `ACCESS EXCLUSIVE` | `CREATE SEQUENCE`, `DROP SEQUENCE`, `CREATE TABLE`, `DROP TABLE` \*, `ALTER TABLE` \*\*, `DROP INDEX` |
| `SHARE` | `CREATE INDEX` |
| `SHARE UPDATE EXCLUSIVE` | `CREATE INDEX CONCURRENTLY`, `DROP INDEX CONCURRENTLY`, `ALTER TABLE VALIDATE CONSTRAINT` \*\*\* |
\*: `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.
#### Business logic locks
| lock | operations | conflict with lock | conflict with operations |
| --------------- | ---------------------------- | --------------------------------------------------------------- | ------------------------------------------- |
| `ACCESS SHARE` | `SELECT` | `ACCESS EXCLUSIVE` | `ALTER TABLE`, `DROP INDEX` |
| `ROW SHARE` | `SELECT FOR UPDATE` | `ACCESS EXCLUSIVE`, `EXCLUSIVE` | `ALTER TABLE`, `DROP INDEX` |
| `ROW EXCLUSIVE` | `INSERT`, `UPDATE`, `DELETE` | `ACCESS EXCLUSIVE`, `EXCLUSIVE`, `SHARE ROW EXCLUSIVE`, `SHARE` | `ALTER TABLE`, `DROP INDEX`, `CREATE INDEX` |
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.
### Postgres row level locks
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:
| lock | `FOR KEY SHARE` | `FOR SHARE` | `FOR NO KEY UPDATE` | `FOR UPDATE` |
| ------------------- | :-------------: | :---------: | :-----------------: | :----------: |
| `FOR KEY SHARE` | | | | X |
| `FOR SHARE` | | | X | X |
| `FOR NO KEY UPDATE` | | X | X | X |
| `FOR UPDATE` | X | X | X | X |
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.
### Transactions FIFO waiting
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 CONSTRAINT`, 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).
### Dealing with timeouts
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).
### Deadlocks
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.
### Rows and values storing
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.
### Multiversion Concurrency Control
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, CONSTRAINTS 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`.
### Django migrations hacks
Any schema changes can be processed with creation of new table and copy data to it, but it can take significant time.
| # | name | safe | safe alternative | description |
| --: |-----------------------------------------------|:----:|:-----------------------------:|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| 1 | `CREATE SEQUENCE` | X | | safe operation, because your business logic shouldn't operate with new sequence on migration time \* |
| 2 | `DROP SEQUENCE` | X | | safe operation, because your business logic shouldn't operate with this sequence on migration time \* |
| 3 | `CREATE TABLE` | X | | safe operation, because your business logic shouldn't operate with new table on migration time \* |
| 4 | `DROP TABLE` | X | | safe operation, because your business logic shouldn't operate with this table on migration time \* |
| 5 | `ALTER TABLE RENAME TO` | | use updatable view | **unsafe operation**, because it's too hard write business logic that operate with two tables simultaneously, so propose to use temporary updatable view and switch names in transaction \* |
| 6 | `ALTER TABLE SET TABLESPACE` | | add new table and copy data | **unsafe operation**, but probably you don't need it at all or often \* |
| 7 | `ALTER TABLE ADD COLUMN` | X | | safe operation if without `SET NOT NULL`, `PRIMARY KEY`, `UNIQUE` \* |
| 8 | `ALTER TABLE ADD COLUMN SET DEFAULT` | X | | safe operation, however it can be unsafe if code default used within `NOT NULL`, for `db_default` or `NULL` there are no issue \* |
| 9 | `ALTER TABLE ADD COLUMN SET NOT NULL` | | +/- | **unsafe operation**, because doesn't work without `SET DEFAULT` or after migration old code can insert rows without new column and raise exception, so propose to use `ALTER TABLE ADD COLUMN SET DEFAULT` with `db_default` or `ALTER TABLE ADD COLUMN` and then populate column and then `ALTER TABLE ALTER COLUMN SET NOT NULL` \* and \*\* |
| 10 | `ALTER TABLE ADD COLUMN PRIMARY KEY` | | add index and add constraint | **unsafe operation**, because you spend time in migration to `CREATE INDEX`, so propose `ALTER TABLE ADD COLUMN` and then `CREATE INDEX CONCURRENTLY` and then `ALTER TABLE ADD CONSTRAINT PRIMARY KEY USING INDEX` \*\*\* |
| 11 | `ALTER TABLE ADD COLUMN UNIQUE` | | add index and add constraint | **unsafe operation**, because you spend time in migration to `CREATE INDEX`, so propose `ALTER TABLE ADD COLUMN` and then `CREATE INDEX CONCURRENTLY` and then `ALTER TABLE ADD CONSTRAINT UNIQUE USING INDEX` \*\*\* |
| 12 | `ALTER TABLE ALTER COLUMN TYPE` | | +/- | **unsafe operation**, because you spend time in migration to check that all items in column valid or to change type, but some operations can be safe \*\*\*\* |
| 13 | `ALTER TABLE ALTER COLUMN SET NOT NULL` | | add check constraint before | **unsafe operation**, because you spend time in migration to check that all items in column `NOT NULL`, so propose `ALTER TABLE ADD CONSTRAINT CHECK` and then `ALTER TABLE VALIDATE CONSTRAINT` and then `ALTER TABLE ALTER COLUMN SET NOT NULL` *\* |
| 14 | `ALTER TABLE ALTER COLUMN DROP NOT NULL` | X | | safe operation |
| 15 | `ALTER TABLE ALTER COLUMN SET DEFAULT` | X | | safe operation |
| 16 | `ALTER TABLE ALTER COLUMN DROP DEFAULT` | X | | safe operation |
| 17 | `ALTER TABLE DROP COLUMN` | X | | safe operation, because your business logic shouldn't operate with this column on migration time, however better `ALTER TABLE ALTER COLUMN DROP NOT NULL`, `ALTER TABLE DROP CONSTRAINT` and `DROP INDEX` before \* and \*\*\*\*\* |
| 18 | `ALTER TABLE RENAME COLUMN` | | use updatable view | **unsafe operation**, because it's too hard write business logic that operate with two columns simultaneously, so propose to use temporary updatable view and switch names in transaction \* |
| 19 | `ALTER TABLE ADD CONSTRAINT CHECK` | | add as not valid and validate | **unsafe operation**, because you spend time in migration to check constraint |
| 20 | `ALTER TABLE DROP CONSTRAINT` (`CHECK`) | X | | safe operation |
| 21 | `ALTER TABLE ADD CONSTRAINT FOREIGN KEY` | | add as not valid and validate | **unsafe operation**, because you spend time in migration to check constraint, lock two tables |
| 22 | `ALTER TABLE DROP CONSTRAINT` (`FOREIGN KEY`) | X | | safe operation, lock two tables |
| 23 | `ALTER TABLE ADD CONSTRAINT PRIMARY KEY` | | add index and add constraint | **unsafe operation**, because you spend time in migration to create index \*\*\* |
| 24 | `ALTER TABLE DROP CONSTRAINT` (`PRIMARY KEY`) | X | | safe operation \*\*\* |
| 25 | `ALTER TABLE ADD CONSTRAINT UNIQUE` | | add index and add constraint | **unsafe operation**, because you spend time in migration to create index \*\*\* |
| 26 | `ALTER TABLE DROP CONSTRAINT` (`UNIQUE`) | X | | safe operation \*\*\* |
| 27 | `ALTER TABLE ADD CONSTRAINT EXCLUDE` | | add new table and copy data | |
| 28 | `ALTER TABLE DROP CONSTRAINT (EXCLUDE)` | X | | |
| 29 | `CREATE INDEX` | | `CREATE INDEX CONCURRENTLY` | **unsafe operation**, because you spend time in migration to create index |
| 30 | `DROP INDEX` | X | `DROP INDEX CONCURRENTLY` | safe operation \*\*\* |
| 31 | `CREATE INDEX CONCURRENTLY` | X | | safe operation |
| 32 | `DROP INDEX CONCURRENTLY` | X | | safe operation \*\*\* |
\*: 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](#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](#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](#dealing-with-unique-constraint).
\*\*\*\*: lets look this point closely in [Dealing with `ALTER TABLE ALTER COLUMN TYPE`](#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
#### Dealing with logic that should work before and after migration
##### Adding and removing models and columns
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
##### Rename models
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](https://docs.djangoproject.com/en/dev/ref/migration-operations/#separatedatabaseandstate) sql operation that **in transaction** rename table and create [updatable view](https://www.postgresql.org/docs/current/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS) that has old table name
- old code can work with [updatable view](https://www.postgresql.org/docs/current/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS) 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
##### Rename columns
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](https://docs.djangoproject.com/en/dev/ref/migration-operations/#separatedatabaseandstate) sql operation that **in transaction** rename column, rename table to temporary and create [updatable view](https://www.postgresql.org/docs/current/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS) that has old table name with both old and new columns
- old code can work with new [updatable view](https://www.postgresql.org/docs/current/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS) and use old column
- new code can work with new [updatable view](https://www.postgresql.org/docs/current/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS) 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
##### Changes for working logic
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
##### Create column not null
Migrations: `ALTER TABLE ADD COLUMN NOT NULL`.
Postgres doesn't allow to create column with `NOT NULL` if table not empty and `DEFAULT` is not provided. So you want to `ALTER TABLE ADD COLUMN DEFAULT NOT NULL`.
Django has two ways to create column default: [code `default`](https://docs.djangoproject.com/en/dev/ref/models/fields/#default) and [`db_default` for django 5.0+](https://docs.djangoproject.com/en/dev/ref/models/fields/#db-default).
Main difference between them for us in operations they do for migration and old code inserts handling after migration:
Code `default` migration and business logic SQL:
```sql
-- migration
ALTER TABLE tbl ADD COLUMN new_col integer DEFAULT 0 NOT NULL;
ALTER TABLE tbl ALTER COLUMN new_col DROP DEFAULT;
-- business logic
INSERT INTO tbl (old_col) VALUES (1); -- old code inserts fail
INSERT INTO tbl (old_col, new_col) VALUES (1, 1); -- new code inserts work fine
```
`db_default` migration and business logic SQL:
```sql
-- migration
ALTER TABLE tbl ADD COLUMN new_col integer DEFAULT 0 NOT NULL;
-- business logic
INSERT INTO tbl (old_col) VALUES (1); -- old code inserts work fine with default
INSERT INTO tbl (old_col, new_col) VALUES (1, 1); -- new code inserts work fine
```
`db_default` is most robust way to apply default and it's works fine with `NOT NULL` constraints too.
In django<5.0 you can use `ZERO_DOWNTIME_MIGRATIONS_KEEP_DEFAULT=True` to emulate `db_default` behaviour for `default` field.
#### Dealing with `NOT NULL` column constraint
Postgres checks that all column values `NOT NULL` (full table scan) when you are applying `ALTER TABLE ALTER COLUMN SET NOT NULL`, this check skipped if appropriate valid `CHECK CONSTRAINT` exists for postgres 12+. So to make existing column `NOT NULL` safe way you can follow next steps:
- `ALTER TABLE ADD CONSTRAINT CHECK (column IS NOT NULL) NOT VALID` - create invalid check constraint for column, this operation takes `ACCESS EXCLUSIVE` lock only for table metadata update
- `ALTER TABLE VALIDATE CONSTRAINT` - validate constraint, at this moment all column values should be `NOT NULL`, this operation takes `SHARE UPDATE EXCLUSIVE` lock until full table scan will be completed
- `ALTER TABLE ALTER COLUMN SET NOT NULL` - set column `NOT NULL` don't check column values if appropriate valid `CHECK CONSTRAINT` exists, in this case this operation takes `ACCESS EXCLUSIVE` lock only for table metadata update
- `ALTER TABLE DROP CONSTRAINT` - clean up `CHECK CONSTRAINT` that duplicates column `NOT NULL`, this operation takes `ACCESS EXCLUSIVE` lock only for table metadata update
#### Dealing with `UNIQUE` constraint
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.
#### Dealing with `ALTER TABLE ALTER COLUMN TYPE`
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.
# django-pg-zero-downtime-migrations changelog
## 0.16
- changed `ADD COLUMN DEFAULT NULL` to safe operation for code default
- changed `ADD COLUMN DEFAULT NOT NULL` to safe operation for `db_default` in django 5.0+
- added `ZERO_DOWNTIME_MIGRATIONS_KEEP_DEFAULT` settings and changed `ADD COLUMN DEFAULT NOT NULL` with this settings to safe operation for django<5.0
- added `ZERO_DOWNTIME_MIGRATIONS_EXPLICIT_CONSTRAINTS_DROP` settings and added dropping constraints and indexes before drop column or table
- fixed sqlmigrate in idempotent mode
- fixed creation unique constraint with include parameter
- fixed idempotent mode tests
- updated unsafe migrations links to documentation
- updated patched code to latest django version
- updated test image to ubuntu 24.04
- improved README
## 0.15
- added idempotent mode and `ZERO_DOWNTIME_MIGRATIONS_IDEMPOTENT_SQL` setting
- fixed django 3.2 degradation with missing `skip_default_on_alter` method
- improved readme
- updated release github action
## 0.14
- fixed deferred sql errors
- added django 5.0 support
- added python 3.12 support
- added postgres 16 support
- drop postgres 11 support
- drop `ZERO_DOWNTIME_MIGRATIONS_USE_NOT_NULL` setting
- marked `migrate_isnotnull_check_constraints` command deprecated
## 0.13
- added django 4.2 support
- marked django 3.2 support deprecated
- marked django 4.0 support deprecated
- marked django 4.1 support deprecated
- marked postgres 11 support deprecated
- drop postgres 10 support
- updated test docker image to ubuntu 22.04
## 0.12
- added `serial` and `integer`, `bigserial` and `bigint`, `smallserial` and `smallint`, same types changes as safe migrations
- fixed `AutoField` type changing and concurrent insertions issue for `django<4.1`
- added sequence dropping and creation timeouts as they can be used with `CASCADE` keyword and affect other tables
- added django 4.1 support
- added python 3.11 support
- added postgres 15 support
- marked postgres 10 support deprecated
- drop django 2.2 support
- drop django 3.0 support
- drop django 3.1 support
- drop postgres 9.5 support
- drop postgres 9.6 support
- add github actions checks for pull requests
## 0.11
- fixed rename model with keeping `db_table` raises `ALTER_TABLE_RENAME` error #26
- added django 3.2 support
- added django 4.0 support
- added python 3.9 support
- added python 3.10 support
- added postgres 14 support
- marked django 2.2 support deprecated
- marked django 3.0 support deprecated
- marked django 3.1 support deprecated
- marked python 3.6 support deprecated
- marked python 3.7 support deprecated
- marked postgres 9.5 support deprecated
- marked postgres 9.6 support deprecated
- move to github actions for testing
## 0.10
- added django 3.1 support
- added postgres 13 support
- drop python 3.5 support
- updated test environment
## 0.9
- fixed decimal to float migration error
- fixed django 3.0.2+ tests
## 0.8
- added django 3.0 support
- added concurrently index creation and removal operations
- added exclude constraint support as unsafe operation
- drop postgres 9.4 support
- drop django 2.0 support
- drop django 2.1 support
- drop deprecated `django_zero_downtime_migrations_postgres_backend` module
## 0.7
- added python 3.8 support
- added postgres specific indexes support
- improved tests clearness
- fixed regexp escaping warning for management command
- fixed style check
- improved README
- marked python 3.5 support deprecated
- marked postgres 9.4 support deprecated
- marked django 2.0 support deprecated
- marked django 2.1 support deprecated
## 0.6
- marked `ZERO_DOWNTIME_MIGRATIONS_USE_NOT_NULL` option deprecated for postgres 12+
- added management command for migration to real `NOT NULL` from `CHECK IS NOT NULL` constraint
- added integration tests for pg 12, pg 11 root, pg 11 compatible not null constraint, pg 11 standard not null constraint and pg 10, 9.6, 9.5, 9.4, postgis databases
- fixed compatible check not null constraint deletion and creation via pg_attribute bugs
- minimized side affect with deferred sql execution between operations in one migration module
- added postgres 12 safe `NOT NULL` constraint creation
- added safe `NOT NULL` constraint creation for extra permissions for `pg_catalog.pg_attribute` with `ZERO_DOWNTIME_MIGRATIONS_USE_NOT_NULL=USE_PG_ATTRIBUTE_UPDATE_FOR_SUPERUSER` option enabled
- marked `AddField` with `null=False` parameter and compatible `CHECK IS NOT NULL` constraint option as unsafe operation and avoid `ZERO_DOWNTIME_MIGRATIONS_USE_NOT_NULL` value in this case
- added version to package
- fixed pypi README images links
- improved README
## 0.5
- extracted zero-downtime-schema to mixin to allow use this logic with other backends
- moved module from `django_zero_downtime_migrations_postgres_backend` to `django_zero_downtime_migrations.backends.postgres`
- marked `django_zero_downtime_migrations_postgres_backend` module as deprecated
- added postgis backend support
- improved README
## 0.4
- changed defaults for `ZERO_DOWNTIME_MIGRATIONS_LOCK_TIMEOUT` and `ZERO_DOWNTIME_MIGRATIONS_STATEMENT_TIMEOUT` from `0ms` to `None` to get same with default django behavior that respect default postgres timeouts
- added updates to documentations with options defaults
- added updates to documentations with best options usage
- fixed adding nullable field with default had no error and warning issue
- added links to documentation with issue describing and safe alternatives usage for errors and warnings
- added updates to documentations with type casting workarounds
## 0.3
- added django 2.2 support with `Meta.indexes` and `Meta.constraints` attributes
- fixed python deprecation warnings for regexp
- removed unused `TimeoutException`
- improved README and PYPI description
## 0.2
- added option that allow disable `statement_timeout` for long operations like index creation on constraint validation when statement_timeout set globally
## 0.1.1
- added long description content type
## 0.1
- replaced default sql queries with more safe
- added options for `statement_timeout` and `lock_timeout`
- added option for `NOT NULL` constraint behaviour
- added option for unsafe operation restriction
Raw data
{
"_id": null,
"home_page": "https://github.com/tbicr/django-pg-zero-downtime-migrations",
"name": "django-pg-zero-downtime-migrations",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.6",
"maintainer_email": null,
"keywords": "django postgres postgresql migrations",
"author": "Pave\u0142 Ty\u015blacki",
"author_email": "pavel.tyslacki@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/37/97/032494ffc65811c154e062ab2820323f11f7be3600eee7cb3b91caa0fd4e/django_pg_zero_downtime_migrations-0.16.tar.gz",
"platform": null,
"description": "[](https://pypi.org/project/django-pg-zero-downtime-migrations/)\n\n\n\n[](https://raw.githubusercontent.com/tbicr/django-pg-zero-downtime-migrations/master/LICENSE)\n\n[](https://pypistats.org/packages/django-pg-zero-downtime-migrations)\n[](https://github.com/tbicr/django-pg-zero-downtime-migrations/commits/master)\n[](https://github.com/tbicr/django-pg-zero-downtime-migrations/actions)\n\n# django-pg-zero-downtime-migrations\n\nDjango postgresql backend that apply migrations with respect to database locks.\n\n## Installation\n\n pip install django-pg-zero-downtime-migrations\n\n## Usage\n\nTo enable zero downtime migrations for postgres just setup django backend provided by this package and add most safe settings:\n\n DATABASES = {\n 'default': {\n 'ENGINE': 'django_zero_downtime_migrations.backends.postgres',\n #'ENGINE': 'django_zero_downtime_migrations.backends.postgis',\n ...\n }\n }\n ZERO_DOWNTIME_MIGRATIONS_LOCK_TIMEOUT = '2s'\n ZERO_DOWNTIME_MIGRATIONS_STATEMENT_TIMEOUT = '2s'\n ZERO_DOWNTIME_MIGRATIONS_FLEXIBLE_STATEMENT_TIMEOUT = True\n ZERO_DOWNTIME_MIGRATIONS_RAISE_FOR_UNSAFE = True\n\n> _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.\n\n> _NOTE:_ this package is in beta, please check your migrations SQL before applying on production and submit issue for any question.\n\n### Differences with standard django backend\n\nThis backend provides same result state (except of `ZERO_DOWNTIME_MIGRATIONS_KEEP_DEFAULT=True` usage for django < 5.0), but different way and with additional guarantees for avoiding stuck table locks.\n\nThis 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 the middle of migration file operations you need to fix db state manually (instead potential downtime). For that reason good practice to make migration modules small as possible. Also `ZERO_DOWNTIME_MIGRATIONS_IDEMPOTENT_SQL=True` allows to automate manual db state fixing.\n\n### Deployment flow\n\nThere are requirements for zero downtime deployment:\n\n1. We have one database;\n1. We have several instances with application - application always should be available, even you restart one of instances;\n1. We have balancer before instances;\n1. Our application works fine before, on and after migration - old application works fine with old and new database schema version;\n1. Our application works fine before, on and after instance updating - old and new application versions work fine with new database schema version.\n\n![deployment timeline](https://raw.githubusercontent.com/tbicr/django-pg-zero-downtime-migrations/0.16/images/timeline.png \"deployment timeline\")\n\nFlow:\n\n1. apply migrations\n1. disconnect instance form balancer, restart it and back to balancer - repeat this operation one by one for all instances\n\nIf our deployment don't satisfy zero downtime deployment rules, then we split it to smaller deployments.\n\n![deployment flow](https://raw.githubusercontent.com/tbicr/django-pg-zero-downtime-migrations/0.16/images/deployment.gif \"deployment flow\")\n\n### Settings\n\n#### ZERO_DOWNTIME_MIGRATIONS_LOCK_TIMEOUT\n\nApply [`lock_timeout`](https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-LOCK-TIMEOUT) for SQL statements that require `ACCESS EXCLUSIVE` lock, default `None`:\n\n ZERO_DOWNTIME_MIGRATIONS_LOCK_TIMEOUT = '2s'\n\nAllowed values:\n\n- `None` - current postgres setting used\n- other - timeout will be applied, `0` and equivalents mean that timeout will be disabled\n\n#### ZERO_DOWNTIME_MIGRATIONS_STATEMENT_TIMEOUT\n\nApply [`statement_timeout`](https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-STATEMENT-TIMEOUT) for SQL statements that require `ACCESS EXCLUSIVE` lock, default `None`:\n\n ZERO_DOWNTIME_MIGRATIONS_STATEMENT_TIMEOUT = '2s'\n\nAllowed values:\n\n- `None` - current postgres setting used\n- other - timeout will be applied, `0` and equivalents mean that timeout will be disabled\n\n#### ZERO_DOWNTIME_MIGRATIONS_FLEXIBLE_STATEMENT_TIMEOUT\n\nSet [`statement_timeout`](https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-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`:\n\n ZERO_DOWNTIME_MIGRATIONS_FLEXIBLE_STATEMENT_TIMEOUT = True\n\n#### ZERO_DOWNTIME_MIGRATIONS_RAISE_FOR_UNSAFE\n\nEnabled option doesn't allow run potential unsafe migration, default `False`:\n\n ZERO_DOWNTIME_MIGRATIONS_RAISE_FOR_UNSAFE = True\n\n#### ZERO_DOWNTIME_DEFERRED_SQL\n\nDefine way to apply deferred sql, default `True`:\n\n ZERO_DOWNTIME_DEFERRED_SQL = True\n\nAllowed values:\n- `True` - run deferred sql similar to default django way\n- `False` - run deferred sql as soon as possible\n\n#### ZERO_DOWNTIME_MIGRATIONS_IDEMPOTENT_SQL\n\nDefine idempotent mode, default `False`:\n\n ZERO_DOWNTIME_MIGRATIONS_IDEMPOTENT_SQL = False\n\nAllowed values:\n- `True` - skip already applied sql migrations\n- `False` - standard non atomic django behaviour\n\nAs this backend doesn't use transactions for migrations any failed migration can be cause of stopped process in intermediate state.\nTo avoid manual schema manipulation idempotent mode allows to rerun failed migration after fixed issue (eg. data issue or long running CRUD queries).\n\n> _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.\n\n#### ZERO_DOWNTIME_MIGRATIONS_EXPLICIT_CONSTRAINTS_DROP\n\nDefine way to drop foreign key, unique constraints and indexes before drop table or column, default `True`:\n\n ZERO_DOWNTIME_MIGRATIONS_EXPLICIT_CONSTRAINTS_DROP = True\n\nAllowed values:\n- `True` - before dropping table drop all foreign keys related to this table and before dropping column drop all foreign keys related to this column, unique constraints on this column and indexes used this column.\n- `False` - standard django behaviour that will drop constraints with `CASCADE` mode (some constraints can be dropped explicitly too).\n\nExplicitly dropping constraints and indexes before dropping tables or columns allows for splitting schema-only changes with an `ACCESS EXCLUSIVE` lock and the deletion of physical files, which can take significant time and cause downtime.\n\n#### ZERO_DOWNTIME_MIGRATIONS_KEEP_DEFAULT\n\nDefine way keep or drop code defaults on database level when adding new column, default `False`:\n\n ZERO_DOWNTIME_MIGRATIONS_KEEP_DEFAULT = False\n\nAllowed values:\n- `True` - after adding column with code default this default will not be dropped, this option allows to use `ALTER TABLE ADD COLUMN SET DEFAULT NOT NULL` as safe operation that much more simple and efficient than creating column without default on database level and populating column next\n- `False` - after adding column with code default this default will be dropped, this is standard django behaviour\n\n> _NOTE:_ this option works only for django < 5.0, in django 5.0+ explicit [`db_default`](https://docs.djangoproject.com/en/dev/ref/models/fields/#db-default) should be used instead.\n\n#### PgBouncer and timeouts\n\nIn case you using [PgBouncer](https://www.pgbouncer.org/) and expect timeouts will work as expected you need make sure that run migrations using [session pool_mode](https://www.pgbouncer.org/config.html#pool_mode) or use direct database connection.\n\n## How it works\n\n### Postgres table level locks\n\nPostgres has different locks on table level that can conflict with each other https://www.postgresql.org/docs/current/static/explicit-locking.html#LOCKING-TABLES:\n\n| | `ACCESS SHARE` | `ROW SHARE` | `ROW EXCLUSIVE` | `SHARE UPDATE EXCLUSIVE` | `SHARE` | `SHARE ROW EXCLUSIVE` | `EXCLUSIVE` | `ACCESS EXCLUSIVE` |\n| ------------------------ | :------------: | :---------: | :-------------: | :----------------------: | :-----: | :-------------------: | :---------: | :----------------: |\n| `ACCESS SHARE` | | | | | | | | X |\n| `ROW SHARE` | | | | | | | X | X |\n| `ROW EXCLUSIVE` | | | | | X | X | X | X |\n| `SHARE UPDATE EXCLUSIVE` | | | | X | X | X | X | X |\n| `SHARE` | | | X | X | | X | X | X |\n| `SHARE ROW EXCLUSIVE` | | | X | X | X | X | X | X |\n| `EXCLUSIVE` | | X | X | X | X | X | X | X |\n| `ACCESS EXCLUSIVE` | X | X | X | X | X | X | X | X |\n\n### Migration and business logic locks\n\nLets split this lock to migration and business logic operations.\n\n- Migration operations work synchronously in one thread and cover schema migrations (data migrations conflict with business logic operations same as business logic conflict concurrently).\n- Business logic operations work concurrently.\n\n#### Migration locks\n\n| lock | operations |\n| ------------------------ | ----------------------------------------------------------------------------------------------------- |\n| `ACCESS EXCLUSIVE` | `CREATE SEQUENCE`, `DROP SEQUENCE`, `CREATE TABLE`, `DROP TABLE` \\*, `ALTER TABLE` \\*\\*, `DROP INDEX` |\n| `SHARE` | `CREATE INDEX` |\n| `SHARE UPDATE EXCLUSIVE` | `CREATE INDEX CONCURRENTLY`, `DROP INDEX CONCURRENTLY`, `ALTER TABLE VALIDATE CONSTRAINT` \\*\\*\\* |\n\n\\*: `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.\n\n\\*\\*: 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.\n\n\\*\\*\\*: Django doesn't have `VALIDATE CONSTRAINT` logic, but we will use it for some cases.\n\n#### Business logic locks\n\n| lock | operations | conflict with lock | conflict with operations |\n| --------------- | ---------------------------- | --------------------------------------------------------------- | ------------------------------------------- |\n| `ACCESS SHARE` | `SELECT` | `ACCESS EXCLUSIVE` | `ALTER TABLE`, `DROP INDEX` |\n| `ROW SHARE` | `SELECT FOR UPDATE` | `ACCESS EXCLUSIVE`, `EXCLUSIVE` | `ALTER TABLE`, `DROP INDEX` |\n| `ROW EXCLUSIVE` | `INSERT`, `UPDATE`, `DELETE` | `ACCESS EXCLUSIVE`, `EXCLUSIVE`, `SHARE ROW EXCLUSIVE`, `SHARE` | `ALTER TABLE`, `DROP INDEX`, `CREATE INDEX` |\n\nSo 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.\n\n### Postgres row level locks\n\nAs 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:\n\n| lock | `FOR KEY SHARE` | `FOR SHARE` | `FOR NO KEY UPDATE` | `FOR UPDATE` |\n| ------------------- | :-------------: | :---------: | :-----------------: | :----------: |\n| `FOR KEY SHARE` | | | | X |\n| `FOR SHARE` | | | X | X |\n| `FOR NO KEY UPDATE` | | X | X | X |\n| `FOR UPDATE` | X | X | X | X |\n\nMain 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.\n\n> _NOTE:_ batch operations also can work faster because postgres can use more optimal execution plan with indexes for small data range.\n\n### Transactions FIFO waiting\n\n![postgres FIFO](https://raw.githubusercontent.com/tbicr/django-pg-zero-downtime-migrations/0.16/images/fifo-diagram.png \"postgres FIFO\")\n\nFound same diagram in interesting article http://pankrat.github.io/2015/django-migrations-without-downtimes/.\n\nIn this diagram we can extract several metrics:\n\n1. operation time - time spent changing schema, in the case of long running operations on many rows tables like `CREATE INDEX` or `ALTER TABLE ADD CONSTRAINT`, so you need a safe equivalent.\n2. 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.\n3. 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.\n4. 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).\n\n### Dealing with timeouts\n\nPostgres has two settings to dealing with `waiting time` and `operation time` presented in diagram: `lock_timeout` and `statement_timeout`.\n\n`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).\n\n`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).\n\n### Deadlocks\n\nThere 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.\n\n### Rows and values storing\n\nPostgres 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.\n\n### Multiversion Concurrency Control\n\nRegarding 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, CONSTRAINTS 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`.\n\n### Django migrations hacks\n\nAny schema changes can be processed with creation of new table and copy data to it, but it can take significant time.\n\n| # | name | safe | safe alternative | description |\n| --: |-----------------------------------------------|:----:|:-----------------------------:|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|\n| 1 | `CREATE SEQUENCE` | X | | safe operation, because your business logic shouldn't operate with new sequence on migration time \\* |\n| 2 | `DROP SEQUENCE` | X | | safe operation, because your business logic shouldn't operate with this sequence on migration time \\* |\n| 3 | `CREATE TABLE` | X | | safe operation, because your business logic shouldn't operate with new table on migration time \\* |\n| 4 | `DROP TABLE` | X | | safe operation, because your business logic shouldn't operate with this table on migration time \\* |\n| 5 | `ALTER TABLE RENAME TO` | | use updatable view | **unsafe operation**, because it's too hard write business logic that operate with two tables simultaneously, so propose to use temporary updatable view and switch names in transaction \\* |\n| 6 | `ALTER TABLE SET TABLESPACE` | | add new table and copy data | **unsafe operation**, but probably you don't need it at all or often \\* |\n| 7 | `ALTER TABLE ADD COLUMN` | X | | safe operation if without `SET NOT NULL`, `PRIMARY KEY`, `UNIQUE` \\* |\n| 8 | `ALTER TABLE ADD COLUMN SET DEFAULT` | X | | safe operation, however it can be unsafe if code default used within `NOT NULL`, for `db_default` or `NULL` there are no issue \\* |\n| 9 | `ALTER TABLE ADD COLUMN SET NOT NULL` | | +/- | **unsafe operation**, because doesn't work without `SET DEFAULT` or after migration old code can insert rows without new column and raise exception, so propose to use `ALTER TABLE ADD COLUMN SET DEFAULT` with `db_default` or `ALTER TABLE ADD COLUMN` and then populate column and then `ALTER TABLE ALTER COLUMN SET NOT NULL` \\* and \\*\\* |\n| 10 | `ALTER TABLE ADD COLUMN PRIMARY KEY` | | add index and add constraint | **unsafe operation**, because you spend time in migration to `CREATE INDEX`, so propose `ALTER TABLE ADD COLUMN` and then `CREATE INDEX CONCURRENTLY` and then `ALTER TABLE ADD CONSTRAINT PRIMARY KEY USING INDEX` \\*\\*\\* |\n| 11 | `ALTER TABLE ADD COLUMN UNIQUE` | | add index and add constraint | **unsafe operation**, because you spend time in migration to `CREATE INDEX`, so propose `ALTER TABLE ADD COLUMN` and then `CREATE INDEX CONCURRENTLY` and then `ALTER TABLE ADD CONSTRAINT UNIQUE USING INDEX` \\*\\*\\* |\n| 12 | `ALTER TABLE ALTER COLUMN TYPE` | | +/- | **unsafe operation**, because you spend time in migration to check that all items in column valid or to change type, but some operations can be safe \\*\\*\\*\\* |\n| 13 | `ALTER TABLE ALTER COLUMN SET NOT NULL` | | add check constraint before | **unsafe operation**, because you spend time in migration to check that all items in column `NOT NULL`, so propose `ALTER TABLE ADD CONSTRAINT CHECK` and then `ALTER TABLE VALIDATE CONSTRAINT` and then `ALTER TABLE ALTER COLUMN SET NOT NULL` *\\* |\n| 14 | `ALTER TABLE ALTER COLUMN DROP NOT NULL` | X | | safe operation |\n| 15 | `ALTER TABLE ALTER COLUMN SET DEFAULT` | X | | safe operation |\n| 16 | `ALTER TABLE ALTER COLUMN DROP DEFAULT` | X | | safe operation |\n| 17 | `ALTER TABLE DROP COLUMN` | X | | safe operation, because your business logic shouldn't operate with this column on migration time, however better `ALTER TABLE ALTER COLUMN DROP NOT NULL`, `ALTER TABLE DROP CONSTRAINT` and `DROP INDEX` before \\* and \\*\\*\\*\\*\\* |\n| 18 | `ALTER TABLE RENAME COLUMN` | | use updatable view | **unsafe operation**, because it's too hard write business logic that operate with two columns simultaneously, so propose to use temporary updatable view and switch names in transaction \\* |\n| 19 | `ALTER TABLE ADD CONSTRAINT CHECK` | | add as not valid and validate | **unsafe operation**, because you spend time in migration to check constraint |\n| 20 | `ALTER TABLE DROP CONSTRAINT` (`CHECK`) | X | | safe operation |\n| 21 | `ALTER TABLE ADD CONSTRAINT FOREIGN KEY` | | add as not valid and validate | **unsafe operation**, because you spend time in migration to check constraint, lock two tables |\n| 22 | `ALTER TABLE DROP CONSTRAINT` (`FOREIGN KEY`) | X | | safe operation, lock two tables |\n| 23 | `ALTER TABLE ADD CONSTRAINT PRIMARY KEY` | | add index and add constraint | **unsafe operation**, because you spend time in migration to create index \\*\\*\\* |\n| 24 | `ALTER TABLE DROP CONSTRAINT` (`PRIMARY KEY`) | X | | safe operation \\*\\*\\* |\n| 25 | `ALTER TABLE ADD CONSTRAINT UNIQUE` | | add index and add constraint | **unsafe operation**, because you spend time in migration to create index \\*\\*\\* |\n| 26 | `ALTER TABLE DROP CONSTRAINT` (`UNIQUE`) | X | | safe operation \\*\\*\\* |\n| 27 | `ALTER TABLE ADD CONSTRAINT EXCLUDE` | | add new table and copy data | |\n| 28 | `ALTER TABLE DROP CONSTRAINT (EXCLUDE)` | X | | |\n| 29 | `CREATE INDEX` | | `CREATE INDEX CONCURRENTLY` | **unsafe operation**, because you spend time in migration to create index |\n| 30 | `DROP INDEX` | X | `DROP INDEX CONCURRENTLY` | safe operation \\*\\*\\* |\n| 31 | `CREATE INDEX CONCURRENTLY` | X | | safe operation |\n| 32 | `DROP INDEX CONCURRENTLY` | X | | safe operation \\*\\*\\* |\n\n\\*: 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](#dealing-with-logic-that-should-work-before-and-after-migration) section.\n\n\\*\\*: postgres will check that all items in column `NOT NULL` that take time, lets look this point closely in [Dealing with `NOT NULL` constraint](#dealing-with-not-null-constraint) section.\n\n\\*\\*\\*: 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](#dealing-with-unique-constraint).\n\n\\*\\*\\*\\*: lets look this point closely in [Dealing with `ALTER TABLE ALTER COLUMN TYPE`](#dealing-with-alter-table-alter-column-type) section.\n\n\\*\\*\\*\\*\\*: 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\n\n#### Dealing with logic that should work before and after migration\n\n##### Adding and removing models and columns\n\nMigrations: `CREATE SEQUENCE`, `DROP SEQUENCE`, `CREATE TABLE`, `DROP TABLE`, `ALTER TABLE ADD COLUMN`, `ALTER TABLE DROP COLUMN`.\n\nThis migrations are pretty safe, because your logic doesn't work with this data before migration\n\n##### Rename models\n\nMigrations: `ALTER TABLE RENAME TO`.\n\nStandard 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:\n1. provide code changes but replace standard migration with [SeparateDatabaseAndState](https://docs.djangoproject.com/en/dev/ref/migration-operations/#separatedatabaseandstate) sql operation that **in transaction** rename table and create [updatable view](https://www.postgresql.org/docs/current/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS) that has old table name \n - old code can work with [updatable view](https://www.postgresql.org/docs/current/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS) by old name\n - new code can work with table by new name \n2. after new code deployment old code is not used anymore, so we can drop view\n - new code can work with renamed table\n\n##### Rename columns\n\nMigrations: `ALTER TABLE RENAME COLUMN`.\n\nStandard 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:\n1. provide code changes but replace standard migration with [SeparateDatabaseAndState](https://docs.djangoproject.com/en/dev/ref/migration-operations/#separatedatabaseandstate) sql operation that **in transaction** rename column, rename table to temporary and create [updatable view](https://www.postgresql.org/docs/current/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS) that has old table name with both old and new columns \n - old code can work with new [updatable view](https://www.postgresql.org/docs/current/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS) and use old column\n - new code can work with new [updatable view](https://www.postgresql.org/docs/current/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS) and use new column \n2. after new code deployment old code is not used anymore, so **in transaction** we can drop view and rename table back\n - new code can work with renamed column\n\n##### Changes for working logic\n\nMigrations: `ALTER TABLE SET TABLESPACE`, `ALTER TABLE ADD CONSTRAINT EXCLUDE`.\n\nFor this migration too hard implement logic that will work correctly for all instances, so there are two ways to dealing with it:\n\n1. create new table, copy exist data, drop old table\n2. downtime\n\n##### Create column not null\n\nMigrations: `ALTER TABLE ADD COLUMN NOT NULL`.\n\nPostgres doesn't allow to create column with `NOT NULL` if table not empty and `DEFAULT` is not provided. So you want to `ALTER TABLE ADD COLUMN DEFAULT NOT NULL`.\nDjango has two ways to create column default: [code `default`](https://docs.djangoproject.com/en/dev/ref/models/fields/#default) and [`db_default` for django 5.0+](https://docs.djangoproject.com/en/dev/ref/models/fields/#db-default).\nMain difference between them for us in operations they do for migration and old code inserts handling after migration:\n\nCode `default` migration and business logic SQL:\n```sql\n-- migration\nALTER TABLE tbl ADD COLUMN new_col integer DEFAULT 0 NOT NULL;\nALTER TABLE tbl ALTER COLUMN new_col DROP DEFAULT;\n\n-- business logic\nINSERT INTO tbl (old_col) VALUES (1); -- old code inserts fail\nINSERT INTO tbl (old_col, new_col) VALUES (1, 1); -- new code inserts work fine\n```\n\n`db_default` migration and business logic SQL:\n```sql\n-- migration\nALTER TABLE tbl ADD COLUMN new_col integer DEFAULT 0 NOT NULL;\n\n-- business logic\nINSERT INTO tbl (old_col) VALUES (1); -- old code inserts work fine with default\nINSERT INTO tbl (old_col, new_col) VALUES (1, 1); -- new code inserts work fine\n```\n\n`db_default` is most robust way to apply default and it's works fine with `NOT NULL` constraints too.\nIn django<5.0 you can use `ZERO_DOWNTIME_MIGRATIONS_KEEP_DEFAULT=True` to emulate `db_default` behaviour for `default` field.\n\n#### Dealing with `NOT NULL` column constraint\n\nPostgres checks that all column values `NOT NULL` (full table scan) when you are applying `ALTER TABLE ALTER COLUMN SET NOT NULL`, this check skipped if appropriate valid `CHECK CONSTRAINT` exists for postgres 12+. So to make existing column `NOT NULL` safe way you can follow next steps:\n- `ALTER TABLE ADD CONSTRAINT CHECK (column IS NOT NULL) NOT VALID` - create invalid check constraint for column, this operation takes `ACCESS EXCLUSIVE` lock only for table metadata update\n- `ALTER TABLE VALIDATE CONSTRAINT` - validate constraint, at this moment all column values should be `NOT NULL`, this operation takes `SHARE UPDATE EXCLUSIVE` lock until full table scan will be completed\n- `ALTER TABLE ALTER COLUMN SET NOT NULL` - set column `NOT NULL` don't check column values if appropriate valid `CHECK CONSTRAINT` exists, in this case this operation takes `ACCESS EXCLUSIVE` lock only for table metadata update\n- `ALTER TABLE DROP CONSTRAINT` - clean up `CHECK CONSTRAINT` that duplicates column `NOT NULL`, this operation takes `ACCESS EXCLUSIVE` lock only for table metadata update\n\n#### Dealing with `UNIQUE` constraint\n\nPostgres 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.\n\n#### Dealing with `ALTER TABLE ALTER COLUMN TYPE`\n\nNext operations are safe:\n\n1. `varchar(LESS)` to `varchar(MORE)` where LESS < MORE\n2. `varchar(ANY)` to `text`\n3. `numeric(LESS, SAME)` to `numeric(MORE, SAME)` where LESS < MORE and SAME == SAME\n\nFor other operations propose to create new column and copy data to it. Eg. some types can be also safe, but you should check yourself.\n\n\n# django-pg-zero-downtime-migrations changelog\n\n## 0.16\n - changed `ADD COLUMN DEFAULT NULL` to safe operation for code default\n - changed `ADD COLUMN DEFAULT NOT NULL` to safe operation for `db_default` in django 5.0+\n - added `ZERO_DOWNTIME_MIGRATIONS_KEEP_DEFAULT` settings and changed `ADD COLUMN DEFAULT NOT NULL` with this settings to safe operation for django<5.0\n - added `ZERO_DOWNTIME_MIGRATIONS_EXPLICIT_CONSTRAINTS_DROP` settings and added dropping constraints and indexes before drop column or table\n - fixed sqlmigrate in idempotent mode\n - fixed creation unique constraint with include parameter\n - fixed idempotent mode tests\n - updated unsafe migrations links to documentation\n - updated patched code to latest django version\n - updated test image to ubuntu 24.04\n - improved README\n\n## 0.15\n - added idempotent mode and `ZERO_DOWNTIME_MIGRATIONS_IDEMPOTENT_SQL` setting\n - fixed django 3.2 degradation with missing `skip_default_on_alter` method\n - improved readme\n - updated release github action\n\n## 0.14\n - fixed deferred sql errors\n - added django 5.0 support\n - added python 3.12 support\n - added postgres 16 support\n - drop postgres 11 support\n - drop `ZERO_DOWNTIME_MIGRATIONS_USE_NOT_NULL` setting\n - marked `migrate_isnotnull_check_constraints` command deprecated\n\n## 0.13\n - added django 4.2 support\n - marked django 3.2 support deprecated\n - marked django 4.0 support deprecated\n - marked django 4.1 support deprecated\n - marked postgres 11 support deprecated\n - drop postgres 10 support\n - updated test docker image to ubuntu 22.04\n\n## 0.12\n - added `serial` and `integer`, `bigserial` and `bigint`, `smallserial` and `smallint`, same types changes as safe migrations\n - fixed `AutoField` type changing and concurrent insertions issue for `django<4.1`\n - added sequence dropping and creation timeouts as they can be used with `CASCADE` keyword and affect other tables\n - added django 4.1 support\n - added python 3.11 support\n - added postgres 15 support\n - marked postgres 10 support deprecated\n - drop django 2.2 support\n - drop django 3.0 support\n - drop django 3.1 support\n - drop postgres 9.5 support\n - drop postgres 9.6 support\n - add github actions checks for pull requests\n\n## 0.11\n - fixed rename model with keeping `db_table` raises `ALTER_TABLE_RENAME` error #26\n - added django 3.2 support\n - added django 4.0 support\n - added python 3.9 support\n - added python 3.10 support\n - added postgres 14 support\n - marked django 2.2 support deprecated\n - marked django 3.0 support deprecated\n - marked django 3.1 support deprecated\n - marked python 3.6 support deprecated\n - marked python 3.7 support deprecated\n - marked postgres 9.5 support deprecated\n - marked postgres 9.6 support deprecated\n - move to github actions for testing\n\n## 0.10\n - added django 3.1 support\n - added postgres 13 support\n - drop python 3.5 support\n - updated test environment\n\n## 0.9\n - fixed decimal to float migration error\n - fixed django 3.0.2+ tests\n\n## 0.8\n - added django 3.0 support\n - added concurrently index creation and removal operations\n - added exclude constraint support as unsafe operation\n - drop postgres 9.4 support\n - drop django 2.0 support\n - drop django 2.1 support\n - drop deprecated `django_zero_downtime_migrations_postgres_backend` module\n\n## 0.7\n - added python 3.8 support\n - added postgres specific indexes support\n - improved tests clearness\n - fixed regexp escaping warning for management command\n - fixed style check\n - improved README\n - marked python 3.5 support deprecated\n - marked postgres 9.4 support deprecated\n - marked django 2.0 support deprecated\n - marked django 2.1 support deprecated\n\n## 0.6\n - marked `ZERO_DOWNTIME_MIGRATIONS_USE_NOT_NULL` option deprecated for postgres 12+\n - added management command for migration to real `NOT NULL` from `CHECK IS NOT NULL` constraint\n - added integration tests for pg 12, pg 11 root, pg 11 compatible not null constraint, pg 11 standard not null constraint and pg 10, 9.6, 9.5, 9.4, postgis databases\n - fixed compatible check not null constraint deletion and creation via pg_attribute bugs\n - minimized side affect with deferred sql execution between operations in one migration module\n - added postgres 12 safe `NOT NULL` constraint creation\n - added safe `NOT NULL` constraint creation for extra permissions for `pg_catalog.pg_attribute` with `ZERO_DOWNTIME_MIGRATIONS_USE_NOT_NULL=USE_PG_ATTRIBUTE_UPDATE_FOR_SUPERUSER` option enabled\n - marked `AddField` with `null=False` parameter and compatible `CHECK IS NOT NULL` constraint option as unsafe operation and avoid `ZERO_DOWNTIME_MIGRATIONS_USE_NOT_NULL` value in this case\n - added version to package\n - fixed pypi README images links\n - improved README\n\n## 0.5\n - extracted zero-downtime-schema to mixin to allow use this logic with other backends\n - moved module from `django_zero_downtime_migrations_postgres_backend` to `django_zero_downtime_migrations.backends.postgres`\n - marked `django_zero_downtime_migrations_postgres_backend` module as deprecated\n - added postgis backend support\n - improved README\n\n## 0.4\n - changed defaults for `ZERO_DOWNTIME_MIGRATIONS_LOCK_TIMEOUT` and `ZERO_DOWNTIME_MIGRATIONS_STATEMENT_TIMEOUT` from `0ms` to `None` to get same with default django behavior that respect default postgres timeouts\n - added updates to documentations with options defaults\n - added updates to documentations with best options usage\n - fixed adding nullable field with default had no error and warning issue\n - added links to documentation with issue describing and safe alternatives usage for errors and warnings\n - added updates to documentations with type casting workarounds\n \n## 0.3\n - added django 2.2 support with `Meta.indexes` and `Meta.constraints` attributes\n - fixed python deprecation warnings for regexp\n - removed unused `TimeoutException`\n - improved README and PYPI description\n\n## 0.2\n - added option that allow disable `statement_timeout` for long operations like index creation on constraint validation when statement_timeout set globally\n\n## 0.1.1\n - added long description content type\n\n## 0.1\n - replaced default sql queries with more safe\n - added options for `statement_timeout` and `lock_timeout`\n - added option for `NOT NULL` constraint behaviour\n - added option for unsafe operation restriction\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "Django postgresql backend that apply migrations with respect to database locks",
"version": "0.16",
"project_urls": {
"Homepage": "https://github.com/tbicr/django-pg-zero-downtime-migrations"
},
"split_keywords": [
"django",
"postgres",
"postgresql",
"migrations"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "ecb74882635a67cb9f0ea275ac042837e9802c9f048bb758a51199e62a44f7b8",
"md5": "7b6365f91e6aa47ded28bc61173d5b5e",
"sha256": "6b1dc75814514c651a8e3fc1b3d9eb4d9ea7b5d7f02267cb04d47c719d59c9ad"
},
"downloads": -1,
"filename": "django_pg_zero_downtime_migrations-0.16-py3-none-any.whl",
"has_sig": false,
"md5_digest": "7b6365f91e6aa47ded28bc61173d5b5e",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.6",
"size": 24945,
"upload_time": "2024-06-07T21:29:21",
"upload_time_iso_8601": "2024-06-07T21:29:21.478031Z",
"url": "https://files.pythonhosted.org/packages/ec/b7/4882635a67cb9f0ea275ac042837e9802c9f048bb758a51199e62a44f7b8/django_pg_zero_downtime_migrations-0.16-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "3797032494ffc65811c154e062ab2820323f11f7be3600eee7cb3b91caa0fd4e",
"md5": "a03bcdcbbe25211abade29fc394f96a6",
"sha256": "47105e5a9bc677560a31ee6df5def66596b63e9dc063fa5ec152c42316e3c280"
},
"downloads": -1,
"filename": "django_pg_zero_downtime_migrations-0.16.tar.gz",
"has_sig": false,
"md5_digest": "a03bcdcbbe25211abade29fc394f96a6",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.6",
"size": 41208,
"upload_time": "2024-06-07T21:29:22",
"upload_time_iso_8601": "2024-06-07T21:29:22.959459Z",
"url": "https://files.pythonhosted.org/packages/37/97/032494ffc65811c154e062ab2820323f11f7be3600eee7cb3b91caa0fd4e/django_pg_zero_downtime_migrations-0.16.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-06-07 21:29:22",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "tbicr",
"github_project": "django-pg-zero-downtime-migrations",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"tox": true,
"lcname": "django-pg-zero-downtime-migrations"
}
JSON
