Hacker News Reader

Comment by jaggederest | Hacker News Reader

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)

View on HN

"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.

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.

Right, but how do you do this concurrently? This sounds like you plan to sequentially execute these batches, which could be painfully slow.

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

I have found that in most real-world cases if you parallelize SQL commands you can significantly increase CPU utilization and thus database throughput.

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.

> 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.

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

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.

I agree with you if you focus on this very example, but I think to it just as an example. When I read this article, I replace <lowercase> with <some business logic implemented server side>.

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.ht...

You could use `in_batches` that way, but in the code from the article it looks like each call to `maximum`/`minimum` is causing a round trip, plus the `map` inside `MyService` is loading each batch into memory.

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.

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)

>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.

I was thinking of this one, I believe, but it's pretty old:

https://github.com/OffgridElectric/rails-data-migrations

This is maybe a more modern one I found:

https://github.com/ilyakatz/data-migrate

and for seeding data https://github.com/pboling/seed_migration

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?

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)

  > processes per-query

Minor nit, this is probably what you meant but it’s process per _connection_.

Reply on news.ycombinator.com