fast_page VS Blazer

There are ways to mitigate the (although not eliminate) the slowing down of offset/limit pagination in later pages. The technique is called a "deferred join" and it is most effective in MySQL. The basic idea is to paginate as little data as necessary, and then do a self-join to get the rest of the data for a single page.

You can read more about it here: https://aaronfrancis.com/2022/efficient-pagination-using-def... or here https://planetscale.com/blog/fastpage-faster-offset-paginati....

There are libraries for Laravel (https://github.com/hammerstonedev/fast-paginate) and Rails (https://github.com/planetscale/fast_page) as well!

Cursor based pagination is wonderful, but sometimes you're stuck with offset/limit for whatever reason. Might as well make it fast.

In both Django and Rails, passing a QuerySet or ActiveRecord::Relation as an argument to a filter/where creates a subquery - in this case, https://github.com/planetscale/fast_page/blob/main/lib/fast_... creates SELECT ... WHERE id IN (SELECT id FROM ...).

So it's not exactly an inner join, but in theory it will be optimized to the same query plan as an inner join. That said, https://stackoverflow.com/questions/2577174/join-vs-sub-quer... has lots of commentary on why this shouldn't be depended on.

I try to avoid these tools wherever possible, given the choice I'd always go for tools like Blazer.

https://github.com/ankane/blazer

No such luck in my current role, Looker and PowerBI are both in use by different bits of the org and nobody has the ability to delve into the underlying figures.

As u/jaxn said you could use Blazer for this kind of thing. I would also look into materialized views or custom tables and a scheduled job that calculates the metrics they care about. That will take you a long way. Eventually you can use something like Metabase but I would put that off for as long as possible as it's really expensive and pretty involved.

And it's Open Source: https://github.com/evidence-dev/evidence

I'd also highly recommend Blazer https://github.com/ankane/blazer if you are into the Ruby on Rails world. It's super solid, and it's been an indispensable tool integrated to all my projects.

I use Ahoy too, but I don't have very good visibility into the data. I should spend more time building queries and creating charts. I should probably set up blazer as well: https://github.com/ankane/blazer

blazer

The Blazer gem provides a nice way to analyze the results easily. It is simple to install and allows SQL queries to run against tables. The query here shows that the candidate implementation is significantly faster than the original.