-1

In DBMSes like MySQL, MariaDB, PostgreSQL and MongoDB, data is saved as BSON, JSON or JSONB for JSON columns or document.

All of these data types use space for key names in JSON.

Does internal data on disk also require space for every key name on every row?

If yes, shouldn't a developer/admin care about it, for performance and storage cost reasons?

Is there any solution or are there any attempts to respond to these concerns?

3
  • Which particular DBMS? Nov 12 at 20:28
  • 1
    Does internal data in disk also take space for every key name on every row? Of course. The datatype claims that the value is JSON one, but it does not contain any info about stored JSON's inner structure, and even more so it does not fix the inner properties names. shouldn't a developer/admin care about it, for performance and storage cost reasons? In most cases - no.
    – Akina
    Nov 13 at 4:46
  • @Charlieface it is not about particular DBMS, it is about space used by keys names. Nov 13 at 5:24

3 Answers 3

1

MySQL/MariaDB:

The string is stored as a string. This takes space and slows down looking into the string.

So, you should, in general, pull the useful columns out of the JSON and put them into columns -- this will usually take less space and provide more efficient indexing, searching, sorting, etc.

If you still have miscellaneous key-value pairs (or structures), then JSON may be a good way to store things. But think of it like storing a JPG. You don't look at the individual pixels in such a BLOB; you merely hand it off to someone else.

1

Yes, the field names are stored in every JSON document. If you use long field names in the JSON document, the document uses more storage on every row of the table.

I showed this in my presentation How to use JSON in MySQL Wrong.

enter image description here

Whereas if you store attributes in the conventional way, in a normal column of the table per attribute, the name of the column is stored only once, in the table metadata. Only the value is stored in every row.

Using JSON in a relational database is great for cloud providers' profit, but is usually a drag on software efficiency.

4
  • So is it different in nosql databases like MongoDB? If yes, how? Nov 14 at 16:27
  • 1
    In BSON, the field names are still stored as strings in every document. Read: mongodb.com/basics/bson Especially: "Documents in BSON use more space than JSON..." Nov 14 at 16:48
  • Therefore, in terms of storage space, relational databases have no weakness compared to nosql databases! Nov 14 at 16:56
  • In fact the opposite: because a relation is a set of rows with a header, and all the rows necessarily have the same columns named in the header, a relational database can store the metadata just once. Some people consider that a limitation, but it does give an advantage in storage efficiency. Nov 14 at 17:10
1

For relational databases (RDBMS) like MySQL, MariaDB, PostgreSql it matters a lot, because they store the JSON as text. See the other answers.

For a NoSQL database like MongoDB it looks a bit different. By default the BSON data is compressed, the storage is more efficient than storing JSON as pure text. However, of course longer field names consume more space on disk than short names.

See also Storage Optimization for Small Documents

2

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.