Django REST Framework: slow browsable UI because of large related table

Question

I have a model in my API that has a foreign key to a table with tens of thousands of records. When I browse to that model's detail page in the browsable UI, the page load takes forever because it is trying to populate the foreign key dropdown with tens of thousands of entries for the HTML form for the PUT command.

Is there anyway to work around this? I think my best solution would be to have the browsable UI not show this field and thus prevent the slow load. People can still update the field by an actual PUT api request directly.

Thanks.

Ermine

Answer 1

Take a look at using an autocomplete widget, or drop down to using a dumb textfield widget.

Autocompletion docs here: http://www.django-rest-framework.org/topics/browsable-api/#autocomplete

Gerbil

Thanks Tom, can you point me to an example of using dumb textfield widget? Also, is there a way to filter the auto complete set that shows up in autocomplete_light? – Ermine

Answer 2

Note that you can disable the HTML form and keep the raw data json entry with:

class BrowsableAPIRendererWithoutForms(BrowsableAPIRenderer):
    """Renders the browsable api, but excludes the forms."""
    def get_rendered_html_form(self, data, view, method, request):
        return None

and in settings.py:

REST_FRAMEWORK = {
    'DEFAULT_RENDERER_CLASSES': (
        'rest_framework.renderers.JSONRenderer',
        'application.api.renderers.BrowsableAPIRendererWithoutForms',
    ),
}

this will speed things up and you can still post from from the browsable ui.

Deteriorate

Answer 3

You can force using TextInput with simple:

from django.forms import widgets
...
class YourSerializer(serializers.ModelSerializer):
    param = serializers.PrimaryKeyRelatedField(
        widget=widgets.TextInput
    )

Or after proper autocomplete_light configuration:

import autocomplete_light
...
class YourSerializer(serializers.ModelSerializer):
    paramOne = serializers.PrimaryKeyRelatedField(
        widget=autocomplete_light.ChoiceWidget('RelatedModelAutocomplete')
    )
    paramMany = serializers.PrimaryKeyRelatedField(
        widget=autocomplete_light.MultipleChoiceWidget('RelatedModelAutocomplete')
    )

To filter out results which are returned by autocomplete_light this part of documentation.

Shedd

This is a great answer - but no longer valid, from DRF docs: "There are a variety of packages for autocomplete widgets, such as django-autocomplete-light, that you may want to refer to. Note that you will not be able to simply include these components as standard widgets, but will need to write the HTML template explicitly. This is because REST framework 3.0 no longer supports the widget keyword argument since it now uses templated HTML generation. Better support for autocomplete inputs is planned in future versions." – Bestialize

Link to the above comment: django-rest-framework.org/topics/browsable-api/#autocomplete – Colossians

Answer 4

It is a very good question for none obvious problem. The fault assumptions that you are suck into while learning Django, and related to it plugins DRF while reading official documentation, will create a conceptual model that is simply not true. I'm talking here about, Django being explicitly design for relational databases, does not make it fast out of the box!

Problem

The reason for Django/DRF being slow while querying a model that contain relationships (e.g. one-to-many) in ORM world is known as N+1 problem (N+1, N+1) and is particularity noticeable when ORM uses lazy loading - Django uses lazy loading !!!

Example

Let's assume that you have a model that looks like this: a Reader has many Books. Now you would like to fetch all books 'title' read by 'hardcore' reader. In Django you would execute this by interacting with ORM in this way.

# First Query: Assume this one query returns 100 readers.
> readers = Reader.objects.filter(type='hardcore')

# Constitutive Queries
> titles = [reader.book.title for reader in readers]

Under the hood. The first statement Reader.objects.filter(type='hardcore') would create one SQL query that looks similar to that. We assume that it will return 100 records.

SELECT * FROM "reader" WHERE "reader"."type" = "hardcore";

Next, for each reader [reader.book.title for reader in readers] you would fetch related books. This in SQL would look similar to that.

