Direct S3 Data Import to Aurora PostgreSQL - A Performance Comparison

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 First, install the extension for S3 integration: CREATE EXTENSION IF NOT EXISTS aws_s3 CASCADE; Import the file from S3: postgres=> SELECT aws_s3.table_import_from_s3( 'test_table', '', '(format csv, header true)', '', 'large_test_data.csv', 'ap-northeast-1', '' ); 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) First, download the file from S3: ~ $ time aws s3 cp s3:///large_test_data.csv ./ download: s3:///large_test_data.csv to ./large_test_data.csv real 0m2.301s 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: Method Processing Time S3 Integration 11.6 seconds Via Application 13.7 seconds (Download: 2.3 seconds, COPY command: 11.4 seconds) 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