5 ms·

While I understand your point, you are quite mistaken if you think that time is just another key. Dealing with time properly requires a concept of point distanc

by ghc 8y ago

While I understand your point, you are quite mistaken if you think that time is just another key. Dealing with time properly requires a concept of point distance, similar to GIS systems requiring 2d distance understanding. You cannot do joins on time with SQL databases unless you want to throw away important data. As an example, in the industry I work in, you may have no readings for days or weeks, and then hundreds of readings from the same sensor. Why? Many systems in industrial environments send new readings only "on-change", and assume the underlying data storage architecture will forward fill to in-between times. This is why the practically ancient time series architecture of data historians still dominates in these use cases. In fact, for many time series functions you actually have to throw away the notion of relational joins to be able to efficiently perform time-preserving joins. Window functions only work in basic use cases with relatively small amounts of data where you're aggregating.

deleted 8y ago
[deleted]
manigandham 8y ago
I'm not sure where the complexity is that you claim, nor what it has to do with data historians? So what if there are missing rows? This doesn't affect the database and any aggregations will work fine. Databases don't "fill-in" data, but you can definitely write whatever SQL you need to create averages and buckets to smooth out results. From reading your website, it seems you're talking about the "last value recorded" as of a certain time, which doesn't seem to be a common query but is totally possible. KDB+ has "asof" joins and others can handle it with window functions using last_value(). We run queries on a table containing 2.7+ trillion rows of data that has no set pattern and infinite cardinality, and results return within seconds. Window functions and joins work without issue. Have you actually tried using a columnstore?
- ced 8y ago
  You don't have "missing rows". For time series A you have a time point at 12:01 AM, a datapoint at 12:02 AM, and another datapoint two weeks later at 5:04 PM. For time series B the times are different. You need some notion of whatever state the physical system was in at any given time.
  - manigandham 8y ago
    Yes, I understand this as the "last value recorded" concept in my comment. KDB+ supports this with "asof" joins. Others can just do it by scanning a wider time frame or the entire table. Null gaps in a columnstore can be skipped over basically instantaneously and usually are just zone map/index lookups. Again I question how common this query is and whether it's really worth limiting yourself to a special TSDB because of it.
    - coder543 8y ago
      > Others can just do it by scanning a wider time frame or the entire table. "Scanning the entire table" for every request to have the last value recorded is rarely a practical option. > KDB+ supports this with "asof" joins. > [...] > Again I question how common this query is and whether it's really worth limiting yourself to a special TSDB because of it. KDB literally markets itself as a time series database. What's the point you're making again? I think TimescaleDB lacks an "asof" function for now, but it makes up for it by having the full power of PostgreSQL for other stuff. Regardless, Time Series databases like KDB and TimescaleDB are useful. EDIT: it looks like TimescaleDB recommends using ORDER BY time DESC LIMIT 1 to get the most recent value for any particular set of sources that you're SELECTing over, which would use indices and be reasonably fast.
      - manigandham 8y ago
        Distributed relational column-oriented databases are best at large data volumes and OLAP queries. KDB+ is one of those, even though they call it a TSDB in marketing terminology because of its FinTech customer base. TimescaleDB is not a TSDB, it's an extension to add automatic partitioning to PostgreSQL tables. Timescale helps Postgres get more performance, but it does not give you the full capabilities of a real distributed column-oriented system. If you must use PostgreSQL though then it's a good compromise. The query you posted does not match the discussion about the last value at a specific instant in time, only the last value ever recorded in the table for that key.
        8y ago
