A Django Admin Paginator For MariaDB With Many Many Rows

admin.py

from django.contrib import admin
from django.core.paginator import Paginator
from django.db import connection, transaction, OperationalError
from django.utils.functional import cached_property


class MariaDbInnoDbPaginator(Paginator):
    """
    Paginator that enforces a 2 second timeout on the count operation.
    If the operations times out, an approximate value is returned.
    """
    @cached_property
    def count(self):
        with transaction.atomic(), connection.cursor() as cursor:

            # for MySQL use max_execution_time; also in MySQL this is defined in milliseconds
            # limit execution time to 2 seconds
            cursor.execute("SET SESSION max_statement_time = 2")
            try:
                # let Django's paginator do it's work
                return super().count
            except OperationalError:
                try:
                    # this must be a large table to let's approximate the total records
                    cursor.execute(
                        "SELECT TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s",
                        (connection.settings_dict['NAME'], self.object_list.model._meta.db_table)
                    )
                    row = cursor.fetchone()
                    if row is not None:
                        return row[0]
                    else:
                        return 0
                except:
                    # return some arbitrary number if all else fails
                    return 9999999999


class TunedAdmin(admin.ModelAdmin):
    # set our paginator here
    paginator = MariaDbInnoDbPaginator
    # this is required to stop showing full counts when filtering results
    show_full_result_count = False

You should now inherit from TunedAdmin instead of admin.ModelAdmin in your admin.py.

Setting the timeout and reading approximate record counts can be adapted to work with
other DBMS like MySql and PostgreSql.

For MySql:

• To set statement timeout use max_execution_time which should be defined in milliseconds
• The query to get approximate record counts remains the same

For PostgreSql:

• To set statement timeout (definied in milliseconds): SET LOCAL statement_timeout TO 200
• To get approximate record counts: SELECT reltuples FROM pg_class WHERE relname = %s. Pass the table name here.

References:

• https://code.djangoproject.com/ticket/8408#comment:59
• https://hakibenita.com/optimizing-the-django-admin-paginator#getting-creative
• https://craiglabenz.me/2013/06/12/how-i-made-django-admin-scale/