Re:Setting Up a Fast, Comprehensive Search Routine with Post...
by purerandomness on Sunday July 29, 2018 @10:06PM
(#17641568)
> 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-joi...
My point is: Postgres offers you a document store and gives you the option to join, if you feel like it.
Re:Setting Up a Fast, Comprehensive Search Routine with Post...
by lowercased on Sunday July 29, 2018 @09:51PM
(#17641530)
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).
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.