3 ms·

I don't understand what is difficult or non trivial about these types of databases and when people try to explain it, it usually just gets more confusing. Filt

I don't understand what is difficult or non trivial about these types of databases and when people try to explain it, it usually just gets more confusing. Filtering values over time is just the same operations that you would find in an audio editor or a one dimensional version of what you find in an image editor (weighted averages of values). I wonder how many customers could just use sqlite but don't know anything about computers and end up buying some sort of subscription to a 'new kind of database'. The web page just drops as many buzzwords as possible - web3, crypto, nfts, monitor soil to fight global warming - it looks like a disaster to anyone who understands the basics of programming.

cleancoder0 5y ago
SQLite does not support column based optimizations. Time series data is insanely compressible.
deleted 5y ago
[deleted]
jacobr1 5y ago
If your data is small enough, then sure, any number of well tested data platforms will work for you. The problem something like timescale tries to solve is dealing with "high cardinality." When you have many unique values the indexing approaches needed to ensure performance start becoming different. You'll run into write performance issues if need indexes on every single column, and every single combination of columns, and each column has a large number of unique values. While the common factor many of these datasets tend to share is that they are being constantly generated by some kind of sensor/probe/live-system, they tend to have a variety of other dimensions that are also high-cardinality.
- CyberDildonics 5y ago
  There are two different things here - the first is people not needing an elaborate solution because computers are fast and the second is that if someone does need a solution with less overhead, why is that difficult? Values over time like audio is a one dimensional signal. Seeking is basic data structure stuff, filtering is basic signal stuff. There aren't going to be other dimensions like time, which makes the other values just other channels. If you need to combine dense values they can be not only filtered, but filtered into individual distributions. People give abstract descriptions like you have here, but I'm just not seeing a difficult problem in all of this.
lopatin 5y ago
> I don't understand what is difficult or non trivial about these types of databases Boy were you right about that
- beanjuiceII 5y ago
  promote that person to management
- CyberDildonics 5y ago
  I think if you had something to say you would have said it already. There are people who actually replied with something worthwhile.
  - ctvo 5y ago
    > There are people who actually replied with something worthwhile. And you dismissed it as still not being difficult. The Timescale folks did all the hard work of finding product / market fit for you. If this problem is so trivial, you'd make a faster, cheaper, more reliable product and steal their lunch. Not only their lunch but Clickhouse, AWS Timestream, etc..
    - CyberDildonics 5y ago
      No, I said I don't understand why it isn't trivial or if most of the people buying it don't realize they don't need a specialized database. Only one person was able to come up with anything, other people, including you just go extremely upset at the question without being able to answer it. The number that stood out the most was 700 million values per second, which is about 11.6 million per second. This is still less than the data in a single 4k image. My guess is that none of this is a feat of engineering, but that it's just enough work that many people that need it won't create their own solution.
jtlisi 5y ago
Have you read the gorilla TSDB paper? https://www.vldb.org/pvldb/vol8/p1816-teller.pdf https://www.vldb.org/pvldb/vol8/p1816-teller.pdf It does a good job laying out why TSDBs are used and some of the tricks they leverage to store this type of data. See the requirements for the service layed out in the paper: • 2 billion unique time series identified by a string key. • 700 million data points (time stamp and value) added per minute. • Store data for 26 hours. • More than 40,000 queries per second at peak. • Reads succeed in under one millisecond. • Support time series with 15 second granularity (4 points per minute per time series). • Two in-memory, not co-located replicas (for disaster recovery capacity). • Always serve reads even when a single server crashes. • Ability to quickly scan over all in memory data. • Support at least 2x growth per year Lots of organizations want to adopt an SRE/devops model and want a similar system. Also one thing you should know is that trying to accomplish this with traditional DBMS is usually possible but since it is not making specifically optimized trade offs it usually is more expensive and requires a lot of tuning/expertise. Lots of organizations (even legacy companies) have a massive need for this kind of service. Also there are very cheap options out there than can handle the million metric use case for basically a <100$ a month is infra costs. The use case is definitely there and even if it's possible with traditional DBMS systems, it usually cheaper and more performant to use a dedicated TSDB.
manigandham 5y ago
Relational databases are historically either OLTP like Postgres, MySQL, SQLite, etc; or OLAP like Vertica, Clickhouse, Greenplum, Redshift, etc. The latter group is designed to analyze lots of data (calculating aggregations across billions of rows) and have developed features like storing data as columns, using compression, batch/vectorized processing, scaling out across multiple servers, and other techniques to get that performance. Timescale is an extension to Postgres that brings these capabilities to Postgres and is one of a very few relational databases that offer OLTP+OLAP in a single product. The time-series niche is what they targeted first, and the product offers lots of useful features around time-related data, but it's also a generic analytical database offering at this point.

