Samuel Mullen What could possibly go wrong?

ActiveRecord::Base.store - Pros, Cons, and Usage

by Samuel Mullen

Posted on Apr 2, 2012


In my post on ActiveRecord’s new explainmethod, I mentioned there were “two” features in ActiveRecord (introduced in Rails 3.2) I wanted to look at. This post covers the second method: ActiveRecord::Base.store.

Store provides a simple means of accessing and storing key/value pairs related to a model. The API documentation uses the example of a User model which has settings. “Settings” may not in themselves warrant their own model, but there still needs to be a means of accessing them. This is where Store comes in to play.

Behind the curtain, store is just a Hash which gets serialized and deserialized upon save and load. It has a few accessors added in to make it look less hashy, but the hash is still there nonetheless as we’ll see later.

Prior to Rails 3.2, if you needed this functionality you had three choices: 1) implement it yourself; 2) Muck up your table with a bunch of extra fields; 3) create another table to store all the fields.

For the sake of laziness, I’ll use the same project I used in my previous post: https://github.com/samullen/ar_explain

Setup

In our example, we’re going to allow users to toggle the different types of email they want to receive. The first thing we’ll need to do is add the field in which to store the settings.

class AddContactSettingsToUsers < ActiveRecord::Migration
  def change
    add_column :users, :contact_settings, :string, :limit => 4096
  end
end

You’ll likely need some space for your settings so set the string limit to something large. Alternatively, you can use text instead of string if you like, but I tend to run more conservative.

Next, we’ll need to let the user model know we’ll be using the contact_settings field as a store.

class User < ActiveRecord::Base
  store :contact_settings, accessors: [ :daily_email, :weekly_email, :account_email ]
  
  has_many :owner_restaurants
end

Well that wasn’t too difficult.

Usage

Like I said, the field used as the store is really just a hash. You can see that in the console when I retrieve the first user record:

1.9.3p125 :001 > u = User.first
  User Load (0.3ms)  SELECT `users`.* FROM `users` LIMIT 1
 => #<User id: 1, name: "John Galt", email: "john@galtsgulch.com", created_at: "2012-03-08 01:50:22", updated_at: "2012-03-08 01:50:22", contact_settings: {}> 
1.9.3p125 :002 > u.contact_settings
 => {} 
1.9.3p125 :003 > u.contact_settings.class
 => Hash 

Instead of accessing the attributes through contact_settings as you would a normal Hash, you access them as if they were attributes on the model itself.

1.9.3p125 :005 > u.weekly_email = true
 => true 
1.9.3p125 :006 > u.account_email = true
 => true 
1.9.3p125 :007 > u.daily_email = false
 => false 
1.9.3p125 :008 > u
 => #<User id: 1, name: "John Galt", email: "john@galtsgulch.com", created_at: "2012-03-08 01:50:22", updated_at: "2012-03-08 01:50:22", contact_settings: {:weekly_email=>true, :account_email=>true, :daily_email=>false}> 
1.9.3p125 :009 > u.contact_settings
 => {:weekly_email=>true, :account_email=>true, :daily_email=>false} 

As mentioned earlier, store fields are just hashes. This means you can access them and use methods on them just like any other hash. You can even add attributes not defined in the store.

1.9.3p125 :010 > u.contact_settings[:foo] = "bar"
1.9.3p125 :012 > u.contact_settings
 => {:weekly_email=>true, :account_email=>true, :daily_email=>false, :foo=>"bar"} 

If we were to save the record and look at it in the database (without :foo => "bar"), it would look like this.

mysql> select * from users;
+----+--------------+----------------------+---------------------+---------------------+-------------------------------------------------------------------+
| id | name         | email                | created_at          | updated_at          | contact_settings                                                  |
+----+--------------+----------------------+---------------------+---------------------+-------------------------------------------------------------------+
|  1 | John Galt    | john@galtsgulch.com  | 2012-03-08 01:50:22 | 2012-04-04 11:23:47 | ---
:weekly_email: true
:account_email: true
:daily_email: false
 |
|  2 | Howard Roark | howard@architect.com | 2012-03-08 01:50:22 | 2012-03-08 01:50:22 | NULL                                                              |
+----+--------------+----------------------+---------------------+---------------------+-------------------------------------------------------------------+

Pros

I think many of the “pros” for ActiveRecord.store are pretty obvious: it eliminates the need for yet another table or extra fields; simplifies adding new attributes; they work just like normal model attributes. Did I mention that validations work on Store attributes? They do, and Rafal Wrzochol has a great write up on using them

So what are the drawbacks?

Cons

There are a number of philosophical reasons against using the new Store feature. The main argument against is that the data really isn’t normalized. But we’re using Rails, which means we have a tendency to abuse normalization for the sake of the application anyway.

Another minus is that dirty attributes don’t work quite as well in the Store. For instance, you can’t call User#weekly_email_changed?. The only thing you can do is check if the Store field has changed (e.g. User#contact_settings_changed?). Again, it’s not really a huge issue and I imagine this will get resolved in future releases.

Really the main “con” with regard to using store - and this really is a big deal - is that you can’t perform efficient searches on the field. The only way to perform a search is by surrounding the search term with “%” characters.

SELECT * FROM users WHERE contact_settings LIKE '%weekly_email: true%';

If the percent sign was just on the end, that would be one thing, but with the leading “%” it’s now the slowest possible way of searching the database.

I really think the new Store feature in Rails 3.2 is a nice feature. They’ve done it well and made its usage fairly seamless (i.e. store attributes look and act like any other attribute). If your application’s database is fairly large or if you plan on running a lot of queries against the attributes in the Store (e.g. gathering lots of metrics) you may want to use a separate table. For most applications out there, however, this is a very safe and sane solution.

Further Reader

Read More

Understanding ActiveRecord::Base.explain in Rails 3.2

by Samuel Mullen

Posted on Mar 7, 2012


With the release of Rails 3.2, comes a host of new features. There are two in particular in ActiveRecord which seem to have attracted a lot of attention, so I thought I would look at them each more closely. This post, as you have no doubt realized from the title, is focused on ActiveRecord::Base.explain

This may come as a shock, but explain is only new in Ruby on Rails; it - or similar features - has been around in the database world forever. Stated most simple, “The EXPLAIN statement provides information about the execution plan for a SELECT statement.” (MySQL 7.8.2. EXPLAIN Output Format) In other words, when you explain a query, the database returns information about how it is going to go about finding all the data. It’s a very useful tool which can help developers uncover why queries are running slowly and what might be done to speed them up.

Let’s look at an example so we can see the value of explain.

Schema Setup

We’ll need a couple tables with data for our example. I’ll use users and user_restaurants.

class CreateUsers < ActiveRecord::Migration                                     
  def change                                                                    
    create_table :users do |t|                                                  
      t.string :name                                                            
      t.string :email                                                                
      t.timestamps                                                                  
    end                                                                         
  end                                                                           
end
class CreateUserRestaurants < ActiveRecord::Migration                       
  def change                                                                    
    create_table :user_restaurants do |t|                                  
      t.integer :user_id                                                        
      t.string :name                                                                  
      t.boolean :like                                                                 
      t.timestamps                                                              
    end                                                                         
  end                                                                           
end    

The users table is self-explanatory. user_restaurants, on the other hand, is a table of restaurants a user likes (I guess that’s self-explanatory as well.)

Well need a couple of users, so let’s add them here.

class AddUsers < ActiveRecord::Migration                                        
  def up                                                                        
    User.create(:name => "John Galt", :email => "john@galtsgulch.com")          
    User.create(:name => "Howard Roark", :email => "howard@architect.com")      
  end                                                               
end

And our users will want to “like” or “dislike” restaurants, so we’ll need to create the liked restaurant records. I’ll name the restaurants “Restaurant n” to save myself from having to come up with 100,000 restaurant names. We’ll split those restaurants between the two users and define 20% of them to be restaurants the users did not “like”.

class AddRestaurants < ActiveRecord::Migration                                  
  def up                                                                        
    users = User.all                                                            
                                                                                
    100000.times do |i|
      UserRestaurant.create(:user_id => i % 2 == 0 ? users.first.id : users.second.id, :name => "Restaurant #{i}", :like => i % 5 == 0 ? false : true)                
    end
  end
end 

Model Associations

The last thing we have to do is associate the users table to the user_restaurants table.

class User < ActiveRecord::Base
  has_many :user_restaurants
end

class UserRestaurant < ActiveRecord::Base
  belongs_to :user
end

Explain Round 1

Okay, we’ve created our tables and populated them with data, and we’ve got our models and associations. Let’s run a query to find “John Galt’s” favorite restaurants and see what’s going on.

User.where(:email => "john@galtsgulch.com").
  joins(:user_restaurants).where("user_restaurants.like = 1")

When I run this from the Rails Console, explain kicks in automatically and returns the table below (this is output from MySQL, PostgreSQL looks and is worded differently). “Active Record monitors queries and if they take more than [config.active_record.auto_explain_threshold_in_seconds] their query plan will be logged using warn.” (What’s new in Edge Rails: EXPLAIN)

Note: To manually execute explain on a query, just append .explain to it.

+----+-------------+------------------+------+---------------+------+---------+------+--------+--------------------------------+
| id | select_type | table            | type | possible_keys | key  | key_len | ref  | rows   | Extra                          |
+----+-------------+------------------+------+---------------+------+---------+------+--------+--------------------------------+
|  1 | SIMPLE      | users            | ALL  | PRIMARY       | NULL | NULL    | NULL |      2 | Using where                    |
|  1 | SIMPLE      | user_restaurants | ALL  | NULL          | NULL | NULL    | NULL | 100041 | Using where; Using join buffer |
+----+-------------+------------------+------+---------------+------+---------+------+--------+--------------------------------+

The columns you’re going to be most interested in are “key” and “rows”. The “key” columns lists the indexes, if any, the database uses to find data. “The rows column indicates the number of rows MySQL believes it must examine to execute the query.” 7.8.2. EXPLAIN Output Format

In the output above, we can see that the query would use no indexes, and would have to search through 100,041 rows (Hey, that’s more rows than are in the database). “For InnoDB tables, this number is an estimate, and may not always be exact.” 7.8.2. EXPLAIN Output Format

Database Refactoring

Let’s add some indexes to a couple of the key table columns and see if we can’t reduce the pain.

class AddTableIndexes < ActiveRecord::Migration
  def up
    add_index :users, :email
    add_index :user_restaurants, :user_id
    add_index :user_restaurants, :like
  end
end

Now, when we run our query it still issues an explain (because it’s a really stupid example), but we now see how adding the indexes improved performance.

+----+-------------+------------------+------+------------------------------------------------------------------+-----------------------------------+---------+---------------------+-------+-------------+
| id | select_type | table            | type | possible_keys                                                    | key                               | key_len | ref                 | rows  | Extra       |
+----+-------------+------------------+------+------------------------------------------------------------------+-----------------------------------+---------+---------------------+-------+-------------+
|  1 | SIMPLE      | users            | ref  | PRIMARY,index_users_on_email                                     | index_users_on_email              | 768     | const               |     1 | Using where |
|  1 | SIMPLE      | user_restaurants | ref  | index_user_restaurants_on_user_id,index_user_restaurants_on_like | index_user_restaurants_on_user_id | 5       | ar_explain.users.id | 25010 | Using where |
+----+-------------+------------------+------+------------------------------------------------------------------+-----------------------------------+---------+---------------------+-------+-------------+

Improvements

Looking at the new explain output, we see two things: 1) the query is now using the indexes we created; and 2) the number of potential rows that have to be searched through has been quartered.

It may not seem like a big deal that we went from two to one for the search by email, but imagine if you had 10,000 users, and by using the index here it was still able to go directly to that one record. And if the data we were searching for was something other than a boolean, as it is with the “like” column, we would be able to further reduce the number of potential records through which were searched.

Of course, if we didn’t have our handy dandy explain tool we might not even have realized it was a database problem to begin with.

As I alluded to in the beginning, there is nothing new about explain. It’s an excellent tool which provides insight into what course of action the database will take to find the data you are searching for. I’ve been using explain for some time directly from the database command line; it’s nice to finally have it accessible from the Rails Console.

I’ve purposely ignored the various implementations and configurations of explain in order to focus more on why it’s useful. If you are interested in configuration and implementation, check out What’s new in Edge Rails: EXPLAIN.

##Further Reader * What’s new in Edge Rails: EXPLAIN * MySQL: EXPLAIN Output Format * MySQL: Optimizing Queries with EXPLAIN * SQLite: EXPLAIN Query Plan * PostgreSQL: Using EXPLAIN

Read More

About me


I live in the greater Kansas City area with my beautiful wife, our two great kids, and our dog. I've been programming using Open Source technologies since '97 and I'm currently an independent software developer specializing in Ruby on Rails and iOS. I am for hire.