4 ms·

This is the perfect time to drop down into the database and let the database handle it. If you're doing mass migration of data, the data should not ever touch r

This is the perfect time to drop down into the database and let the database handle it. If you're doing mass migration of data, the data should not ever touch rails. Also, this is another example of why normalizing your schemas is important - in a normalized schema, you would only have fiftyish state records, and a few tens of thousands of city records. (Yes, this is just an example, I'm sure they wouldn't actually update city and state on every record this way, but it's a useful example of why normalized data is easier to work with. Imagine if you missed a few records - now you have a random assortment of Cleveland, CLEVELAND, and cleveland, where if your schema were normalized with a constraint, you'd have one city_id pointing to Cleveland, OH)

whalesalad 4y ago
Agreed. You could add/apply them as rails migrations, too, that way the transformations exist as code and can be referenced/seen in the future.
- jaggederest 4y ago
  There's a useful gem that creates a data_migrations directory (and associated table once run) for things like this, that are not changing the schema. I tend to write them as rake tasks with an `up` and `down` method if they are reversible. Also, hopefully, the migrations are idempotent and just do nothing if there are no affected records (though not all data changes are like that of course)
  - chao- 4y ago
    >There's a useful gem that creates a data_migrations directory Would you please share this useful gem's name? I have hand-rolled some variety of this solution so many times that it feels like just a part of life, like doing the laundry. If a gem can do that setup for me, I would gladly use it.
    - mrcb123 4y ago
      https://github.com/ilyakatz/data-migrate https://github.com/ilyakatz/data-migrate and for seeding data https://github.com/pboling/seed_migration https://github.com/pboling/seed_migration
    - jaggederest 4y ago
      I was thinking of this one, I believe, but it's pretty old: https://github.com/OffgridElectric/rails-data-migrations https://github.com/OffgridElectric/rails-data-migrations This is maybe a more modern one I found: https://github.com/ilyakatz/data-migrate https://github.com/ilyakatz/data-migrate
simonw 4y ago
"Letting the database handle it" for truly large tables can have some potentially tricky issues. The most common I've seen relate to replication. If your database has replicas and uses row-based replication (as opposed to statement-based replication) running "update huge_table set title = upper(title)" could create a billion updated rows, which then need to be copied to all of those replicas. This can cause big problems. In those cases, it's better to update 1,000 rows at a time using some kind of batching mechanism, similar to the one described in the article.
- jaggederest 4y ago
  I agree, but I would do the batching in the database rather than in a job, using something like a function that counts up or pg_sleep between queries with a subset of the range of existing rows.
  - whakim 4y ago
    Right, but how do you do this concurrently? This sounds like you plan to sequentially execute these batches, which could be painfully slow.
    - jaggederest 4y ago
      You can either limit the load on the database by doing it "painfully slow", or run it all at once, there's no third option. The batches can be arbitrarily large per-query - 5000; 50,000; 500,000 rows per sequential query; depending on how much your database can handle at once without being unacceptably overloaded. There's zero need for concurrency in this context - let the database handle the concurrency internally to the storage engine, and just specify the logical batch size. N.B. postgresql can use parallel execution of workers in batches above a certain size, Depending On Your Settings™ and version
      - whakim 4y ago
        I have found that in most real-world cases if you parallelize SQL commands you can significantly increase CPU utilization and thus database throughput.
hk1337 4y ago
Agreed. Rails uses ActiveRecord and you'll most likely be trying to load millions of records through an ORM which will make you go insane trying to get it to work efficiently. I have no problem with Rails or ActiveRecord but bulk upload of data through any ORM is a big no for me.
- izietto 4y ago
  > Rails uses ActiveRecord and you'll most likely be trying to load millions of records through an ORM which will make you go insane trying to get it to work efficiently. You just need to use batches (that `.in_batches` in the link), it's an easy-peasy approach which is way more acceptable for your database than a single update of 500m records. I've been using it for years and it has never raised a single issue.
  - FrancoisBosun 4y ago
    The downcasing in done in Ruby-land, thus the records had to be loaded in memory, on the worker. If the downcasing was done on the DB itself, then the records would not have needed to travel all the way to the worker, only to be discarded 200ms later. Better to run an UPDATE query direct against the DB instance, with IDs to control concurrency: UPDATE addresses SET city = lower(city), state = lower(state) WHERE id BETWEEN a AND b
    - inferiorhuman 4y ago
      Yeah, one of the first things I learned (Ruby 1.8) was that Ruby is slow. If you can avoid creating a bunch of Ruby objects and push the logic into the database you'll typically see performance gains. The big exception was Mongo, where more "advanced" features got shoved off to the JS interpreter which was an expensive operation – at least until the 'aggregation framework' became a thing. Anyways. Before blindly doing string manipulation with postgres make sure that all the locale stuff is set correctly.
    - revscat 4y ago
      This is what the `in_batches` method already does. See [1], which is called by the `in_batches` method for update operations. [1] https://api.rubyonrails.org/classes/ActiveRecord/Relation.html#method-i-update_all https://api.rubyonrails.org/classes/ActiveRecord/Relation.ht...
itake 4y ago
I've tried this, but we ran into high CPU usage while the task was being run, causing other queries to time out or be delayed. Is there a way to send low priority updates that wont interfere with other requests?
- jaggederest 4y ago
  Depends on whether you have access to the underlying system - postgres queries run in processes per-query so you can renice the process, or you can do similar things in C with extensions. But not out of the box or on something like RDS - then your best bet is to write a script or function that uses sleep or pg_sleep to wait between calls to the database - I tend to do it in the database itself by using `CREATE FUNCTION` to write it, but depending on your comfort with postgres it may be better to do it in a scripting language or shell script (as in the parent article using a background job)
  - brasic 4y ago
    > processes per-query Minor nit, this is probably what you meant but it’s process per _connection_.

