Django User Model Optimization

Question

UserModel has many entries on it. The 3 UserModel queries run very slow. How to make needed changes and optimizations to UserModel to make the 3 queries run faster?

class UserModel(models.Model):
  username = CharField(max_length=255)
  role = JSONField() # format of ['admin', 'operator']

UserModel.objects.filter(username='john').first()
UserModel.objects.filter(username__contains='doe').first()  
UserModel.objects.filter(role__contains='operator').first()

If there is an option you should change JSONField to separate table/M2M field. Storing JSON in relational database most of the times have no sense and should be avoided due to performance — PTomasz, Mar 20 '23 at 09:59

score 0 · Accepted Answer · answered Mar 20 '23 at 17:22

If you have many records in the table, you have to use indexes. Let's go by the queries.

UserModel.objects.filter(username='john') just filters records for equality, so a BTree index will simply handle this.
UserModel.objects.filter(username__contains='doe') is translated to ILIKE '%doe%', so we need something like trigram index. To use that add django.contrib.postgres in your INSTALLED_APPS and activate the pg_trgm extension on PostgreSQL. You can install the extension using the TrigramExtension migration operation.
UserModel.objects.filter(role__contains='operator') filters only records which contain given key. This query can be optimized with Gin index. See this answer for implementation details.

Django User Model Optimization

1 Answers1