3 ms·

There's a lot of misconceptions there. If you want to save JSON documents, you can pick Postgres [1] or MySQL and still have a RDBMS for free additionally. El

There's a lot of misconceptions there. If you want to save JSON documents, you can pick Postgres [1] or MySQL and still have a RDBMS for free additionally. Elasticsearch does that too, but its access certainly isn't O(1) - it has a complexity of O(log n), just like Postgres or MySQL would have, because all of them use tree-like index data structures. (Please note that ElasticSearch is not meant as a data storage layer) Now, if you just want to have the JSON, you're done. O(log n) for ElasticSearch, Postgres or MySQL. That's it. But normally, you want to do something with that data. You have a user and want all the products. Or you have a book and want all the authors, because you have a real app to write. Most data in our world is relational: A student can partitipate in courses, a store sells products, and so on. Queriyng your database using JOINs will make use of indexes in each of the tables that you have previously set up. These indexes will prevent the theoretical worst-case (the "Full Table Scan", where your complexity is O(n) for the table). The query planner will decide which index is better to use for the optimal response time. You can, however, choose to not leverage this feature of RDBMS. What do you do now, if you need all the courses of a student? Do you get all the JSONs and compare their IDs? Do you parse the JSON keys in your own app? But having your app iterate over all the JSON keys already has O(n) complxity. So you end up recreating tree-like indexes anyway somewhere in your stack. But all of that is already provided for you, with decades of debugging and performance optimization - for free. To sum up: Use Postgres JSONB type if you only need to write JSON documents. You might later want to query the nested JSON data structure and join it. I highly recommend "Mastering PostgreSQL in Application Development" [2] [1] https://www.postgresql.org/docs/current/static/datatype-json.html https://www.postgresql.org/docs/current/static/datatype-json... [2] https://masteringpostgresql.com/ https://masteringpostgresql.com/

threeseed 8y ago
You have missed the point here. The comparison was between ElasticSearch and a relational store. PostgreSQL's JSON type is not relational. And if you are fetching any entity by a primary key then it should be O(1). The point was that document stores offer you the ability to nest data structures whilst relational forces you to join. And so if that is your query/data pattern then a document store can be orders of magnitude faster than a purely relational store e.g. 360 Customer View.
- lowercased 8y ago
  There's not many use cases I come across where the 'nested' data is the only instance of that data. A student record might have some nested class information, but the class information itself lives as a master someplace else. If you nest things, you now have data living in multiple places, no? Updates need to update multiple locations, which would potentially introduce lots of errors. I've got a handful of situations where nested structures are making sense, but it doesn't seem to be the norm (at least in the data worlds I work in).
- purerandomness 8y ago
  > O(1) Primary key fetching will always be O(log n) due to how indexes are implemented. Access time complexity will never be independent of the item count in your data store, relational or not. > PostgreSQL's JSON type is not relational. It is. In Postgres, you can join nested JSON structures' keys with normal, relational tables: https://dba.stackexchange.com/questions/83932/postgresql-joining-using-jsonb https://dba.stackexchange.com/questions/83932/postgresql-joi... My point is: Postgres offers you a document store and gives you the option to join, if you feel like it.

threeseed 8y ago

You have missed the point here. The comparison was between ElasticSearch and a relational store. PostgreSQL's JSON type is not relational. And if you are fetching any entity by a primary key then it should be O(1). The point was that document stores offer you the ability to nest data structures whilst relational forces you to join. And so if that is your query/data pattern then a document store can be orders of magnitude faster than a purely relational store e.g. 360 Customer View.

lowercased 8y ago
There's not many use cases I come across where the 'nested' data is the only instance of that data. A student record might have some nested class information, but the class information itself lives as a master someplace else. If you nest things, you now have data living in multiple places, no? Updates need to update multiple locations, which would potentially introduce lots of errors. I've got a handful of situations where nested structures are making sense, but it doesn't seem to be the norm (at least in the data worlds I work in).
purerandomness 8y ago
> O(1) Primary key fetching will always be O(log n) due to how indexes are implemented. Access time complexity will never be independent of the item count in your data store, relational or not. > PostgreSQL's JSON type is not relational. It is. In Postgres, you can join nested JSON structures' keys with normal, relational tables: https://dba.stackexchange.com/questions/83932/postgresql-joining-using-jsonb https://dba.stackexchange.com/questions/83932/postgresql-joi... My point is: Postgres offers you a document store and gives you the option to join, if you feel like it.