Given two similar tables,
metrics_b, PostgreSQL 9.6:
create table metric_a ( logdate date not null; point_id int4 not null; m00 numeric(9,5) not null; ... m23 numeric(9,5) not null; CONSTRAINT metric_a_pk PRIMARY KEY (logdate, point_id) ); create index metric_a_point_idx on metric_a (point_id);
Each with about 150,000
point_id values with hourly measurements into
m01, etc. for 24 hours a day. That's about 55 million rows per year per table, and we have 10 years of data so far. There will always be only 2 metrics, a and b.
I assume I should partition by date range, which I'm working on. But in general, for queries where metrics like this are joined for at least 30 days of data, is it better to store them as:
- one wide table -- 48 hourly columns, 24 for each metric
- one "half-wide" table -- a point_id column and 24 hourly columns
- one long table -- logdate, point_id, hour, metric_value
- two tables, wide or long?
- range partitioned by month, week, or metric? long or wide?
Of course the long table version is by far the easiest to deal with. It's much simpler to write queries to total the whole day, or compare several hours' metrics against each other. However, the table size for the long version could easily total over 13 billion rows with very inefficient use of storage.
I've tried using two wide tables and one combined 48-column wide table (neither partitioned), with little difference in performance.
Current queries look like this:
select a.point_id, count(*) tot_ct, :lim as lim, count(case when a.m00 <= :lim then 1 end) a_ct00, count(case when a.m01 <= :lim then 1 end) a_ct01, count(case when a.m02 <= :lim then 1 end) a_ct02, ... count(case when (a.m00 - b.m00) >= 4.25 and b.m00 <= :lim then 1 end) w_ct00, count(case when (a.m01 - b.m01) >= 4.25 and b.m01 <= :lim then 1 end) w_ct01, count(case when (a.m02 - b.m02) >= 4.25 and b.m02 <= :lim then 1 end) w_ct02, ... avg(a.m00) av_a00, avg(a.m01) av_a01, avg(a.m02) av_a02, ... ... from metrics_a a, metrics_b b where a.logdate = b.logdate and a.point_id = b.point_id and a.logdate between '6/1/2017' and '7/31/2017'
The query above and most anything similar to it takes about 8 minutes to run. That's not too bad except I need to run several of these in a batch for different values of
:lim and the
4.25 value and then combine and filter the results (currently in Python).
I can provide more details, but this post is already very long. Thanks for any help.