1

I have around 6 million objects in my database. For these objects, I need to have a structure page, where the users would browse through the aforementioned 6 million objects as paginated content.

My code for the pagination is a total duplicate of what is given in the documentation of Django:

def listing(request):
    movie_list = Movie.objects.all()
    paginator = Paginator(movie_list , 100) # Show 100 movies per page.

    page_number = request.GET.get('page')
    page_obj = paginator.get_page(page_number)
    return render(request, 'movies.html', {'page_obj': page_obj})

Howevre, most of the resulting 60,000 pages with the movies are too long to open, and give 504 timeout errors, which also results in the content being unreadable for GoogleBot (which is actually the main aim of the structure page).

What can be the workaround to prevent these timeouts and ensure the normal load speed for the large paginated content in Django? The database I am using is PostgreSQL.

Edmond
  • 59
  • 2
  • 15
  • You seriously expect a user to browse through 6mln or even 60k movies manually? He won't even look through the first page. I advice changing the design, adding proper filtering/categorization and indexation. – freakish Aug 15 '21 at 14:27
  • @freakish The paginated content is primarily for avoiding orphan pages and generating proper sitemaps with interlinked pages. – Edmond Aug 15 '21 at 14:30
  • `movie_list = Movie.objects.all()`, really you expect to pull in 6 million rows at once and not to get a timeout? That pretty much defeats the purpose of pagination which is to fetch only what is needed for a page at a time. – Adrian Klaver Aug 15 '21 at 14:46
  • @AdrianKlaver That's why I'm asking what turnaround may exist. For the admin page, it is a dumb paginator, but this approach obviously does not work when dealing with SEO and sitemaps. – Edmond Aug 15 '21 at 14:48
  • 3
    @AdrianKlaver, `Movie.objects.all()` is a lazy queryset. The real database hit fetches only 100 rows. – Paul R Aug 15 '21 at 15:02
  • 2
    I did not spend enough time in the pagination code. It is taking slices of the QuerySet which would be lazy as you say. From [Pagination](https://docs.djangoproject.com/en/3.2/ref/paginator/#django.core.paginator.Paginator) the slicing leads to this "If you’re using a QuerySet with a very large number of items, requesting high page numbers might be slow on some databases, because the resulting LIMIT/OFFSET query needs to count the number of OFFSET records which takes longer as the page number gets higher." – Adrian Klaver Aug 15 '21 at 15:52
  • @AdrianKlaver, what is the workaround? – Paul R Aug 15 '21 at 17:46
  • As @freakish said, break it down into smaller bites by using filtering/categories. – Adrian Klaver Aug 15 '21 at 17:48
  • I've created very similar things and have not experienced the same slowness that you are describing. Are you joining foreign keys or doing anything else that may make it slow? Can you take a look at the SQL queries that are being executed and CPU time? Django-debug-toolbar can help with getting them. – Zev Aug 15 '21 at 19:32

0 Answers0