I am creating a REST API that connects to an AWS RDS instance (Aurora Serverless/Postgres) using the RDS Data API. The request flow:
Client -> API Gateway -> Lambda -> RDS Data API -> RDS
The Lambda maps the request to a sql statement which is sent to RDS via the Data API. The results (rows) of the query are sent back to the client in json format.
All my tables have an indexed primary key column. However the value of this primary key does not always increment by 1, there are gaps. The tables can contain up to a couple billion rows.
The solution is simple for smaller tables, but for larger tables some sort of pagination has to be implemented due to Data API limits (1 MB response size) and Lambda runtime limits (max 15 minutes). I have read about different methods to implement pagination. The only suitable method seems to be cursor-based pagination, as it's the only method that can guarantee a stable result set without using an order by clause.
However I can't use (server side) cursors because when the Lambda times out, the database connection gets closed, as well as the cursor.
I could use url parameters containing a start and an end value to filter the primary key column of the table, however this column does not always increment by 1. So doing something like https://website.com/table/users?start_idx=0&end_idx=100 would not necessarily return 100 records, which is undesirable.
How can I best solve this in an idiomatic manner?