awswrangler.postgresql.to_sql is too slow, inserting row-by-row

[ilyanoskov]

I am using this library quite extensively in my pipelines and I have noticed that even small dataframes (44K rows) take a VERY long time to get uploaded to Postgres. Would it be possible to introduce some bulk upload support? Something like described here : https://stackoverflow.com/questions/29706278/python-pandas-to-sql-with-sqlalchemy-how-to-speed-up-exporting-to-ms-sql

Otherwise, I think I will be forced to write my own custom method for bulk uploading, the waiting times are too much. Thank you very much in advance, and thanks for such an amazing project 💪

[maxispeicher]

I've added a chunksize parameter to the to_sql function, which tells how many rows should be inserted inside a single SQL query. In a local test it decreased the time for inserting from 120 to 1 second for me. Could you test if it works for you too?:

pip uninstall awswrangler -y
pip install git+https://github.com/maxispeicher/aws-data-wrangler.git@to_sql_add_batching
import awswrangler as wr
...
wr.postgresql.to_sql(..., chunksize=500)

Note that the default value is 1, so you have to explicitly set it.

[ilyanoskov]

Hi @maxispeicher, thanks a lot for such a quick response! I won't be able to test this new feature this week, but I did look at your Pull Request and it looks good to me! 🚀

[igorborgest]

P.S. We end up with 200 as the default value.

[jaidisido]

Covered in release 2.6.0