4 ms·

Dumb question: why can't I just insert a bunch of rows with a timestamp column and indices? Where does that fall short? At a certain # of rows or something? Wh

Dumb question: why can't I just insert a bunch of rows with a timestamp column and indices? Where does that fall short? At a certain # of rows or something? What does this let me do that can't be achieved with "regular PostgreSQL without the extension"?

gonzo41 2y ago
Time based partitioning.
- MuffinFlavored 2y ago
  CREATE TABLE logs ( id SERIAL PRIMARY KEY, log_time TIMESTAMP NOT NULL, message TEXT ) PARTITION BY RANGE (log_time); Why won't this work on stock PostgreSQL?
  - gonzo41 2y ago
    read the docs, it's not saying that won't work. This extension along with timescale just makes some things more ergonomic.
    - darkstar_16 2y ago
      And provides helper functions to deal with regular time series tasks
  - jmaker 2y ago
    That won’t work already because your timestamp isn’t part of your primary key.
  - kbolino 2y ago
    I think what's meant here is windowing (partitioning the query) not partitioning the table per se. Though even with this strategy, you must manually create new partitions all the time. This also isn't typical time-series data, which generally stores numbers. Supposing you had a column "value INTEGER" as well, how do you do something like the following (pseudo-SQL)? SELECT AVG(value) AS avg_value FROM logs GROUP BY INTERVAL '5m' Which should output rows like the following, even if the data were reported much more frequently than every 5 minutes: log_time | avg_value 2024-05-20T00:00:00Z | 10.3 2024-05-20T00:05:00Z | 7.8 2024-05-20T00:10:00Z | 16.1
    - ants_a 2y ago
      SELECT date_bin('5 minutes', log_time, '2000-01-01') log_time, AVG(value) avg_value FROM logs GROUP BY 1
skibbityboop 2y ago
I'm with you, I need to read up more on where timeseries could benefit, at work we have a PostgreSQL instance with around 27 billion rows in a single partitioned table, partitioned by week. Goes back to January of 2017 and just contains tons of data coming in from sensors. It's not "fast", but also not ridiculously slow to say e.g. "Give me everything for sensor 29380 in March of 2019". I guess depends on your needs but I do think I need to investigate timeseries more to see if it'd help us.
- klaussilveira 2y ago
  Same boat here, but with DSP/SSP bidding statistics. Generating around 1 billion rows a day and still going strong. Single table, partitioned by week. BRIN index on timestamp, normal index on one column. Postgres is just a beast.
- Too 2y ago
  Now give me a plot with the average of all sensors of model=A in region=B, grouped by customer, for the past 3 months, downsampled to 500 points. Assuming 1 sensor reading per minute. I have no doubt sql can do it without too much trouble, but for a time series this is really an instant operation, even on a small server. A time series will first find the relevant series and then simply for-loop through all the data. It takes just a handful of milliseconds. Sql will need to join with other tables, traverse index, load wider columns. And you better have set the correct index first, in your case you also spent extra effort on partitioning tables. Likely you are also using a beefy server.
  - defrost 2y ago
    Adding to your comment, from my perspective (exploration geophysics) > and just contains tons of data coming in from sensors. it's also desired to, on the fly, deal with missing sensor data, clearly bad sensor data, identify and smooth spikes in data (weird glitch or actual transient spike of interest), apply a variety of running filters; centred average is basic, parameterised Savitzky–Golay filters provide "beefed up" better than running average handling .. and there are more. It's not just better access to sequential data that makes a dedicated time series engine desirable, it's the suite of time (and geo spatial) series operations that close the deal.
citizen_friend 2y ago
It will work just fine.
ishikawa 2y ago
there are several good articles explaining this, especially on Timescable blog, but in short, without time partitioning and just index, at some given point the performance for reads and writes degrades exponencially.

gonzo41 2y ago

Time based partitioning.

MuffinFlavored 2y ago
CREATE TABLE logs ( id SERIAL PRIMARY KEY, log_time TIMESTAMP NOT NULL, message TEXT ) PARTITION BY RANGE (log_time); Why won't this work on stock PostgreSQL?
- gonzo41 2y ago
  read the docs, it's not saying that won't work. This extension along with timescale just makes some things more ergonomic.
  - darkstar_16 2y ago
    And provides helper functions to deal with regular time series tasks
- jmaker 2y ago
  That won’t work already because your timestamp isn’t part of your primary key.
- kbolino 2y ago
  I think what's meant here is windowing (partitioning the query) not partitioning the table per se. Though even with this strategy, you must manually create new partitions all the time. This also isn't typical time-series data, which generally stores numbers. Supposing you had a column "value INTEGER" as well, how do you do something like the following (pseudo-SQL)? SELECT AVG(value) AS avg_value FROM logs GROUP BY INTERVAL '5m' Which should output rows like the following, even if the data were reported much more frequently than every 5 minutes: log_time | avg_value 2024-05-20T00:00:00Z | 10.3 2024-05-20T00:05:00Z | 7.8 2024-05-20T00:10:00Z | 16.1
  - ants_a 2y ago
    SELECT date_bin('5 minutes', log_time, '2000-01-01') log_time, AVG(value) avg_value FROM logs GROUP BY 1

skibbityboop 2y ago

I'm with you, I need to read up more on where timeseries could benefit, at work we have a PostgreSQL instance with around 27 billion rows in a single partitioned table, partitioned by week. Goes back to January of 2017 and just contains tons of data coming in from sensors. It's not "fast", but also not ridiculously slow to say e.g. "Give me everything for sensor 29380 in March of 2019". I guess depends on your needs but I do think I need to investigate timeseries more to see if it'd help us.

klaussilveira 2y ago
Same boat here, but with DSP/SSP bidding statistics. Generating around 1 billion rows a day and still going strong. Single table, partitioned by week. BRIN index on timestamp, normal index on one column. Postgres is just a beast.
Too 2y ago
Now give me a plot with the average of all sensors of model=A in region=B, grouped by customer, for the past 3 months, downsampled to 500 points. Assuming 1 sensor reading per minute. I have no doubt sql can do it without too much trouble, but for a time series this is really an instant operation, even on a small server. A time series will first find the relevant series and then simply for-loop through all the data. It takes just a handful of milliseconds. Sql will need to join with other tables, traverse index, load wider columns. And you better have set the correct index first, in your case you also spent extra effort on partitioning tables. Likely you are also using a beefy server.
- defrost 2y ago
  Adding to your comment, from my perspective (exploration geophysics) > and just contains tons of data coming in from sensors. it's also desired to, on the fly, deal with missing sensor data, clearly bad sensor data, identify and smooth spikes in data (weird glitch or actual transient spike of interest), apply a variety of running filters; centred average is basic, parameterised Savitzky–Golay filters provide "beefed up" better than running average handling .. and there are more. It's not just better access to sequential data that makes a dedicated time series engine desirable, it's the suite of time (and geo spatial) series operations that close the deal.

citizen_friend 2y ago

It will work just fine.

ishikawa 2y ago

there are several good articles explaining this, especially on Timescable blog, but in short, without time partitioning and just index, at some given point the performance for reads and writes degrades exponencially.