Accelerating Text Search in Django Admin for Large Tables

The Challenge of Searching Large Tables in Django Admin

At Ubiquiti, our systems often deal with enormous datasets — tables with over 1 billion records are not uncommon. While Django Admin is a powerful tool for managing and interacting with data, its default search functionality can become a bottleneck in these scenarios. The built-in icontains query used in search_fields scans every column included, leading to sluggish performance as the dataset scales.

Consider a user management table with billions of records:

• We want email prefix search to quickly locate users whose email addresses start with a specific term (e.g., searching for john@domain.com should instantly find matches).
• We also want email alias search that identifies aliases for a given email using regular expressions (e.g., searching for schwannden@gmail.com should also return schwannden+test1@gmail.com and other variants).
• We also want to search user by UUID, using index would be very fast in support cases when we can obtain user’s UUID.

Here’s the catch:

• These two searches are independent use cases by. A regex-based alias search is computationally expensive and shouldn’t be performed unless explicitly needed.
• Django Admin’s get_search_results function only accepts a single search_term, making it impossible to differentiate between the prefix search and alias search.

To make matters worse, adding multiple search fields by overwriting the change_list template doesn’t help because Django passes all queries through the same q=<some search term> parameter, preventing us from distinguishing between search types.

The Solution: Adding Custom Filters for Large Tables

The Django list_filter feature could be an ideal solution for custom filtering logic. However, its default implementation generates choices for filtering, which is impractical for fields like UUIDs or emails where precomputing billions of choices is infeasible.

This problem led us to explore a creative approach to customize the list_filter component and integrate specialized search functionality directly into the Django Admin interface. After much research, we came across a post that inspired us to extend Django’s list_filter to support dynamic input fields for our custom search needs.

Here’s how we implemented it:

Create a Custom Input Filter Template

First, we added a new template to render input fields dynamically in the admin filter area.

Create the file templates/admin/input_filter.html:

{% load i18n %} 
 
<details data-filter-title="{{ title }}" open class="filter-item"> 
    <summary> 
      {% blocktranslate with filter_title=title %} {{ filter_title }} {% endblocktranslate %} 
    </summary> 
    <ul> 
        <li> 
            {% with choices.0 as all_choice %} 
            <form method="GET" action=""> 
                {% for k, v in all_choice.query_parts %} 
                <input type="hidden" name="{{ k }}" value="{{ v }}" /> 
                {% endfor %} 
                <input 
                    type="text" 
                    value="{{ spec.value|default_if_none:'' }}" 
                    name="{{ spec.parameter_name }}"/> 
                {% if not all_choice.selected %} 
                    <strong><a href="{{ all_choice.query_string }}">⨉ {% trans 'Remove' %}</a></strong> 
                {% endif %} 
            </form> 
            {% endwith %} 
        </li> 
    </ul> 
  </details>

2. Define an InputFilter Base Class

Next, we created an InputFilter class to manage custom input fields:

class InputFilter(SimpleListFilter): 
    """ 
    Base class for filters that take a text input 
    """ 
 
    template = "admin/input_filter.html" 
    parameter_name: str 
    title: str 
 
    def lookups(self, request, model_admin): 
        # Dummy, required to show the filter. 
        return ((),) 
 
    def choices(self, changelist): 
        # Grab only the "all" option. 
        all_choices = super().choices(changelist) 
        try: 
            all_choice = next(all_choices) 
            all_choice["query_parts"] = ( 
                (k, v) for k, v in changelist.get_filters_params().items() if k != self.parameter_name 
            ) 
            yield all_choice 
        except StopIteration: 
            return  # Clean exit when no values remain 
 
    def queryset(self, request, queryset): 
        value = self.sanitized_value() 
        if value is not None: 
            return queryset.filter(**{f"{self.parameter_name}": value}) 
        return queryset 
 
    def sanitized_value(self): 
        value = self.value() 
        if value is None: 
            return None 
        return value.strip()

With this base filter, we can now inplement our own filters, we can use parameter_name to overwrite the way we wish query is made, and we could overwrite def queryset to provide more customized search query like finding email aliases.

class UUIDFilter(InputFilter): 
    """ 
    Filter for UUID exact match 
    Ignore filter if the value is not a valid UUID 
    """ 
 
    parameter_name = "id" 
    title = "By User ID" 
 
    def sanitized_value(self): 
        value = super().sanitized_value() 
        if value is None: 
            return None 
        try: 
            return uuid.UUID(value) 
        except ValueError: 
            return None 
 
 
class EmailFilter(InputFilter): 
    """ 
    Filter for email prefix search on the user model itself 
    """ 
 
    parameter_name = "email__startswith" 
    title = "By Email Prefix" 
 
 
class EmailAliasFilter(InputFilter): 
    """ 
    Filter for email alias search 
    For example, "test+alias@example.com" will be matched by "test@example.com" 
    """ 
 
    parameter_name = "email" 
    title = "Search Email Alias" 
 
    def queryset(self, request, queryset): 
        value = self.sanitized_value() 
        if value is not None: 
            split_result = value.split("@") 
            if len(split_result) != 2: 
                return queryset.none() 
            local_part, domain = split_result 
            # Adjust the local part to remove any existing '+' and its suffix 
            local_part = local_part.split("+")[0] 
            regex = rf"{local_part}(\+.+)?@{domain}" 
            return queryset.filter(email__iregex=regex) 
        return queryset

And in our admin model, we can mix this custom filters with built in field filter like:

class UserAdmin(admin.ModelAdmin): 
    list_filter = (UUIDFilter, EmailFilter, EmailAliasFilter, "is_legacy")

The built in choice filter still work on fields with less options.

Reusing filter

Assuming we have another model with a foreign key poiting to User, and we wish to filter this record by its related user’s email, we simply need to inherit from the original EmailFilter and change the parameter_name

class ShoppingCartUserFilter(EmailFilter) 
    parameter_name = "user__email" 
 
class CartAdmin(admin.ModelAdmin): 
    list_filter = [ShoppingCartUserFilter, "cart_type"]

Bravo: A Scalable Solution

This approach transforms Django Admin’s search functionality into a powerful, scalable tool capable of handling massive datasets efficiently. By introducing customizable filters, you can address diverse search scenarios without compromising performance. Whether managing billions of records or implementing tailored search logic, this method ensures your admin remains user-friendly and lightning-fast.

“By wisdom a house is built, and by understanding it is established; by knowledge the rooms are filled with all precious and pleasant riches.”
Proverbs 24:3–4