5

A while ago I asked a question about custom text data formats, instead of using existing tools such as XML, JSON, YAML, etc. Now, in favor of converting our custom format to a relational database and some segments of JSON (in a JSON field), I'm running into the problem of 'data bloat'. By this I mean that there will be a lot of increase in duplication for the JSON (of which there is quite a bit, because much of the system needs to be flexible in different environments). For example, the part of the custom text format:

Data data data [#tag, #tag, ...]: some more data

Field: [id] description of data; [id] description

could be converted to:

{
    "data": "...",
    tags: ["tag", "tag", ...],
    "more_data": "some more data",
    "fields": [
        {
            "id": 123
            "description": "description of data"
        },
        {
            "id": 456
            "description": "description"
        }
    ]
}

A lot of these JSON property names are now extra bloat, e.g. more_data, id, description, which are duplicated across millions of JSON entries; we'll more than quadruple our data storage requirements. However, that only ends up being about 100 MB over our previous 25 MB, for a lot more flexibility and sanity. Granted, this is a mobile app, so 75 MB could shock some users once we transition - "why does this app now take up 4x as much space with no additional features?". The custom format keeps things nice and compact, but obviously parsers have to be maintained, and the data can't be queried efficiently on anything other than a few primary fields (which are all manually indexed... by the way).

Edit: To clarify some comments and an answer: the data that I have is highly relational, except for some 'tag'-like information that varies per database row; that is to say, 90% of our custom format can be converted to a relational structure, but the other 10% is the 'tag', which is unstructured and can vary per record. These 'tags' carry semantic information that is relevant for users, but would never be queried on. And because it is unstructured, JSON seems like the best fit. I should also note that the tags can be (theoretically) infinitely varying in their structure, though there are still commonalities across tags (for instance id and description are usually common to all). It would not, however, be feasible to have an explicitly structured XXXTag join table for each variation of the JSON; I initially thought it would be a good idea, to alleviate the issue in the question that I'm asking right now, but the number of join tables is theoretically infinite, which makes me think JSON is appropriate for the problem. The JSON would only be a single column in a relational table, which is a small portion of the data as a whole. I'm sorry for being so vague with this, but I can't make my question specific enough to identify the actual project that I'm working on.

When does it make sense to bloat storage requirements to make coding and maintenance easier?

  • 1
    Could you say what else you have tried? There are more compact options like bson or protobufs, more human readable formats like ini or toml, and for mobile especially a SQLite DB would be my first choice. Do these files have to be human editable? Is it possible to compress them and decompress at runtime? – walpen Dec 20 '16 at 5:27
  • 1
    Loved the question. I am going to make it my next design interview question. – Ajeet Ganga Dec 20 '16 at 5:48
  • 1
    Consider GZIP. You probably already have the libs to do it and if you have a lot of repetitive strings, you should get pretty decent compression ratios. – JimmyJames Dec 20 '16 at 17:13
  • 1
    Your duplications of "description" would't matter if you would zip your file. You can easily save a zip on the garrdive and then unzip at runtime inside your memory – BlueWizard Dec 21 '16 at 4:56
  • 1
    Have you considered splitting the data across 2 databases, say sql and MongoDB where you can store the json data in Mongo with a key to the sql stuff. MongoDB is designed to store json objects. – adeady Dec 21 '16 at 22:13
4

You shouldn't store JSON text in a relational database in the first place, especially not if you are concerned about storage space. Store the data in regular tables, and then construct the JSON when you need it for communication. This will be much more efficient than storing either JSON or your custom data format.

There are some use cases where it could be an appropriate solution to store data in a structured text format (xml, json, csv, whatever.) in a database column. But if you are in a place where you worry about the storage requirements for various text-based formats, then you should just bite the bullet and save the data in relational format.


Regarding your edit: There is no such thing as unstructured data. If your data can be expressed in JSON in a meaningful way, then it has a structure. And if it has a structure this structure can be expressed in other formats including as relational data. JSON is just a particular way to serialize data as text.

|improve this answer|||||
  • I added clarification to my question. I'm not storing everything as JSON - merely a subset of the converted custom format. This JSON would be stored in one column of the database, and would represent only a small portion of the data as a whole. – Chris Cirefice Dec 20 '16 at 14:25
  • @ChrisCirefice: Still, if this single JSON-column leads to storage-space issue, you should store the data in relational format instead. It will be much more efficient in storage space and performance, and it will be simpler to query and update. – JacquesB Dec 20 '16 at 15:16
  • 2
    "You shouldn't store JSON text in a relational database in the first place" this is incorrect. Many top relational databases even have a JSON column type. Also, it's actually the only correct thing to do in many cases. – Sklivvz Dec 20 '16 at 15:59
  • @Sklivvz: Storing data in JSON format have its uses, e.g. if you need to store some data which is completely opaque from the perspective of the backend. But this is clearly not the case here. – JacquesB Dec 20 '16 at 16:10
  • Regarging your edit response to my edit: when I say "unstructured", I really mean that it can be represented in a structured format because it has properties and values. However, it is not structured in the sense that there is not any explicit 'rhyme or reason' to what data is in what JSON object. In one database row, we could have 4 fields. In the next database row, there could be 13, with several layers of embedded objects for different JSON keys. The point is, it would be incredibly difficult, and really unhelpful to try to model these differences in actual database tables... – Chris Cirefice Dec 20 '16 at 17:01
3

My personal fav principle is 'Premature optimization is the root of messy problems.'

At the same time, your are storing your objects in json, without any benefits of flexibility, it will cut through architecture anti-pattern of 'resume-buildup before job'.

In the pro-con analysis, I will consider following points:

Pros of storing in Json

  • [a] Faster development (optional)
  • [b] Faster feature delivery to customers (optional)
  • [c] Easier debugging (optional)

Cons of storing in Json

  • [d] Increased memory footprint, which might deter some customers who do not have sufficient space (guaranteed, but it will be mostly fraction of the customers.)
  • [e] Learn/adopt to new technology

So now you have an easier way to determine:

  1. If you can not monetize on a,b,c: it is straight fwd decision to NOT use Json.
  2. If possible, run some analytics about what percentage of customers are likely to not use an application because of memory footprint. Bonus if you find what customers will be very annoyed because of the higher footprint. Let's quantify it as N%.
  3. If you are running your app on toaster/IoT and the majority of the customers will leave if u increase footprint, then you know NOT to use Json.
  4. Now we are in terrain where N% is small enough. Now have a debate with product (or in your own mind :) ), is it worth delivering features faster or being able to debug issues faster ?

