[postgres] calculating on metrics from multiple records

Grafana PostgreSQL
1

My postgres database has a table with records from multiple sources. The schema looks like this:

time   | source  | bid | ask
12:01  | bitmex  | 10  | 11
12:01  | deribit | 10  | 11
12:02  | bitmex  | 9   | 11

The exact times per source are different. I’m able to plot different lines for every source:

20
201518×542 75.8 KB

I’m looking to plot the difference (grouped per minute) of two different sources. I think I need to select grouped minutes where “source = x” and the same where “source = y” and subtract those, while keeping the time macros in there to keep the whole thing speedy. Can anyone point me in the right direction?

2

The closest I’ve come so far is with this query:

SELECT
    a.bid - b.bid AS difference,
    a.time
FROM
(
    SELECT
        $__timeGroupAlias("time",$__interval,previous),
        avg(bid) AS "bid"
      FROM bbo_20s
      WHERE
        $__timeFilter("time") AND
        market = 3
      GROUP BY 1
      ORDER BY 1
)
    a
CROSS JOIN
(
    SELECT
      $__timeGroupAlias("time",$__interval,previous),
      avg(bid) AS "bid"
    FROM bbo_20s
    WHERE
      $__timeFilter("time") AND
      market = 2
    GROUP BY 1
    ORDER BY 1
)
    b;

However the query is very slow and the result looks strange:

09
092818×496 37.1 KB

I have more experience with grafana & prometheus where these adhoc calculations are quick and easy. I can’t imagine this being that hard with postgres? Would it be easier with TimescaleDB?

3

Create materialized view from your query and use that view in Grafana. Performance should be better.

4

Besides the performance, I’m unable to get it to display proper results.

I would like to use grafana to do adhoc queries and see how they change over time. Is it possible in grafana plot the difference between two queries? eg. I enter two queries and Grafana plots the difference between them.

5

Calculation of difference in Grafana is not good idea, because all calculations must be executed in the browser. That is fine for small dataset, but you will reach browser limits for bigger datasets.

6

That’s a very good point. I followed your suggestion and was able to figure it out: After messing around with different queries for a few hours (UNION, LITERAL, subqueries, WITH nested queries). I was able to create a single query that is fast and returns exactly the result I’m looking for. Here is the final query:

SELECT (first / second * 10000 - 10000) as spread, time
FROM 
  (
    SELECT   $__timeGroupAlias("time",$__interval,previous), avg(bid) AS "first"
    FROM     bbo_20s
    WHERE    $__timeFilter("time") AND market = 1
    GROUP BY time
  ) AS e
FULL JOIN 
  (
  SELECT   $__timeGroupAlias("time",$__interval,previous), avg(bid) AS "second"
  FROM     bbo_20s
  WHERE    $__timeFilter("time") AND market = 2
  GROUP BY time
  ) AS c 
USING ("time") ;

Plopping that into grafana will generate a chart like this:

51
511406×564 47.3 KB

It shows the difference in values in basepoints (0.001%).

1 Like