Pros/cons Of Storing Serialized Hash Vs. Key/value Database Object In Activerecord?

If I have several objects that each have basically a Profile, what I'm using to store random attributes, what are the pros and cons of: Storing a serialized hash in a column for a

Solution 1:

I had the same problem, but finally made the decision.

Hash serialization option makes maintenance problem. It is hard to query, extend or refactor such data - any subtle change needs migration which means reading each record deserializing and serializing back, and depending on refactoring serialization exception may happen. I tried both binary serialization and JSON - the second is easier to extract and fix but still too much hassle.

Separate settings table is what I'm trying to use now - much easier to maintain. I plan to use Preferences gem for that which mostly does all abstraction for easy use. I'm not sure if it works with Rails 3 yet - it is small so I can extend it if needed.

Update Nov 2013

Recently released Rails 4 supports great new features of PostgreSQL 9.1+ such as hstore or json column types for your dynamic data sets. Here is an article covering hstore usage in Rails 4. Both types support indexing and advanced querying capabilities (Json with Pg 9.3). Hstore is also available to Rails 3 users with activerecord-postgres-hstore gem.

I am in the process of migrating some of non critical preference tables in my project to hstores. In migrations I just update table definitions and execute one SQL query per table to move the data.

Solution 2:

I would recomend just creating a model call Attribute and have each of your objects that need many of them has_many. Then you don't have to mess around with serialization or anything brittle like that. If you use the :join syntax you don't have any real performance issues with this.

Serializing data into your RDBMS is almost always unwise. It's more than about queries, it's about the ability to describe and migrate your data (and serialization shatters that ability).

classBuilding < ActiveRecord::Base
  has_many :attributesendclassAttribute < ActiveRecord::Base
   belongs_to :buildingend

create_table :attributes, :force => truedo|t|
  t.integer :building_id
  t.string :att_name
  t.string :data
  t.timestamp
end

Solution 3:

I was facing the same dilemma you described and ended up going with the key/value table implementation because of the potential maintenance advantages that others mentioned. It's just easier to think through how I could select and update information in separate rows of the database in a future migration as opposed to a single serialized Hash.

Another catch I've personally experienced when using a serialized Hash is that you have to be careful that the serialized data you're storing isn't larger than what the DB text field can hold. You can easily end up with missing or corrupted data if you aren't careful. For example, using the SerializedProfile class & table you described, you could cause this behavior:

profile = SerializedProfile.create(:settings=>{})
100.times{ |i| profile.settings[i] = "A value" }
profile.save!
profile.reload
profile.settings.class#=> Hash
profile.settings.size #=> 1005000.times{ |i| profile.settings[i] = "A value" }
profile.save!
profile.reload
profile.settings.class#=> String
profile.settings.size #=> 65535

All that code to say, be aware of your DB limits or your serialized data will be clipped the next time it's retrieved and ActiveRecord won't be able to re-serialize it.

For those of you that do want to use a Serialized Hash, go for it! I think it has potential to work well in some cases. I stumbled across the activerecord-attribute-fakers plugin which seems like a good fit.