django-postpone-index 0.0.3

Postpone index creation to provide Zero Downtime Migration feature

Django Postpone Index

This package provides modules and tools to postpone any index creation instead doing it inside the migration, to provide Zero Downtime Migration feature.

The package is now using the PostgresSQL-specific CREATE INDEX CONCURRENTLY SQL command, so is applicable only to the PostgreSQL backend.

Installation

Stable version from the PyPi package repository

pip install django-postpone-index

Last development version from the GitHub source version control system

pip install git+git://github.com/nnseva/django-postpone-index.git

Problem Description

Large data leads to long index creation time.

When the migration is automatically created, it executes all SQL commands creating index inside a transaction.

Large data and index creation inside a transaction lead to long-term table lock which blocks any data writting to the table.

On the other side, CREATE INDEX CONCURRENTLY SQL command may solve the problem, but this SQL command can not be executed inside a transaction block.

The AddIndexConcurrently might be created in a separate migration, moving out the automatically generated AddIndex from the migration, but not all indexes are created using AddIndex.

Solution

All index creation SQL commands (as well as unique constraints creation) are catched and postponed using a special PostponedSQL model (the DROP INDEX and DROP CONSTRAINT SQL commands are still executed immediately).

When the migration is finished, the postponed indexes may be created in a separate process using CREATE INDEX CONCURRENTLY SQL command by the apply_postponed management command. Apart from the standard migration, this process doesn't lock the whole table for a long time.

Failed index creation statements don't lead to the command failure (until a special command line parameter passed). Every failed statement is stored as erroneous instead. When the data is fixed, you can execute the apply_postponed management command again to restore the failed indexes.

Complex Use Cases

The following complex use cases are processed by the package.

• Several create/drop pairs. There can be several create/drop index pairs if several migrations applied at once.
• Back Migration. The both, forward and backward migrations are processed.
• Implicit index drop while removing the table. The Django doesn't issue a separate SQL to drop indexes of the dropped table.
• Implicit index drop while removing the field. The Django doesn't issue a separate SQL to drop indexes related to the dropped column.

Using

Include the postpone_index application in setting.py:

INSTALLED_APPS = [
    ...
    'postpone_index',
    ...
]

Use pospone_index.contrib.postgres or postpone_index.contrib.postgis engines instead of the Django-provided in settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'postpone_index.contrib.postgres',
        ...
    }
}

If you provide your own database engine instead of the Django-provided, you can also combine pospone_index.contrib.postgres.schema.DatabaseSchemaEditorMixin with your own Database Schema Editor, f.e.:

mybackend/schema.py

from django.db.backends.postgresql.schema import DatabaseSchemaEditor as _DatabaseSchemaEditor
from pospone_index.contrib.postgres.schema import DatabaseSchemaEditorMixin

class PostponeIndexDatabaseSchemaEditor(DatabaseSchemaEditorMixin, _DatabaseSchemaEditor):
    # Your own code
    ...

mybackend/base.py

from django.db.backends.postgresql.base import (
    DatabaseWrapper as _DatabaseWrapper,
)

from mybackend.schema import PostponeIndexDatabaseSchemaEditor


class DatabaseWrapper(_DatabaseWrapper):
    """Database wrapper"""

    SchemaEditorClass = PostponeIndexDatabaseSchemaEditor
    # Your own code
    ...

Execute apply_postponed management command every time after the migrate management command to create new postponed indexes.

Monitor PostponedSQL model instances to see errors on the SQL execution.

After the data is fixed, you can try to recreate the postponed invalid indexes just calling the apply_postponed migration command again. All not-applied indexes will be tried to create again.

NOTICE the apply_postponed management command doesn't have any explicit locking mechanics. Avoid starting this command concurrently with itself or another migrate command on the same database.

Intermediate migration state

Apart from standard Django migrations, using the postpone_index package leads to the intermediate migration state after the migrate management command finished:

• new model structure is applied
• indexes to be deleted are deleted
• indexes to be created are not created yet

You should be aware that if you introduce a new unique index or constraint, the database does not control uniqueness based on not yet created indexes at this time.

Your code works now as expected everywhere, except the code which is based on new unique constraints introduced in applied migrations.

