We have a scenario in which all the important and transactional fields of our business entities are highly structured and relational. The data size of these important fields is also very small. However, there is a raw JSON associated with each entity that is very rarely updated (only in exceptional cases). However, most of our read APIs require all the data including the raw JSON.

Considering this, we have chosen MySql as our datastore with the data-bag (raw JSON) stored as a blob with each entity. This doesn't cause any functional issues. However, the data size is increasing rapidly and the contribution of the JSON blob is around 70%.

So, we are thinking of moving the raw data into a NoSql store and using the primary key of MySql as a referential key in NoSql (to be enforced by code).

However, this somehow appears to me as an anti-pattern because it introduces distributed transactions (as we need to ensure the consistency across both the DBs). This can be avoided using Saga pattern wherein the write to NoSql goes through a message queue. But we need strong consistencies in reads so we can't rely on this. Moreover, it introduces further complexity that can cause maintenance/monitoring issues.

We can choose to move completely to a NoSql store, but our main domain entities don't really need it and we will lose the goodness of relational data-structure. We can shard MySql based on size, but this will force us to have some cross-shard queries.

Is there a common pattern to address this and is the "multiple databases per service" a pattern or an anti-pattern?

  • Why is the data in question stored in JSON format?
    – JacquesB
    Oct 21, 2019 at 11:30
  • And is the issue purely storage size? Because don't think moving the JSON to a different kind of database will help the storage size.
    – JacquesB
    Oct 21, 2019 at 11:40
  • @JacquesB The data in question is unstructured (many fields and mostly sparce). The downstream services and the UI read and understand it. Our service only passes it through. The issue is purely size.
    – iavanish
    Oct 21, 2019 at 11:51
  • IMHO there is no such thing as unstructured data (that would just be noise). The best solution depends on the nature of this data, so I think you should describe it in more detail.
    – JacquesB
    Oct 21, 2019 at 12:17
  • @JacquesB There is such a thing as data that doesn't have a predictable relational structure. This can be as simple as an unbounded string. SQL DBs win when data fields have predictable size limits. Not when they're simply null terminated. That makes locating the next field a pain. There are work arounds of course but now your using workarounds rather than a natural representation. Oct 21, 2019 at 13:11

3 Answers 3


Adding a second database isn't going to solve your problem. It will give you a whole bunch of new problems that you've identified, and likely more. What you need to do is structure your data better. Truly unstructured data is rare, most applications are a way to present data in a structured way, reflecting that structure correctly in a database can be difficult. There is likely more structure to your data than you are willing to admit, and moving as much as possible into your database will result in better performance. At a minimum breaking a large JSON blob into multiple blobs will give you some benefit, and may be a good first step to better analyze your data to find structures. Another thing to consider is using the JSON datatype withing MYSQL, this will help the database better optimize storage and performance, and could allow you to do more filtering at the database level which will ultimately lead to a more performant solution than a coded approach.

Multiple databases or distributed databases are a last resort solution. They are a huge cost in both the extra hardware, and bodies required to keep everything synced and maintained. Once you go down this route everything gets more difficult, and it takes a lot to justify that difficulty.

  • Thanks for the answer. We have already used the JSON datatype. We have also considered breaking it down into tables and did come up with the best possible normalized structure. Still, the tables are going to be sparse therefore, we didn't implement it. Moreover, there is no querying required on this data, every call needs to return either the entire JSON or nothing. From optimization perspective, we have ensured that there is absolutely no duplication in this JSON's data. So, I think there is no more optimization possible if we continue storing it in MySql.
    – iavanish
    Oct 21, 2019 at 13:40

You need to implement horizontal scaling for your whole database, not just the JSON parts. Extracting the JSON parts will only buy you 70% more space once, so you will have the same problem again soon enough with the relational data.

Since the JSON parts seem to be basically "black box" to your application you don't get a lot of benefit by storing it in a relational database - but you won't get any benefit from using a different database system either. And the increased complexity and maintenance cost of having two database systems is vastly higher.

Depending on your database engine, you can probably combine vertical and horizontal partitioning (sharding), so you shard the column with the JSON blobs separately from the relational data.

