Hi All,

I am working on an application that uses climate data stored in a database. The amount of datapoints is quite overwhelming.

We have 64800 cells to cover the whole panet
* for each variable (about 20)
* for each month
* for each year (100 years total)
* for each model scenario (3 total)
* for each model (starting with one but adding more later up to 5-6).
= A whole lot of datapoints.

To keep things manageable and somewhat searchable we had decided to use the following structure.
One table for each model and each scenario for a total of about 15 tables.
Then each table would have a cell ID (so 64800 rows) and a column for each variable. Then all the data for that variable at that cell (12months*100years=1200datapoints) would be stored in a postgres array. By using the array data type we can call up any month easily.

Any other suggestions for the data structure, does the above outlined structure even make sense? Relational databases are not that great at handling multidimensional data. When I read about the array datatype in postrgres it seemed like a way to get it to work.

Bert

What a strange comment! Relational databases are all about handling multidimensional data. Each dimension equates to a column in a relational table, so your data could be stored in a table like this:
I don't know PostgresSQL, but most modern DBMSs have features (not just indexes) to improve performance of queries on large data sets - such as Materialized Views to store pre-computed aggregates.