By end of this journey you should have your answer.

|improve this answer|||||
  • [a], [b], and [c] are our biggest points right now in this design decision. The custom text format is causing massive headaches in maintaining parsers, complex build processes, a binary (flatfile) database that can't be opened with standard tools, and debugging is extremely cumbersome. With a relational database and some JSON columns, we'll probably increase our storage by 25-75 MB depending on compression. Our competitors are using even more than that. It would just be a large jump for our user base of 400% storage space increase on mobile devices. Cons here are negligible, compared to pros! – Chris Cirefice Dec 20 '16 at 5:42
  • 1
    1. How critical is ur app to ur customers ? 2. what is the average/P90/P99 size of ur customer phone capacity ? 3. Is ur app a start-up app or Govt-app/monopoly-app ? – Ajeet Ganga Dec 20 '16 at 5:47
  • It's a non-critical startup app that runs on smartphones with on average 8 GB of hard drive space. The increase in data storage is negligible for what we could provide our customers if we do the migration. We'll be able to develop better features, faster, which of course they would appreciate. I was curious as to if there was a threshold to "how much increase in footprint is too much" - and your answer identifies some key aspects to examine (customer base, particularly). – Chris Cirefice Dec 20 '16 at 5:55
1

You should however move to any kind of standard just to be able to access your data also from other clients than your current main application! Think about a rewrite, a second app doing analytics using the data or just someone wants to access the database directly to investigate on a bug.

If you only store small parts of your data which have a relationship to other parts of the relational model, it is valid to store this documents somehow in the same database. I would assume you use a large VARCHAR, TEXT or BLOB/CLOB column for that (for most databases VARCHAR is faster than any kind of LOB type but typically limited (to a length between 2000 and 65000 characters depending on the vendor) and also LOB types normally do not support any query functionality).

Here are some options:

plain JSON: readable, easy to migrate, direct mapping to existing data structure, multi language support, large amount of data

binary JSON (BSON): like plain JSON, but smaller

Protobuf (Google): smaller than BSON but less language support, need own model with mapping due to proto specification (schema)

MessagePack: smaller than BSON (like Protobuf) but faster and without schema

compressed JSON (using zip): small and easy to migrate but bad performance

If you are using any LOB column performance is out of scope anyway and you can go for compressed JSON. If you need querying (WHERE json LIKE '...') you are bound to a plain text format but should not pretty print. If you need seamless integration but small size, go for MessagePack.

