[Question] how is this performance-wise compared to df.to_sql(method='multi')?

[lefnire]

From reading around, it seems INSERT .. ON CONFLICT) is higher-performance than DELETE .. INSERT .., which is how I'm doing things now, so this library is compelling. However, one big boon of df.to_sql is method='multi', which creates a big-ol' insert statement rather than individual ones, which (combined with chunksize) I've found improves my bulk-insert performance massively. I realize this is more a question about sqlalchemy.dialects.postgresql.insert, but I'm asking here because - being less familiar with that method - I don't see any arguments that can be passed to pangres.upsert for managing the insert approach. Eg, I see postgresql.insert(inline=True) might be something along these lines? Or does postgresql.insert handle like that by default?

TL;DR: is pangres as fast as df.to_sql(method='multi'), or are there plans to add a options which get passed to sqlalchemy..postgresql for performance management? (Does this make sense?)

[lefnire]

Looking at performance.html I see bulk_save_objects and bulk_insert_mappings - look faster than ORM .insert(), but not sure if those are compatible with on_conflict_*?

[Update] Indeed, I've tested (average tqdm ?it/s) and delete-rows -> pd.to_sql(method='multi') is faster for me than pangres.upsert (10k chunksize, ~40M total). So here's hoping there might be some bulk_save_objects + on_conflict possibility.

[ThibTrip]

Hello @lefnire ! I am not extremely knowledgeable in the topic but I can tell you a few things:

• pd.to_sql(method='multi') is faster than pangres.upsert. I suppose that statements with ON_CONFLICT will be slower (this is pure speculation since I could not find a document exactly backing that up but there sure are topics when googling "slow upserts postgres"). Since pandas does not provide this functionality it is hard to make a performance comparison 🤔 . When pandas eventually does provide it, it will be interesting to compare speed 😄 !
• I already use multi inserts so just like method='multi' in pandas (by default with postgres or MySQL it is 10.000 chunks at a time, you can configure this with the argument chunksize). You can find the upserts functions in the upsert.py module. Those upsert functions are used in the module helpers.py and together this represents the most essential part of the library (so upserting data).
• I have made a gist with a speed comparison for simple inserts in postgres
• I am not using sqlalchemy ORM in the library 😛 . Btw ORM can indeed be slower since it is very high level but it is the price to pay for convenience 🐒 .

[lefnire]

Not its ORM, but you are using it's dialects helpers for the insert() .. on_conflict_do function (from sqlalchemy.dialects.postgresql import insert as pg_insert from https://github.com/ThibTrip/pangres/blob/master/pangres/upsert.py). My hunch was the bulk_insert capabilities are in the same non-ORM family of operations, so it might be worth investigating compatibility for on_conflict. Actually, I'll take a look myself - Pandas upsert is pretty important to me, so if I can figure something out I'll submit a PR.

Thanks for the response!

[lefnire]

Oh you know what, you're right & I'm wrong. bulk_insert* are from the ORM library, and they appear to be slower (performance.html) anyway.

SQLAlchemy ORM bulk_save_objects(): Total time for 100000 records 0.568987131119 secs
SQLAlchemy ORM bulk_insert_mappings(): Total time for 100000 records 0.320806980133 secs
SQLAlchemy Core: Total time for 100000 records 0.206904888153 secs

But I'll bop around & tell you what I find out