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
2. Confidential │ © VMware, Inc. 2
3. Confidential │ © VMware, Inc. 3 Pager Duty 😱
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 🔥
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 🔥 🔥
6. Confidential │ © VMware, Inc. 6 Full Table Scan
7. Confidential │ © VMware, Inc. 7 Engineer @ VMware, Inc. Spring Data Christoph Strobl
8. Confidential │ © VMware, Inc. 8 DEMO
9. 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
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
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
12. 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.
13. Confidential │ © VMware, Inc. 13 Markus Winand
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
15. Confidential │ © VMware, Inc. 15 DEMO
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
17. With Springyou get built in support for Offset & Keyset pagination that can directly be used within your GraphQL or any other project.
18. Thank You https://github.com/christophstrobl/graphql-pagination