Including Columns

There are many advantages to segregating the flex columns associated with a particular model into an entirely separate table. If the attributes in the flex column are not used in most bulk operations (which we recommend), this will prevent the database from having to load the flex-column data at all, and keep the original table far smaller.

For example, consider this migration:

class CreateUsers < ActiveRecord::Migration
  def up
    create_table :users do |t|
      t.string :first_name, :limit => 64
      t.string :last_name, :limit => 64
      t.string :username, :limit => 64

      t.float :current_latitude, :precision => 15, :scale => 12
      t.float :current_longitude, :precision => 15, :scale => 12

      t.date :date_of_birth
    end

    create_table :user_details, :id => :user_id do |t|
      t.text :details
    end
  end
end

Assuming a four-byte primary key, with an average first-name length of 5 characters, last-name length of 7 characters, and username length of 11 characters, this results in a row width of 49 bytes. A hundred million users would take 4.56 GiB of memory to fully cache.

If we store the user's hashed password (a surprisingly good choice for a flex column), locale, whether or not they've agreed to our terms of service, and custom background color in the flex column, we can easily end up with a JSON string that's 140 characters long. If we stored this directly in the users table, we would suddenly need 17.6 GiB of memory to fully cache the table — an increase of 286%! (Even if we stored this data directly in columns in the users table, it would still be an increase of 152% — the key is to get this data out of the users table entirely.)