Slide 1

Slide 1 text

SELECT * FROM news ORDER BY date DESC OFFSET 10 LIMIT 10 Pagination Done the Right way STOP USING IT!

Slide 2

Slide 2 text

Pagination

Slide 3

Slide 3 text

avoid fetching data you don't need

Slide 4

Slide 4 text

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; }

Slide 5

Slide 5 text

Load More Load a “News Feed” Using Infinite Scroll

Slide 6

Slide 6 text

| 1 | 2 | 3 | 4 | 5 | Display a Table

Slide 7

Slide 7 text

avoid putting too much pressure on the database

Slide 8

Slide 8 text

SQL OFFSET & LIMIT A Simple Solution to the Problem?

Slide 9

Slide 9 text

1 2 3 4 5 6 7 8 Id OFFSET & LIMIT

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

OFFSET & LIMIT Simple?

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 text

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

Slide 21

Slide 21 text

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

Slide 22

Slide 22 text

OFFSET & LIMIT A Solution…

Slide 23

Slide 23 text

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?

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

KeySet Pagination The Seek Method I don’t want to sort on the ID field

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

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

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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;

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

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

Slide 39

Slide 39 text

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

Slide 40

Slide 40 text

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, …

Slide 41

Slide 41 text

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.

Slide 42

Slide 42 text

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.

Slide 43

Slide 43 text

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.

Slide 44

Slide 44 text

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.

Slide 45

Slide 45 text

I want the Table with Pages Ok… let’s do some math

Slide 46

Slide 46 text

I want the Table with Pages Ok… let’s do some math 10 columns

Slide 47

Slide 47 text

I want the Table with Pages Ok… let’s do some math 10 columns x 32-bytes

Slide 48

Slide 48 text

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?

Slide 49

Slide 49 text

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?

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

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…

Slide 53

Slide 53 text

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)

Slide 54

Slide 54 text

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)

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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.

Slide 57

Slide 57 text

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.

Slide 58

Slide 58 text

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.

Slide 59

Slide 59 text

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.

Slide 60

Slide 60 text

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.

Slide 61

Slide 61 text

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.

Slide 62

Slide 62 text

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.

Slide 63

Slide 63 text

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.

Slide 64

Slide 64 text

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.

Slide 65

Slide 65 text

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.

Slide 66

Slide 66 text

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.

Slide 67

Slide 67 text

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.

Slide 68

Slide 68 text

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.

Slide 69

Slide 69 text

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.

Slide 70

Slide 70 text

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.

Slide 71

Slide 71 text

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.

Slide 72

Slide 72 text

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.

Slide 73

Slide 73 text

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

Slide 74

Slide 74 text

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

Slide 75

Slide 75 text

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.

Slide 76

Slide 76 text

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