search

upper / db

Data Access Layer (DAL) for PostgreSQL, CockroachDB, MySQL, SQLite and MongoDB with ORM-like features.
https://upper.io/
MIT License
3.53k stars 235 forks source link

Don't wrap batch queries in atomic transactions #528

Open mcandre opened 4 years ago

mcandre commented 4 years ago

Atomic transactions are unnecessary overhead for bulk operations, and lead to memory spikes in distributed deployments. Please remove the tx wrapping that happens in StatementExec for batch insertion and other batch queries.

As a workaround, I am skipping the upper.io batch inserter and submitting a traditional INSERT INTO... string query. This runs 100x faster than the transaction wrapped, ORM'ed query.

xiam commented 4 years ago

This is the desired behaviour: if an error happens in the middle of a bulk operation, the whole operation is rolled back, if we remove the transaction then we won't be able to ensure that the bulk operation is atomic. Maybe it will run 100x faster, but I think knowing whether the whole batch succeeded or not is better.

Using a regular INSERT INTO sounds good in this case.

mcandre commented 4 years ago

Ah, I think I understand.

Yes, for many applications and queries, having each batch operate atomically is a good fail-safe in case of any errors.

In my case, however, I actually don't mind if the inserts fail. I want the error propagated through the Go client but otherwise don't care for my particular database configuration. So an option to disable locking for batch operations would be appreciated.