[Django_Rest_Framework] Eager loading

Straightforward usage of the Django REST Framework and its nested serializers can kill performance of API endpoints. The database is queried inside a loop, and the problem is called the N+1 selects problem.

straightforward

class Account(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32, null=True)

class Product(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32, null=True)

class Coupon(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=32, null=True)

class Order(models.Model):
    account = models.ForeignKey(Account) # One To Many
    product = models.ForeignKey(Product) # Many To One
    coupon = OneToOneField(Coupon)       # One To One

class OrderSerializer(ModelSerializer):
    account = AccountSerializer()
    product = productSerializer()
    coupon = couponSerializer()

• OrderSerializer will fetch all orders, and that requires a query through database.
• For the first returned order, fetch its account, and that requires another query through database.
• For the first returned order, fetch its product, and that requires another query through database.
• For the first returned order, fetch its coupon, and that requires another query through database.
• For the second returned order, fetch its account, and that requires another query through database.
• For the second returned order, fetch its product, and that requires another query through database.
• For the second returned order, fetch its coupon, and that requires another query through database.
• The database is queried inside a loop, and gets worse if there have other related fields in account, product, coupon.
• This happens because the Django ORM is in lazy loading, and it only fetches the minimum amount of data that needed to respond to the current query.

select_related

• For one-to-one or one-to-many relationships, that need data from the related objects.
• Can also refer to the reverse direction of a one-to-one relationships, and reverse back to the object.
• Translates into a SQL JOIN, and related rows are fetched in the same query.
• It will cause much larger result set when joining across a many relationship.

prefetch_related

• For many-to-many or many-to-one relationships, which can’t be done using select_related.
• Does a separate lookup for each relationship, and does the joining in Python.
• Translates into a SQL WHERE … IN, and query on the related table to select relevant rows.
• Additional queries in prefetch_related() are executed after the QuerySet has begun to be evaluated and the primary query has been executed.
• The result cache of the primary QuerySet and all specified related objects will then be fully loaded into memory.

Usage

• Setup eager loading in serializer

class EagerLoadingMixin:
  @classmethod
  def setup_eager_loading(cls, queryset):
    if hasattr(cls, "SELECT"):
      queryset = queryset.select_related(*cls.SELECT)

    if hasattr(cls, "PREFETCH"):
      queryset = queryset.prefetch_related(*cls.PREFETCH)

    return queryset

class OrderSerializer(ModelSerializer, EagerLoadingMixin):
  account = AccountSerializer()
  product = productSerializer()
  coupon = couponSerializer()

  SELECT = ["account", "coupon"]
  PREFETCH = ["product"]

• Activate eager loading in view

orders = Order.objects.all()
queryset = OrderSerializer.setup_eager_loading(orders)
data = OrderSerializer(queryset, many=True).data