- ghc 8y ago
  Relational databases represent a column/row-oriented architecture. Data historians are a specialized, non-relational, time-oriented architecture. Using time as a key in a relational index implies that only ordering is important, but that is not the case. Distance between points in time is extremely important because time operates on a continuous 1d line and data points are represented at varying distances between each other on that line. Data historians are architected to both preserve this temporal relationship and take advantage of this by eliminating duplicate data, employing temporal compression techniques to be able to store millions of readings per second for years worth or data. > From reading your website My website doesn't have much to do with this because Sentenai isn't a time series database system. I did, however, spend most of my time in research working on temporal data systems, and have been fortunate to collaborate with or learn from researchers who have spent decades solving the unique problems that temporal data presents. What you might consider uncommon for your use cases is extremely common in manufacturing, defense and other areas. There's a decades-old industry around database systems that handle time natively. And while many support SQL as a lingua franca, and some are column stores, they're not relational by any means as they either extend SQL to support time, or limit non-temporal joins to ensure performance. StreamBase, Kdb, Aurora and many other specialized architectures exist because one size does not fit all. Michael Stonebraker, whose work has included StreamBase, Vertica, Tamr, Postgres, Aurora, and many others, famously published this paper about the very problem: https://cs.brown.edu/~ugur/fits_all.pdf https://cs.brown.edu/~ugur/fits_all.pdf . Further reading that might be illuminating: 1. http://cs.brown.edu/research/aurora/vldb03_journal.pdf 2. http://www.cs.rochester.edu/u/james/Papers/AllenFerguson-events-actions.pdf 3. https://books.google.com/books?id=BK6oCAAAQBAJ&pg=PA9&source=gbs_toc_r&cad=4#v=onepage&q&f=false (excerpt)
  - manigandham 8y ago
    Most data/operational historians are separate programs on top of databases, so is that what you're actually talking about? Your papers seem to suggest that. Stonebraker is talking about OLTP vs OLAP. I agree that they are very different scenarios.
    - ghc 8y ago
car10s 8y ago
Vertica has this functionality and it has been there for years. Fully functional database and you can do time series joins, gap filling with linear interpolation or constant. You can define the intervals at what you want the data points. And you can scale from a few gigs to petabytes of data. https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AnalyzingData/TimeSeries/UsingTimeSeriesAnalytics.htm%3FTocPath%3DAnalyzing%2520Data%7CTime%2520Series%2520Analytics%7C_____0 https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/...
- ghc 8y ago
  I agree! Vertica's temporal capabilities are marvelous and the engineers who worked on the planner optimizations for the time extensions are brilliant. There are of course trade-offs to the approach Vertica takes -- look at StreamBase for a very different take on the problem, another Stonebraker project. Any of course historians represent yet another take, optimized for point-in time queries that are native and don't need the processing extensions Vertica uses.

by ghc 8y ago

deleted 8y ago

[deleted]

manigandham 8y ago

I'm not sure where the complexity is that you claim, nor what it has to do with data historians? So what if there are missing rows? This doesn't affect the database and any aggregations will work fine. Databases don't "fill-in" data, but you can definitely write whatever SQL you need to create averages and buckets to smooth out results. From reading your website, it seems you're talking about the "last value recorded" as of a certain time, which doesn't seem to be a common query but is totally possible. KDB+ has "asof" joins and others can handle it with window functions using last_value(). We run queries on a table containing 2.7+ trillion rows of data that has no set pattern and infinite cardinality, and results return within seconds. Window functions and joins work without issue. Have you actually tried using a columnstore?

