Store two metric sets long or wide if frequently joined?

Question

Given two similar tables, metrics_a and 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 m00, 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.

Answer 1

If that's the complete situation I would merge metrics_a and metrics_b for considerably smaller storage and considerably faster and simpler queries.

Your example query could then look like this:

SELECT point_id
     , count(*) AS tot_ct
     , :lim AS lim
     , count(a00 <= :lim OR NULL) AS a_ct00
     , count(a01 <= :lim OR NULL) AS a_ct01
     , count(a02 <= :lim OR NULL) AS a_ct02
     -- ,  ... more
     , count((a00 - b00) >= 4.25 AND b00 <= :lim OR NULL) w_ct00
     , count((a01 - b01) >= 4.25 AND b01 <= :lim OR NULL) w_ct01
     , count((a02 - b02) >= 4.25 AND b02 <= :lim OR NULL) w_ct02
     -- ,  ... more
     , avg(a00) AS av_a00, avg(a01) AS av_a01, avg(a02) AS av_a02  -- , ... more
FROM   metrics
WHERE  logdate BETWEEN '2017-06-01' AND '2017-07-31';

• Renamed m00 to a00 and b00 etc. respectively.
• Saves at least 28 bytes (tuple header + item identifier) + 8 - 16 bytes for redundant columns per row. Smaller is faster, too. See:
  • Configuring PostgreSQL for read performance
• No join, faster query.
• Shorter, simpler code.

Asides:

• I shortened the count syntax. Should be about as fast. See:

  • For absolute performance, is SUM faster or COUNT?

• Don't omit the AS keyword for column aliases. See:

  • Aliases with correlated subqueries

• Always use standard ISO 8601 date format. No exceptions. Related:

  • Can't execute dynamic DDL, argument is NULL