Designing Schemaless, Uber Engineering’s Scalable Datastore Using MySQL (2016)

It has some great properties. It lets you mix data with a strict schema and data without a strict schema, getting some of the benefits of both worlds.

The JSON datatype avoids many of the annoying legacy considerations that other SQL column types have. You don't have to specify a length--so you won't make a VARCHAR(255), then get burned when one day a value has more than 255 characters. You don't have specify a character encoding--JSON is always utf8mb4, the right one. (MySQL's 'utf8' encoding, perversely, supports only a subset of utf8 and will break if you try to write an emoji.)

Here's a table that illustrates some of the power:

  create table unitType (
    id bigint not null auto_increment,
    buildingId bigint not null,
  
    info json,
    name varchar(255) as (info->>'$.name') not null,
  
    primary key(id),
    foreign key (buildingId) references building(id) on delete cascade,
    unique key(buildingId, name)
  );

- It's very easy to add new fields. If, tomorrow, we decide that each unit type needs a `minSqft` and `maxSqft`, I can add them with no database migration.

- We still get most of the benefits of a schema. The database makes it impossible for a unitType to exist that does not belong to a building. The database also makes it impossible for a single building to have two unitTypes with the same name. (With a truly schemaless DB like Mongo, the complexity of preventing or dealing with those kinds of invalid data end up in the application code.)

- It makes it easy to use SQL directly, with no ORM. SQL is a powerful language; ORMs are often a leaky abstraction and a source of unessential complexity. With JSON columns for extensibility, you end up with way fewer migrations and way less need for auto-generated SQL.

- Computed columns (like name above) are really powerful.

Most of the above is possible in Postgres as well. Postgres does not have computed columns, as far as I can tell.

--

This is just to say: 99% of people on Hacker News are closer to where we are (rapid prototype phase) than where Uber is (Web ScaleTM). If that's you, consider just using JSON columns to maximize your development velocity! You can always do something fancier (like Schemaless) later on.

Naming products is demonstrably a hard problem.

I'm pretty sure they've seen a few iterations on their data stores. I remember attending a meetup at Urban Airship in 2012 or so where Uber engineers gave a presentation about a data store migration (I think from Mongo to MySQL)

It does, but it's still length limited--more precisely, there are several different TEXT data types, with different length limits. This bit me recently on a project, where I was sending strings over the length limit to a TEXT field and wondered why they got truncated until I read the spec more carefully.