4 ms·

Ooohh umm...no transaction? WhenEVER I make an insert, update, or delete -- I always use a transaction. That's what we should always do, right? Right?

whalesalad 3y ago
If you’re updating a single entity it doesn’t really matter. An exception is if you’re doing something like select for update in psql where you’re explicitly introducing locks. If you’re performing multiple operations simultaneously and want them all to go or die together, yes.
chrisandchris 3y ago
I think you really don't want to keep a transaction > 5 hours open. > For our setup/task (just update few fields on a table) the process of probing different batch sizes & Sidekiq thread numbers with couple of thousands/millions records took about 5 hours [...]
- jaggederest 3y ago
  I've seen transactions (pathologically) held open for weeks with no huge issues. Postgres is amazing. You're 100% right that you should not do it though. Also little things like if you have more than some very large number of transactions while one transaction is open, things start to break down.
- zzzeek 3y ago
  you certainly dont want to autocommit every row at a time as depending on backend that will slow you down by a large margin. this rails feature that runs a bulk update/insert I would assume is certainly doing so within either a single tranasction, or a set of relatively few per-chunk transactions.
  - chrisandchris 3y ago
    No, you don't. You want to commit a set of statements that is a business transaction.
SkyPuncher 3y ago
Transactions are only necessary when the change is tightly coupled. --- In this case, it's okay for individual records to fail. They can either be manually processed, re-run, or root caused for failure and handled individually.
- swe_dima 3y ago
  Groupping several updates inside a transaction actually makes it faster because database doesn't have to "commit" changes for each one. This has diminishing returns however, if too much data changes inside a transaction it can becomes slower due to memory issues. So something like 1000-10000 updates per transaction is a sweet spot I think.
  - what-no-tests 3y ago
    THIS. A lot of the work I do involves analyzing and improving Ruby applications' performance. Often, simply wrapping transactions around the database operations will help - without making other (often much needed) structural changes.
  - SkyPuncher 3y ago
    While you're technically correct, it's probably unlikely performance is going to matter for these types of mass updates. As long as performance isn't atrocious, theres one-time updates will be "fast enough". Typically, these get run during off-peak hours. This running in 30 minutes vs 12 hours simply isn't important.

whalesalad 3y ago

If you’re updating a single entity it doesn’t really matter. An exception is if you’re doing something like select for update in psql where you’re explicitly introducing locks. If you’re performing multiple operations simultaneously and want them all to go or die together, yes.

chrisandchris 3y ago

I think you really don't want to keep a transaction > 5 hours open. > For our setup/task (just update few fields on a table) the process of probing different batch sizes & Sidekiq thread numbers with couple of thousands/millions records took about 5 hours [...]

jaggederest 3y ago
I've seen transactions (pathologically) held open for weeks with no huge issues. Postgres is amazing. You're 100% right that you should not do it though. Also little things like if you have more than some very large number of transactions while one transaction is open, things start to break down.
zzzeek 3y ago
you certainly dont want to autocommit every row at a time as depending on backend that will slow you down by a large margin. this rails feature that runs a bulk update/insert I would assume is certainly doing so within either a single tranasction, or a set of relatively few per-chunk transactions.
- chrisandchris 3y ago
  No, you don't. You want to commit a set of statements that is a business transaction.

SkyPuncher 3y ago

Transactions are only necessary when the change is tightly coupled. --- In this case, it's okay for individual records to fail. They can either be manually processed, re-run, or root caused for failure and handled individually.

swe_dima 3y ago
Groupping several updates inside a transaction actually makes it faster because database doesn't have to "commit" changes for each one. This has diminishing returns however, if too much data changes inside a transaction it can becomes slower due to memory issues. So something like 1000-10000 updates per transaction is a sweet spot I think.
- what-no-tests 3y ago
  THIS. A lot of the work I do involves analyzing and improving Ruby applications' performance. Often, simply wrapping transactions around the database operations will help - without making other (often much needed) structural changes.
- SkyPuncher 3y ago
  While you're technically correct, it's probably unlikely performance is going to matter for these types of mass updates. As long as performance isn't atrocious, theres one-time updates will be "fast enough". Typically, these get run during off-peak hours. This running in 30 minutes vs 12 hours simply isn't important.