AWSWrangler : wr.s3.read_parquet is painfully slow

The slow performance you're experiencing with wr.s3.read_parquet for a small file size is unexpected and could be due to several factors. Here are some potential reasons and suggestions to improve the situation:

1. Memory allocation: Although you've set 512MB of memory for your Lambda function, this might not be optimal. Increasing the memory allocation can improve performance, as it also increases CPU power proportionally. Consider raising the memory to 1024MB or higher to see if it resolves the issue.

2. Cold starts: Lambda functions can experience slower performance during cold starts. If this is happening infrequently, it could be due to the function being initialized. You might want to implement a warm-up strategy or use provisioned concurrency to mitigate this.

3. Network latency: The time taken to establish a connection with S3 and download the file could be contributing to the delay. Ensure your Lambda function is in the same region as your S3 bucket to minimize latency.

4. AWS SDK for pandas optimization: The AWS SDK for pandas (which includes AWS Data Wrangler) might be performing some behind-the-scenes operations that are not optimal for very small files. You could try using a lower-level library like pyarrow or fastparquet directly for more control over the reading process.

5. Parallel processing: If you're dealing with multiple small files, consider implementing parallel processing using concurrent.futures to improve overall performance.

6. Alternative approach: Instead of using wr.s3.read_parquet, you could try downloading the file to Lambda's temporary storage first using boto3, and then read it using pandas or pyarrow locally. This might be faster for very small files.

7. Logging and monitoring: Enable detailed logging and use AWS X-Ray to trace the execution of your Lambda function. This will help you identify exactly where the bottleneck is occurring.

If these suggestions don't improve the situation, you may want to consider reaching out to AWS support or the AWS Data Wrangler community for further assistance, as this behavior seems unusual for such a small file size.
Sources
Configure Lambda function memory - AWS Lambda
Accessing S3 Objects takes time | AWS re:Post
AWS SDK for pandas' read_parquet using too much memory even when fetching in chunks | AWS re:Post

Quick addition : this lambda will be writing to RDS and thus is on a VPC.

I found the issue. You don't always get explicit errors when connecting to a resource you don't have proper network access to, sometimes you just kinda hang...... For anyone in a similar situation here's the details.

If you have a lambda that talks to RDS you have to attach the Lambda to the same VPC.

If the Lambda also connects to another AWS service (like S3 in my case, from which I'm reading the parquet file), you have to also:

• add the default Security Group to the lambda under the VPC settings for the lambda (you should already have the SG for RDS there, named something like "lambda-rds-1")
• create a VPC Endpoint for S3 (or whichever AWS service you connect to)
  • see https://docs.aws.amazon.com/vpc/latest/privatelink/vpc-endpoints-s3.html
  • My steps:
    • VPC > Endpoints > Create endpoint
    • type : AWS services
    • Enter S3 and you'll see a couple choices
      • Service Name : com.amazonaws.us-west-2.s3
        • or similar name depending on your region
      • Type : Gateway
    • VPC : the VPC that your lambda is attached to
    • Policy : Full access (or create a mode fine grained policy if desired)
    • Post creation : Go to VPC Endpoints, pick the one you just created, and add VPC route table to the VPC Endpoint (I had a private one for RDS, and used that one)