ced 8y ago
You don't have "missing rows". For time series A you have a time point at 12:01 AM, a datapoint at 12:02 AM, and another datapoint two weeks later at 5:04 PM. For time series B the times are different. You need some notion of whatever state the physical system was in at any given time.
- manigandham 8y ago
  Yes, I understand this as the "last value recorded" concept in my comment. KDB+ supports this with "asof" joins. Others can just do it by scanning a wider time frame or the entire table. Null gaps in a columnstore can be skipped over basically instantaneously and usually are just zone map/index lookups. Again I question how common this query is and whether it's really worth limiting yourself to a special TSDB because of it.
  - coder543 8y ago
    > Others can just do it by scanning a wider time frame or the entire table. "Scanning the entire table" for every request to have the last value recorded is rarely a practical option. > KDB+ supports this with "asof" joins. > [...] > Again I question how common this query is and whether it's really worth limiting yourself to a special TSDB because of it. KDB literally markets itself as a time series database. What's the point you're making again? I think TimescaleDB lacks an "asof" function for now, but it makes up for it by having the full power of PostgreSQL for other stuff. Regardless, Time Series databases like KDB and TimescaleDB are useful. EDIT: it looks like TimescaleDB recommends using ORDER BY time DESC LIMIT 1 to get the most recent value for any particular set of sources that you're SELECTing over, which would use indices and be reasonably fast.
    - manigandham 8y ago
      Distributed relational column-oriented databases are best at large data volumes and OLAP queries. KDB+ is one of those, even though they call it a TSDB in marketing terminology because of its FinTech customer base. TimescaleDB is not a TSDB, it's an extension to add automatic partitioning to PostgreSQL tables. Timescale helps Postgres get more performance, but it does not give you the full capabilities of a real distributed column-oriented system. If you must use PostgreSQL though then it's a good compromise. The query you posted does not match the discussion about the last value at a specific instant in time, only the last value ever recorded in the table for that key.
      - 8y ago
ghc 8y ago
Relational databases represent a column/row-oriented architecture. Data historians are a specialized, non-relational, time-oriented architecture. Using time as a key in a relational index implies that only ordering is important, but that is not the case. Distance between points in time is extremely important because time operates on a continuous 1d line and data points are represented at varying distances between each other on that line. Data historians are architected to both preserve this temporal relationship and take advantage of this by eliminating duplicate data, employing temporal compression techniques to be able to store millions of readings per second for years worth or data. > From reading your website My website doesn't have much to do with this because Sentenai isn't a time series database system. I did, however, spend most of my time in research working on temporal data systems, and have been fortunate to collaborate with or learn from researchers who have spent decades solving the unique problems that temporal data presents. What you might consider uncommon for your use cases is extremely common in manufacturing, defense and other areas. There's a decades-old industry around database systems that handle time natively. And while many support SQL as a lingua franca, and some are column stores, they're not relational by any means as they either extend SQL to support time, or limit non-temporal joins to ensure performance. StreamBase, Kdb, Aurora and many other specialized architectures exist because one size does not fit all. Michael Stonebraker, whose work has included StreamBase, Vertica, Tamr, Postgres, Aurora, and many others, famously published this paper about the very problem: https://cs.brown.edu/~ugur/fits_all.pdf https://cs.brown.edu/~ugur/fits_all.pdf . Further reading that might be illuminating: 1. http://cs.brown.edu/research/aurora/vldb03_journal.pdf 2. http://www.cs.rochester.edu/u/james/Papers/AllenFerguson-events-actions.pdf 3. https://books.google.com/books?id=BK6oCAAAQBAJ&pg=PA9&source=gbs_toc_r&cad=4#v=onepage&q&f=false (excerpt)
- manigandham 8y ago
  Most data/operational historians are separate programs on top of databases, so is that what you're actually talking about? Your papers seem to suggest that. Stonebraker is talking about OLTP vs OLAP. I agree that they are very different scenarios.
  - ghc 8y ago

car10s 8y ago

Vertica has this functionality and it has been there for years. Fully functional database and you can do time series joins, gap filling with linear interpolation or constant. You can define the intervals at what you want the data points. And you can scale from a few gigs to petabytes of data. https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AnalyzingData/TimeSeries/UsingTimeSeriesAnalytics.htm%3FTocPath%3DAnalyzing%2520Data%7CTime%2520Series%2520Analytics%7C_____0 https://www.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/...

ghc 8y ago
I agree! Vertica's temporal capabilities are marvelous and the engineers who worked on the planner optimizations for the time extensions are brilliant. There are of course trade-offs to the approach Vertica takes -- look at StreamBase for a very different take on the problem, another Stonebraker project. Any of course historians represent yet another take, optimized for point-in time queries that are native and don't need the processing extensions Vertica uses.