Ahhhh ... I hadn't thought of that solution. So, instead of a wide table with a million rows, I'd have a narrow table with 8.7 billion rows. I have to admit, the idea of "billions and billions" of rows (with a nod to Carl Sagan) sort of scares me, but that's probably my problem, not postgre's problem.

Since the current Site ID is a long character string, I imagine a Site ID table should be created with an auto-increment index....

To clarify, I have a million sets of data, and each set contains 8760 values. This is hourly data for an entire year for 1 million sites.

The question is, how to efficiently store the data in a way that allows me to quickly "view" the data for particular set of sites? My application needs to load all the hourly data for a subset of rows. The simplest thing to do would be to store the data in a table with 8760 columns, one...

I'm not that experienced with database design and I cannot figure out the best way to design this. I have annual hourly data (8760 values) for a million sites. The total size of the data is not a problem, but how would one store the data such that I can manipulate annual sets of data?

I often read that designing tables with 100s of columns is a mistake. Clearly, I cannot store the data as I would in a program, which is in an array...