Infrequent bulk load data to Amazon RDS for PostgresSql instance

For your requirement to bulk load large volumes of product price data into Amazon RDS for PostgreSQL, you're on the right track with using the aws_s3 extension and table_import_from_s3 function, which leverages PostgreSQL's COPY command for efficient data loading.

Based on your current setup (db.m6g.large with GP3 SSD and 3000 IOPS), here are recommendations to optimize for infrequent but intensive bulk loads:

Instance Class Options:

• Consider upgrading to a memory-optimized instance like db.r6g.xlarge or db.r6g.2xlarge during bulk load operations. These instances provide more memory and CPU resources to handle the intensive data processing.
• If you want to stay with general-purpose instances, at least move to db.m6g.xlarge or db.m6g.2xlarge to get more CPU and memory.

Storage Configuration:

• Your current GP3 SSD with 3000 IOPS is a good starting point, but for bulk loading up to 100 million records, consider:
• Increasing provisioned IOPS to 6000-12000 during bulk load operations
• Increasing storage throughput to at least 500 MB/s
• Ensuring you have sufficient storage capacity (add buffer beyond your 200 GiB if needed)

Operational Recommendations:

1. Schedule bulk loads during off-peak hours to minimize impact on regular workloads
2. Consider temporarily disabling automated backups during the load process to improve performance (but create a manual snapshot before starting)
3. Split very large files (>1 GiB) into smaller chunks for more efficient loading
4. Order the data by primary key if possible to improve load times

Cost Efficiency Approach: Since these loads are infrequent, you could temporarily scale up your instance and IOPS just for the bulk load operations, then scale back down afterward. This "just-in-time" scaling approach provides better performance when needed without incurring ongoing costs.

Your database must be running PostgreSQL version 10.7 or higher to use the S3 import functionality, which appears to be the case given your current setup.
Sources
Importing data from Amazon S3 to your RDS for PostgreSQL DB instance - Amazon Relational Database Service
Importing data from Amazon S3 into an RDS for PostgreSQL DB instance - Amazon Relational Database Service
Importing data from any source to an Amazon RDS for MySQL DB instance - Amazon Relational Database Service
Overview of importing data from Amazon S3 data - Amazon Relational Database Service