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

Transcript

1. SELECT * FROM news ORDER BY date DESC OFFSET 10 LIMIT 10 Pagination Done the Right way STOP USING IT!
2. Pagination
3. avoid fetching data you don't need
4. 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; }
5. Load More Load a “News Feed” Using Infinite Scroll
6. | 1 | 2 | 3 | 4 | 5 | Display a Table
7. avoid putting too much pressure on the database
8. SQL OFFSET & LIMIT A Simple Solution to the Problem?
9. 1 2 3 4 5 6 7 8 Id OFFSET & LIMIT
10. 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
11. 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
12. 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
13. 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
14. OFFSET & LIMIT Simple?
15. 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
16. 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
17. 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
18. 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
19. 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
20. 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
21. 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
22. OFFSET & LIMIT A Solution…
23. 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?
24. 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
25. 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
26. 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
27. 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
28. 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
29. 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
30. 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
31. KeySet Pagination The Seek Method I don’t want to sort on the ID field
32. 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
33. 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
34. 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
35. 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
36. 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;
37. 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
38. KeySet Pagination Limitations You cannot directly navigate to arbitrary pages because you need the values from the previous page. | 1 | 2 | 3 | 4 | 5 |
39. 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
40. 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, …
41. 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.
42. 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.
43. 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.
44. 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.
45. I want the Table with Pages Ok… let’s do some math
46. I want the Table with Pages Ok… let’s do some math 10 columns
47. I want the Table with Pages Ok… let’s do some math 10 columns x 32-bytes
48. 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?
49. 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?
50. 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
51. 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
52. 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…
53. 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)
54. 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)
55. 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
56. 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.
57. 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.
58. 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.
59. 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.
60. 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.
61. 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.
62. 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.
63. 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.
64. 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.
65. 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.
66. 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.
67. 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.
68. 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.
69. 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.
70. 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.
71. 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.
72. 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.
73. 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
74. Pagination SQL OFFSET might seem like a simple solution But has performance, memory, and data integrity issues.
75. 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.
76. 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