4 ms·
Update millions of records in Rails
- what-no-tests 4y agoOoohh 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 4y agoIf 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 4y agoI 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 4y agoI'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 4y agoyou 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 4y agoNo, you don't. You want to commit a set of statements that is a business transaction.
- SkyPuncher 4y agoTransactions 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 4y agoGroupping 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 4y agoTHIS. 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 4y agoWhile 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.
- jaggederest 4y agoThis 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 agoAgreed. 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 agoThere'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 agohttps://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
- manv1 4y agoReally, a better solution might be to spew the results into another table and then schedule downtime and replace the original table (via rename or whatever). It all depends on your database, which is why you need an actual DBA role on your team instead of just using your database like an abstract storage entity.
- alex_lav 4y ago> It all depends on your database, which is why you need an actual DBA role on your team instead of just using your database like an abstract storage entity. People seem to be getting along alright without it, no?
- manv1 4y agoThat's true, but that's sort of suboptimal. I mean ideally you would want someone on your team who understands how the technology behind your product actually works and how to use it efficiently. "We built this awesome tower. The foundation? It's great so far, I guess. But we really are tower builders, not foundation builders." - The team behind the Leaning Tower of Pisa The Leaning Tower stands today, so obviously they built their tower really well. But the problems with the foundation takes away from it somewhat.
- Alifatisk 4y agoCool, is the same applicable if I run mysql?
- albertgoeswoof 4y agoIf you want to build scalable software, this is the only line you need from this blog post: > You need to schedule few thousand record samples and monitor how well/bad will your worker perform I find it staggering how many senior developers don’t know how to profile or performance test their software. Just throwing a few million rows into a local db and measuring your app performance is so basic, yet I’ve seen many teams not even consider this, and others farm it to a “QA”.
- inferiorhuman 4y agoI've seen two things that prompt this behavior: - Fear of the database. For some folks keeping the logic in the app which is fully under their control is comforting. I find this to be common by folks who've been burnt by their db in the past. It's a lot easier to assert (quality) control over your own software. - Difficulty in modeling data. This is more tricky, but IMO separates code monkeys from developers.
- what-no-tests 4y agoYes - often what once worked when the application only had a few hundred or thousand records doesn't work anymore, now that it has millions of records. Those milliseconds, N+1 queries, nested queries, large joins (owing to incongruence between logical data structure and how that data is stored on disk) and poorly-indexed tables combine into lots of sad times.
- jmuguy 4y agoWe have a need to upsert millions of records in Rails on an ongoing basis (weekly). Biggest problem we ran into was attempting to use Sidekiq and using too many workers each making smallish inserts. You don't run out of connections in that case - PG cannot handle that many parallel inserts into the same table(s). After discussing with a friend that frequently works with large data sets, he suggested that single very large upserts/inserts (50k to 100k) records would be handled much better by Postgresql, and they were. Our final setup still has all those Sidekiq workers, but they dump the records as JSON blobs into Kafka. From there a single consumer pulls them out and buffers. Once it has 50k, it dumps them all in a single ActiveRecord Upsert. Its been working very well for us for a while now.
- mrj 4y agoTable partitions work well for this, too. You would do your mass import in a table that would be less likely to affect prod queries. After import, then change the temp table to extend the parent. After that it is used for queries against the parent.
- tinglymintyfrsh 4y agoWasteful and the wrong place to do it. Also, Sidekiq is slow, old, and commercial crippleware. Generate and send SQL (wrapped in transactions) from C bindings via a proper background job processor like beanstalkd in batches of say 50k. Getting Ruby involved in millions of records is asking for trouble.
- peteforde 4y agoFeel free to do things however you like, but Sidekiq is excellent. It's extremely well-designed and battle-hardened code, and it recently had a major release. Most of the non-enterprise customers pay because we want to support Mike Perham, not because we need paid-tier features.
- tinglymintyfrsh 4y ago"Battle-hardened" where besides tiny startups and 8 companies? Beanstalkd is simple, scales, and is platform agnostic. At scale, simple wins.
- peteforde 4y ago8 companies? Where do you get that [absurdly incorrect] number? Mike has actual thousands of enterprise customers. I don't think anyone here suggested that Beanstalkd is bad or should not be used, if it fits your use case like a glove. Speed-wise, let's not forget that Redis is also written in C. Bigger picture, folks outside of the Rails community love to sidestep the simple fact that something like 75% of the total value created by YC-backed companies was created by the subset who built with Rails. Clearly, we're connecting the dots in a way others are not.
- colonwqbang 4y agoPostgres has a built in function that converts strings into lowercase. Why not use that? This seems like an extremely convoluted way of performing the example task. One could suppose that it's just a contrived simple example to show the point, but the headline suggests otherwise.
- stevebmark 4y agoThe best part is the map that not only mutates its own arguments, but also mutates the arguments to the function, which all is in a "service" which has to be instantiated and then "called." I really wish the Rails community would pick up best practices from other languages.
- Toutouxc 4y agoThis. I think that the Rails community is slowly forgetting how to actually write Ruby, and reinventing known and tried OOP patterns using as little OOP as possible — in a purely object-oriented language! I’m working on a codebase where someone had the amazing idea to implement some of the multi-model business logic using the “Interactor” gem. I’ve just spent two days rewriting one of these interactor abominations into a simple 250 LOC module of module_function-ed methods with clear dependencies and data flow, as a temporary solution to be able to see what’s even happening there and build some actual domain models from it. I can’t imagine anyone with some CS background and a bit of experience with other languages to look at the interactor stuff and go: omg this is perfect, exactly what this codebase needs. It’s like a normal Ruby class but it: - hides all instance variables into an opaque “context” bag - has methods which don’t take arguments (they reach into the “context” bag instead) - has methods that don’t have return values (side effects go into the “context” bag) - has no constructor and just chucks everything you pass to #new into the “context” bag - is not understood by RubyMine, arguably the most intelligent Ruby IDE, at all
- gregors 4y agokind of a tangent here, but I wish this would go out of style: >>> MyService.new.call(address_batch) The call method doesn't hold state past execution, so don't instantiate a new object for no reason. Just call a static method.