Hacker News Reader

Comment by fbonetti | Hacker News Reader

It's really easy to paginate data with SQL - just use LIMIT and OFFSET.

  limit = page_size
  offset = (current_page - 1) * limit

For example, let's say you have a table with 1000 products in it, and you want to display page 3 with a page size of 50. Here's how you would write it:

  SELECT *
  FROM products
  LIMIT 50
  OFFSET 100

View on HN

The downside is that OFFSET is slow if you have a lot of items, like on a forum.

At which point you could assign each post in a topic an indexed `position` column that always increases from 0 within that topic, which lets you jump to arbitrary pages and parameterize perPage.

    fromPos = (page - 1) * perPage
    
    SELECT *
    FROM posts
    WHERE topic_id = $1
      AND position >= fromPos
      AND position < fromPos + perPage
    ORDER BY id

For simpler needs, your "Next" button could load items that come after the last ID on the current page but with a LIMIT.

    <ul>
      {% for item in items %}
        <li>{{ item }}</li>
      {% endfor %}
    </ul>

    <a href="/items?afterId={{ items[items.length - 1].id }}">Next</a>

    SELECT *
    FROM items
    WHERE id > $afterId
    LIMIT $perPage

That's fast and really easy.

This sounds like a really premature optimization to me. Let the DB do it's job unless you have an explain trace showing it's slow for your workload. Trying to do things like sequence columns adds complexity, when this is often handled by the engine.

You need this if it's for a feed that gets frequent updates. Imagine a paginated Twitter feed, and the user clicks a link with ?page=3, and suddenly there's a new tweet, causing page 3 to have a tweet the user saw on page 2. Using afterId solves that.

OFFSET is not "handled by the engine". It heapsorts the entire dataset before you offset to a page.

The top-level comment is asking for good ways to paginate. OFFSET works on localhost and with hundreds of rows. My solution is one that works in production once OFFSET fails you. For me, it was day 2.

It was day 2 for you, with the specific engine you used.

You don't need to heapsort the entire dataset before you offset to a page, and there are engines capable of doing this.

Or they could be speaking from experience, because back in the day when webforums where the place to make online communities, as they grew beyond a certain size this sort of thing became THE bottleneck, especially for large threads.

I empirically know this was an issue back then. I also agree it's something a DB engine should handle, and I do hope that databases do a better job today.

So instead of calling out "premature optimization", maybe instead come with a practical example of a DB engine that properly handles this?

I just wrote about doing it this way yesterday.

http://www.mozartreina.com/

You should at least reference the fact that with MySQL, the server has to step through OFFSET result rows before it can start streaming you data. This can get surprisingly expensive, surprisingly quickly. See http://stackoverflow.com/questions/4481388/why-does-mysql-hi...

And if the data frequently changes during pagination?

That's why it's often better to use a cursor in your client-facing API (or a pseudo-cursor that maps to something you have in your database), rather than passing the limit/offset through.

Reply on news.ycombinator.com