5 ms·

I have worked extensively with systems addressing the Australian National Energy Market, where data ticks either every five minutes (dispatch of generation) or

I have worked extensively with systems addressing the Australian National Energy Market, where data ticks either every five minutes (dispatch of generation) or thirty minutes (settlement). I’ve often wondered whether a proper TSDB would make my life easier when storing/querying meter and market data, but I always ended up with SQL.

matt2000 7y ago
My guess is that at 5min resolution (288 data points per day), you're probably just better off going stock SQL. My understanding is that time series DBs start getting useful at higher write loads that standard SQL DBs aren't necessarily optimized for, and where you might want things like data to coalesce into larger timeframes to save storage, etc. I'm not an expert though, just what I've seen from my somewhat limited experience.
- akulkarni 7y ago
  (TimescaleDB founder) One of the advantages of TimescaleDB is the additional SQL functions that make time-series manipulation easier (eg interpolation, locf, aggregating by arbitrary time intervals). So if you need any of that functionality I'd recommend taking a look, even if you don't have massive scale.
shereadsthenews 7y ago
At that rate each of your series will occupy << 1MB of space per year. I wonder why specialized tools would be needed for such a tiny problem.
- new4thaccount 7y ago
  Umm no. I'm not an expert of the Australian energy market, but do data analysis for a large energy market and we have petabytes of data and need to run queries against multiple years where a single day of the market generates many GB of data. Still SQL is usually my go-to tool, but we have a Time series database for other tasks that is helpful when just looking at a few data points.
  - shereadsthenews 7y ago
    If observations are spaced every five minutes and are high-precision (8 bytes each) that’s only 2KB per day of data, which can be greatly reduced for most real data by delta-encoding adjacent observations. If you have many GB per day then you must have millions of such series, if not then what you meant was you’ve wasted a tremendous amount of disk storing a negligible quantity of data in sparse btrees.
    - new4thaccount 7y ago
      Neither. The original poster doesn't point out that they likely store a lot lot more kinds of data. I think your math probably makes sense off of just two data points, but the below should help you get a better idea for the size of the data. Keep in mind that all large grid operators and utilities are probably in the same boat. This holds for America to China. A single generator has well over a hundred properties you would care about and although we're talking about 5 minute data, there are many many overlapping simulations being run around the clock and many of those simulations look into the future for many days. Then you have load data, topology data, equipment outages, load and wind forecast, thousands of pricing points that can be further decomposed into constituent prices, changing bids and offers, energy transactions...I could type for the next hour and only cover a small amount of what I know which is still a small percentage of what we actually store. From a time-series perspective, a single generator can show well over a hundred line-charts and you can do that off of multiple study types...and you might have well over a thousand generators in your market, not to mention all the other logged objects such as transmission lines which also have lots of properties logged. You're right that time-series data can be compressed, but the SQL data is somewhat different I think. In short, energy markets are hugely complex creatures. There is just so much going on that I have yet to get bored and have a non-busy day in nearly a decade of employment. It's an extremely exciting field for sure as there is just so much going on and so much change occurring. In the not so distant future, many experts think the lines between transmission and distribution will become blurred and we'll all need even more data to run the grid at peak efficiency and reliability.

matt2000 7y ago

My guess is that at 5min resolution (288 data points per day), you're probably just better off going stock SQL. My understanding is that time series DBs start getting useful at higher write loads that standard SQL DBs aren't necessarily optimized for, and where you might want things like data to coalesce into larger timeframes to save storage, etc. I'm not an expert though, just what I've seen from my somewhat limited experience.

akulkarni 7y ago
(TimescaleDB founder) One of the advantages of TimescaleDB is the additional SQL functions that make time-series manipulation easier (eg interpolation, locf, aggregating by arbitrary time intervals). So if you need any of that functionality I'd recommend taking a look, even if you don't have massive scale.

shereadsthenews 7y ago

At that rate each of your series will occupy << 1MB of space per year. I wonder why specialized tools would be needed for such a tiny problem.

new4thaccount 7y ago
Umm no. I'm not an expert of the Australian energy market, but do data analysis for a large energy market and we have petabytes of data and need to run queries against multiple years where a single day of the market generates many GB of data. Still SQL is usually my go-to tool, but we have a Time series database for other tasks that is helpful when just looking at a few data points.
- shereadsthenews 7y ago
  If observations are spaced every five minutes and are high-precision (8 bytes each) that’s only 2KB per day of data, which can be greatly reduced for most real data by delta-encoding adjacent observations. If you have many GB per day then you must have millions of such series, if not then what you meant was you’ve wasted a tremendous amount of disk storing a negligible quantity of data in sparse btrees.
  - new4thaccount 7y ago
    Neither. The original poster doesn't point out that they likely store a lot lot more kinds of data. I think your math probably makes sense off of just two data points, but the below should help you get a better idea for the size of the data. Keep in mind that all large grid operators and utilities are probably in the same boat. This holds for America to China. A single generator has well over a hundred properties you would care about and although we're talking about 5 minute data, there are many many overlapping simulations being run around the clock and many of those simulations look into the future for many days. Then you have load data, topology data, equipment outages, load and wind forecast, thousands of pricing points that can be further decomposed into constituent prices, changing bids and offers, energy transactions...I could type for the next hour and only cover a small amount of what I know which is still a small percentage of what we actually store. From a time-series perspective, a single generator can show well over a hundred line-charts and you can do that off of multiple study types...and you might have well over a thousand generators in your market, not to mention all the other logged objects such as transmission lines which also have lots of properties logged. You're right that time-series data can be compressed, but the SQL data is somewhat different I think. In short, energy markets are hugely complex creatures. There is just so much going on that I have yet to get bored and have a non-busy day in nearly a decade of employment. It's an extremely exciting field for sure as there is just so much going on and so much change occurring. In the not so distant future, many experts think the lines between transmission and distribution will become blurred and we'll all need even more data to run the grid at peak efficiency and reliability.