1

Is there any way to do sampling on time series data in PostgreSQL (v11) based on a fixed time interval?

For example, we are storing 1 record per second for 24 hours in a given table. This means approximately 86400 records are in the table per day.

Is there any simpler way to retrieve a data point for every 10 seconds of data? The query should return 8640 records.

Currently, I have implemented a solution using a window function. But for larger data sets it is taking too much time.

Current implementation

SELECT
message_date,
message
FROM (
    SELECT ROW_NUMBER() OVER (
        PARTITION BY date_trunc('minute', message_date)
        ORDER BY message_date DESC
    ) AS r, t.*
    FROM rdm.telemetry_2021_01_21 t
    WHERE device_id = 'CP2'
    AND message_date >= '1/21/2021 01:00:00 AM'
    AND message_date <= '1/21/2021 11:00:00 PM'
) x
WHERE x.r <= 1 ORDER BY message_date DESC;

The above solution supports sampling for 1 sec, 1 min or 1 hr depending on which values is provided in the date_trunc options. And also, this query takes too much time against a table that contains millions of records.

"Explain analysis" result

EXPLAIN plan

Can anyone please suggest some other option/solution? Maybe using lateral join?

5
  • You mean the table has millions of lines, or the query will return millions of lines? Also can you post EXPLAIN ANALYZE?
    – bobflux
    Feb 11, 2021 at 14:55
  • "So much time" being what exactly? Is that for everything, or just a specific connection_ids?
    – user212533
    Feb 11, 2021 at 15:22
  • @bobflux Table has millions of rows (1 per second). But, it should return sampled data. Like every minute first record, or every 5 min first record. So, lets say out of 1 million record it should return only few hundreds records. (16666 for 1 min sampling or 3333 records for 5 min sampling etc..).
    – Yash Mochi
    Feb 12, 2021 at 4:44
  • @bbaird Can you please elaborate your question?
    – Yash Mochi
    Feb 12, 2021 at 4:46
  • 1
    Note that images are discouraged for textual information. We cannot easily copy/paste/search it. Plus, links are subject to bitrot. Feb 12, 2021 at 9:13

2 Answers 2

2

Basic query to sample data for arbitrary fixed time intervals:

SELECT grid_time, message_date, message
FROM   generate_series(timestamp '2021-01-21 01:00:00'   -- always use unambiguous ISO format
                     , timestamp '2021-01-21 11:00:00'
                     , interval '73 minutes') grid_time  -- arbitrary time interval
LEFT   JOIN LATERAL (
   SELECT message_date, message
   FROM   rdm.telemetry_2021_01_21
   WHERE  device_id = 'CP2'
   AND    message_date >= grid_time
   AND    message_date <  grid_time + interval '73 minutes'  -- same interval
   ORDER  BY message_date
   LIMIT  1
   ) t ON true;

Support this with a multicolumn index on (device_id, message_date).

For a time interval of 10 seconds like you mentioned, you don't need an index. This would fetch 10 % of all rows, and Postgres will typically default to a sequential scan as that is faster. (Exceptions with index-only scans apply.)

The added filter device_id = 'CP2' can change a lot. But that's only in your query, not in your expressed requirements.

An index typically only helps performance for filters retrieving a few percent of all rows or less. So like 30 seconds or more in your example. For selective filters, an index can help a lot.

Further reading:

1
  • Thanks a lot man. You saved my day.
    – Yash Mochi
    Feb 12, 2021 at 10:38
1

Depending on the size of your dataset querying over a 'large' interval may not use an index (as your explain shows), there are several options I'd try:

  1. Generate a series over your 'outer interval' (01hr - 11hr) splitted by your sampling interval (1m, 1hr, etc), idea:
select  (
        query and group rdm.telemetry
        where   message_date >= a and
                message_date <= a + '1 min'
) as x
from    generate_series('today 01:00'::timestamp, 'today 11:00'::timestamp, '1 min') as s(a);

This would have the effect generating lots of inner loops that operate over shorter intervals and are better candidates for index scans, also would have the side effect of showing you '0' for time ranges where there are no rows instead of holes. You could remove them later if undesirable.

  1. A version of previous but you could add an expression index on date_trunc('minute', message_date);
create index on tbl ((date_trunc('minute', message_date)));

And query by equality using that value.

Of course you'll need to try different versions and sampling intervals to find sweet spots, the larger the interval/sampling interval the less likely the query planner would choose and index scan.

There's more info about cost analysis in EXPLAIN DOCS

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.