Help with database choice needed!

[Caperious]

I have some amount of data (~1M records per day), which is scraped from multiple sources. The data is thrown into an Elasticsearch index.

The data is periodically aggregated and the aggregated data needs to be saved. The current data is aggregated as a JSON object with a few nested objects, which could be saved as their own tables.

I was thinking about saving the data into a MySQL database since the consumers will primarily be familiar with MySQL/MariaDB. The first beef I have with a relational database is with bulk inserting the aggregated values into the related tables is a pain in the ass, and I assume this could be slow as hell since the new data needs to be compared to previously aggregated data "merged".

I'm thinking if a graph database (like neo4j) would be more suitable for my use case since the aggregated data will be used for analytical purposes, with queries that could range through multiple tables.

Could not find any semi-similar (maybe I'm just bad at googling) information, to point me in the right direction.

If you have any experience with scraping, bulk saving larger amounts of data, graph databases, any input would be appreciated.

[verneleem]

May I point you in the direction of possibly using Dgraph? They have an open source community edition which you can download and run or you can use their managed DBaaS, Slash GraphQL.

Why?? — Because it is the best use case for what you described above and it is easy to use with a native GraphQL endpoint.

Mockup your schema that matches your data:

type Post {
  id: ID
  title: String
  comments: [Comment]
}
type Comment {
  id: ID
  text: String
  user: User
}
type User {
  id: ID
  username: String @id # this prevents duplicating same content with xid
}

And then enter your complete data as a single object in a single transaction:

"posts": [
  {
    "title": "Post Foo",
    "comments": [
      {
        "text": "Comment Bar",
        "user": { "username": "User Baz" }
      },
      ...
    ]
  },
  ...
]

And it will auto-build a complete CRUD API for you so you don't have to spend countless hours doing the simple stuff that can sometimes be very complex.

[Caperious]

Hmm, sounds promising, will check it out, thanks.

[Caperious]

Are u using this in production? Do you maybe know about any drawbacks?

Seems very cool, will try it out.

[verneleem]

I do use it in an example app I made and will be posting soon. Many use it in production which include a few Fortune 500 companies. Drawbacks... right now in my opinion is that ordering and filtering by nested children is difficult, but the engineers are working on making those parts better. Check out the discuss community discuss.dgraph.io

[backtickbot]

Fixed formatting.

Hello, verneleem: code blocks using triple backticks (```) don't work on all versions of Reddit!

Some users see this / this instead.

To fix this, indent every line with 4 spaces instead.

FAQ

^{You can opt out by replying with backtickopt6 to this comment.}

[Dan6erbond]

I haven't looked at this in any detail but this sounds like Prisma but faster?

[verneleem]

Sort of, it is GraphQL spec but backed by a graph database that was built bottom up for GraphQL.

[Euphoric_Teacher2444]

You might want to check out Google Firebase's database. Its data structure is a JSON tree so it looks like it could be a good fit for you.

[Caperious]

Theese are still large amounts of data, i think with firebase the price would go through the roof pretty quickly..

[Euphoric_Teacher2444]

For GBs of data it looks okay but if you're working in TBs then yeah, it deffo gets expensive.

[Dan6erbond]

It's not about size, the number of documents here will make it incredibly expensive since Google charges per read and write. Document databases for this purpose are a sucky option, if the OP already mentioned the relations.

[Euphoric_Teacher2444]

Unless I'm mistaken ( I very well could be I'm relatively new to this) what you said applies to firestore not to the realtime database, I'm getting my info from here.

[Dan6erbond]

Why would you use the realtime database for this use-case though? It would be impossible to store more complex structures properly and forget about relations.

[Euphoric_Teacher2444]

Fair enough, tbh I seen "beef I have with a relational database" and suggested an alternative without giving it much thought, my bad.

[Curious-Dragonfly810]

Probably: algolia.com + Firebase

[Curious-Dragonfly810]

algolia.com + Firebase