Introduction

Aurora PostgreSQL has a built-in feature that allows direct data exchange with S3. This capability enables you to import data directly from S3 to PostgreSQL without going through your application layer, which was previously the standard approach.

This approach offers several advantages:

• No need to handle files on the application side
• No concerns about application disk space
• Simpler implementation

In this article, I'll demonstrate this feature and compare it with traditional import methods.

For more details about this feature, please refer to the AWS documentation.

Test Environment

• AWS Tokyo Region
• Aurora PostgreSQL 17.4 (db.t3.medium)
• large_test_data.csv (10 million rows test data)
• test_table (table for data storage)

Importing Files from S3

1. First, install the extension for S3 integration:

CREATE EXTENSION IF NOT EXISTS aws_s3 CASCADE; 

1. Import the file from S3:

postgres=> SELECT aws_s3.table_import_from_s3( 'test_table', '', '(format csv, header true)', '<S3 bucket name>', 'large_test_data.csv', 'ap-northeast-1', '<IAM role name associated with Aurora>' ); table_import_from_s3 ----------------------------------------------------------------------------------------------------- 10000000 rows imported into relation "test_table" from file large_test_data.csv of 208888910 bytes (1 row) Time: 11639.759 ms (00:11.640) postgres=> 

Traditional Method (Import via Application)

1. First, download the file from S3:

~ $ time aws s3 cp s3://<S3 bucket name>/large_test_data.csv ./ download: s3://<S3 bucket name>/large_test_data.csv to ./large_test_data.csv real 0m2.301s 

1. Import using the COPY command:

postgres=> \COPY test_table (id, name, age) FROM 'large_test_data.csv' WITH (FORMAT csv, HEADER true); COPY 10000000 Time: 11416.487 ms (00:11.416) postgres=> 

Summary

Here are the results:

The S3 integration method proved to be slightly faster. While the performance difference isn't dramatic, the main advantage is bypassing the application layer entirely. This article demonstrated importing, but exporting is also possible. Additionally, Aurora can even invoke Lambda functions.

Although this feature isn't widely used or known in the field, it offers many benefits and should be actively utilized in more projects.

🤖 Generated with Claude Code