I'm working on a project to store large JSON files (~1 MB each) in a database, with 10 of these files linked to one user profile.

Normally I would use MySQL, and have two tables, one with the meta user information, and another storing the large JSON files for each user.

However, MySQL has a max cell size of 66KB, so for that reason alone I can't store my files in the MySQL cells. Also, even if my files were small enough, it would seem that my files wouldn't be appropriate for the typical tabular format anyways.

Since MySQL is out of the question, it seems like it makes the most sense to go to a non-sql database like MongoDB.

Is storing large files (JSON or txt) the typical use case for MongoDB (or any other no-sql database)? Is my thought process correct here?

| improve this question | | | | |
  • 3
    Looks like you got some of your assumptions wrong. "MySQL has a max cell size of 66KB" -- where does this come from? And what is a "cell" anyway? As for whether it is "appropriate" or "typical" to store JSON in a relational database depends a lot on what you're actually doing with these JSON documents. – mustaccio May 1 '19 at 15:55
  • @mustaccio You're right -- I didn't interpret what I read correctly. "what you're actually doing with these JSON documents" -- yep, that makes sense too, I get that it is different if you are looking to search them versus you already know exactly where they're going to be. I don't need to search, I just need to retrieve them. – bones225 May 1 '19 at 20:22

The 64KiB (65,536 bytes) row length limit (I am assuming you are referring to this limit and have rounded up in decimal) is for on-page data only. You can store more in BLOB or TEXT fields. See https://dev.mysql.com/doc/refman/8.0/en/blob.html for more detail.

"Each BLOB or TEXT value is represented internally by a separately allocated object. This is in contrast to all other data types, for which storage is allocated once per column when the table is opened."

As noted on that page, you may need to increase the max_allowed_packet setting depending on the size of the large objects you want to store and mySQL version (the default used to be 4MB, in newer releases it is 64MB, it an be made much larger). See https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_allowed_packet for more detail.

As mustaccio states in his comment, appropriateness of data storage options can and will vary greatly based on what you need to do with the stored data, so you would need to describe more about the application to get meaningful answers on that matter (and even then they may be very opinion based). For better or worse, Storing LOBs in SQL databases is certainly often done.

| improve this answer | | | | |
  • Besides, these days RDBMSes (including MySQL 5.7+) can handle JSON data better than simply LOBs. – mustaccio May 1 '19 at 16:41
  • Thank you David. The most helpful thing was correcting my thought that it couldn't be done due to row length limit restrictions. If it can be done, it probably makes the most sense for me to start with that. – bones225 May 1 '19 at 23:59

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