Optimize pagination on "Explore Snippets"

Problem to solve

This is a follow-up to #30877 (closed).

We've optimized the "Explore Snippets" page from ~20s response times to around ~1-2s:

• gitlab-foss!32576 (merged) got rid of N+1 queries
• !18092 (merged) reduced the scope of the page to only show personal snippets, and optimized the DB indexes

But the snippet queries are still taking almost ~200ms each (one for the total count, another for the current page).

Intended users

Snippet enthusiasts.

Further details

Proposal

We can improve response times further by using "endless" pagination, i.e. showing only a "Next" button (as with projects on https://gitlab.com/explore/) instead of buttons for each page, which saves us one whole expensive query to get the total count of snippets.

Another option we could look into (additionally) is using keyset pagination, though I'm not sure yet if we have a reusable pattern we could use here (there's lib/api/helpers/pagination.rb for APIs only).

Permissions and Security

Documentation

Testing

What does success look like, and how can we measure that?

• Individual SQL queries should be <100ms
• Total response times should be <2s

https://dashboards.gitlab.net/d/thYzurImk/rails-controllers?orgId=1&var-action=Explore::SnippetsController%23index&var-database=influxdb-01-inf-gprd

What is the type of buyer?

Links / references