Level 61
Speed will depend on your use case. PostgreSQL is faster while handling huge data sets, complex queries, and read-write operations. On the other hand, MySQL is faster with read-only use cases.
1 like
Jun 28, 2021
6
Level 13
MySQL Vs. MariaDB Vs. PostgreSQL - Which one would you pick for JSON data?
I've been using MariaDB for all my Laravel projects; and it works really awesome. For one of my latest projects, I need to store large JSON objects in my database. I'm making use of the Laravel Migrations $table->json('data') to build my JSON data column.
I'm pretty new to this data-type; and while reading about it, I found several blog posts and QnA that recommend using PostgreSQL over MySQL (MariaDB).
One cool feature I'd need is to be able to perform searches within the stored JSON data. I plan to make use of Eloquent to perform all my queries; and would switch over to DB::raw() only when absolutely required.
The amount of records I'm starting with is close to a million. Can someone help me make a decision?
Level 13
My application is going to be read-heavy. That's where I'm going to need to use the indexes on the json data.
Level 61
I remember reading somewhere that you gain better performance if you create additional columns on the table that are indexed than to index data on JSON column.
1 like
Level 13
That's true. That's why this question. The challenge here is that the contents of data column will be unstructured; otherwise, I'd have gone with regular normalization.
Level 73
Agreed. Storing data in json columns defeats the whole purpose of a relational database and it will add unecessary complexity and performance issues to the application.
As for choosing which dbms you should use is more of a preference issue that a performance one. They are more or less equal. MySQL and MariaDB are almost identical since MariaDB is forked from MySQL.
If you choose Postgree then be aware that the syntax differs a bit in some cases.
1 like
Level 61
The nullable column is still faster than the missing key in JSON. So I would still follow the suggested approach.
Please or to participate in this conversation.