-1

This question relates to choosing the best database storing data on a pc running windows. The pc is part of a commercial product which generates a big amount of data over time. Data consists of

  • Test sequences
  • Test results
  • Configuration data

The connection between the software and the database is done via EntityFrameworkCore.

There is already an existing relational database model which has been normalized and optimized for usage in an relational usage. Querying large amounts of data will be essential. It consists of ~25 tables that are interconnected.

We chose MS SQL Server Express in the past for those local installation.

Meanwhile the amount of data generated by those devices (test results) grew in a way that we will probably reach the 10GB limit faster than expected. As there are much more curve/chart data which should be saved as well, the 10GB will probably be reached.

As of my knowledge there are no good practices how to handle the data after the Limit is reached. The only way would be to export/archive old data to free up space again. But querying these archived/old backups wont be easy.

Right now we are consider switching to another RDBMS for our commercial product like:

Our aim is to use the existing DB-model for larger analyses of the data later on like trends, statistics and so on. The structure of the different results is very similar to make comparison/querying easier later on.Those queries to generate trends etc. will span over a large number of rows. Queries will include JOINs over multiple tables of the stored data. Our preference here was always a relational-DB over a NoSQL-Database. This requirement seems to be not optimal to consider a NoSQL-Database for the way how we want to store and query the data of these Testresults.

But we also looked into MongoDB, which makes storing of data very easy in the first place. But there also is the need from customer side to query that data later on (statistics/trends etc.)

  • Which of the RDBMS would you suggest and why?
  • How good is MongoDB in such a use case to query large amounts of testresults?
Improve this question
5
  • I personally would always choose Postgres over MariaDB or MySQL (or MongoDB)
    – a_horse_with_no_name
    Jul 28 at 9:58
  • I agree, MySQL and MariaDB don't follow the SQL standard while PostgreSQL is the closest to truly following the standard. Also, to this day MySQL has a list of severe bugs that have not been fixed. And if you ever want to do NoSQL, you should never consider MongoDB as it has been slower than PostgreSQL in almost all metrics since like v10 (this includes handling JSONB/BSON objects). PostgreSQL is also more feature rich and can easily be extended to support different needs of a business.
    – Chessbrain
    Jul 28 at 10:21
  • 1
    You could add more databases to store more data in SQL Express
    – Stephen Morris - Mo64
    Jul 28 at 10:25
  • Does Express require you to pay after some size? Maybe that should lead you to avoid that product?
    – Rick James
    Jul 30 at 16:11
  • MariaDB and MySQL can be used (for free) well past 10GB; no need to split into separate "databases". (I do not know the rules on when you must pay.)
    – Rick James
    Jul 30 at 16:13

1 Answer 1

Reset to default
1

As Stephen points out above, a solution I normally recommend to people who are hard stuck on using SQL Server Express edition is to find a way to partition their data into multiple databases. Most times this is by taking historical data and moving it to an archive database (but some can even divide their modern data by function across databases instead).

Your application should mostly be querying data by views, such that it's decoupled from the applications perspective when an archive table (or separated functional table) needs to be unioned or joined back in. This makes the management on the data side immaterial to the application.

Aside from that, if you want to switch to another database system, I agree with everyone else on choosing PostgreSQL. I think it has the best following in the non-enterprise community and is a very well built out database system.

I would not recommend a NoSQL solution here as you've already invested in creating and using a data model for your application. NoSQL is useful when the data model is not well defined, not well controlled, and / or changes very frequently. NoSQL (and in general any modern database system of any kind) is not necessarily "better" or "faster" with large amounts of data. They all perform relatively equally when your database is architected properly.

Improve this answer
1
  • 1
    Also SQL Server Express supports Columnstore tables, which provide amazing compression for the right kind of table.
    – David Browne - Microsoft
    Jul 28 at 13:12

Not the answer you're looking for? Browse other questions tagged or ask your own question.