4 ms·

Hm I googled and found this draft of the encoding - https://sqlite.org/draft/jsonb.html https://sqlite.org/draft/jsonb.html It feels like it would be better to

by chubot 3y ago

Hm I googled and found this draft of the encoding - https://sqlite.org/draft/jsonb.html https://sqlite.org/draft/jsonb.html It feels like it would be better to use a known binary encoding. I thought the MessagePack data model corresponded pretty much exactly to JSON ? Edit: someone else mentioned BSON - https://bsonspec.org/ https://bsonspec.org/ To be honest the wins (in this draft) don't seem that compelling The advantage of JSONB over ordinary text RFC 8259 JSON is that JSONB is both slightly smaller (by between 5% and 10% in most cases) and can be processed in less than half the number of CPU cycles. JSON has been optimized to death; it seems like you could get the 2x gain and avoid a new format with normal optimization, or perhaps compile-time options for SIMD JSON techniques --- And this seems likely to confuse: The "JSONB" name is inspired by PostgreSQL, but the on-disk format for SQLite's JSONB is not the same as PostgreSQL's. The two formats have the same name, but they have wildly different internal representations and are not in any way binary compatible. --- Any time data is serialized, SOMEBODY is going to read it. With something as popular as sqlite, that's true 10x over. So to me, this seems suboptimal on 2 fronts.

gleenn 3y ago
I thought the point was that the DB could reach into JSONB for you (potentially indexed as well) instead of having to deserialize outside. So the exact serialization isn't maybe as interesting as the direct queryability.
- fbdab103 3y ago
  Does SQLite make any promises on the internal implementation? I assumed that the only guarantee was at the SQL interface.
  - banana_giraffe 3y ago
    They do promise it'll be a stable format: > The JSONB format is not intended as an interchange format. Nevertheless, JSONB is stored in database files which are intended to be readable and writable for many decades into the future. To that end, the JSONB format is well-defined and stable. The separate SQLite JSONB format document provides details of the JSONB format for the curious reader. And indeed the functions jsonb() and json() will let you convert to and from jsonb.
lifthrasiir 3y ago
It takes a lot of complex code to optimize a JSON parser. You don't need to optimize that hard to make an equally performant parser for binary serialization formats, and given SQLite's JSONB is purely an internal optimization, it doesn't have to be a well-known interchange format (which has much more concerns than SQLite). > Any time data is serialized, SOMEBODY is going to read it. With something as popular as sqlite, that's true 10x over. And this statement is equally true for the SQLite format itself. That doesn't mean that the SQLite format should be replaced with something more standard, of course.
topspin 3y ago
> JSON has been optimized to death; it seems like you could get the 2x gain and avoid a new format with normal optimization Either I'm experiencing a reading comprehension mishap or this is self contradictory. Where is a "2x gain" supposed to come from through "normal optimization" from after something has already been optimized "to death?" > SIMD JSON techniques Which are infeasible in key SQLite use cases.
- brazzy 3y ago
  > Credit to SQLite developers for adopting an extant binary JSON format, as opposed to inventing yet another one. The comment you are replying to cites a statement saying explicitly that they are not adopting the Postgres JSONB binary format, only the name and the abstract concept. The API is not compatible with Postgres either.
  - topspin 3y ago
    I noted that and removed that bit prior to your reply. Points off for a.) inventing yet another binary JSON and/or b.) using the same name as an existing binary JSON.
    - chubot 3y ago
      Those 2 points are exactly what my comment said
- chubot 3y ago
  I just meant there are a zillion different known ways of optimizing JSON, that maybe could be applied here. But maybe they already optimized it to the degree they're willing and wanted a binary format
  - topspin 3y ago
    SQLite developers aren't in the habit of making poor technical choices. This choice certainly has precedent: Oracle, MySQL, MongoDB, Postgres and others have implemented some form of binary encoding for JSON. Perhaps they know something you don't and deserve the benefit of the doubt.
fbdab103 3y ago
SQLite says you should not attempt to access it: https://sqlite.org/draft/jsonb.html https://sqlite.org/draft/jsonb.html > JSONB is not intended as an external format to be used by applications. JSONB is designed for internal use by SQLite only. Programmers do not need to understand the JSONB format in order to use it effectively. Applications should access JSONB only through the JSON SQL functions, not by looking at individual bytes of the BLOB. > However, JSONB is intended to be portable and backwards compatible for all future versions of SQLite. In other words, you should not have to export and reimport your SQLite database files when you upgrade to a newer SQLite version. For that reason, the JSONB format needs to be well-defined. If SQLite intends to own the format forever, I can believe that their requirements are such that leaning on an existing implementation is not worth the savings to implement.
- chubot 3y ago
  Users don't read docs, and any visible implementation detail becomes set in stone: https://www.hyrumslaw.com/ https://www.hyrumslaw.com/ It looks like they're aware of that ... it's probably fine -- not ideal, but fine If users didn't care about what the blob format was, there wouldn't be JSON support in the first place! You would have started with something like JSONB
  - cryptonector 3y ago
    Also SQLite3 will have to remain backwards compatible with its JSONB forever, which means it's stable, which means there will be users who will feel comfortable using the raw JSONB from SQLite3.
