Speed up AWS PostgreSQL insertion via Python script
The reason the database performance drop from 50 to 3.33 csv per min is because of the AWS RDS instance class.
I am using db.t2.micro class, which I just learnt that it's limited by CPU credits. After I change the instance class to t3, my code is back to 50 csv per min.

Amazon RDS T3 DB instances run in Unlimited mode, which means that you will be charged if your average CPU utilization over a rolling 24-hour period exceeds the baseline of the instance. CPU Credits are charged at $0.075 per vCPU-Hour. The CPU Credit pricing is the same for all T3 instance sizes across all regions and is not covered by Reserved Instances.

Considering that my code takes <1 sec to execute, and only 1 record to insert each time. pandas.to_sql shouldn't be the bottleneck. Though I do believe using SQLAlchemy will perform better than pandas.to_sql. For the same reason psycopg2 performs better than SQLAlchemy.
In short, this is an issue due to hardware rather than software. Fix it by upgrading to a more powerful instance. Thanks.
I have 120,000 csv inside my AWS EC2 instance, each containing 120,000 rows. I can't insert each of them as is into my AWS RDS postgresql DB, that will be 120,000^2 = 14,400,000,000 records. Each csv is about 2 MB.
My approach is:

Python script that converts 120,000 records into just 1 record (list of dictionary) for each csv (now 5 MB after condensing the data) with the help of pandas library
The python script then insert each csv's 1 record into AWS postgresql database via pandas.to_sql (which uses sqlalchemy
I use python multiprocessing module to fully utilize my AWS EC2 to speed up data insertion
I did not create additional indexes in order to speed up my data insertion
I use AWS EC2 instance with up to 25GB of network connection

My observation is:

At the beginning, my code will insert 50 csv per min, which is decent speed
However, right now, with 50k csv being inserted, it only insert 1k csv in 5 hours, about 3.33 csv per min
I tried using psql \copy and realized that it takes between 30-50 sec to insert 1 csv, that's slower than my script that converts the data and insert into the DB directly

I am not sure how I can speed up things up.