使用django-filter查询40k+数据超时，求优化方案与解决办法

Hey there! Dealing with 40k+ records and timeouts even with pagination is super frustrating—let’s break down the practical fixes that work for Django + django-filter setups:

This is the low-hanging fruit that often fixes 80% of timeout issues:

• Add indexes to filtered fields: For your created_at__gte query, make sure the created_at field has a database index. Update your model:

  class YourModel(models.Model):
      created_at = models.DateTimeField(db_index=True)
      # other fields...
  

  Then run migrations to apply the index. If you use multiple filter conditions together (e.g., created_at__gte + category), create a composite index for those fields to speed up combined queries.
• **Avoid SELECT ***: Use QuerySet.only() or QuerySet.values() to fetch only the fields your frontend needs. This reduces data transfer from the database and cuts down on memory usage:

  # Instead of YourModel.objects.all()
  queryset = YourModel.objects.only('id', 'title', 'created_at')
  

• Simplify custom filters: If you’re using custom filter methods in your FilterSet, check if they’re triggering extra database queries or inefficient logic. Stick to built-in filters like DateTimeFilter whenever possible—they’re optimized for performance.
• Pre-filter the base queryset: In your FilterSet’s get_queryset method, add preliminary filters to reduce the dataset size early (e.g., exclude soft-deleted records):

  class YourFilterSet(filters.FilterSet):
      def get_queryset(self):
          queryset = super().get_queryset()
          # Exclude deleted records first
          return queryset.filter(is_deleted=False)
  

Django’s default offset-based pagination gets slow with large datasets because the database has to skip thousands of rows. Try these fixes:

• Switch to cursor-based pagination: Use Django’s CursorPaginator (available since Django 2.0) which uses a unique, ordered field (like created_at + id) to paginate without offset overhead. Example:

  from django.core.paginator import CursorPaginator
  
  def your_view(request):
      queryset = YourModel.objects.order_by('created_at', 'id')
      paginator = CursorPaginator(queryset, page_size=20)
      cursor = request.GET.get('cursor')
      page = paginator.get_page(cursor)
      # return response...
  

• Limit maximum page size: Prevent users from requesting 1000+ records per page by setting a max_page_size in your pagination setup. Keeping pages small (20-50 records) reduces the load per request.

If your filtered data doesn’t need to be real-time, caching can drastically reduce database load:

• Cache entire views: Use Django’s cache_page decorator for your filtered view to cache responses for a set duration:

  from django.views.decorators.cache import cache_page
  
  @cache_page(60 * 15)  # Cache for 15 minutes
  def your_filter_view(request):
      # view logic...
  

• Cache frequent filter combinations: For popular filters (e.g., "last 30 days"), manually cache the query results using Django’s cache framework. Store the list of object IDs, then fetch them in bulk later to avoid re-running the query.

Before making random changes, identify the bottleneck:

• Use django-debug-toolbar to see exactly how long each query takes and spot N+1 query issues.
• Run EXPLAIN ANALYZE on your slow query directly in the database (e.g., PostgreSQL) to check if indexes are being used correctly. If the query is doing a full table scan, your index isn’t working as expected.
• Check database connection limits: If you have high concurrency, insufficient database connections can cause timeouts. Adjust CONN_MAX_AGE in your settings.py or use a connection pool tool like pgBouncer (for PostgreSQL).

If even after all optimizations the query is still slow (e.g., complex aggregations with 40k+ records), offload the work to an async task:

• Use Celery to run the filter query in the background. Return a "processing" status to the frontend, then notify the user when the results are ready. This works best for reports or non-urgent data requests.

内容的提问来源于stack exchange，提问作者Jackquin