AWS API Gateway & Lambda - API Pagination

Question

I am thinking to use AWS API Gateway and AWS Lambda(Python) to create a serverless API's , but while designing this i was thinking of some aspects like pagination,security,caching,versioning ..etc

so my question is: What is the best approach performance & cost wise to implement API pagination with very big data (1 million records)?

1. should i implement the pagination in postgresql db? (i think this would be slow)
2. should i not use postgresql db pagination and just cache all the results i get from db into aws elastic cache and then do server side pagination in lambda.

I appreciate your help guys.

Answer 1

If your data is going to live in a postgresql data base anyway I would start with your requests hitting the database and profile the performance. You've made assumptions about it being slow but you haven't stated what your requirements for latency are or what your schema is, so any assertions people would make about whether or not it would fit your case is completely speculative.

If you do decide that after profiling that it is not fast enough, than adding a cache would make sense, though storing the entire contents in the cache seems wasteful unless you can guarantee your clients will always iterate through all results. You may want to consider a mechanism that prefetches blocks of data that would service a few requests rather than trying to cache the whole data.

TL;DR : Don't prematurely optimize your solution. Quantify how you want your system to respond and test and validate your assumptions.