cleancoder0 5y ago

SQLite does not support column based optimizations. Time series data is insanely compressible.

deleted 5y ago

[deleted]

jacobr1 5y ago

If your data is small enough, then sure, any number of well tested data platforms will work for you. The problem something like timescale tries to solve is dealing with "high cardinality." When you have many unique values the indexing approaches needed to ensure performance start becoming different. You'll run into write performance issues if need indexes on every single column, and every single combination of columns, and each column has a large number of unique values. While the common factor many of these datasets tend to share is that they are being constantly generated by some kind of sensor/probe/live-system, they tend to have a variety of other dimensions that are also high-cardinality.

CyberDildonics 5y ago
There are two different things here - the first is people not needing an elaborate solution because computers are fast and the second is that if someone does need a solution with less overhead, why is that difficult? Values over time like audio is a one dimensional signal. Seeking is basic data structure stuff, filtering is basic signal stuff. There aren't going to be other dimensions like time, which makes the other values just other channels. If you need to combine dense values they can be not only filtered, but filtered into individual distributions. People give abstract descriptions like you have here, but I'm just not seeing a difficult problem in all of this.

lopatin 5y ago

> I don't understand what is difficult or non trivial about these types of databases Boy were you right about that

beanjuiceII 5y ago
promote that person to management
CyberDildonics 5y ago
I think if you had something to say you would have said it already. There are people who actually replied with something worthwhile.
- ctvo 5y ago
  > There are people who actually replied with something worthwhile. And you dismissed it as still not being difficult. The Timescale folks did all the hard work of finding product / market fit for you. If this problem is so trivial, you'd make a faster, cheaper, more reliable product and steal their lunch. Not only their lunch but Clickhouse, AWS Timestream, etc..
  - CyberDildonics 5y ago
    No, I said I don't understand why it isn't trivial or if most of the people buying it don't realize they don't need a specialized database. Only one person was able to come up with anything, other people, including you just go extremely upset at the question without being able to answer it. The number that stood out the most was 700 million values per second, which is about 11.6 million per second. This is still less than the data in a single 4k image. My guess is that none of this is a feat of engineering, but that it's just enough work that many people that need it won't create their own solution.

jtlisi 5y ago

Have you read the gorilla TSDB paper? https://www.vldb.org/pvldb/vol8/p1816-teller.pdf https://www.vldb.org/pvldb/vol8/p1816-teller.pdf It does a good job laying out why TSDBs are used and some of the tricks they leverage to store this type of data. See the requirements for the service layed out in the paper: • 2 billion unique time series identified by a string key. • 700 million data points (time stamp and value) added per minute. • Store data for 26 hours. • More than 40,000 queries per second at peak. • Reads succeed in under one millisecond. • Support time series with 15 second granularity (4 points per minute per time series). • Two in-memory, not co-located replicas (for disaster recovery capacity). • Always serve reads even when a single server crashes. • Ability to quickly scan over all in memory data. • Support at least 2x growth per year Lots of organizations want to adopt an SRE/devops model and want a similar system. Also one thing you should know is that trying to accomplish this with traditional DBMS is usually possible but since it is not making specifically optimized trade offs it usually is more expensive and requires a lot of tuning/expertise. Lots of organizations (even legacy companies) have a massive need for this kind of service. Also there are very cheap options out there than can handle the million metric use case for basically a <100$ a month is infra costs. The use case is definitely there and even if it's possible with traditional DBMS systems, it usually cheaper and more performant to use a dedicated TSDB.

manigandham 5y ago

Relational databases are historically either OLTP like Postgres, MySQL, SQLite, etc; or OLAP like Vertica, Clickhouse, Greenplum, Redshift, etc. The latter group is designed to analyze lots of data (calculating aggregations across billions of rows) and have developed features like storing data as columns, using compression, batch/vectorized processing, scaling out across multiple servers, and other techniques to get that performance. Timescale is an extension to Postgres that brings these capabilities to Postgres and is one of a very few relational databases that offer OLTP+OLAP in a single product. The time-series niche is what they targeted first, and the product offers lots of useful features around time-related data, but it's also a generic analytical database offering at this point.