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

Database Pagination - SQL OFFSET vs the Seek Me...

Database Pagination - SQL OFFSET vs the Seek Method

While SQL OFFSET might seem like a simple solution for pagination, it comes with severe performance, memory, and data integrity issues.
As your application and database grow, these problems will become more apparent and can hinder scalability.

Keyset pagination offers a more efficient and reliable alternative,
providing consistent performance, predictable memory usage, and stable data ordering. By adopting keyset pagination, we can build applications that are not only performant but also scalable and reliable in the long run.

https://youtu.be/LFjtgFPQ734

Matteo Bertozzi

August 10, 2023
Tweet

More Decks by Matteo Bertozzi

Other Decks in Programming

Transcript

  1. SELECT * FROM news ORDER BY date DESC OFFSET 10

    LIMIT 10 Pagination Done the Right way STOP USING IT!
  2. Processing Records In Batches let offset = 0; while (true)

    { const batch = await table.fetch(offset, 100); if (!batch) break; // process the batch of records offset += batch.length; }
  3. | 1 | 2 | 3 | 4 | 5

    | Display a Table
  4. SELECT * FROM news ORDER BY id DESC OFFSET 0

    LIMIT 5 1 2 3 4 5 6 7 8 Id OFFSET & LIMIT Offset 0 Page 1
  5. SELECT * FROM news ORDER BY id DESC OFFSET 0

    LIMIT 5 1 2 3 4 5 6 7 8 Id 8 7 6 5 4 Id OFFSET & LIMIT Page 1 Offset 0 Page 1
  6. SELECT * FROM news ORDER BY id DESC OFFSET 5

    LIMIT 5 SELECT * FROM news ORDER BY id DESC OFFSET 0 LIMIT 5 1 2 3 4 5 6 7 8 Id 8 7 6 5 4 Id Offset 5 OFFSET & LIMIT Page 1 Page 2
  7. 3 2 1 Id SELECT * FROM news ORDER BY

    id DESC OFFSET 5 LIMIT 5 SELECT * FROM news ORDER BY id DESC OFFSET 0 LIMIT 5 1 2 3 4 5 6 7 8 Id 8 7 6 5 4 Id OFFSET & LIMIT Page 1 Page 2 Offset 5 Page 2
  8. OFFSET & LIMIT Problem 1 - Data Integrity 1 2

    3 4 5 6 7 8 Id SELECT * FROM news ORDER BY id DESC OFFSET 0 LIMIT 5 8 7 6 5 4 Id Page 1 Offset 0 Page 1
  9. OFFSET & LIMIT Problem 1 - Data Integrity 1 2

    3 4 5 6 7 8 Id SELECT * FROM news ORDER BY id DESC OFFSET 0 LIMIT 5 8 7 6 5 4 Id Page 1 Offset 0 Page 1 something new gets Inserted
  10. OFFSET & LIMIT Problem 1 - Data Integrity 1 2

    3 4 5 6 7 8 Id SELECT * FROM news ORDER BY id DESC OFFSET 0 LIMIT 5 8 7 6 5 4 Id Offset 0 Page 1 something new gets Inserted 9 10 Page 1
  11. OFFSET & LIMIT Problem 1 - Data Integrity 1 2

    3 4 5 6 7 8 Id SELECT * FROM news ORDER BY id DESC OFFSET 0 LIMIT 5 Page 1 9 10 SELECT * FROM news ORDER BY id DESC OFFSET 5 LIMIT 5 Offset 5 Page 2 8 7 6 5 4 Id
  12. OFFSET & LIMIT Problem 1 - Data Integrity 1 2

    3 4 5 6 7 8 Id SELECT * FROM news ORDER BY id DESC OFFSET 0 LIMIT 5 Page 1 9 10 SELECT * FROM news ORDER BY id DESC OFFSET 5 LIMIT 5 5 4 3 2 1 Id 8 7 6 5 4 Id Page 2 Offset 5 Page 2
  13. OFFSET & LIMIT Problem 1 - Data Integrity 1 2

    3 4 5 6 7 8 Id SELECT * FROM news ORDER BY id DESC OFFSET 0 LIMIT 5 Page 1 9 10 SELECT * FROM news ORDER BY id DESC OFFSET 5 LIMIT 5 5 4 3 2 1 Id 8 7 6 5 4 Id Page 2 Offset 5 Page 2 We have Already seen News 4 & 5 on the previous page
  14. OFFSET & LIMIT Problem 1 - Data Integrity SELECT *

    FROM news ORDER BY id DESC OFFSET 0 LIMIT 5 Page 1 1 2 3 4 5 6 7 8 Id 9 10 SELECT * FROM news ORDER BY id DESC OFFSET 5 LIMIT 5 5 4 3 2 1 Id 8 7 6 5 4 Id Page 2 Offset 5 Page 2 We have Already seen News 4 & 5 on the previous page The Offset “row count” Is performed on the Current state
  15. KeySet Pagination The Seek Method 1 2 3 4 5

    6 7 8 Id How to determine the starting point for the next set of results?
  16. KeySet Pagination The Seek Method 1 2 3 4 5

    6 7 8 Id SELECT * FROM news ORDER BY id DESC LIMIT 5 8 7 6 5 4 Id Page 1 Page 1
  17. KeySet Pagination The Seek Method 1 2 3 4 5

    6 7 8 Id SELECT * FROM news ORDER BY id DESC LIMIT 5 8 7 6 5 4 Id Page 1 The last row Last Row
  18. KeySet Pagination The Seek Method 1 2 3 4 5

    6 7 8 Id SELECT * FROM news ORDER BY id DESC LIMIT 5 8 7 6 5 4 Id Page 1 The last row Page 2 Should have News older than “4” Last Row
  19. KeySet Pagination The Seek Method 1 2 3 4 5

    6 7 8 Id SELECT * FROM news ORDER BY id DESC LIMIT 5 8 7 6 5 4 Id Page 1 The last row Page 2 Should have News older than “4” Last Row 9 10 new stuff Inserted
  20. KeySet Pagination The Seek Method 1 2 3 4 5

    6 7 8 Id SELECT * FROM news ORDER BY id DESC LIMIT 5 8 7 6 5 4 Id Page 1 SELECT * FROM news WHERE id < 4 ORDER BY id DESC LIMIT 5 Page 2 9 10
  21. KeySet Pagination The Seek Method 1 2 3 4 5

    6 7 8 Id SELECT * FROM news ORDER BY id DESC LIMIT 5 8 7 6 5 4 Id Page 1 SELECT * FROM news WHERE id < 4 ORDER BY id DESC LIMIT 5 Page 2 3 2 1 Id Page 2 9 10
  22. KeySet Pagination The Seek Method 1 2 3 4 5

    6 7 8 Id SELECT * FROM news ORDER BY id DESC LIMIT 5 8 7 6 5 4 Id Page 1 SELECT * FROM news WHERE id < 4 ORDER BY id DESC LIMIT 5 Page 2 3 2 1 Id Page 2 9 10 Now we have a consistent resultset
  23. KeySet Pagination The Seek Method s-aaa Surname Name Age n-aaa

    20 s-aaa n-bbb 10 s-aaa n-ccc 30 s-aaa n-ddd 5 s-bbb n-aaa 5 s-bbb n-bbb 8 s-bbb n-ccc 30 s-bbb n-ddd 25 s-ccc n-aaa 30 s-ccc n-bbb 40 s-ccc n-ccc 12
  24. KeySet Pagination The Seek Method SELECT age, name, surname FROM

    person ORDER BY age DESC, name DESC, surname DESC We want the pagination With a different order (Age, Name, Surname) Age 40 31 30 30 30 25 20 10 8 5 5 Name n-ccc n-aaa n-ccc n-ccc n-bbb n-ddd n-aaa n-bbb n-bbb n-ddd n-aaa s-ccc Surname s-ccc s-bbb s-aaa s-ccc s-bbb s-aaa s-aaa s-bbb s-aaa s-bbb s-aaa Surname Name Age n-aaa 20 s-aaa n-bbb 10 s-aaa n-ccc 30 s-aaa n-ddd 5 s-bbb n-aaa 5 s-bbb n-bbb 8 s-bbb n-ccc 30 s-bbb n-ddd 25 s-ccc n-aaa 30 s-ccc n-bbb 40 s-ccc n-ccc 12
  25. Age 40 31 30 30 30 25 20 10 8

    5 5 Name n-ccc n-aaa n-ccc n-ccc n-bbb n-ddd n-aaa n-bbb n-bbb n-ddd n-aaa s-ccc Surname s-ccc s-bbb s-aaa s-ccc s-bbb s-aaa s-aaa s-bbb s-aaa s-bbb KeySet Pagination The Seek Method SELECT age, name, surname FROM person ORDER BY age DESC, name DESC, surname DESC SELECT age, name, surname FROM person ORDER BY age DESC, name DESC, surname DESC LIMIT 4; Age 40 31 30 30 Name n-ccc n-aaa n-ccc n-ccc s-ccc Surname s-ccc s-bbb s-aaa Age 40 31 30 30 Name n-ccc n-aaa n-ccc n-ccc s-ccc Surname s-ccc s-bbb s-aaa s-aaa Surname Name Age n-aaa 20 s-aaa n-bbb 10 s-aaa n-ccc 30 s-aaa n-ddd 5 s-bbb n-aaa 5 s-bbb n-bbb 8 s-bbb n-ccc 30 s-bbb n-ddd 25 s-ccc n-aaa 30 s-ccc n-bbb 40 s-ccc n-ccc 12
  26. Age 40 31 30 30 30 25 20 10 8

    5 5 Name n-ccc n-aaa n-ccc n-ccc n-bbb n-ddd n-aaa n-bbb n-bbb n-ddd n-aaa s-ccc Surname s-ccc s-bbb s-aaa s-ccc s-bbb s-aaa s-aaa s-bbb s-aaa s-bbb KeySet Pagination The Seek Method SELECT age, name, surname FROM person ORDER BY age DESC, name DESC, surname DESC SELECT age, name, surname FROM person ORDER BY age DESC, name DESC, surname DESC LIMIT 4; SELECT age, name, surname FROM person WHERE (age, name, surname) < (30, 'n-ccc', 's-aaa') ORDER BY age DESC, name DESC, surname DESC LIMIT 4; Age 40 31 30 30 Name n-ccc n-aaa n-ccc n-ccc s-ccc Surname s-ccc s-bbb s-aaa Age Name Surname 30 25 20 10 n-bbb n-ddd n-aaa n-bbb s-ccc s-bbb s-aaa s-aaa s-aaa Surname Name Age n-aaa 20 s-aaa n-bbb 10 s-aaa n-ccc 30 s-aaa n-ddd 5 s-bbb n-aaa 5 s-bbb n-bbb 8 s-bbb n-ccc 30 s-bbb n-ddd 25 s-ccc n-aaa 30 s-ccc n-bbb 40 s-ccc n-ccc 12
  27. KeySet Pagination The Seek Method s-aaa Surname Name Age n-aaa

    20 s-aaa n-bbb 10 s-aaa n-ccc 30 s-aaa n-ddd 5 s-bbb n-aaa 5 s-bbb n-bbb 8 s-bbb n-ccc 30 s-bbb n-ddd 25 s-ccc n-aaa 30 s-ccc n-bbb 40 s-ccc n-ccc 12 Age 40 31 30 30 30 25 20 10 8 5 5 Name n-ccc n-aaa n-ccc n-ccc n-bbb n-ddd n-aaa n-bbb n-bbb n-ddd n-aaa s-ccc Surname s-ccc s-bbb s-aaa s-ccc s-bbb s-aaa s-aaa s-bbb s-aaa s-bbb SELECT age, name, surname FROM person ORDER BY age DESC, name DESC, surname DESC SELECT age, name, surname FROM person ORDER BY age DESC, name DESC, surname DESC LIMIT 4; Age 40 31 30 30 Name n-ccc n-aaa n-ccc n-ccc s-ccc Surname s-ccc s-bbb s-aaa Age Name Surname 30 25 20 10 n-bbb n-ddd n-aaa n-bbb s-ccc s-bbb s-aaa s-aaa Age Name Surname 8 5 5 n-bbb n-ddd n-aaa s-bbb s-aaa s-bbb SELECT age, name, surname FROM person WHERE (age, name, surname) < (30, 'n-ccc', 's-aaa') ORDER BY age DESC, name DESC, surname DESC LIMIT 4; SELECT age, name, surname FROM person WHERE (age, name, surname) < (10, 'n-bbb', 's-aaa') ORDER BY age DESC, name DESC, surname DESC LIMIT 4;
  28. KeySet Pagination Tuple Comparison (SQL-92) (x, y) < (a, b)

    (x < a) OR (x = a AND y < b) Same as Tuple Comparison Row Values Comparison Composite Values Comparison different names, same meaning
  29. KeySet Pagination Limitations You cannot directly navigate to arbitrary pages

    because you need the values from the previous page. | 1 | 2 | 3 | 4 | 5 |
  30. KeySet Pagination Limitations You cannot directly navigate to arbitrary pages

    because you need the values from the previous page. | 1 | 2 | 3 | 4 | 5 | Load More Perfect match for when implementing infinite scrolling the “last key” cursor is already there
  31. I want the Table with Pages SELECT * FROM news

    ORDER BY id DESC OFFSET 0 LIMIT 5 Page 1 SELECT * FROM news ORDER BY id DESC OFFSET 5 LIMIT 5 5 4 3 2 1 Id 8 7 6 5 4 Id Page 2 The concept of Pages only works when rows are not added or removed. Why? Duplicate Entries, Unknown Start point, …
  32. I want the Table with Pages SELECT * FROM news

    ORDER BY id DESC OFFSET 0 LIMIT 5 Page 1 SELECT * FROM news ORDER BY id DESC OFFSET 5 LIMIT 5 5 4 3 2 1 Id 8 7 6 5 4 Id Page 2 The concept of Pages only works when rows are not added or removed. Rethink your UI Why? Duplicate Entries, Unknown Start point, … Why the user goes on page 5? It’s a guess, “maybe what I’m looking for is there”. Better give them a search box, filters or an index.
  33. I want the Table with Pages SELECT * FROM news

    ORDER BY id DESC OFFSET 0 LIMIT 5 Page 1 SELECT * FROM news ORDER BY id DESC OFFSET 5 LIMIT 5 5 4 3 2 1 Id 8 7 6 5 4 Id Page 2 The concept of Pages only works when rows are not added or removed. Rethink your UI Rethink your Endpoints Why? Duplicate Entries, Unknown Start point, … Why the user goes on page 5? It’s a guess, “maybe what I’m looking for is there”. Better give them a search box, filters or an index.
  34. I want the Table with Pages SELECT * FROM news

    ORDER BY id DESC OFFSET 0 LIMIT 5 Page 1 SELECT * FROM news ORDER BY id DESC OFFSET 5 LIMIT 5 5 4 3 2 1 Id 8 7 6 5 4 Id Page 2 The concept of Pages only works when rows are not added or removed. Rethink your UI /news?page=N /peoples?page=N You don’t know where you end up Rethink your Endpoints Why? Duplicate Entries, Unknown Start point, … Why the user goes on page 5? It’s a guess, “maybe what I’m looking for is there”. Better give them a search box, filters or an index.
  35. I want the Table with Pages SELECT * FROM news

    ORDER BY id DESC OFFSET 0 LIMIT 5 Page 1 SELECT * FROM news ORDER BY id DESC OFFSET 5 LIMIT 5 5 4 3 2 1 Id 8 7 6 5 4 Id Page 2 The concept of Pages only works when rows are not added or removed. Rethink your UI /news?page=N /peoples?page=N You don’t know where you end up /news?page=DATE /peoples?page=SURNAME/FIRST-LETTER/PREFIX Better since you know what you’ll be seeing Rethink your Endpoints Why? Duplicate Entries, Unknown Start point, … Why the user goes on page 5? It’s a guess, “maybe what I’m looking for is there”. Better give them a search box, filters or an index.
  36. I want the Table with Pages Ok… let’s do some

    math xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx 10 columns x 32-bytes Column 1 Column 2 Column 3 Column 4 Column 5 Column 6 Column 7 Column 8 Column 9 Can not even fit in the screen… Are you implementing Excel?
  37. I want the Table with Pages Ok… let’s do some

    math xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx 10 columns x 32-bytes = 320-bytes per row Column 1 Column 2 Column 3 Column 4 Column 5 Column 6 Column 7 Column 8 Column 9 Can not even fit in the screen… Are you implementing Excel?
  38. I want the Table with Pages Ok… let’s do some

    math xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx 10 columns x 32-bytes = 320-bytes per row * 10_000 rows (200 pages of 50 rows) Column 1 Column 2 Column 3 Column 4 Column 5 Column 6 Column 7 Column 8 Column 9
  39. I want the Table with Pages Ok… let’s do some

    math xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxx 10 columns x 32-bytes = 320-bytes per row * 10_000 rows (200 pages of 50 rows) ———————————————————————————————————— 3 MiB Column 1 Column 2 Column 3 Column 4 Column 5 Column 6 Column 7 Column 8 Column 9
  40. I want the Table with Pages Ok… let’s do some

    math 10 columns x 32-bytes = 320-bytes per row * 10_000 rows (200 pages of 50 rows) ———————————————————————————————————— 3 MiB If you have large fields…
  41. I want the Table with Pages Ok… let’s do some

    math 10 columns x 32-bytes = 320-bytes per row * 10_000 rows (200 pages of 50 rows) ———————————————————————————————————— 3 MiB If you have large fields… Fetch the minimum fields (at least the primary key)
  42. I want the Table with Pages Ok… let’s do some

    math 10 columns x 32-bytes = 320-bytes per row * 10_000 rows (200 pages of 50 rows) ———————————————————————————————————— 3 MiB If you have large fields… On page next, fetch the missing values by key SELECT a, b, c FROM table WHERE key IN (123, 456, 789, …) Fetch the minimum fields (at least the primary key)
  43. Pagination From the Storage point of view SELECT * FROM

    foo ORDER BY a DESC, b DESC Have an index for your ORDER BY Sorting requires lots of Memory, Disk I/O and CPU Computing it for every query which will make the system unusable under load. An index allows to “pre-compute” it
  44. Offset & Limit Count & Skip Records SELECT * FROM

    foo ORDER BY a DESC, b DESC OFFSET 5000 LIMIT 100; A non-clustered index with the prefix (a, b) will probably only be used for small offsets. When using OFFSET, the database engine iterates over all the skipped rows, before returning the requested portion of data. As the offset increases, so does the processing time, resulting in slower and slower queries.
  45. Offset & Limit Count & Skip Records SELECT * FROM

    foo ORDER BY a DESC, b DESC OFFSET 5000 LIMIT 100; A non-clustered index with the prefix (a, b) will probably only be used for small offsets. When using OFFSET, the database engine iterates over all the skipped rows, before returning the requested portion of data. As the offset increases, so does the processing time, resulting in slower and slower queries.
  46. Offset & Limit Count & Skip Records SELECT * FROM

    foo ORDER BY a DESC, b DESC OFFSET 5000 LIMIT 100; A non-clustered index with the prefix (a, b) will probably only be used for small offsets. When using OFFSET, the database engine iterates over all the skipped rows, before returning the requested portion of data. As the offset increases, so does the processing time, resulting in slower and slower queries.
  47. Offset & Limit Count & Skip Records SELECT * FROM

    foo ORDER BY a DESC, b DESC OFFSET 5000 LIMIT 100; A non-clustered index with the prefix (a, b) will probably only be used for small offsets. When using OFFSET, the database engine iterates over all the skipped rows, before returning the requested portion of data. As the offset increases, so does the processing time, resulting in slower and slower queries.
  48. Offset & Limit Count & Skip Records SELECT * FROM

    foo ORDER BY a DESC, b DESC OFFSET 5000 LIMIT 100; A non-clustered index with the prefix (a, b) will probably only be used for small offsets. When using OFFSET, the database engine iterates over all the skipped rows, before returning the requested portion of data. As the offset increases, so does the processing time, resulting in slower and slower queries.
  49. Offset & Limit Count & Skip Records SELECT * FROM

    foo ORDER BY a DESC, b DESC OFFSET 5000 LIMIT 100; A non-clustered index with the prefix (a, b) will probably only be used for small offsets. When using OFFSET, the database engine iterates over all the skipped rows, before returning the requested portion of data. As the offset increases, so does the processing time, resulting in slower and slower queries.
  50. Offset & Limit Count & Skip Records SELECT * FROM

    foo ORDER BY a DESC, b DESC OFFSET 5000 LIMIT 100; A non-clustered index with the prefix (a, b) will probably only be used for small offsets. When using OFFSET, the database engine iterates over all the skipped rows, before returning the requested portion of data. As the offset increases, so does the processing time, resulting in slower and slower queries.
  51. Offset & Limit Count & Skip Records SELECT * FROM

    foo ORDER BY a DESC, b DESC OFFSET 5000 LIMIT 100; A non-clustered index with the prefix (a, b) will probably only be used for small offsets. When using OFFSET, the database engine iterates over all the skipped rows, before returning the requested portion of data. As the offset increases, so does the processing time, resulting in slower and slower queries.
  52. Offset & Limit Count & Skip Records SELECT * FROM

    foo ORDER BY a DESC, b DESC OFFSET 5000 LIMIT 100; A non-clustered index with the prefix (a, b) will probably only be used for small offsets. When using OFFSET, the database engine iterates over all the skipped rows, before returning the requested portion of data. As the offset increases, so does the processing time, resulting in slower and slower queries.
  53. Offset & Limit Count & Skip Records SELECT * FROM

    foo ORDER BY a DESC, b DESC OFFSET 5000 LIMIT 100; A non-clustered index with the prefix (a, b) will probably only be used for small offsets. When using OFFSET, the database engine iterates over all the skipped rows, before returning the requested portion of data. As the offset increases, so does the processing time, resulting in slower and slower queries.
  54. Offset & Limit Count & Skip Records SELECT * FROM

    foo ORDER BY a DESC, b DESC OFFSET 5000 LIMIT 100; A non-clustered index with the prefix (a, b) will probably only be used for small offsets. When using OFFSET, the database engine iterates over all the skipped rows, before returning the requested portion of data. As the offset increases, so does the processing time, resulting in slower and slower queries.
  55. KeySet Pagination The Seek Method SELECT * FROM foo WHERE

    (a, b) < (27, 'xyz') ORDER BY a DESC, b DESC LIMIT 100; Don’t touch what you don’t need An index with the prefix (a, b) must be present Consistent Performance O(log n) Regardless of the dataset size. Does not rely on the number of skipped rows A Stable Sort order is required! Low Memory footprint & Disk I/O Only the returned rows needs to be in-memory. No extra disk I/O and cache usage needed.
  56. KeySet Pagination The Seek Method SELECT * FROM foo WHERE

    (a, b) < (27, 'xyz') ORDER BY a DESC, b DESC LIMIT 100; Don’t touch what you don’t need An index with the prefix (a, b) must be present Consistent Performance O(log n) Regardless of the dataset size. Does not rely on the number of skipped rows A Stable Sort order is required! Low Memory footprint & Disk I/O Only the returned rows needs to be in-memory. No extra disk I/O and cache usage needed.
  57. KeySet Pagination The Seek Method SELECT * FROM foo WHERE

    (a, b) < (27, 'xyz') ORDER BY a DESC, b DESC LIMIT 100; Don’t touch what you don’t need An index with the prefix (a, b) must be present Consistent Performance O(log n) Regardless of the dataset size. Does not rely on the number of skipped rows A Stable Sort order is required! Low Memory footprint & Disk I/O Only the returned rows needs to be in-memory. No extra disk I/O and cache usage needed.
  58. KeySet Pagination The Seek Method SELECT * FROM foo WHERE

    (a, b) < (27, 'xyz') ORDER BY a DESC, b DESC LIMIT 100; Don’t touch what you don’t need An index with the prefix (a, b) must be present Consistent Performance O(log n) Regardless of the dataset size. Does not rely on the number of skipped rows A Stable Sort order is required! Low Memory footprint & Disk I/O Only the returned rows needs to be in-memory. No extra disk I/O and cache usage needed.
  59. KeySet Pagination The Seek Method SELECT * FROM foo WHERE

    (a, b) < (27, 'xyz') ORDER BY a DESC, b DESC LIMIT 100; Don’t touch what you don’t need An index with the prefix (a, b) must be present Consistent Performance O(log n) Regardless of the dataset size. Does not rely on the number of skipped rows A Stable Sort order is required! Low Memory footprint & Disk I/O Only the returned rows needs to be in-memory. No extra disk I/O and cache usage needed.
  60. KeySet Pagination The Seek Method SELECT * FROM foo WHERE

    (a, b) < (27, 'xyz') ORDER BY a DESC, b DESC LIMIT 100; Don’t touch what you don’t need An index with the prefix (a, b) must be present Consistent Performance O(log n) Regardless of the dataset size. Does not rely on the number of skipped rows A Stable Sort order is required! Low Memory footprint & Disk I/O Only the returned rows needs to be in-memory. No extra disk I/O and cache usage needed.
  61. KeySet Pagination The Seek Method SELECT * FROM foo WHERE

    (a, b) < (27, 'xyz') ORDER BY a DESC, b DESC LIMIT 100; An index with the prefix (a, b) must be present Consistent Performance O(log n) Regardless of the dataset size. Does not rely on the number of skipped rows A Stable Sort order is required! Low Memory footprint & Disk I/O Only the returned rows needs to be in-memory. No extra disk I/O and cache usage needed. Don’t touch what you don’t need
  62. Pagination SQL OFFSET might seem like a simple solution But

    has performance, memory, and data integrity issues.
  63. Pagination SQL OFFSET might seem like a simple solution But

    has performance, memory, and data integrity issues. KeySet Pagination (the seek method) Provides a more efficient and reliable alternative consistent performance, predictable memory usage, and stable data ordering.
  64. Pagination SQL OFFSET might seem like a simple solution But

    has performance, memory, and data integrity issues. KeySet Pagination (the seek method) Provides a more efficient and reliable alternative consistent performance, predictable memory usage, and stable data ordering. Embrace infinite scroll And KeySet Pagination (the Seek Method) Stop using OFFSET Rethink your paged tables UI & Endpoints