Amazon aurora postgres serverless: Database returned more than the allowed response size limit

Question

We are exploring aurora serverless as a database storage for one of our project. While doing the POC to fetch events from aurora serverless cluster we are receiving "Database returned more than the allowed response size limit" exception.

1. Is there an in build solution for this? Is there a token mechanism similar to dynamoDB in aurora serverless?
2. Fetching 50,000 quickly is one of our key requirement. Should we use aurora or aurora serverless?

More details about database: Aurora PostgreSQL server less.

For doing POC We are using data APIs, which have these limitations.

import boto3;

client = boto3.client('rds-data')

import sys;
sql_statement = "select * from table_name limit 1000"

response = client.execute_statement(  
    database='mydb',  
    secretArn='<secret_arn',  
    resourceArn='<resource_arn>',  
    sql=sql_statement  
) 

print(response);

Answer 1

When you do a query in any Aurora Serverless instance through the Data API, you have two size limitations:

1. Each returned row cannot be greater than 64 KB; (source)
2. 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.