7 ms·

In what scenarios does one work on data without any relations? Honest question - perhaps I'm too used to think in SQL but even with e.g. a completely flat bunc

In what scenarios does one work on data without any relations? Honest question - perhaps I'm too used to think in SQL but even with e.g. a completely flat bunch of documents one usually needs at least access control (i.e. document owners, which is a relation).

dheera 6y ago
When you get piles of JSON data from somewhere else (e.g. an API response from a provider you subscribe to) and want to store all of it intact, query it, and that somewhere else may insert new fields at any time in the future without telling you. When you have multiple types of objects with differently populated fields that you want to store in a single collection and query across them in currently unknown ways, and where you need flexibility for the unknown.
- drodgers 6y ago
  That sounds like a great use case for OLAP and fuzzy search solutions. Eg. Dump all of the data to S3 and search it in parallel via AWS Athena. If you want fast fuzzy search, then elasticsearch is built for this. If the data is small enough that you want B-tree indexes (with the accompanying slower writes and lower query latencies), then any SQL database with a JSON column will work.
  - dheera 6y ago
    > Eg. Dump all of the data to S3 and search it in parallel via AWS Athena. Too proprietary. Can't be run locally, on robots, etc. Can't be transferred to other cloud providers. Also S3 is horribly slow to write or delete thousands of records at once.
    - kornish 6y ago
      > Too proprietary. Every major cloud provider has a blob store. Most are S3-interface compatible, or can be fronted by something which is, like Minio. > Can't be run locally, on robots, etc. If the contents of your S3 keys are just line-delineated JSON files, you can easily download those files and run scripts or process them locally. > Can't be transferred to other cloud providers. Again, not true — a tool like rclone handles this case in a single command. Depends on the amount of data, but if anything it's easier to move a bunch of flat files between providers than copying a database backup around. You have to pay for egress bandwidth, though, of course. > Also S3 is horribly slow to write or delete thousands of records at once. If you're keeping one JSON record per S3 key, that is a blatant misuse of the tool and performance will be terrible. On the other hand, if you batch records into files of appropriate sizes, it's very cost-effective to store and query via Athena, Spark on EMR, etc. If you need individual record-level access as identifiable by a key, then you'd probably be better off with Redis or Memcached. (though, those will not be as good for bulk offline processing) It's all about your access patterns.
      - WJW 6y ago
        > Also S3 is horribly slow to write or delete thousands of records at once. I'll add that S3 is only slow to delete things sequentially. You can delete or write an effectively infinite amount of items in parallel.
      - dheera 6y ago
        > On the other hand, if you batch records into files of appropriate sizes Too much work. If you're a startup that's too much stuff to maintain. Much easier to get a hosted MongoDB and launch tomorrow. And when you only have 2 months rent in the bank and investors want demo after demo in order to give you cash, tools like MongoDB do make a difference. And yes, I've been in that situation before.
        dolni 6y ago
        Your ability to turn the smallest of technical hurdles into an insurmountable problem is truly impressive.
- saturn_vk 6y ago
  That's what I'm using postgresql for, among other things. The only drawback I've ran into so far is that it sometimes doesn't pick the proper index for a specific query.
- mschuster91 6y ago
  > and that somewhere else may insert new fields at any time in the future without telling you. That's what interface contracts and API versioning is for - and new fields shouldn't be any problem at all unless you're parsing the JSON by hand.
catmanjan 6y ago
Search engines
- alexchamberlain 6y ago
  Search indices, at least in my experience, are denormalised views of relational data.
- Fordec 6y ago
  For search a Graph database would be my go-to if starting one now, not a NoSQL one.
  - catmanjan 6y ago
    Graph databases are a type of NoSQL database,
eicnix 6y ago
Financial data In basically every case you are querying all data for a symbol in a given timeframe and then process it further outside of the database.
- Tostino 6y ago
  What financial systems are you designing?
- jpz 6y ago
  Mongo is obviously not competitive with KDB though, if you are talking of timeseries data as you suggest. I would think it is in fact very much the wrong tool for the job. Time series data screams structured storage, not unstructured.
- twic 6y ago
  But a relational database is great for that. select * from prices where symbol = any (?) and timestamp >= ? and timestamp < ? I've worked at a couple of places where we kept price data in relational databases and did pretty much this. The only way to beat a relational database here would be in performance. Because of course, for a specific use-case, you can always write a specialised data store that is faster than a general-purpose one. But the performance advantage might not be enough to justify the cost. If you're talking about storing every tick on an exchange, it might be, but if you're storing close prices, probably not.
