Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

eng.uber.com

Post reply on HN
Print view View original

21–30 of 39 posts

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

#21

Post by dllthomas »

dcposch wrote: post #14

FYI, MySQL has a fresh new JSON data type now. 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…

> If, tomorrow, we decide that each unit type needs a `minSqft` and `maxSqft`, I can add them with no database migration.

Only if they're optional, though, right?

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

#23

Post by hiram112 »

dcposch wrote: post #14

FYI, MySQL has a fresh new JSON data type now. 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…

How easy is it to use standard SQL to join, project, and query on columns within the JSON fields?

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.

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

#24

Post by nerfhammer »

dcposch wrote: post #14

FYI, MySQL has a fresh new JSON data type now. 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…

> MySQL's 'utf8' encoding, perversely, supports only a subset of utf8 and will break if you try to write an emoji.

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.

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

#25

Post by dcposch »

dllthomas wrote: post #21
dcposch wrote: post #14

FYI, MySQL has a fresh new JSON data type now. 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…

> If, tomorrow, we decide that each unit type needs a `minSqft` and `maxSqft`, I can add them with no database migration. Only if they're optional, though, right?

Yes, exactly.

(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.)

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

#26

Post by nerfhammer »

pdonis wrote: post #20
pritambaral wrote: post #17

Earlier quoted context omitted.

> 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. 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…

> Does MySQL not have a TEXT data type 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.

does the new json storage not re-use mysql's TEXT storage? It is stored directly on the data pages? But then it would be limited in length, or does it use something else entirely?

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

#27

Post by dcposch »

nerfhammer wrote: post #24
dcposch wrote: post #14

FYI, MySQL has a fresh new JSON data type now. 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…

> MySQL's 'utf8' encoding, perversely, supports only a subset of utf8 and will break if you try to write an emoji. 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, f…

Well, sort of.

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.

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

#28

Post by dcposch »

hiram112 wrote: post #23
dcposch wrote: post #14

FYI, MySQL has a fresh new JSON data type now. 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…

How easy is it to use standard SQL to join, project, and query on columns within the JSON fields? 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 gener…

Quite easy:

  select * from unitType where info->>'$.maxSqft' > ?
If you want it to be indexed for production use, that's also pretty easy:

  alter table unitType add column maxSqft float as info->>'$.maxSqft'
  alter table unitType add key(maxSqft)
  ...
  select * from unitType where maxSqft > ?

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

#30

Post by mangatmodi »

dcposch wrote: post #14

FYI, MySQL has a fresh new JSON data type now. 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…

I really had no idea about json datatype. Thanks for sharing.
Post reply on HN