Apply the apply_postponed run management command to make these new indexes work.

Any error while apply_postponed run execution is stored in the PostponedSQL model instance.

You can see erroneous lines using apply_postponed list command. See the [E] mark at the start of the line.

You also can see the error details using the format parameter of the apply_postponed list -f '... %(error)s' management command.

The apply_postponed run -x breaks execution on any error. You can see the error in the standard error or logging streams.

The apply_postponed run (without -x parameter) doesn't stop on error, but outputs warning to the log stream instead.

When the error happened, it most probably is caused by the non-unique records. Fix the data and try to execute apply_postponed run again to create an index.

After the successfull apply_postponed run execution, the migration state is finalised to be equal as if you applied the migration without postpone_index package at all.

The apply_postponed run management command marks all successfully executed PostponedSQL instances as done. You can see [X] mark at the start of the line produced by apply_ponsponed list management command.

You can cleanup done instances using apply_postponed cleanup management command. This step is optional.

Django testing

Django migrates testing database before tests. Always use POSTPONE_INDEX_IGNORE = True settings to avoid postpone index for the testing database.

If you want to check your own migration with the postpone index switched on, use the postpone_index.testing_utils.TestCase and override_settings Django feature with the following trick:

from django.core.management import call_command
from django.test import override_settings
from postpone_index.models import PostponedSQL
from postpone_index import testing_utils

class ModuleTest(testing_utils.TestCase):
    # Notice that the base TestCase is TransactionalTestCase

    @classmethod
    def setUpClass(cls):
        # If you want to have customized setUpClass, call the method of the base class
        super().setUpClass()

    @classmethod
    def tearDownClass(cls):
        # If you want to have customized tearDownClass, call the method of the base class
        super().tearDownClass()

    def test_my_special_migration_case(self):
        """Explicitly check my migration with postpone_index"""

        module_to_check = "my_module"           # Your Django App
        migration_before_the_check = "0005"     # Just before your migration
        migration_to_check = "0006"             # The migration you check

        # Notice that POSTPONED_INDEX_IGNORE is True by default while testing
        call_command('migrate', module_to_check, migration_before_the_check)

        with override_settings(
            POSTPONE_INDEX_IGNORE=False
        ):
            # Here we can check how it's going with `postpone_index` activated

            # Check whether your migration works as expected with postponed indexes
            call_command('migrate', module_to_check, migration_to_check)

            # Here you can check how the module works before apply_postponed
            ...

            # Check whether the indexes applied properly. The `-x` parameter
            # causes exception on errors
            call_command('apply_postponed', 'run', '-x')

Django settings

POSTPONE_INDEX_IGNORE

The setting totally switches off the functionality of the package.

Always use this setting in the test environment to avoid using postponed index creation for the test database.

May be used in a heterogeneous database environment to switch off the package functionality on unsupported databases.

POSTPONE_INDEX_ADMIN_IGNORE

The PostponedSQL model admin view is switched on by default. You can totally switch it off, or create your own admin class instead. Use postpone_index.admin.PostponedSQLAdminMixin as a base class if necessary.

Django database

The Django supports heterogeneous database environment in a single project. Every single database has it's own state of migrations executed by the manage.py migrate --database <alias>.

The apply_postponed command also supports selection of the database alias using similar syntax:

# The 'default' database alias is used as a default
python manage.py migrate
python manage.py apply_postponed

# A non-default database alias parameter has similar syntax
python manage.py migrate --database another-postgres-database
python manage.py apply_postponed --database another-postgres-database

Use POSTPONE_INDEX_IGNORE=1 environment to switch off the package functionality on migrations running on unsupported database engines like:

POSTPONE_INDEX_IGNORE=1 python manage.py migrate --database non-postgres-database

Special migrations to avoid postpone index

Sometimes you may need to avoid the postpone_index applied to a single migration.

Just include the PostponeIndexIgnoreMigrationMixin into a base class list for your special migration:

from django.db import migrations, models
from postpone_index.migration_utils import PostponeIndexIgnoreMigrationMixin

class Migration(PostponeIndexIgnoreMigrationMixin, migrations.Migration):
    ...