Make a paginated table without offset limit

Question

I use cursor pagination and want to display my data like this:

at the bottom you can go to page 1 until 29. To calculate my pages I have to count all my rows. Is that not heavy everytime to count all rows? Is there maybe another solution or I can't get away from it to count all rows?

I also don't use offset so how can I do it with cursor pagination?

  SELECT 
      wp.id,
      wp.updated_wst,
      u.id as user_id, u.firstname, u.lastname, 
      c.id as company_id, c.company,
      wst.id as wst_id,
      wst.start_time,
      wst.end_time,
      wst.send_start_at,
      wst.send_end_at,
      wst.accepted_by,
      wst.accepted_at,
      l.name as location
  FROM workers_plan wp
  LEFT JOIN users u
      ON u.id = wp.user_id  
  LEFT JOIN clients c
      ON c.id = u.company_id
  LEFT JOIN workers_send_times wst
      ON wst.workers_plan_id = wp.id
  LEFT JOIN location_orders lo
      ON lo.id = wp.location_orders_id
  LEFT JOIN location l
      ON l.id = lo.location_id  
  WHERE lo.id = $1 AND wp.tenant_id = $2 AND (wp.id, wp.updated_wst) > ($3, $4)          
  GROUP BY wp.id, wst.id, u.id, c.id, l.name
  ORDER BY wp.id, wp.updated_wst LIMIT 50

Thats my current code. But to display pages I have to count rows and then calculate it. How would I do it (the best performant way)?

Answer 1

If you need to know how many pages there will be, you need to know the total result set count, and calculating that is expensive. Moreover, the data may change, and then the 29 pages may actually become 28 or 30, so the page count is misleading.

I want to encourage you to not calculate the number of result rows. Web search engines don't do it, and they know why. Here are some suggestions what to do instead:

• Simply don't show the number of pages. Provide a way to browse to the next page, 10 pages or so ahead and (perhaps) the last page.

• Use EXPLAIN (FORMAT JSON) on the query, then you can easily extract the estimated row count from the result. That allows you to say "page 1 of approximately 29" and should be good enough in most cases.

• If you really, truly need an exact row count, don't calculate that immediately. Rather, provide a button "exact row count" and calculate the (slow) result only when the user insists.

This article my be interesting for you.