jomohke 3y ago
Re-using standards is a great idea, and should remain people's default, but I don't see the benefit here. What's the advantage of re-using a format? Ecosystem? That won't help SQLite here, who don't have dependencies. Keep in mind that anyone trying to write a parser for this is also writing a parser for the entire SQLite file format (the only way to access bytes). And it's a spec simple enough to fit on one monitor. Design? BSON (and others?) seem to have different goals. SQLite's format seems to minimise conversion/parsing (eg. it has multiple TEXT types depending on how much escaping is needed; BSON has one fully-parsed UTF-8 string type). BSON is more complex: includes many types not supported by json (dates, regexs, uuids...) and has a bunch of deprecated features already. SQLite's on disk-format is something they intend to support "forever", and as with the rest of SQLite, they enjoy pragmatic simplicity.
cryptonector 3y ago
> JSON has been optimized to death; [...] Not the JSON format. > [..] it seems like you could get the 2x gain and avoid a new format with normal optimization, or perhaps compile-time options for SIMD JSON techniques SIMD isn't always appropriate, and there are still no SIMD incremental JSON parsers either. There are things that can't easily be done with SIMD and JSON.
microtherion 3y ago
CBOR should compress even better than BSON.

by chubot 3y ago

gleenn 3y ago

I thought the point was that the DB could reach into JSONB for you (potentially indexed as well) instead of having to deserialize outside. So the exact serialization isn't maybe as interesting as the direct queryability.

fbdab103 3y ago
Does SQLite make any promises on the internal implementation? I assumed that the only guarantee was at the SQL interface.
- banana_giraffe 3y ago
  They do promise it'll be a stable format: > The JSONB format is not intended as an interchange format. Nevertheless, JSONB is stored in database files which are intended to be readable and writable for many decades into the future. To that end, the JSONB format is well-defined and stable. The separate SQLite JSONB format document provides details of the JSONB format for the curious reader. And indeed the functions jsonb() and json() will let you convert to and from jsonb.

lifthrasiir 3y ago

It takes a lot of complex code to optimize a JSON parser. You don't need to optimize that hard to make an equally performant parser for binary serialization formats, and given SQLite's JSONB is purely an internal optimization, it doesn't have to be a well-known interchange format (which has much more concerns than SQLite). > Any time data is serialized, SOMEBODY is going to read it. With something as popular as sqlite, that's true 10x over. And this statement is equally true for the SQLite format itself. That doesn't mean that the SQLite format should be replaced with something more standard, of course.

topspin 3y ago

> JSON has been optimized to death; it seems like you could get the 2x gain and avoid a new format with normal optimization Either I'm experiencing a reading comprehension mishap or this is self contradictory. Where is a "2x gain" supposed to come from through "normal optimization" from after something has already been optimized "to death?" > SIMD JSON techniques Which are infeasible in key SQLite use cases.

brazzy 3y ago
> Credit to SQLite developers for adopting an extant binary JSON format, as opposed to inventing yet another one. The comment you are replying to cites a statement saying explicitly that they are not adopting the Postgres JSONB binary format, only the name and the abstract concept. The API is not compatible with Postgres either.
- topspin 3y ago
  I noted that and removed that bit prior to your reply. Points off for a.) inventing yet another binary JSON and/or b.) using the same name as an existing binary JSON.
  - chubot 3y ago
    Those 2 points are exactly what my comment said
chubot 3y ago
I just meant there are a zillion different known ways of optimizing JSON, that maybe could be applied here. But maybe they already optimized it to the degree they're willing and wanted a binary format
- topspin 3y ago
  SQLite developers aren't in the habit of making poor technical choices. This choice certainly has precedent: Oracle, MySQL, MongoDB, Postgres and others have implemented some form of binary encoding for JSON. Perhaps they know something you don't and deserve the benefit of the doubt.

fbdab103 3y ago

SQLite says you should not attempt to access it: https://sqlite.org/draft/jsonb.html https://sqlite.org/draft/jsonb.html > JSONB is not intended as an external format to be used by applications. JSONB is designed for internal use by SQLite only. Programmers do not need to understand the JSONB format in order to use it effectively. Applications should access JSONB only through the JSON SQL functions, not by looking at individual bytes of the BLOB. > However, JSONB is intended to be portable and backwards compatible for all future versions of SQLite. In other words, you should not have to export and reimport your SQLite database files when you upgrade to a newer SQLite version. For that reason, the JSONB format needs to be well-defined. If SQLite intends to own the format forever, I can believe that their requirements are such that leaning on an existing implementation is not worth the savings to implement.

chubot 3y ago
Users don't read docs, and any visible implementation detail becomes set in stone: https://www.hyrumslaw.com/ https://www.hyrumslaw.com/ It looks like they're aware of that ... it's probably fine -- not ideal, but fine If users didn't care about what the blob format was, there wouldn't be JSON support in the first place! You would have started with something like JSONB
- cryptonector 3y ago
  Also SQLite3 will have to remain backwards compatible with its JSONB forever, which means it's stable, which means there will be users who will feel comfortable using the raw JSONB from SQLite3.

jomohke 3y ago

Re-using standards is a great idea, and should remain people's default, but I don't see the benefit here. What's the advantage of re-using a format? Ecosystem? That won't help SQLite here, who don't have dependencies. Keep in mind that anyone trying to write a parser for this is also writing a parser for the entire SQLite file format (the only way to access bytes). And it's a spec simple enough to fit on one monitor. Design? BSON (and others?) seem to have different goals. SQLite's format seems to minimise conversion/parsing (eg. it has multiple TEXT types depending on how much escaping is needed; BSON has one fully-parsed UTF-8 string type). BSON is more complex: includes many types not supported by json (dates, regexs, uuids...) and has a bunch of deprecated features already. SQLite's on disk-format is something they intend to support "forever", and as with the rest of SQLite, they enjoy pragmatic simplicity.

cryptonector 3y ago

> JSON has been optimized to death; [...] Not the JSON format. > [..] it seems like you could get the 2x gain and avoid a new format with normal optimization, or perhaps compile-time options for SIMD JSON techniques SIMD isn't always appropriate, and there are still no SIMD incremental JSON parsers either. There are things that can't easily be done with SIMD and JSON.

microtherion 3y ago

CBOR should compress even better than BSON.