Paginator solutions for when default paginator encounters scaling issues

[Harvey Bellini]

The new paginator provides a way to navigate results without performing a COUNT(*) query, instead determining the existence of a "next page" by fetching N + 1 items.

Problem

On large PostgreSQL or MySQL tables, SELECT COUNT(*) requires a full or partial index scan that scales linearly with data size. Even if the actual data fetch is fast (via indexed slicing), the Paginator is forced to wait for the count to:

• Validate the page number
• Determine if a "next" page exists
• Calculate the total number of pages for the UI

In many modern UIs (like "Infinite Scroll" or "Next/Prev" only navigation), the total page count is unnecessary and actively hurts performance.

​https://github.com/django/new-features/issues/136

[Simon Charette]

Shouldn't we favour adopting cursor based pagination in core instead?

The latter doesn't require COUNT and avoid using OFFSET entirely which can be even of a performance problem for large offsets.

To me CursorPaginator has the same limitation as this suggested N+1 item approach (no listing of pages, has next page can also be achieved by fetching an extra item) but with less drawback (no OFFSET) so I see limited benefits in maintaining a solution that only address performance concerns partially. Now that we have proper totally ordered expression inference in core there should be less obstacle to implementing cursor pagination than previously.

[Harvey Bellini]

I agree cursor based pagination is the ideal technical solution but that doesn't mean there isn't value in the CountlessPaginator approach.

Does it solve the COUNT(*) penalty, yes. Does it solve the OFFSET penalty, no. But it would provide a positive impact for all cases where users rarely dig past the first few pages. Personally I can't remember the last time I went past say page 5 on a paginated display.

I don't think these features are mutually exclusive.

Admittedly this is search engine related, and written by a company trying to sell you their SEO software, but I think the psychology still applies: ​https://kafkai.ai/articles/ai-seo/why-users-never-scroll-past-page-one/

[Harvey Bellini]

?

[Sarah Boyce]

Accepting as the Steering Council had agreed to pursue this. We can repurpose the ticket for cursor based pagination in future if this is what is decided
I have cc-ed most members of the current Steering Council in case they wish to discuss more here

[Carlton Gibson]

This was moved forward to “Idea Refinement” with the comment that there’s “a clear need for better pagination options for when people hit scaling problems with the default paginator.” — that doesn’t entail an SC commitment to a particular implementation. (Quite the contrary!)

I suspect cursor based pagination is going to be more generally useful, and then there’s no reason folks can’t maintain their own paginator if they have specific other needs. I doubt we need to maintain numerous options in Django itself. (These are just suspicions at this point — I haven’t reviewed the various strategies in some time.)

[Sarah Boyce]

Historical ticket in case useful #26451
Changing to "Someday/Maybe" until the implementation idea is further defined as “Idea Refinement” includes the decision to make a 3rd party package (see #26451)
Updating the title to capture more of the goal of the ticket