ETL from an Oracle database into RDS Postgres too slow

Here are some of the basic points that you should be aware while from Oracle (on EC2 or on-premises), especially applicable to large source databases.

• By default DMS migrates 8 tables in parallel. If you have larger number of tables that are small in size, recommended would be to increase this setting to migrate more than 8 tables at a time (up to 49). This can be done modifying task "Advanced settings" or by modifying the parameter "MaxFullLoadSubTasks" from CLI.

• By default DMS uses commit rate of 10000 which can be increased up to 50000. This indicates the maximum number of events that can be transferred together and will help in one-time migration. This can be done modifying task "Advanced settings" or by modifying the parameter "CommitRate" from CLI.

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TaskSettings.FullLoad.html

• You can improve the performance of full load by using parallel load strategy of DMS using which DMS will read data from single table in multiple threads. This can be done by modifying the task table mappings to specify the number of threads.

https://aws.amazon.com/blogs/database/introducing-aws-dms-replication-engine-version-3-1-2/

• It is always recommended to create task with limited lob size. So recommendation would be to identify the size of lob columns from source database and use it as the "maximum size of lob column" in task settings.

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html

• You can increase the maximum size (in KB) of any .csv file used to transfer data to PostgreSQL by modifying the extra connect attribute (maxFileSize) for target Postgres endpoint. By default it is set to 32MB and you can increase it up to 1.1 GB

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.PostgreSQL.html#CHAP_Target.PostgreSQL.ConnectionAttrib