Amazon aurora postgres serverless: Database returned more than the allowed response size limit
Solution 1:
When you do a query in any Aurora Serverless instance through the Data API, you have two size limitations:
- Each returned row cannot be greater than 64 KB; (source)
- The result set cannot be greater than 1 MB. (source)
Currently, there are no solutions in the Data API to overcome these limits. In the Amazon Aurora User Guide, there is a recommended solution for the second problem in p.164:
In this case, the size of the result set returned by the database was too large. The Data API limit is 1 MB in the result set returned by the database. To solve this issue, make sure that calls to the Data API return 1 MB of data or less. If you need to return more than 1 MB, you can use multiple ExecuteStatement calls with the LIMIT clause in your query.
Considering it, you could do an exponential backoff until you find an acceptable LIMIT for your result set, or set a fixed LIMIT that you are comfortable that it will be always lower than 1 MB, even if your rows size increase in the future.
After defining how to set your LIMIT clause value (depending on whether your cluster uses MySQL 5.6, MySQL 5.7 or PostgreSQL 10.7), you could do a COUNT query to know how many results you will get, and then iterate until you execute COUNT / LIMIT statements.
Another option would be to iterate until your statement response has fewer rows than your LIMIT.