As for "patterns" and "anti-patterns" - that is the wrong way to think of it. Surely there are scenarios where having both a relational database and a NoSql system in the same service might make sense. But in your case it doesn't bring you any benefit and doesn't solve the problem you have.

  • The reason for considering NoSql DBs like HBase or Cassandra is that they are inherently distributed. With MySql, we need to handle the horizontal scaling. But I understand your approach. Sharding the MySql is one of the approaches that we are considering. Just that a lot of our db-access will result into cross-shard queries. As already pointed out, the majority of our queries that are transactional don't need to touch the JSON part. Thus, if we distribute all our data to accommodate the JSON, our majority queries will slow down as they'll have to access different shards.
    – iavanish
    Oct 21, 2019 at 14:07
  • 1
    @iavanish: But it doesn't help you to make only 70% of your data distributed. That is still not scalable. You might just as well just buy a 70% bigger harddisk, it will help just as much.
    – JacquesB
    Oct 21, 2019 at 14:21
  • @Iavanish you keep bringing up sharding as if it's about segregating kinds of data. I don't believe that's true. If you believe kinds of data need to be segregated consider micro services. If you can put a full blown microservice boundry between your kinds of data maybe two different data stores is viable. Otherwise I think JacquesBs concerns are valid. Oct 21, 2019 at 14:38
  • @candied_orange: The JSON can be physically separated in the database via vertical partitioning. No need to bring microservices into play.
    – JacquesB
    Oct 21, 2019 at 15:07
  • @JacquesB are you saying vertical partitioning is a form of shading? Oct 21, 2019 at 19:23

I would tend to agree in general with the idea that having two DBs is more trouble than it's worth but based on your situation, it's worth considering. One option you could also consider is gzipping the JSON. Depending on the size of the documents, this could save a significant amount of space. A nice thing about this is that you can set the MIME-type on the response and return the raw gzipped data from a service.

But assuming there's no easy way to extend your MySQL capacity, you need to either move the whole thing to a horizontal scaling DB or just move the LOBs. Since you are using relational features for the core data, this complicates the former. There are probably ways to do but it's going to create a lot of challenges and work that needs to be done.

The main stumbling block to using a document store seems to be that you are worried about is consistency. This is a challenge if you truly need consistency between both DBs but it could be workable if you can weaken that requirement slightly. The way you might be able to do this is to require that your JSON document be written and confirmed before you commit the relational data. Additionally, it would be important to never modify the JSON records. In the case you were able to write the JSON data but the relational part failed, you would simply have an orphaned record in the document store. There's no clear reason why this is a (big) problem as I would expect that you retrievals from the NoSQL store would be based on the data returned from the relation queries. You could implement some sort of cleanup process if needed.

JacquesB brings up a good point on the space usage. If everything holds as you have explained, you would simply delay your problem until later. In order to account for that, you need to work out what your long term storage needs are. If you must keep everything in one DB forever, you need a horizontally scaling solution for everything. If you can divide your data up in some meaningful way (e.g. by date) you might have more options.

  • Gzipped JSON in a database field? I don't think this is what Edgar F. Codd intended.
    – JacquesB
    Oct 21, 2019 at 16:44
  • @JacquesB I guess that's possible depending on the size of the documents but it wasn't my thought. It's not really relevant to the point either way. If you can compress the documents 25% of their size (which I think is pretty typical) that turns the documents from 70% of the DB to 37% and more than halves the space used. Like you pointed out, it's just delaying the inevitable if it continues to grow unchecked, though.
    – JimmyJames
    Oct 21, 2019 at 17:05
  • @JacquesB I'm also doubtful that Dr. Codd intended to have explicit column types for holding hierarchical data structures. I kind of doubt LOBs were part of the plan either.
    – JimmyJames
    Oct 21, 2019 at 18:00
  • Some databases supported compressed storage transparently, which may be a fine space/time tradeoff. But gzipping data in individual fields will mean you lose all the advantages of having a database. JSON is rather inefficient in the first place, so serializing data to JSON and then gzipping to save space is IMHO bizarre.
    – JacquesB
    Oct 22, 2019 at 9:30
  • @JacquesB I'm not sure I understand your point. I don't see anything that suggests these JSON files are used as part of any relational query, they are just being stored for later retrieval. Otherwise, how would moving them to a separate DB make any sense?
    – JimmyJames
    Oct 22, 2019 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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