Is saving disk space a valid reason to forgo migrating to a standard text format (e.g. JSON)?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.