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

Confidential │ © VMware, Inc. 2

Confidential │ © VMware, Inc. 3 Pager Duty 😱

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

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 🔥 🔥

Confidential │ © VMware, Inc. 6 Full Table Scan

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

Confidential │ © VMware, Inc. 8 DEMO

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

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

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

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.

Confidential │ © VMware, Inc. 13 Markus Winand

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

Confidential │ © VMware, Inc. 15 DEMO

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

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

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