Django: Really slow RunPython migration on related models

Question

I am trying to optimise the speed of a migration on a large table (250K objects). The goal is to add to each row a user field based on the user of an object related to this row:

I tried using an F expression but sadly django won't allow relations in them. Note that I am mostly a beginner in SQL :)

## models.py
class Band(models.Model):
    user = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.CASCADE,
        null=True,
        default=None)
    ...

class Album(models.Model):
    band = models.ForeignKey(
        Band
        on_delete=models.CASCADE,
        null=True,
        default=None)

    user = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.CASCADE,
        null=True,
        default=None)
    ...

## 0035_album_add_user.py
def forwards_add_user(apps, schema_editor):
    Album = apps.get_model('band', 'Album')
    db_alias = schema_editor.connection.alias

    albums = Album.objects \
        .using(db_alias) \
        .filter(band__isnull=False) \
        .select_related('band', 'band__user')

    for each in albums:
        each.user = each.band.user

    Album.objects \
        .using(db_alias) \
        .bulk_update(albums, ['user'])

class Migration(migrations.Migration):
    dependencies = [ ... ]

    operations = [
        migrations.RunPython(forwards_add_user, reverse_add_user),
    ]

This migrations currently takes an hour on my local with 100% usage on the database for the whole duration. I have a better CPU thank my production database so this worries me a lot, downing the production database for an hour is not really an option.

I'm looking at two different solutions both of which I have no idea of how to accomplish : - Optimize more the python code to make it run faster - Throttle the database's CPU consummation during the migration so it stays available. Both being the best case scenario :)

I'm running Python3.6.9 with Django2.2.9 and PostgreSQL10.6 on RDS in production an in a Docker in local.

Thanks !

FYI https://stackoverflow.com/questions/12518560/django-update-table-using-data-from-another-table — Ivan Starostin, Feb 06 '20 at 16:21

iklinac · Accepted Answer · 2020-02-06T16:06:49.250

4

Use subquery to update field, something similar to following as you cannot use F() function in update joining tables

Album.objects.update(
    user=Subquery(
        Band.objects.filter(
            id=OuterRef('band')
        ).values('user')[:1]
    )
)

edited Feb 06 '20 at 16:06

answered Feb 06 '20 at 16:01

iklinac

14,944
4
28
30

Thanks it worked ! I had completly forgotten about SubQueries. I had to add a `[:1]` after the values call though. – Taek Feb 06 '20 at 16:06

Django: Really slow RunPython migration on related models

1 Answers1