It looks like you're encountering the payload size limitation in OutSystems Developer Cloud (ODC) when handling large file uploads to AWS S3. This is indeed a known limitation, as OutSystems enforces a payload size restriction of approximately 5.5MB for both request and response payloads. Here are some best practices and feasible patterns you can use to overcome this limitation:

1. Split Records into Batches

Instead of querying all 1,000,000 records at once, you can divide the dataset into smaller batches and create multiple Excel files. Each file will stay under the payload size limit and can be uploaded sequentially to S3.

• Use the Timer to handle batches by adding pagination logic to your query.
• After processing each batch into an Excel file, use AWS S3's PutObject or a similar action to upload it.

Benefits:

• Prevents memory and payload bottlenecks.
• Avoids potential timeouts for the Timer.

Challenges:

• You will end up with multiple files instead of a single file. If a single file is mandatory, you would need to consolidate them post-upload (e.g., in S3 or during a later processing step).

2. Use Pre-Signed URLs for Direct S3 Uploads

Pre-signed URLs are a more efficient way to handle large file uploads to S3. In this approach:

• Generate the pre-signed URL via an AWS service (e.g., using custom REST API logic or AWS SDK).
• Use this URL to upload the Excel file directly to S3 from the client or server, instead of passing the binary through OutSystems payload limits.

Implementation Steps:

• Create an external AWS API endpoint to provide pre-signed URLs or use a custom integration library in OutSystems.
• Generate the Excel file within OutSystems but upload it directly to S3 using the pre-signed URL from the front-end or via server-side logic.

This method bypasses OutSystems' payload size limitations entirely and is highly efficient.

3. Stream Data to Create Excel Files

Instead of generating the entire Excel file in memory, explore libraries/services that allow streaming rows to an Excel file, writing data incrementally to the file system or directly to S3. Memory consumption stays low, and payload size issues can be avoided.

Example Workflow:

• Use logic to stream large datasets into temporary files stored on a server or in temporary S3 storage.
• Once the stream is complete, upload the file.

Note: This would require either a custom connection to AWS S3 or integration of third-party libraries that support streaming.

4. Leverage External Logic

For processes requiring high scalability, you may offload this operation to external logic hosted in AWS Lambda, a custom backend, or another service. Here’s how:

• Use OutSystems to trigger the batch export process and send operational parameters, such as filter criteria or query definitions, to the external system.
• The external service handles querying the database, generating the file, and uploading to S3.

This keeps the OutSystems application lightweight and avoids sizing bottlenecks.

Suggested Approach

For your specific case, combining batch processing with pre-signed URLs is likely the best solution. Here's a step-by-step outline:

1. Batch the Query Results: Divide the dataset of 1,000,000 records into smaller, manageable chunks (e.g., 10,000 records per batch).
2. Pre-Signed URL Workflow:
   • Generate a pre-signed URL for each batch using AWS tools or a custom REST API.
   • Convert each batch result into an Excel file.
   • Use the pre-signed URL to upload directly to S3.
3. Post-Processing: If required, merge the files into a single Excel file after upload using an S3 Lambda function or other AWS services.

This approach avoids memory overloading, adheres to the platform's payload limits, and provides better scalability.

You may also refer to this useful community post on addressing payload limits and large file exports in ODC Large Files in ODC.

Let me know if you need further guidance!