Upgrade to Pro — share decks privately, control downloads, hide ads and more …

No Offset - Pagination with the Seek Method

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.

Christoph Strobl

May 23, 2023
Tweet

More Decks by Christoph Strobl

Other Decks in Programming

Transcript

  1. Confidential │ © VMware, Inc. 4 0 2 4 6

    8 10 12 09:15 09:30 09:45 10:00 App - Dashboard Response Time Application 🔥
  2. 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 🔥 🔥
  3. Confidential │ © VMware, Inc. 9 “… rows are first

    sorted according to the <order by clause> and then limited by dropping the number of rows specified in the <result offset clause> from the beginning… SQL:2016, Part 2, §4.15.3 Derived tables
  4. 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
  5. 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
  6. Confidential │ © VMware, Inc. 12 …the rows are first

    sorted according to the <order by clause> and then limited by dropping the number of rows specified in the <result offset clause> 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.
  7. 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
  8. 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
  9. With Springyou get built in support for Offset & Keyset

    pagination that can directly be used within your GraphQL or any other project.