No Offset - Pagination with the Seek Method

Who doesn't know them, the "The system is too slow" support tickets. The new GraphQL interface works flawlessly in tests, but suddenly causes problems in production. The ever-growing amounts of data in particular can present us with challenges that we are often not aware of, or hide behind seemingly simple requirements such as a GraphQL interface.
In this talk we'll take a look at a Spring Data/GraphQL application and take a look at offset-based paging and why an alternative approach, also known as the seek method or keyset pagination, can be useful when dealing with large datasets.

Transcript

1. No Offset
   Pagination using the Seek Method
   Christoph Strobl (he/him)
   Spring Data Team @ VMware, Inc.
   May, 2023
   

   View Slide

2. Confidential │ © VMware, Inc. 2
   

   View Slide

3. Confidential │ © VMware, Inc. 3
   Pager Duty
   😱
   

   View Slide

4. Confidential │ © VMware, Inc. 4
   0
   2
   4
   6
   8
   10
   12
   09:15 09:30 09:45 10:00
   App - Dashboard
   Response Time Application
   🔥
   

   View Slide

5. Confidential │ © VMware, Inc. 5
   0
   2
   4
   6
   8
   10
   12
   09:15 09:30 09:45 10:00
   App - Dashboard
   Response Time Application Database
   🔥
   🔥
   

   View Slide

6. Confidential │ © VMware, Inc. 6
   Full Table Scan
   

   View Slide

7. Confidential │ © VMware, Inc. 7
   Engineer @ VMware, Inc.
   Spring Data
   Christoph Strobl
   

   View Slide

8. Confidential │ © VMware, Inc. 8
   DEMO
   

   View Slide

9. Confidential │ © VMware, Inc. 9
   “… rows are first sorted according to the
   and then limited by
   dropping the number of rows specified in
   the from the
   beginning…
   SQL:2016, Part 2, §4.15.3 Derived tables
   

   View Slide

10. Confidential │ © VMware, Inc. 10
    row 1
    row 2
    row 3
    row 6
    row 5
    row 4
    row 1
    row 2
    row 3
    row 6
    row 5
    row 4
    new row
    limit 5 offset 0
    li
    offset 5
    

    View Slide

11. Confidential │ © VMware, Inc. 11
    row 10.001
    row 10.002
    row 10.003
    row 10.006
    row 10.005
    row 10.004
    limit 5 offset 10.000
    row 10.000
    row 9.999
    skip
    

    View Slide

12. Confidential │ © VMware, Inc. 12
    …the rows are first sorted according to the and then limited
    by dropping the number of rows specified in the from
    the beginning…
    Use the skip() method on a cursor to control where MongoDB begins
    returning results. It corresponds to the LIMIT ... OFFSET ... clause in a SQL
    SELECT statement.
    SKIP defines from which row to start including the rows in the output. LIMIT
    constrains the number of returned rows.
    To page through a larger set of results, you can use the search API’s from and
    size parameters
    …
    Avoid using from and size to page too deeply or request too many results at
    once.
    

    View Slide

13. Confidential │ © VMware, Inc. 13
    Markus Winand
    

    View Slide

14. Confidential │ © VMware, Inc. 14
    row 1: id-100
    row 2: id-101
    row 3: id-102
    row 6: id-105
    row 5: id-104
    row 4: id-103
    row 1: id-100
    row 2: id-101
    row 3: id-102
    row 6: id-105
    row 5: id-104
    row 4: id-103
    new row: id-99
    limit 5
    WHERE ID > 104
    ORDER BY id ASC ORDER BY id ASC
    

    View Slide

15. Confidential │ © VMware, Inc. 15
    DEMO
    

    View Slide

16. Confidential │ © VMware, Inc. 16
    Recap
    There are no solutions, there are only trade-offs; and you try to get the best trade-off you
    can get, that's all you can hope for
    OFFSET - LIMIT KEYSET
    Straight Forward Approach
    Easy to Understand
    Allows direct page jumps
    May return duplicates
    May miss newly added data
    Use with caution on large data sets
    Entry Safe
    Suitable for processing large data sets
    Server resource friendly
    Requires stable sort
    Complex WHERE clauses
    No direct page jumps
    Thomas Sowell
    

    View Slide

17. With
    Springyou get built in
    support for
    Offset & Keyset pagination
    that can directly be used within your
    GraphQL or any other
    project.
    

    View Slide

18. Thank You
    https://github.com/christophstrobl/graphql-pagination
    

    View Slide