Incrementing in ActiveRecord
This week at Versa, we worked on optimizing our analytics system. In doing so, we've been presented with a problem where we want to increment certain model attributes as fast as possible (and we don't need any callbacks to run).
In Mongo, we can use the $inc command to achieve this out of the box. With ActiveRecord, however, we could not find a baked-in solution. So today I wanted to go through what we came up with, along with some refactorings to make it more flexible.
Note: ActiveRecord CounterCaches are close, but we aren't basing these totals on the number of relations, so they are out.
Let's say we have an ActiveRecord model, Foo, which has an attribute called bars. At a certain point in the code we want to increment bars.
It seems very Rails-y to do something like this:
class Foo < ActiveRecord::Base # other model stuff⌠def increment self.bars += 1 end def increment! increment save! end end
Then our app (in a controller for example) can do something like:
f = Foo.find(params[:id]) f.increment!
Which works fine for most cases. Huzzah.
At Versa, we need to do lots of operations like this throughout the day, so we wanted to see if it could be optimized.
The first thing we noticed is that the implementation above makes two calls to the DB (one for the find and one for the save).
Sidebar: one of the great things about Rails (and frameworks in general) is that it abstracts away the db interaction, making your app database agnostic (as long as it's SQL-based). This is great in theory and it makes prototyping an app pretty simple. You can start out with SQLite, make a prototype, then easily switch to something like PostgreSQL for production.
Realistically though, once you've got a production db (especially in the relational DB world), the odds of you switching are pretty low. And if you do, it is probably so you can access DB-specific functionality.
I say all of this to bring up the point that once you are "locked" into a database, you should not be afraid to drop down to the DB layer when necessary. It's ripe with optimizations you can use when the abstractions don't do exactly what you need. /sidebar
Rails people should look at our example above and see that it is essentially doing:
SELECT * FROM foos WHERE id = params[:id];
Loading the result into a Foo model, getting the bars attribute (let's say it's 4). Then it hits the DB with an update like
UPDATE foos SET bars = 5 WHERE id = params[:id];
This isn't bad for most cases, but people familiar with PostgreSQL (it might work in all SQL flavors, I didn't confirm) will see those two queries and say, why don't you just one-line it with:
UPDATE foos SET bars = bars + 1 WHERE id = params[:id]
Which produces exactly the same result, but only involves one call to the db. Postgres, ftw.
The problem here is that (as far as I could tell) the one-liner isn't possible with ActiveRecord. So let's think through how we could refactor the model.
First, we don't need to instantiate an instance of Foo, so let's make increment! a class method that accepts an ID.
class Foo < ActiveRecord::Base # other model stuff⌠# I know, I know, we shouldn't have a bang (!) method without a # corresponding non-bang method, but the whole point of this method # is that it does the lookup and update in one shot. So I thought # it appropriate to make a bang-only method (since it persists to the DB). def self.increment!(foo_id) the_sql = "UPDATE foos SET bars = bars + 1 WHERE id = %d" % foo_id self.connection.execute(the_sql) end end
This is pretty good and fulfills all of our stated requirements.
I would, however, like to refactor increment! so that can accept an array of IDs (our particular use case requires this).
class Foo < ActiveRecord::Base # other model stuff⌠def self.increment!(foo_ids) # Note: we know that the IDs in our table are integers, so we # `to_i` everything to sanitize the values we plug into SQL. # Also, I would normally one-line this, but it becomes too # wide for blogging :) if foo_ids.is_a?(Array) query_ids = foo_ids.map(&:to_i).join(',') else query_ids = foo_ids.to_i end the_sql = "UPDATE %s SET bars = bars + 1 WHERE id IN (%s)" % [self.table_name, query_ids] self.connection.execute(the_sql) end end
As you can see, I also changed the SQL string so that the table name is derived from the current class.
Let's go one step further and make the attribute being incremented an argument to the method. Then we can increment bars with
Foo.increment!(123, :bars)
and increment some other attribute, say loads with
Foo.increment!([123, 124], :loads)
The class would then look like:
class Foo < ActiveRecord::Base # other model stuff⌠def self.increment!(foo_ids, attr_name) # Note: we know that the IDs in our table are integers, so we # `to_i` everything to sanitize the values we plug into SQL. # Also, I would normally one-line this, but it becomes too # wide for blogging :) if foo_ids.is_a?(Array) query_ids = foo_ids.map(&:to_i).join(',') else query_ids = foo_ids.to_i end # This line was getting too long so I broke it up. the_fields = [self.table_name, attr_name, attr_name, query_ids] the_sql = "UPDATE %s SET %s = %s + 1 WHERE id IN (%s)" % the_fields self.connection.execute(the_sql) end end
At this point we've abstracted all of the Foo-specific code from the method. That makes this a perfect candidate for a module (or a concern) that would allow us to drop increment! into any ActiveRecord model in our appâŚbut I'll leave that as an exercise for the reader ;)
If you find this type of refactoring interesting, you should get in touch with us at Versa. The example we just walked through is a slightly simplified version of something I worked on yesterday.