Could you please explain me how extreme de-normalization (store the value as a serialized blob will help to ALTER TABLE online? source

Additionally, it's crucial to be able to change the schema: either to use a database where ALTER TABLE can be done online, work around the database's limitations and build a way to non-destructively perform an ALTER TABLE, or use extreme de-normalization (store the value as a serialized blob along with a version that can be used to infer the schema in use at that time). This is probably important for any kind of a real-time database driven application, but crucial for social networks where new features (e.g., new profile fields) are added all the time.

  • Are you talking about Entity-Attribute-Value schema?
    – Rick James
    Mar 22, 2017 at 22:20

1 Answer 1


That's not what this is saying. Few systems can change a schema without taking the system offline -- even modern databases typically require exclusive table locks and table-rewriting to remove a column. This presents a problem in some workloads. Think about a contact book where you can add a field, and a delete a field. If each of those operations changed the schema of the data by issuing an ALTER TABLE, you'd have absolutely chaos. That's not how schema is supposed to work.

So you have two traditional options,

  1. Schemaless data,
  2. Entity–attribute–value model (which is frequently both schema-less and type-less, and slower.)

Schemaless Data

Storing the column as a serialized blob may sound bad, but that's only because DBAs are usually not capable of that level of engineering and there could be dragons there. It's just out of their pay grade to implement binary types. That's exactly what happens though with advanced types under the hood. Take for instance, in PostgreSQL,

These types are in fact binary blobs under the hood. The database just provides operators and stringification for them. In PostgreSQL they're TOASTABLE binary types.

Before JSONB, and before hstore, we used to achieve the same thing with Storable::nfreeze. This is what the author is calling extreme demoralization. You serialize the version of the object (metadata), and the object itself and dump the whole thing into the database.

DBA's write in a declarative language (SQL) and model and query relational data. That's entirely different than the skillset required to create something like JSONB. We're a different community from Stackoverflow because we have a different skill set. How many databases have hstore, or jsonb -- or even SQL Arrays? These are hard problems. Getting it right is a niche skill for a DBA.

  • Actually, JSON is pretty ubiquitous nowadays in relational databases (it's even part of the SQL:2016 standard). And XML has been part of relational database for a long time - which is also a way of denormalizing.
    – user1822
    Mar 22, 2017 at 21:29
  • @EvanCarroll thank you for detailed answer! I'm not a DBA, so sorry if I haven't got some points yet. Could u pls explain what is actually a difference between removing String and JSONB column through alter table in contact book? Schemaless (JSONB) data type belongs to column iteself, not to table, but u need to add/remove tables schema (not column schema). So how does JSONB helps to alter table's schema?
    – VB_
    Mar 23, 2017 at 14:23
  • @VolodymyrBakhmatiuk you never remove the JSONB column. If you want to remove an attribute and they're all stored as JSONB, you simply remove the key from the object stored on the table. You don't have to change the table's schema. Mar 23, 2017 at 16:26
  • @EvanCarroll ow, finally got it! Thank you a lot!
    – VB_
    Mar 23, 2017 at 16:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.