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.

  • You've condensed each file from 2MB to 5MB? What are the column types into which you are inserting this data? What indexes exist? What class of RDS machine is this?
    – jjanes
    Apr 4, 2020 at 14:11
  • The 5 MB file size is if I export the interim data into csv (which I don't do after I realized that \copy is not faster). The column type that I am inserting into is jsonb The only index is the default id column. This is just a t2.micro class, I check and see that the cpu usage is between 10 - 60% with my multiprocessing @jjanes
    – shawnngtq
    Apr 4, 2020 at 14:50

1 Answer 1


t2 classes are unsuitable for high work loads, by design. Once you have used up all your CPU credits, performance is abysmal. Again, by design.

Don't use t2 (or any t*) instances if you care about performance.

As far as I can tell, there is no way to monitor the CPU credits on RDS (the way you can on EC2)

  • What instance class would you recommend I change to?
    – shawnngtq
    Apr 4, 2020 at 16:10
  • I don't know how intensively you will be using the database after it is populated. I'd probably start out with db.m5.large.
    – jjanes
    Apr 4, 2020 at 16:16
  • Thanks, I change t2 into a different instance class as you suggested, and it works.
    – shawnngtq
    Apr 5, 2020 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.