Skip to content

Why Use flex_columns?

Andrew Geweke edited this page Dec 11, 2013 · 2 revisions

flex_columns gives you some of the significant benefits of a schemaless or lightweight-schema database (like MongoDB or CouchDB) while living inside your ordinary relational DB (like MySQL or PostgreSQL). It's basically a very nice interface on top of a column that stores JSON data, allowing you to declare fields, use Rails validations, write your own custom methods, and so on.

For example: you're working on a real production system with millions of users, in a nice MySQL schema. Invariably, you end up needing to store a set of fairly random-seeming per-user attributes — what language they want to see the site in, whether or not they've agreed to the Terms of Service, whether they've dismissed your "get the mobile app!" banner (n.b.: please don't have one of these awful banners!). None of these actually need to be queried across users (you just need to know if it's true for the current user or not), but they need to be stored somewhere. You could add several more columns to your users table (gross, and your ops person will kill you for that). You could create a brand-new one-to-one table just to store them (a little less ops anger, a little more grossness). You could spin up MongoDB just to handle these attributes (your ops person will tell you you're out of your mind).

Or...you could just create a single CLOB column (or a separate one-to-one table with a CLOB column), and use flex_columns to store the data there. Problem solved, and now adding new attributes takes you about ten seconds and zero downtime. If you want to break the data out into another table or a non-RDBMS store later, you can, but, in the meantime, you're not paying the heavy price of those options.

Features flex_columns gives you above just using basic ActiveRecord serialization to store JSON in a CLOB column (a.k.a. there's a reason I wrote this, after all):

  • Define field names in code trivially -- misspellings will give you a nice error, rather than store data under an incorrect key silently;
  • Automagically delegate column contents across classes -- a UserDetails table with a flex column can magically make that flex column's attributes show up transparently on your User objects, which is really nice;
  • Use the entire Rails validation API (ActiveRecord::Validations) to validate your data in any way you see fit, along with a nice "RDBMS-style" shorthand;
  • Data can be transparently compressed with GZip if you want, to make your storage requirements (and, critically, in-memory buffer-pool requirements) considerably smaller;
  • Full support for bulk operations means that you can manipulate column contents without instantiating ActiveRecord models if you want — use connection.select_all(...) and friends for rapid processing of millions of rows.

Also see: [what flex_columns is NOT good for]((wiki/What-flex_columns-Is-Not-Good-For).

Clone this wiki locally