On Fri, Dec 13, 2013 at 12:15 AM, Seb <splu...@gmail.com> wrote:
> Hi, > > I'm working on the design of a database for time series data collected > by a variety of meteorological sensors. Many sensors share the same > sampling scheme, but not all. I initially thought it would be a good > idea to have a table identifying each parameter (variable) that the > sensors report on: > > CREATE TABLE parameters ( > parameter_id serial PRIMARY KEY, > parameter_name character_varying(200) NOT NULL, > ... > ) > > and then store the data in a table referencing it: > > CREATE TABLE series ( > record_id serial PRIMARY KEY, > parameter_id integer REFERENCES parameters, > reading ???? > ... > ) > > but of course, the data type for the parameters may vary, so it's > impossible to assign a data type to the "reading" column. The number of > variables measured by the sensors is quite large and may grow or > decrease over time, and grouping them into subjects (tables) is not > clear, so it's not simple to just assign them to different columns. > > I've been trying to search for solutions in various sources, but am > having trouble finding relevant material. I'd appreciate any advice. > > Cheers, > > -- > Seb > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > If you are not keen on using PostgreSQL, you could have a look at http://opentsdb.net/ That was one project we found interesting when we were faced with a similar problem a couple of years ago. In the end, many other factors made us opt for Cassandra. We started with PostgreSQL. But our requirements included, among others, ability to add new devices/parameters quickly. So the persistence layer was mostly a data sink and we planned to move cleansed/aggregated data to PostgreSQL for analysis. Most of the master data was also in PostgreSQL - devicies, parameters, units.
