PostgreSQL Alter Table – ALTER TABLE Online by Keeping Data as BLOBs

alter-tabledatabase-designdenormalizationeavpostgresql

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.

Best Answer

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.

Related Question