SELECT * FROM "book" WHERE "book"."id" = 1;
SELECT * FROM "book" WHERE "book"."id" = 2;
...
SELECT * FROM "book" WHERE "book"."id" = N;

What you left with is, 1 select to fetch 100 readers, and N selects to get books -where N is number of books. So in total you have N+1 queries against database.

Consequence of this behavior is 101 queries against database, that at the end results with extremely long loading time for small amount of data and making Django slow!

Solution

Solution is easy but not obvious. Following official documentation for Django or DRF does not highlight the problem. At the end you follow the best practices and you end up with slow application.

To fix slow loading problem you will have to eager load your data in Django. Usually, this mean using appropriate prefetch_related() or select_related() method to construct SQL INNER JOIN on models/tables, and fetch all your data just in 2 queries instead of 101.

Related Reads

• Solution DRF 1
• Solution DRF 2
• Solution Django
• N+1 Link 1
• N+1 Link 2

Townley

Apologies for the down-vote (I lose a point too), but the options for the select list will be extracted in a single query, so this is not an N+1 problem. The delay, if I'm reading the question right, is due to rendering thousands+ of them as OPTION tags in the browser, not querying them. – Colossians

@Colossians Have you profiled your website? Do you see significant time for DOM rendering? Other words, majority of time comes from frontend (rendering of elements) rather than backend (fetching and serializing)? – Townley

Thanks Lukasz, good point, I have not and explained myself lazily. I suspect the delay is the time to serialize and transfer the HTML rather than the browser rendering said HTML. – Colossians

Answer 5

There is a section in the DRF documentation that gives the following suggestion:

author = serializers.HyperlinkedRelatedField(
    queryset=User.objects.all(),
    style={'base_template': 'input.html'}
)

If a text field is too simple, as @Chozabu mentioned above in a comment long before I wrote this answer, they recommend manually adding an autocomplete in the HTML template:

An alternative, but more complex option would be to replace the input with an autocomplete widget, that only loads and renders a subset of the available options as needed. If you need to do this you'll need to do some work to build a custom autocomplete HTML template yourself.

There are a variety of packages for autocomplete widgets, such as django-autocomplete-light, that you may want to refer to. Note that you will not be able to simply include these components as standard widgets, but will need to write the HTML template explicitly. This is because REST framework 3.0 no longer supports the widget keyword argument since it now uses templated HTML generation.

Colossians

Answer 6

This is apparently a known issue with the DRF BrowsableAPI.

If you use the DjangoFilterBackend as your default DRF filter backend, you are in luck. It's easy to disable generating these slow-building filters in the BrowsableAPI template - for all views, or just a single view.

Just subclass DjangoFilterBackend like so:

class DjangoFilterBackendWithoutForms(DjangoFilterBackend):
    """
    The Browsable API renders very slowly for models with foreign keys to large tables.
    As a workaround, views can swap in this filter backend to skip form rendering.
    """
    def to_html(self, request, queryset, view):
        return None

Then use that as your default filter backend or pick and choose which views you want to disable the filter forms on:

class MyThingyViewSet(viewsets.ModelViewSet):
    queryset = models.MyThingy.objects.all()
    serializer_class = serializers.MyThingySerializer
    filter_backends = (DjangoFilterBackendWithoutForms,)

Pyxidium

Answer 7

For me, the views for the browsable UI were still slow when the POST requests were available due to the rendering of the __str__ representation. To speed those up as well I use a custom manager for the Model-Class and overwrite the get_queryset() function to load the related fields by default.

Example:

from django.db import models

class OptimizedMyClassManager(models.Manager):
    """Override the default Manager to load related fields."""

        def get_queryset(self):
        """Load the related fields by default for better performance."""
        return super().get_queryset().select_related(
            "created_by", "other_field__some_subfield",
        )

class MyClass(models.Model):
    objects = OptimizedMyClassManager()

    other_field= models.ForeignKey(MyOtherClass, on_delete=models.CASCADE)
    created_by = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.PROTECT)

Demurrer