Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Writing Safe Database Migrations (DjangoCon Eur...

Writing Safe Database Migrations (DjangoCon Europe 2021)

My talk from DjangoCon Europe 2021

Markus H

June 02, 2021
Tweet

More Decks by Markus H

Other Decks in Programming

Transcript

  1. DjangoCon Europe 2021 • @m_holtermann Don’t Be Afraid Of Writing

    Migrations https://markusholtermann.eu/2016/04/dont-be- afraid-of-writing-migrations/
  2. DjangoCon Europe 2021 • @m_holtermann DjangoCon Europe 2021 • @m_holtermann

    #!/bin/sh set -e cmd="$@" until django-admin dbshell -- --command '\q'; do >&2 echo "Postgres is unavailable - sleeping " sleep 1 done >&2 echo "Postgres is up - executing command " django-admin migrate -v 2 exec $cmd
  3. DjangoCon Europe 2021 • @m_holtermann DjangoCon Europe 2021 • @m_holtermann

    from django.db import models class AddFieldModel1 (models.Model): name = models. CharField( max_length =10) class AddFieldModel2 (models.Model): name = models. CharField( max_length =10) from django.db import models class AddFieldModel1(models.Model): name = models.CharField( max_length=10) field = models. CharField( max_length =10, null=True) class AddFieldModel2(models.Model): name = models.CharField( max_length=10) field = models. CharField( default ="aaaaaaaaaa", max_length=10)
  4. DjangoCon Europe 2021 • @m_holtermann DjangoCon Europe 2021 • @m_holtermann

    from django.db import migrations, models class Migration(migrations.Migration ): dependencies = [ ("add_field", "0001_initial"), ] operations = [ migrations. AddField( model_name="addfieldmodel1 ", name="field", field=models.CharField(max_length=10, null=True), ), migrations. AddField( model_name="addfieldmodel2 ", name="field", field=models.CharField(default="aaaaaaaaaa", max_length=10), ), ]
  5. DjangoCon Europe 2021 • @m_holtermann DjangoCon Europe 2021 • @m_holtermann

    BEGIN; -- -- Add field field to addfieldmodel1 -- ALTER TABLE "add_field_addfieldmodel1 " ADD COLUMN "field" varchar(10) NULL; -- -- Add field field to addfieldmodel2 -- ALTER TABLE "add_field_addfieldmodel2 " ADD COLUMN "field" varchar(10) DEFAULT 'aaaaaaaaaa' NOT NULL; ALTER TABLE "add_field_addfieldmodel2 " ALTER COLUMN "field" DROP DEFAULT; COMMIT;
  6. DjangoCon Europe 2021 • @m_holtermann DjangoCon Europe 2021 • @m_holtermann

    from django.core.management.base import BaseCommand from django.db import transaction from safe_migrations.add_field .models import AddFieldModel1 CHUNK_SIZE = 5000 class Command(BaseCommand): def handle(self, *args, **options): updated = CHUNK_SIZE while updated >= CHUNK_SIZE: with transaction.atomic(): ids = ( AddFieldModel1.objects. filter(field__isnull=True) . select_for_update () . values_list("id", flat=True)[:CHUNK_SIZE] ) updated = AddFieldModel1.objects. filter(id__in=ids).update( field="bbbbbbbbbb" )
  7. DjangoCon Europe 2021 • @m_holtermann DjangoCon Europe 2021 • @m_holtermann

    from django.db import models class AddIndexModel1 (models.Model): name = models. CharField( max_length =10) class AddIndexModel2 (models.Model): name = models. CharField( max_length =10) from django.db import models class AddIndexModel1(models.Model): name = models.CharField( max_length=10, db_index=True) class AddIndexModel2(models.Model): name = models.CharField( max_length=10) class Meta: indexes = [ models. Index( fields =("name",), name="my_idx") ]
  8. DjangoCon Europe 2021 • @m_holtermann DjangoCon Europe 2021 • @m_holtermann

    from django.db import migrations, models class Migration(migrations.Migration ): dependencies = [ ("add_index", "0001_initial"), ] operations = [ migrations. AlterField( model_name="addindexmodel1 ", name="name", field=models.CharField(db_index=True, max_length=10), ), migrations. AddIndex( model_name="addindexmodel2 ", index=models.Index(fields=["name"], name="my_idx"), ), ]
  9. DjangoCon Europe 2021 • @m_holtermann DjangoCon Europe 2021 • @m_holtermann

    BEGIN; -- -- Alter field name on addindexmodel1 -- CREATE INDEX "add_index_addindexmodel1_name_adf72323" ON "add_index_addindexmodel1 " ("name"); CREATE INDEX "add_index_addindexmodel1_name_adf72323_like" ON "add_index_addindexmodel1 " ("name" varchar_pattern_ops); -- -- Create index my_idx on field(s) name of model addindexmodel2 -- CREATE INDEX "my_idx" ON " add_index_addindexmodel2 " ("name"); COMMIT;
  10. DjangoCon Europe 2021 • @m_holtermann DjangoCon Europe 2021 • @m_holtermann

    from django.contrib.postgres.operations import AddIndexConcurrently from django.db import migrations, models class Migration(migrations.Migration): atomic = False dependencies = [ ("add_index", "0001_initial"), ] operations = [ migrations.AlterField( model_name="addindexmodel1", name="name", field=models.CharField(db_index=True, max_length=10), ), AddIndexConcurrently ( model_name="addindexmodel2", index=models.Index(fields=["name"], name="my_idx"), ), ]
  11. DjangoCon Europe 2021 • @m_holtermann DjangoCon Europe 2021 • @m_holtermann

    BEGIN; -- -- Alter field name on addindexmodel1 -- CREATE INDEX "add_index_addindexmodel1_name_adf72323" ON "add_index_addindexmodel1" ("name"); CREATE INDEX "add_index_addindexmodel1_name_adf72323_like" ON "add_index_addindexmodel1" ("name" varchar_pattern_ops); -- -- Create index my_idx on field(s) name of model addindexmodel2 -- CREATE INDEX CONCURRENTLY "my_idx" ON "add_index_addindexmodel2" ("name"); COMMIT;
  12. DjangoCon Europe 2021 • @m_holtermann • Apply migrations before you

    deploy • Only go forwards & never look back • Only add nullable fields • Populate default value with management command • Add indexes concurrently • Use meaningful index names • Have working backups of your database • Test (complex) on production-like data Summary
  13. DjangoCon Europe 2021 • @m_holtermann Sources • https://www.postgresql.org/docs/10/sql-altertable.html • https://www.postgresql.org/docs/11/sql-altertable.html

    • https://www.citusdata.com/blog/2018/02/22/seven-tips-for -dealing-with-postgres-locks/ • https://docs.djangoproject.com/en/3.2/ref/contrib/postgres /operations/#concurrent-index-operations