large json data in django model slows down any query

user_11302328 0
Viewed 376

I have a django model that looks like this:

class SomeModel(Model):
    some_field = CharField(max_length=10)
    large_data = JsonField(blank=True, null=True)

the "large_data" field can get really large, like 150mb. I know this is really bad practice, most of the data could decoupled into other sql database tables, but this is legacy application and the impact of the rework would be huge.

It turns out that any query to this table is really slow, sometimes I just want to modify the "some_field" value, but even when I save that field individually like in the code below, the update query takes ages

obj.some_field = "ice cream"
obj.save(update_fields=["some_field"])

I am sure the large_data is the guilty because if I set its value to "None" then the save operation is committed right away.

Are there optimization tricks that can be applied to my example without reworking the json field?

Thanks in advance

mysql django-models django sql
2 Answers

Fetching the object likely takes too much time. You can work with .only(…) [Django-doc] to only retrieve the primary key and some_field:

obj = SomeModel.objects.only('pk', 'some_field').get(pk=some_pk)
obj.some_field = "ice cream"
obj.save()

You can also .update(…) [Django-doc] in a queryset, in that case the object is never loaded in memory, so:

SomeModel.objects.filter(pk=some_pk).update(some_field='ice cream')
user_675790

Find the JSON fields that are most commonly searched on. Copy them into separate, MySQL, columns. Then change the code to fetch on them. This should speed up locating the desired rows, and only then does it get a steam shovel to copy the JSON to the app.

If many Selects only fetch a small collection of fields, also copy those out of JSON into MySQL columns. Then, if you can avoid fetching the JSON whale, such Selects will be faster.

I hope you are not actually Updating columns in the JSON; that would be miserably slow.

user_17668310
Related