Direct S3 Data Import to Aurora PostgreSQL - A Performance Comparison

#aws #postgres #s3 #aurora

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)',
    '<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)

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

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

Top comments (1)

오피셜메일 • Nov 7

다운로드 시간을 포함한 전체 시간과 DB 적재 시간을 따로 제시해 비교 범위가 명확하네요. 앱의 디스크 사용과 파일 처리 경로를 없애는 장점을 성능 수치와 분리한 점도 좋습니다. 반복 측정 횟수와 캐시 상태, 인덱스·제약 조건의 유무까지 덧붙이면, 11.6초와 13.7초의 차이를 다른 환경에서 해석하기 더 쉬울 것 같습니다.