threeseed 6y ago
Most data in large enterprises e.g. telcos, banks, insurance etc are not stored in a single data warehouse with well-defined relationships. They are in multiple disparate silos of which their EDW is just one. It's why Data Lakes become so commonplace because it was an easy way to just get all of the data out of the silos into one place so that the business could attempt to join between them. And it's why MongoDB (and other unstructured friendly storage systems) have remained so popular because the reality is that those datasets are not well governed, no-one knows the schemas and no-one knows how to join between them.
- HelloNurse 6y ago
  If an enterprise wants to "get all the data out of the silos", unstructured storage is only "friendly" to the temptation to be sloppy in such an export. Not only missing or corrupted individual columns, but completely incoherent and arbitrary complex document structures. If "no-one knows the schemas and no-one knows how to join between them" the data lake project has already failed to provide value.
  - JeremyNT 6y ago
    Yeah, it's kicking the can down the road! If you can't or won't do the work to understand and define the schema at the time of ingestion, you're asking consumers of that data to do the work for you later on, and then you're looking at an archeology project to try and figure out what the heck people were thinking in the past. It's only going to get worse as you keep shoving new data in there with its own incompatible poorly defined schema. It probably ticks a compliance box but good luck making use of it...
    - HelloNurse 6y ago
      I've only ever witnessed consumer-driven ad-hoc integration initiatives, where the team that needs data coerces the team that has data into providing it in a usable form, and people notice stale or incorrect feeds because they are actually in use. Why should a company invest server resources and development effort on "ingesting" data that nobody cares about in a way that doesn't fit any purpose? Attempting to collect "all" data is meaningless.
Ceezy 6y ago
Redis is schemaless and make perfect sense that s an high availability storage in memory storage. And most (web) projects use something like Redis.
- dalu 6y ago
  Redis is a key value store and not a database. Also I'd strongly disagree that it's used in most web projects. I have never, not a single time had the need to use Redis in 17 years of web development
NicoJuicy 6y ago
Microservices / DDD "Relations" are stored as an object, not a separate table. See: AggregateRoot Eg. Document: { metadata: {}, owner: {}, sharedWith:{}}
tgv 6y ago
I've got a project where one part of the application is just for editing a medium-sized, hierarchical object. So it's easiest to just get it from the database as a whole, and update it like that. It's also very easy to understand for the frontend programmers. The other part gets data from users and dumps it in its entirety in another table. Then the analysis part simply retrieves them all (at most a few thousand objects), and can build graphs, or whatever visualization with them. There's not a single JOIN involved, and updating is simple. If it goes beyond that, it's time to look at a relational database, either for performance reasons or because the API becomes cumbersome.
twic 6y ago
I will pedantically note that in database theory, "relation" is a name for what we usually call a table. You're talking about relationships. https://en.wikipedia.org/wiki/Relation_(database) https://en.wikipedia.org/wiki/Relation_(database) If your data is completely flat, and fits in one table, that's still a relation!
- bildung 6y ago
  TIL, thanks :)

dheera 6y ago

When you get piles of JSON data from somewhere else (e.g. an API response from a provider you subscribe to) and want to store all of it intact, query it, and that somewhere else may insert new fields at any time in the future without telling you. When you have multiple types of objects with differently populated fields that you want to store in a single collection and query across them in currently unknown ways, and where you need flexibility for the unknown.

drodgers 6y ago
That sounds like a great use case for OLAP and fuzzy search solutions. Eg. Dump all of the data to S3 and search it in parallel via AWS Athena. If you want fast fuzzy search, then elasticsearch is built for this. If the data is small enough that you want B-tree indexes (with the accompanying slower writes and lower query latencies), then any SQL database with a JSON column will work.
- dheera 6y ago
  > Eg. Dump all of the data to S3 and search it in parallel via AWS Athena. Too proprietary. Can't be run locally, on robots, etc. Can't be transferred to other cloud providers. Also S3 is horribly slow to write or delete thousands of records at once.
  - kornish 6y ago
    > Too proprietary. Every major cloud provider has a blob store. Most are S3-interface compatible, or can be fronted by something which is, like Minio. > Can't be run locally, on robots, etc. If the contents of your S3 keys are just line-delineated JSON files, you can easily download those files and run scripts or process them locally. > Can't be transferred to other cloud providers. Again, not true — a tool like rclone handles this case in a single command. Depends on the amount of data, but if anything it's easier to move a bunch of flat files between providers than copying a database backup around. You have to pay for egress bandwidth, though, of course. > Also S3 is horribly slow to write or delete thousands of records at once. If you're keeping one JSON record per S3 key, that is a blatant misuse of the tool and performance will be terrible. On the other hand, if you batch records into files of appropriate sizes, it's very cost-effective to store and query via Athena, Spark on EMR, etc. If you need individual record-level access as identifiable by a key, then you'd probably be better off with Redis or Memcached. (though, those will not be as good for bulk offline processing) It's all about your access patterns.
    - WJW 6y ago
      > Also S3 is horribly slow to write or delete thousands of records at once. I'll add that S3 is only slow to delete things sequentially. You can delete or write an effectively infinite amount of items in parallel.
    - dheera 6y ago
      > On the other hand, if you batch records into files of appropriate sizes Too much work. If you're a startup that's too much stuff to maintain. Much easier to get a hosted MongoDB and launch tomorrow. And when you only have 2 months rent in the bank and investors want demo after demo in order to give you cash, tools like MongoDB do make a difference. And yes, I've been in that situation before.
      - dolni 6y ago
        Your ability to turn the smallest of technical hurdles into an insurmountable problem is truly impressive.
