Pagination: Needed but Problematic

Transcript

1. Pagination Needed but problematic Vayupranaditya
2. About me I Gusti Bagus Vayupranaditya P Informatics @ Telkom University • Programmer @ Proclub • Likes to join competitive programming competition Ex Backend Engineer Intern @ Garena • Design, create, deploy, and maintain services for hundreds of thousands of users • Research new technologies, methods, algorithms for production uses linkedin.com/in/vayupranaditya github.com/vayupranaditya closa.me/vay
3. Pagination - Needed but problematic
4. Pagination - Needed but problematic
5. Pagination - Needed but problematic
6. Pagination - Needed but problematic How exactly?
7. Pagination - Needed but problematic How exactly?
8. Pagination - Needed but problematic 1. WHERE clause
9. Pagination - Needed but problematic 1. WHERE clause
10. Pagination - Needed but problematic 1. WHERE clause 2. Helped with LIMIT
11. Pagination - Needed but problematic 1. WHERE clause 2. Helped with LIMIT 3. Finalized using OFFSET
12. Pagination - Needed but problematic Returned data
13. What to do? As software is a result of a team, we can also tackle problems as a team
14. What to do? As software is a result of a team, we can also tackle problems as a team Approaches: • Backend • Frontend • Product
15. Backend solution • Good indexing is a must
16. Backend solution • Good indexing is a must
17. Backend solution • Good indexing is a must
18. Backend solution • Good indexing is a must Read more on http://gg.gg/medium-dbindexing
19. Backend solution • Good indexing is a must • Caching
20. Backend solution • Good indexing is a must • Caching
21. Backend solution • Good indexing is a must • Caching + Fast because using in-memory storage + Support high-load request - Expensive
22. Backend solution • Good indexing is a must • Caching • More algorithms (ie. seek method)
23. Pagination - Needed but problematic How exactly?
24. Pagination - Needed but problematic How exactly?
25. Pagination - Needed but problematic 1. WHERE clause
26. Pagination - Needed but problematic 1. WHERE clause
27. Pagination - Needed but problematic 1. WHERE clause 2. Helped with LIMIT
28. Pagination - Needed but problematic 1. WHERE clause 2. Helped with LIMIT 3. Finalized using OFFSET
29. Pagination - Needed but problematic Returned data
30. Backend solution • Good indexing is a must • Caching • More algorithms (ie. seek method)
31. Backend solution • Good indexing is a must • Caching • More algorithms (ie. seek method) How exactly?
32. Backend solution • Good indexing is a must • Caching • More algorithms (ie. seek method) id user_id item_id seek_number 15478 9660609 472039 1 17229 3004676 3319360 1 84582 9660609 5395528 2 87296 1772092 8339172 1 89519 9660609 8933407 3 125599 9660609 3239712 4 148158 1772092 8441369 2 162545 9660609 4005824 5 229591 9660609 3840137 6 302868 9660609 8896916 7
33. Backend solution • Good indexing is a must • Caching • More algorithms (ie. seek method) id user_id item_id seek_number 15478 9660609 472039 1 17229 3004676 3319360 1 84582 9660609 5395528 2 87296 1772092 8339172 1 89519 9660609 8933407 3 125599 9660609 3239712 4 148158 1772092 8441369 2 162545 9660609 4005824 5 229591 9660609 3840137 6 302868 9660609 8896916 7
34. Backend solution • Good indexing is a must • Caching • More algorithms (ie. seek method) id user_id item_id seek_number 15478 9660609 472039 1 17229 3004676 3319360 1 84582 9660609 5395528 2 87296 1772092 8339172 1 89519 9660609 8933407 3 125599 9660609 3239712 4 148158 1772092 8441369 2 162545 9660609 4005824 5 229591 9660609 3840137 6 302868 9660609 8896916 7
35. Backend solution • Good indexing is a must • Caching • More algorithms (ie. seek method) id user_id item_id seek_number 15478 9660609 472039 1 17229 3004676 3319360 1 84582 9660609 5395528 2 87296 1772092 8339172 1 89519 9660609 8933407 3 125599 9660609 3239712 4 148158 1772092 8441369 2 162545 9660609 4005824 5 229591 9660609 3840137 6 302868 9660609 8896916 7
36. Backend solution • Good indexing is a must • Caching • More algorithms (ie. seek method) id user_id item_id seek_number 15478 9660609 472039 1 17229 3004676 3319360 1 84582 9660609 5395528 2 87296 1772092 8339172 1 89519 9660609 8933407 3 125599 9660609 3239712 4 148158 1772092 8441369 2 162545 9660609 4005824 5 229591 9660609 3840137 6 302868 9660609 8896916 7
37. Backend solution • Good indexing is a must • Caching • More algorithms (ie. seek method) id user_id item_id seek_number 125599 9660609 3239712 4 162545 9660609 4005824 5 229591 9660609 3840137 6
38. Backend solution • Good indexing is a must • Caching • More algorithms (ie. seek method) Seek Method • seek_number can be changed into anything • seek_number needs to be indexed id user_id item_id seek_number 125599 9660609 3239712 4 162545 9660609 4005824 5 229591 9660609 3840137 6
39. Backend solution • Good indexing is a must • Caching • More algorithms (ie. seek method) Seek Method • seek_number can be changed into anything • seek_number needs to be indexed + Fast: O(n) - Spec need to be defined correctly id user_id item_id seek_number 125599 9660609 3239712 4 162545 9660609 4005824 5 229591 9660609 3840137 6
40. Frontend solution Frontend caching
41. Frontend solution Frontend caching
42. Frontend solution Frontend caching
43. Frontend solution Frontend caching
44. Frontend solution Frontend caching • All data stored in local storage • API called only once • Data then filtered as required
45. Frontend solution Frontend caching • All data stored in local storage • API called only once • Data then filtered as required + Lower network call + Lower backend load - Data is not consistent - More complex on high-update data
46. Product-PoV Solution More filters
47. Product-PoV Solution More filters
48. Product-PoV Solution More filters • Basically not a pagination • Benefiting from human psychology “no one is really looking into 9th page” + Fast + Intuitive + Only using WHERE clause on backend - Might need to implement advanced filtering - Can be very long / very short if using invalid filter
49. Thanks! Got a question? linkedin.com/in/vayupranaditya closa.me/vay