Search Postgresql Archives

SQL - planet redundant data

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I have ~500,000 data files each containing ~1,000 records that I want to put into a database for easy access. Fictive example for illustration: File w. meteorological data from a given station.

stat_id |  yr  | d_o_y | hr | mn | tmp  | wind
---------|------|-------|----|----|------|------
   78   | 2001 |  211  | 14 |  0 | 15.3 |  4.7
   78   | 2001 |  211  | 14 |  1 | 15.4 |  5.4
   78   | 2001 |  211  | 14 |  2 | 15.4 |  5.0
   ..   | .... |  ...  | .. |  . | .... |  ...
   78   | 2001 |  211  | 14 | 24 | 15.8 |  2.7

Some parameters are constant for any file, e.g. station id. Others are constant for a varying percentage of the files (hr~58%, d_o_y~98%, yr~99.995% due to the oddly chosen 25 min. pr. file)

It seems like a table with file summaries (1 row for each file) would be very useful. Some of the columns/rows could be:

   file     | st_id |  yr  | d_o_y | avg_tmp | std_dev_tmp
-------------|-------|------|-------|---------|--------------
78_f6548.dat |   78  | 2001 |  211  |   15.5  |    0.24
57_f4296.dat |   57  | 2000 |   -1  |   8.3   |    0.11
   ...      |  ...  |  ... |  ...  |   ...   |    ...
(-1 for day of year indicates an odd file spanning across midnight)

To store the detailed records the SQL novice would construct one table pr. file and exclude any constant columns since these are redundant (given in summary table). These detailed tables would then have different column schemas - (mn,tmp,wind) for ~58%, (hr,mn,tmp,wind) for ~40%, (d_o_y,hr,mn,tmp,wind) for ~2% and (yr,d_o_y,hr,mn,tmp,wind) for ~0.005%.

SQL will discourage this novice. To enable simple queries data tables must be combined. Say I want to find all summer data with high temperature standard deviation and low wind. If I made the ultimate table for easy queries, containing all columns for both detailed data and file summary, I could find the data like this:

SELECT file, std_dev_tmp, tmp, wind FROM huge_table WHERE std_dev_tmp>0.2 AND d_o_y>150 AND d_o_y<240 AND wind<4.0

The ability to make this easy query comes with a ridiculous amount of redundant data in huge_table. In order to remove all the stuff that is already given by the summary table I must split the huge table into a number of tables; one for each of above mentioned column schemas (in my case many more than in the example above). Difficult to build and no easy queries anyway - I'd need to write a C program to launch the many queries that would in effect return the same data as the single query above. Defeats the purpose of easy, efficient access.

This novice must be missing a sneaky way to avoid massive redundancy and still maintain easy access. I've been suggested to look at inheritance and foreign keys. Foreign keys I don't see how to use, but I could make an inheritance hierarchy of the split up tables to make the parent table columns (mn,tmp,wind in above example) available across tables. But accessing the rest of the columns still require child-table specific queries, and my many child tables will not be nicely nested as in above example, so I still don't see how it can get me there. I could really use some input.

One thought: PostgreSQL adds to every table a system column tableoid containing a constant value. Is that value really stored 1,000 times for a 1,000-row table? Or...?

(Apologies for the length of this post)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux