Designing Schemaless, Uber Engineering’s Scalable Datastore Using MySQL (2016)
eng.uber.com
eng.uber.com
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.
Does MySQL not have a TEXT data type, or is it not well-supported or otherwise disadvantaged?
> 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.
TBF, column-adding being a pain is really a MySQL-specific problem.
> 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.
ORMs being a "leaky abstraction" is a good thing; good ORMs are not too far removed from SQL precisely because they're leaky.
Pushing schema management to the app layer (as opposed to migrations) is also a source for "unnecessary complexity".
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.
[1] https://dev.mysql.com/doc/refman/8.0/en/storage-requirements...
Only if they're optional, though, right?
(Though you can always add them as optional, fill them in with some combination of scripting and human entry, then make them required once that's done.)
For example, with something like SpringData JPA (and probably other tools in other languages), I can create methods like findUnitTypesWithMaxSqftGreaterThan(int size)
The framework is smart enough to deduce the fields and create the necessary SQL data and make the query.
Similarly, a lot of tools can do things like generate data objects based on columns with correct types and constraints.
I'm hesitant to start using JSON too much, as then I'm back to the same problems we've got with other NoSQL dbs like Mongo - basically our code is a jumble of marshalling json to objects where one never knows if fields are null or empty or any random type of data.
select * from unitType where info->>'$.maxSqft' > ?
alter table unitType add column maxSqft float as info->>'$.maxSqft'
alter table unitType add key(maxSqft)
...
select * from unitType where maxSqft > ?Almost every programming platform makes the same mistake. Characters outside of the basic multilingual plane (i.e., characters taking more than two bytes to store) will break certain string functions.
It's due to using (fixed length) ucs2 for in-memory storage, instead of variable-length utf8. Imagine, for example, if you tried to replace a one-byte ascii character at a given index with a 4 byte cuneiform character - you would need to reallocate, copy+transpose the entire string buffer. Every string function would need to take variable byte length into account and would need to traverse the entire string with a state machine to do any operation, plus maybe a full reallocate+strcpy.
So instead we can make every character a fixed byte length. We could make every character 4 bytes in memory, but since strings are 99% ascii that seems wasteful, so instead let's just make every character a fixed-length 2 bytes (ucs2), that should cover 99.9% of it.
Java and Javascript use 2-byte strings in memory, yet both can represent an emoji just fine. (They do so via a hack that uses multiple indices in a String to represent a single character. If you want to go down the rabbit hole of how much this sucks, check out the MDN pages for charCodeAt vs codePointAt ...)
Modern languages like Go just represent strings as UTF8 in memory. This has lots of advantages:
- Most strings are mostly 1-byte-per-character, saving memory
- Full Unicode support.
- Faster IO, since you don't have to re-encode UTF8 strings to/from the network or disk.
MySQL did something weird and enormously stupid with its `utf8` encoding. It supports up-to-3-byte-per-character UTF8. This is idiosyncratic, nobody else does this. It supports some emoji and some Chinese characters but not others.
Fortunately, you don't have to worry about it or learn about it. When using MySQL, just always use utf8mb4 ; never use utf8.
> We ended up building a key-value store which allows you to save any JSON data without strict schema validation, in a schemaless fashion (hence the name). It has append-only sharded MySQL with buffered writes to support failing MySQL masters and a publish-subscribe feature for data change notification which we call triggers. Lastly, Schemaless supports global indexes over the data.
That comment was edited with more info...
I’m all for avoiding premature optimization but this was taken to the extreme.
PostgreSQL is capable of all of this out of the box. Wonder why a custom tool was built instead?
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)
They have over-hired engineers is the obvious answer.
Naming products is demonstrably a hard problem.
This also should be titled "(2016)"