Which DB for 1 table with 100 Million rows per year? [closed]

Question

I've a questionnaire with 70 questions and need to store the answers.

Problem: it's 100 Million records per year.

I've experience with different types of storages, but never had to deal with these huge numbers. Now I'm afraid that every wrong decision might lead to big negative impact.

Information about the data:

• I was thinking about 1 table with 70 columns
• The columns are already defined and might be slightly adjusted after a while (+/- 10 columns)
• The data type of each column is mainly Integer and String with mostly 2 chars, max. 10 chars.
• No nested (tree) structures needed
• No flexible data types needed
• No joins needed

Data definition (pseudo-code)

COLUMN           |   TYPE   | MAX. LENGTH
-----------------------------------------
id               | Integer  | 10  
questionnaire_id | Integer  | 10
answered_at      | Datetime | -
answered_by      | Integer  | 10
answer1          | Integer  | 2
answer2          | Integer  | 2
answer3          | Integer  | 2
answer4          | Integer  | 2
...
answer35         | String   | 2
answer36         | String   | 2
...
answer70         | String   | 2

Priorities:

• Storing big data
• Run standard aggregate functions (avg, min, max, count, ...), filter and sort in an acceptable time

Is there any best practice or a checklist to follow which reduces my options and therefore wrong decisions?

Thank you in advance!

Edit: normalized, inspired by Dave

# questionnaire
- id (PK, AI)

# questions
- id (PK, AI)
- questionnaire_id (FK)
- label

# submits
- id (PK, AI)
- questionnaire_id (FK)
- answered_by
- answered_at

# answers
- id (PK, AI)
- submit_id (FK)
- question_id (FK)
- value      // Integers only (strings are mapped: A => 1, B => 2)

Answer 1

With that table an no extra keys/indexes you have 160 bytes per row, so 100,000,000 rows per year is about 16GByte per year. Any decent DBMS (SQL Server, postgres, [YourFavouriteDBHere], ...) should be able to cope with that and (assuming proper indexing) query it efficiently if you have appropriate hardware/virtual resources. The extra space taken by keys and other indexes shouldn't balloon the space requirements much, if it does then it is likely that the structure is not optimal more generally.

So simply storing the data should not be a worry.

Some databases support compression, sparse tables, and other techniques, that will help considerably with this structure if space is your primary concern, but before considering them first ensure that this is in fact the structure that you need.

As others discuss in the comments, your current structure may not be optimal for the analysis that you need to perform, so you would need to edit your question to include such details if you need help there. A key thing in all database design is to consider your desired outputs as well as your inputs.

Unfortunately I don't know every required select-query at the moment.

You must have some idea of the sort of reports you expect to run against the data. It doesn't need to be every query you'll ever run, or even close to, but optimising for some expected reports is far better than blindly ploughing in data in the hope that it might be optimal for something/anything that will eventually come up.

Where at all possible, do not design based purely on your inputs.

Without any idea of the outputs, perhaps yes: just throw the data into a table like that with indexes on the date at least as that is very likely to be one of the key ways you'll want to filter/partition/aggregate the data in reports, and transform the data into something else via ETL when you do know what analysis you need to perform. But if you have an idea of the outputs to start with, you might be able to avoid having to create and maintain two structures (one for active recording and one for reporting) and the process to transform data from one into the other. Of course, this two structure system might be optimal, but we simply can't tell you one way or the other without more detail.

Answer 2

Shrink the datatypes. Where appropriate, changing from INT to TINYINT saves 300MB or more. Where appropriate, use a 'natural' PK instead of surrogate. Example: PRIMARY KEY(submit_id, question_id) for answers See: http://mysql.rjweb.org/doc.php/schema_best_practices_mysql

You say that the 70 questions may change slightly over time. This could be a serious problem or just a minor nuisance. Ask and answer these:

• What will you do with the answers when there were 70 questions after changing to 72?
• Will you delete any questions?
• Might it be OK to end up with 80 questions, some of which are no longer in use? That is, question number 15 always refers to "..." or it is gone. That is, it won't be replaced by some other question. (This approach might be wise.)
• 70 columns vs 70 rows -- ALTER TABLE to add a new columns will be somewhat costly, but it rarely happens.
• 70 columns vs 70 rows -- 70 columns may take only half the disk space. Does this matter?

It will be about 40GB/year.

Is it reasonable to summarize the data each, say, week? You can then rapidly produce reports for year(s) much more rapidly than querying the raw data.