Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Input Data

Multiple Tests Beds generate measurement data of various complexity.

In it's most basic form, not considering any meta-data, one measurement on a Test Bed will be a small (1 - a few thousand samples) time-series with a couple of dozen channels/signals/attributes per sample.

Measurements across time and Test Beds will have a similar set of signals, but not always the same as sensors are added and removed for the test setups.

Data volume

Currently we estimate our data rate at 6 testbeds x 4 test per hour x 12 hours a day x 4000 samples per test == 1,152,000 samples per day x 365 == 420,480,000 samples per year

_ x 48 columns per sample (currently 32 bit floats, mostly) ~~~ 75 GB per year

(columns in this case refers to channel/signal)

If/When more testbeds are added the data volume might increase accordingly.

Data Input

The test beds generate the data locally and the data is then imported asynchronously into the db. (A few thousand samples might be generated in a the time of one second, then reviewed locally and then either scratched or imported.)

Queries

We expect queries to be mostly on aggregates of the single measurements. I.e., you like to find all measurements (each having 4k samples) where e.g. the mean of channel_output_voltage is within a certain range.

Database layout?

What is a good way to set up tables for this? What factors have to be taken into account?

Theoretically I could go with one table per measurement generating 100,000 tables per year, but it doesn't strike me as a good idea.

Or I could stick everything into one big table (with hundreds of columns) that has room for all channels and channels get added as needed: One row per sample. Unused channels remain NULL.

MEASUREMENTS
------------
measurement_id } PK
time_stamp     }
channel_1 (may be NULL for a certain measurement_id ...)
channel_2
...
channel_n(+1)

Or I could go with an approach of having one table for the samples (timestamps) and one table containing all the values: (one row per sample in MEASUREMENTS table and n rows per sample in the SAMPLE_VALUES table)

SAMPLES            SAMPLE_VALUES
------------       -------------
measurement_id     sample_id
time_stamp         channel_id (links to a channels table where there is a name etc.)
sample_id          channel_value

What other options are there? How to further investigate which option we should choose?

Database products

Due to customer constraints we would like to put this in MS SQLS or Oracle.


From one answer:

Don't store the raw data, only store aggregates. Seriously.

This assumes that there is a meaningful way to determine ex antes what queries the customer is going to want to run against their data. No way :-)

share|improve this question
    
Neither Oracle nor SQL Server will allow an unlimited number of columns in a table –  Jack Douglas May 11 '12 at 20:00
    
@Jack - well, thousand columns would be something we could live with for the time being. (Expectations are that we'll start with ~100 or so with slow growth.) It's not exactly future proof though ... –  Martin May 12 '12 at 10:31
    
Have you thought about using SQL Server Analysis Services? –  Jon Seigel May 12 '12 at 21:13
1  
I think this could be stored quite efficiently in Postgres using the hstore datatype ("NoSQL" like key/value type) or an array but that is obviously not an option. I think having one table for the values is probably going to be the most maintainable solution. You probably want to partition that table by measurement_id –  a_horse_with_no_name May 22 '13 at 9:38
add comment

3 Answers

We expect queries to be mostly on aggregates of the single measurements Well, take advantage of that. Don't store the raw data, only store aggregates. Seriously.

OK, you will need to debug your aggregation code. So, save the raw data in some trivial formant (csv?) in disk files. Plan on tossing them after a few days.

This will eliminate your 75GB/yr requirement, your 20 INSERTs/sec requirement, etc.

100,000 tables per year Bad idea. The OS will groan whenever you open a table. I have seen it done, but it takes seconds to minutes just to open a table.

share|improve this answer
    
Rick, thanks for this input. I have edited the Q wrt. your comment on the aggregates. (Also note that there is no INSERT/Sec requirement as such, as the raw data will be imported into the DB asynchronously.) –  Martin May 18 '12 at 17:23
add comment

Are your queries supposed to collect data for each month/year?

You can use partitions to store your information in different physical files. Partitions can increase the speed of SELECT statements when you only need information about a specific period. http://msdn.microsoft.com/en-us/library/ms345146%28v=sql.90%29.aspx

When creating a partitioned table in Microsoft SQL Server you can also create different file groups on different physical locations and back those up separately.

With regards to your question about the database design, you may want to read about normalization here: http://en.wikipedia.org/wiki/Database_normalization

share|improve this answer
    
While the queries will probably be implicitly across date/time ranges, the explicit aggregation will be via measurement_id and some - changing - customer specific metadata attached to the measurement_id so we really didn't see any obvious way to partition the tables along some in-database parameters. –  Martin Sep 10 '12 at 6:11
add comment

Re: the big table approach

Or I could stick everything (all samples) into one big table (with hundreds of columns) that has room for all channels and channels get added as needed: One row per sample. Unused channels remain NULL.

I found this in the Oracle documentation:

Storage of Null Values

A null is the absence of a value in a column. Nulls indicate missing, unknown, or inapplicable data.

Nulls are stored in the database if they fall between columns with data values. In these cases, they require 1 byte to store the length of the column (zero). Trailing nulls in a row require no storage[emph. added] because a new row header signals that the remaining columns in the previous row are null. For example, if the last three columns of a table are null, then no data is stored for these columns.

So this would appear to make the one-table-for-sample-data approach pretty attractive, as space wise a lot of overhead of the SAMPLE_VALUES table could be saved.

It'd get more complicated, as there is a maximum of 1000 columns per table and we do have data sets with 2000-3000 channels, but I guess one could just add more 1000 column tables. (Realistically I'd say 3 or 4 would be enough for quite some time).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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