saturn_vk 6y ago
That's what I'm using postgresql for, among other things. The only drawback I've ran into so far is that it sometimes doesn't pick the proper index for a specific query.
mschuster91 6y ago
> and that somewhere else may insert new fields at any time in the future without telling you. That's what interface contracts and API versioning is for - and new fields shouldn't be any problem at all unless you're parsing the JSON by hand.

catmanjan 6y ago

Search engines

alexchamberlain 6y ago
Search indices, at least in my experience, are denormalised views of relational data.
Fordec 6y ago
For search a Graph database would be my go-to if starting one now, not a NoSQL one.
- catmanjan 6y ago
  Graph databases are a type of NoSQL database,

eicnix 6y ago

Financial data In basically every case you are querying all data for a symbol in a given timeframe and then process it further outside of the database.

Tostino 6y ago
What financial systems are you designing?
jpz 6y ago
Mongo is obviously not competitive with KDB though, if you are talking of timeseries data as you suggest. I would think it is in fact very much the wrong tool for the job. Time series data screams structured storage, not unstructured.
twic 6y ago
But a relational database is great for that. select * from prices where symbol = any (?) and timestamp >= ? and timestamp < ? I've worked at a couple of places where we kept price data in relational databases and did pretty much this. The only way to beat a relational database here would be in performance. Because of course, for a specific use-case, you can always write a specialised data store that is faster than a general-purpose one. But the performance advantage might not be enough to justify the cost. If you're talking about storing every tick on an exchange, it might be, but if you're storing close prices, probably not.

threeseed 6y ago

Most data in large enterprises e.g. telcos, banks, insurance etc are not stored in a single data warehouse with well-defined relationships. They are in multiple disparate silos of which their EDW is just one. It's why Data Lakes become so commonplace because it was an easy way to just get all of the data out of the silos into one place so that the business could attempt to join between them. And it's why MongoDB (and other unstructured friendly storage systems) have remained so popular because the reality is that those datasets are not well governed, no-one knows the schemas and no-one knows how to join between them.

HelloNurse 6y ago
If an enterprise wants to "get all the data out of the silos", unstructured storage is only "friendly" to the temptation to be sloppy in such an export. Not only missing or corrupted individual columns, but completely incoherent and arbitrary complex document structures. If "no-one knows the schemas and no-one knows how to join between them" the data lake project has already failed to provide value.
- JeremyNT 6y ago
  Yeah, it's kicking the can down the road! If you can't or won't do the work to understand and define the schema at the time of ingestion, you're asking consumers of that data to do the work for you later on, and then you're looking at an archeology project to try and figure out what the heck people were thinking in the past. It's only going to get worse as you keep shoving new data in there with its own incompatible poorly defined schema. It probably ticks a compliance box but good luck making use of it...
  - HelloNurse 6y ago
    I've only ever witnessed consumer-driven ad-hoc integration initiatives, where the team that needs data coerces the team that has data into providing it in a usable form, and people notice stale or incorrect feeds because they are actually in use. Why should a company invest server resources and development effort on "ingesting" data that nobody cares about in a way that doesn't fit any purpose? Attempting to collect "all" data is meaningless.

Ceezy 6y ago

Redis is schemaless and make perfect sense that s an high availability storage in memory storage. And most (web) projects use something like Redis.

dalu 6y ago
Redis is a key value store and not a database. Also I'd strongly disagree that it's used in most web projects. I have never, not a single time had the need to use Redis in 17 years of web development

NicoJuicy 6y ago

Microservices / DDD "Relations" are stored as an object, not a separate table. See: AggregateRoot Eg. Document: { metadata: {}, owner: {}, sharedWith:{}}

tgv 6y ago

I've got a project where one part of the application is just for editing a medium-sized, hierarchical object. So it's easiest to just get it from the database as a whole, and update it like that. It's also very easy to understand for the frontend programmers. The other part gets data from users and dumps it in its entirety in another table. Then the analysis part simply retrieves them all (at most a few thousand objects), and can build graphs, or whatever visualization with them. There's not a single JOIN involved, and updating is simple. If it goes beyond that, it's time to look at a relational database, either for performance reasons or because the API becomes cumbersome.

twic 6y ago

I will pedantically note that in database theory, "relation" is a name for what we usually call a table. You're talking about relationships. https://en.wikipedia.org/wiki/Relation_(database) https://en.wikipedia.org/wiki/Relation_(database) If your data is completely flat, and fits in one table, that's still a relation!

bildung 6y ago
TIL, thanks :)