whalesalad 4y ago

Agreed. You could add/apply them as rails migrations, too, that way the transformations exist as code and can be referenced/seen in the future.

jaggederest 4y ago
There's a useful gem that creates a data_migrations directory (and associated table once run) for things like this, that are not changing the schema. I tend to write them as rake tasks with an `up` and `down` method if they are reversible. Also, hopefully, the migrations are idempotent and just do nothing if there are no affected records (though not all data changes are like that of course)
- chao- 4y ago
  >There's a useful gem that creates a data_migrations directory Would you please share this useful gem's name? I have hand-rolled some variety of this solution so many times that it feels like just a part of life, like doing the laundry. If a gem can do that setup for me, I would gladly use it.
  - mrcb123 4y ago
    https://github.com/ilyakatz/data-migrate https://github.com/ilyakatz/data-migrate and for seeding data https://github.com/pboling/seed_migration https://github.com/pboling/seed_migration
  - jaggederest 4y ago
    I was thinking of this one, I believe, but it's pretty old: https://github.com/OffgridElectric/rails-data-migrations https://github.com/OffgridElectric/rails-data-migrations This is maybe a more modern one I found: https://github.com/ilyakatz/data-migrate https://github.com/ilyakatz/data-migrate

simonw 4y ago

"Letting the database handle it" for truly large tables can have some potentially tricky issues. The most common I've seen relate to replication. If your database has replicas and uses row-based replication (as opposed to statement-based replication) running "update huge_table set title = upper(title)" could create a billion updated rows, which then need to be copied to all of those replicas. This can cause big problems. In those cases, it's better to update 1,000 rows at a time using some kind of batching mechanism, similar to the one described in the article.

jaggederest 4y ago
I agree, but I would do the batching in the database rather than in a job, using something like a function that counts up or pg_sleep between queries with a subset of the range of existing rows.
- whakim 4y ago
  Right, but how do you do this concurrently? This sounds like you plan to sequentially execute these batches, which could be painfully slow.
  - jaggederest 4y ago
    You can either limit the load on the database by doing it "painfully slow", or run it all at once, there's no third option. The batches can be arbitrarily large per-query - 5000; 50,000; 500,000 rows per sequential query; depending on how much your database can handle at once without being unacceptably overloaded. There's zero need for concurrency in this context - let the database handle the concurrency internally to the storage engine, and just specify the logical batch size. N.B. postgresql can use parallel execution of workers in batches above a certain size, Depending On Your Settings™ and version
    - whakim 4y ago
      I have found that in most real-world cases if you parallelize SQL commands you can significantly increase CPU utilization and thus database throughput.

hk1337 4y ago

Agreed. Rails uses ActiveRecord and you'll most likely be trying to load millions of records through an ORM which will make you go insane trying to get it to work efficiently. I have no problem with Rails or ActiveRecord but bulk upload of data through any ORM is a big no for me.

izietto 4y ago
> Rails uses ActiveRecord and you'll most likely be trying to load millions of records through an ORM which will make you go insane trying to get it to work efficiently. You just need to use batches (that `.in_batches` in the link), it's an easy-peasy approach which is way more acceptable for your database than a single update of 500m records. I've been using it for years and it has never raised a single issue.
- FrancoisBosun 4y ago
  The downcasing in done in Ruby-land, thus the records had to be loaded in memory, on the worker. If the downcasing was done on the DB itself, then the records would not have needed to travel all the way to the worker, only to be discarded 200ms later. Better to run an UPDATE query direct against the DB instance, with IDs to control concurrency: UPDATE addresses SET city = lower(city), state = lower(state) WHERE id BETWEEN a AND b
  - inferiorhuman 4y ago
    Yeah, one of the first things I learned (Ruby 1.8) was that Ruby is slow. If you can avoid creating a bunch of Ruby objects and push the logic into the database you'll typically see performance gains. The big exception was Mongo, where more "advanced" features got shoved off to the JS interpreter which was an expensive operation – at least until the 'aggregation framework' became a thing. Anyways. Before blindly doing string manipulation with postgres make sure that all the locale stuff is set correctly.
  - revscat 4y ago
    This is what the `in_batches` method already does. See [1], which is called by the `in_batches` method for update operations. [1] https://api.rubyonrails.org/classes/ActiveRecord/Relation.html#method-i-update_all https://api.rubyonrails.org/classes/ActiveRecord/Relation.ht...

itake 4y ago

I've tried this, but we ran into high CPU usage while the task was being run, causing other queries to time out or be delayed. Is there a way to send low priority updates that wont interfere with other requests?

jaggederest 4y ago
Depends on whether you have access to the underlying system - postgres queries run in processes per-query so you can renice the process, or you can do similar things in C with extensions. But not out of the box or on something like RDS - then your best bet is to write a script or function that uses sleep or pg_sleep to wait between calls to the database - I tend to do it in the database itself by using `CREATE FUNCTION` to write it, but depending on your comfort with postgres it may be better to do it in a scripting language or shell script (as in the parent article using a background job)
- brasic 4y ago
  > processes per-query Minor nit, this is probably what you meant but it’s process per _connection_.