2

I need to build a web-based system which takes a number of measurements for various items and stores them, a later allows to create various graphs and reports from this data, e.g. plotting a few fields over time from all records matching a certain filter, plotting calculated values such as averages of aggregated time buckets (e.g. monthly), etc. The actual fields existing for each record may vary over time and depending on the type of item measured, and it would be best to have them structured because for each field there are actually multiple values which belong together (field name, the measured value, the expected value, the tolerance range, the error in percent). The project will start with something like 30,000 rows per year with an average of 40 of these fields for each record.

Now, I did such things in the past, and basically there are two main database approaches I used:

1) MySQL - Was easy and flexible at first, but became very slow once the dataset grew large with multi-millions of records. In this case, the amount of data is probably not such an issue, at least at this point, but I don't want to add road blocks for the future here either ("640K ought to be enough for anyone." - probably not Bill Gates). A big downside for me here though would be that the tables require a schema, and that I would therefore need to store item fields in a separate table with one row per record and field.

2) Elasticsearch - I successfully used Elasticsearch several times to store and query large amounts of data (millions of records per day, with various different fields). It is schemaless, fast and has all kinds of aggregations which we can use, however it really shines once text search becomes involved (the only text here will be field names and one piece of metadata), and one problem could be that certain operations are expected not to be 100% exact (such as counting records in aggregated buckets) while for this task it would be great if everything could be exact. Also, administration is a bit more complicated.

In both cases above, I would have to build my own interface to let the user query the data, create graphs and reports with all kinds of settings, save them per user, allow generating some of them on a schedule automatically, etc. For Elasticsearch there is Kibana but I can't give it to these kinds of users who would use this project, it's too technical, and doesn't have all features I need.

I would now plan to use Elasticsearch again but I wanted to ask you now because I feel like I'm missing some more options, maybe even some obvious choice.

Is there some database designed exactly for this kind of applications, without need for a fixed schema, fast, with a solid featureset when it comes to aggregations and analytic functions, but delivering exact results? And is there maybe already some web-based kind of tool or framework on top of that, designed to handle statistics generation similar to Kibana (or maybe even better than that when it comes to my requirements), ideally something tweakable and expandable?

Thanks, David

  • I don't understand your issues enough to post an Answer, but I'll mention Postgres and its data type jsonb. The 'b' is for binary, where Postgres parses your submitted JSON data, enabling indexes on specified attributes. So you get the best of both worlds: (a) A fast scalable relational database with full A.C.I.D. support for ensuring the safe storage of your data, along with (b) the convenience of saving parts of your data in semi-structured JSON format but with fast searching. – Basil Bourque Aug 31 '17 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.