|improve this answer|||||
  • As I note in my answer, compression can actually improve performance overall by significantly reducing disk read/write times which tend to be the 'long pole' in a lot of designs. – JimmyJames Dec 20 '16 at 17:00
  • @JimmyJames in principle yes, but compared to formats which are compact out of the box a 2nd compress/decompress step is slower. If only comparing noisy JSON to compressed you are right – Arne Burmeister Dec 20 '16 at 17:12
  • It's not just in principle. Databases often compress data by default in order to improve performance. Disk IO can be 1000 times slower that memory access. YMMV. – JimmyJames Dec 20 '16 at 17:17
0

I think there are two (highly related) parts to this question:

  1. Should I store 'large objects' in the database
  2. What format should I store data in

The first part is primary because if the answer is no, the the second part is irrelevant. If you had asked me a few years ago, I'd say "sure, why not?" But my experiences since then have been that storing large object data in a database is non-optimal at best and can be really problematic. The main reason is that the conceptual model behind databases doesn't really include this kind of data. If you want to put stuff in an RDBMS, then ideally, you put it in as relational data. That's the only way you get the real value from the RDMBS. Yes there are fancy datatypes that understand things like XML and JSON but again, these are outside of the conceptual relational model and are non-standard.

In the real world, we do this because it's easy and often the database is the only thing that's there that we can easily read and write data from and we can find it by using the relational key. What I can't get past though is that when we do this, we are using it like a glorified hashtable. Your database is probably the most burdened component in your architecture. It's questionable to load it up with work that it isn't specialized for. Here's a thought: add a database or storage system to your architecture that is designed specifically for holding byte streams. Store UUIDs for those streams in your DB. Maybe it's overkill for your needs but worth considering.

If you decide to put your JSON data in the database, you should probably GZIP it. This will save space and create less stress on the RDBMS and (somewhat unituitively) can improve performance. The reason it can be faster is that by compressing the data, you have less disk IO. The savings on IO time can easily outweigh the time required to compress and decompress. You can even just stream the compressed data directly to the client since this is a almost universally supported compression algorithm.

You could look at other structures but you probably won't get much tighter than you will be compressing it and it adds a lot of complexity. If you are going to go through all that, you might as well normalize it.

|improve this answer|||||
0

If you have a large array of dictionaries with identical structure, you can just turn this into a dictionary of arrays. Take your example

[
    {
        "id": 123
        "description": "description of data"
    },
    {
        "id": 456
        "description": "description"
    }
]

and turn this into

{
    "ids": [123,456],
    "descriptions": ["description of data","description"]
}

Another dictionary in the original turns into just values, with the only overhead being a comma as separator. If there are missing values, you can of course add null values.

Now accessing your data through JSON (parsed into dictionaries, arrays etc. ) is obviously nonsense - typically you have say an array of dictionaries, each dictionary known to represent an instance of some class, and you have a constructor turning a dictionary into an instance (or failing). You'd replace that with a class method that turns a dictionary of arrays into an array of instances which is usually what you want anyway.

|improve this answer|||||
  • 1
    Then I would have to rely on array indexing hacks to present the data once I've loaded it - and if some items were missing descriptions, for example (which they can), the array indexing would break due to the arrays not being of equal length. This seems like a really hacky way to 'compress' things; no offense, I just don't think this is a good idea. – Chris Cirefice Dec 20 '16 at 23:08
  • You are basically saying if you take my idea and do stupid things then it doesn't work. Fine. Don't do stupid things then. – gnasher729 Dec 21 '16 at 9:04
  • @gnasher729 This isn't always a bad thing to do. But, it's basically a reinvention of the relational model. If the records are all guaranteed to have the same fields, and if the schema is reasonably stable, this is exactly what a relational database is for ... – svidgen Dec 21 '16 at 19:27
  • @svidgen And the fact that this one JOSN column has a large number of variations, it doesn't fit into a relational model at all :) if I could make this JSON into a set of relational tables, I would for obvious reasons, but I can't. Our custom text format is some form of 'compression' that works, I guess, but I have to now maintain 4 separate parsers in 3 languages to support it, which is why I'm looking for alternatives. – Chris Cirefice Dec 21 '16 at 20:05
  • @ChrisCirefice Umm ... but you said, "the data that I have is highly relational, except for some 'tag'-like information that varies per database row" ... and even "tags" in and of themselves are relations... I'm confused. Which is it? Do the fields change? Or don't they? – svidgen Dec 21 '16 at 20:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.