4 ms·

I'm surprised that it has taken so long for time series databases to take off. I remember in 2015 I wrote an application to wedge time-series features into MyS

I'm surprised that it has taken so long for time series databases to take off. I remember in 2015 I wrote an application to wedge time-series features into MySQL because the only option I could find for a native db was paid.

mulmen 6y ago
My day job relies heavily on Amazon Redshift. Most of our fact tables are sorted by date and almost always a locale. I wrote a Python script to delete the stale records based on that time key. We have a separate vacuum service that cleans up the mess continuously. Is this considered a time series database? Or is there something a dedicated time series database does differently?
- manigandham 6y ago
  Time-series databases themselves are usually key/value or relational. The "time-series" features come from the focus around time as a primary component of the data (or some kind of increasing numeric value). Features like automatic partitioning and sharding, better compression, aggregating into time buckets, extracting and managing date values, smoothing missing values in queries, etc.
  - deleted 6y ago
    [deleted]
  - mulmen 6y ago
    Redshift does a lot of that. Is there something unique to a "time series" database that Redshift does not do? Is it the inclusion of some ETL capabilities? I'm not sure what makes a database truly time-series. It sounds like there is some compromise made that requires a time dimension. I'm not sure what that compromise would be beyond the general case of a columnar database.
    - manigandham 6y ago
      Nothing unique. Any database can store "time-series" data. If you look at InfluxDB (key/value) or TimescaleDB (relational), they offer things like high precision time values, higher compression algorithms that work better with timestamps, utilities to make it easier to query for time (buckets, timezones, gap-filling), automatic rollups and aggregations, data deletions based on time, etc. You can do all that yourself on Redshift but they just offer more built-in functionality for it.
      - akulkarni 6y ago
        Exactly what mani says. For many people, Redshift, or just plain Postgres, is good enough. But if you find yourself wresting with poor performance, slow queries, high costs, or just an annoying developer experience, I'd encourage you to look at time-series databases (eg TimescaleDB, where I work).
- mywittyname 6y ago
  I tend to think of them as databases where rows are an aggregate count of events over a period of time. An overly simplistic example being an API that captures upvotes. Instead of storing the individual upvotes, you incoming requests in a queue and only write out a count of upvotes over, say 1 minute. That way, if you want to get a count over a larger period of time, you're setting a ceiling on the number of records involved in your operation. If you have 1 minute resolution and you're looking over a year of data, you're adding at most, 6024366 records for each post. This is really handy for analytics data where you don't necessarily care about the individual records. The project I was specifically referencing was one that captured data streams from sensors like accelerometers and thermometers, which is inherently time-series data, because it's literally just (timestamp, sensor-reading). But to make use of that, you need a library of tools that understand that the underlying data is a time-series to do things like smoothing data, or highlight key events. For example, a torque spike had a "signature" which involved looking at the difference over time periods at a particular resolution. But a temperature spike would look different. Etc.
  - mulmen 6y ago
    I see, thanks for the explanation. Is aggregation in general a required feature of a time-series database? Or specifically the ability to calculate aggregates continuously over changing (incoming?) data? Do time series databases store the raw underlying data for the aggregates or just enough to calculate the next window?
- fnord123 6y ago
  You could have made a timeseries db. There's nothing magic about it. It's just a collection of engineering tradeoffs where you throw away relational features and indexes on various fields, instead focusing on performance for the huge number of events coming into an append only system (you can update but it's super rare).
  - manigandham 6y ago
    If you build it on a modern relational data warehouse then you don't have to throw away any of that. That's more of how specific databases (like InfluxDB) choose to handle it but not necessarily a component of time-series itself.
    - fnord123 6y ago
      Yes absolutely. However if you are aggressive in getting performance then you're likely to do things like batching the data and storing compressed blobs in the db and the rest of the row is essentially just metadata to the blob. Once you do this, relations on the data are basically thrown away because you can no longer do any joins on the blob data. You can choose to not do this but then get ready to eat mediocre performance.
      - manigandham 6y ago
        Sure but that's because a "time-series" database is trying to get functionality that isn't already part of the underlying data store, for example if you're using RocksDB underneath and want to avoid slow scans. You don't need to do that with a relational columnstore like Redshift, BigQuery, MemSQL, Vertica, Kdb+, or others. They're designed for massive aggregations with rows stored in highly compressed column segments that support updates and joins. And they're much faster than any custom compression scheme on top of another slower data store. There's also the in-between options like OpenTSDB and Pinalytics that use Hbase/Bigtable. That's a sorted key/value datastore but it still applies compression on the row segments so you can leverage that in scans without a custom compression scheme on top.
- akulkarni 6y ago
  Ajay from TimescaleDB here. In general, a time-series database makes architectural decisions and focuses on capabilities for time-series data that lead to orders of magnitude higher performance and a better developer experience. For example, TimescaleDB: - Auto-partitions data into 100s-10,000s of chunks, resulting in faster inserts and queries, yet provides the Hypertable abstraction that allows you to treat the data as if it lives in a single table (eg full SQL, secondary indexes, etc) - Provides Continuous Aggregates, which automatically calculate the results of a query in the background and materialize the results [1] - Supports native compression, using a variety of best-in-class algorithms based on datatype (eg delta-of-delta encoding, Simple-8b RLE, XOR-based compression (aka "Gorilla" compression), dictionary compression [2] - Adds other capabilities like interpolation, LOCF, data retention policies, etc, that are necessary for time-series data - Scales up on a single node, or out across multiple nodes There's more, but I'll stop because I don't want this to sound like marketing. You are welcome to implement this yourself (and our docs explain how if you'd like), but most find it easier to use the database we already provide for free[3]. :-) [1] https://docs.timescale.com/latest/using-timescaledb/continuous-aggregates https://docs.timescale.com/latest/using-timescaledb/continuo... [2] https://docs.timescale.com/latest/using-timescaledb/compression https://docs.timescale.com/latest/using-timescaledb/compress... [3] https://blog.timescale.com/blog/multi-node-petabyte-scale-time-series-database-postgresql-free-tsdb/ https://blog.timescale.com/blog/multi-node-petabyte-scale-ti...
  - mulmen 6y ago
    Thanks for the detailed explanation, that helps a lot. It sounds like a time-series database does in fact include some elements of ETL at the database layer. I can see how that would be helpful.
    - akulkarni 6y ago
      Glad to hear. I'm also available via email ajay (at) timescale.com if you have additional questions We also have a pretty active community on Slack (4,000+ members): https://slack.timescale.com/ https://slack.timescale.com/

mulmen 6y ago

My day job relies heavily on Amazon Redshift. Most of our fact tables are sorted by date and almost always a locale. I wrote a Python script to delete the stale records based on that time key. We have a separate vacuum service that cleans up the mess continuously. Is this considered a time series database? Or is there something a dedicated time series database does differently?

manigandham 6y ago
Time-series databases themselves are usually key/value or relational. The "time-series" features come from the focus around time as a primary component of the data (or some kind of increasing numeric value). Features like automatic partitioning and sharding, better compression, aggregating into time buckets, extracting and managing date values, smoothing missing values in queries, etc.
- deleted 6y ago
  [deleted]
- mulmen 6y ago
  Redshift does a lot of that. Is there something unique to a "time series" database that Redshift does not do? Is it the inclusion of some ETL capabilities? I'm not sure what makes a database truly time-series. It sounds like there is some compromise made that requires a time dimension. I'm not sure what that compromise would be beyond the general case of a columnar database.
  - manigandham 6y ago
    Nothing unique. Any database can store "time-series" data. If you look at InfluxDB (key/value) or TimescaleDB (relational), they offer things like high precision time values, higher compression algorithms that work better with timestamps, utilities to make it easier to query for time (buckets, timezones, gap-filling), automatic rollups and aggregations, data deletions based on time, etc. You can do all that yourself on Redshift but they just offer more built-in functionality for it.
    - akulkarni 6y ago
      Exactly what mani says. For many people, Redshift, or just plain Postgres, is good enough. But if you find yourself wresting with poor performance, slow queries, high costs, or just an annoying developer experience, I'd encourage you to look at time-series databases (eg TimescaleDB, where I work).
mywittyname 6y ago
I tend to think of them as databases where rows are an aggregate count of events over a period of time. An overly simplistic example being an API that captures upvotes. Instead of storing the individual upvotes, you incoming requests in a queue and only write out a count of upvotes over, say 1 minute. That way, if you want to get a count over a larger period of time, you're setting a ceiling on the number of records involved in your operation. If you have 1 minute resolution and you're looking over a year of data, you're adding at most, 6024366 records for each post. This is really handy for analytics data where you don't necessarily care about the individual records. The project I was specifically referencing was one that captured data streams from sensors like accelerometers and thermometers, which is inherently time-series data, because it's literally just (timestamp, sensor-reading). But to make use of that, you need a library of tools that understand that the underlying data is a time-series to do things like smoothing data, or highlight key events. For example, a torque spike had a "signature" which involved looking at the difference over time periods at a particular resolution. But a temperature spike would look different. Etc.
- mulmen 6y ago
  I see, thanks for the explanation. Is aggregation in general a required feature of a time-series database? Or specifically the ability to calculate aggregates continuously over changing (incoming?) data? Do time series databases store the raw underlying data for the aggregates or just enough to calculate the next window?
fnord123 6y ago
You could have made a timeseries db. There's nothing magic about it. It's just a collection of engineering tradeoffs where you throw away relational features and indexes on various fields, instead focusing on performance for the huge number of events coming into an append only system (you can update but it's super rare).
- manigandham 6y ago
  If you build it on a modern relational data warehouse then you don't have to throw away any of that. That's more of how specific databases (like InfluxDB) choose to handle it but not necessarily a component of time-series itself.
  - fnord123 6y ago
    Yes absolutely. However if you are aggressive in getting performance then you're likely to do things like batching the data and storing compressed blobs in the db and the rest of the row is essentially just metadata to the blob. Once you do this, relations on the data are basically thrown away because you can no longer do any joins on the blob data. You can choose to not do this but then get ready to eat mediocre performance.
    - manigandham 6y ago
      Sure but that's because a "time-series" database is trying to get functionality that isn't already part of the underlying data store, for example if you're using RocksDB underneath and want to avoid slow scans. You don't need to do that with a relational columnstore like Redshift, BigQuery, MemSQL, Vertica, Kdb+, or others. They're designed for massive aggregations with rows stored in highly compressed column segments that support updates and joins. And they're much faster than any custom compression scheme on top of another slower data store. There's also the in-between options like OpenTSDB and Pinalytics that use Hbase/Bigtable. That's a sorted key/value datastore but it still applies compression on the row segments so you can leverage that in scans without a custom compression scheme on top.
akulkarni 6y ago
Ajay from TimescaleDB here. In general, a time-series database makes architectural decisions and focuses on capabilities for time-series data that lead to orders of magnitude higher performance and a better developer experience. For example, TimescaleDB: - Auto-partitions data into 100s-10,000s of chunks, resulting in faster inserts and queries, yet provides the Hypertable abstraction that allows you to treat the data as if it lives in a single table (eg full SQL, secondary indexes, etc) - Provides Continuous Aggregates, which automatically calculate the results of a query in the background and materialize the results [1] - Supports native compression, using a variety of best-in-class algorithms based on datatype (eg delta-of-delta encoding, Simple-8b RLE, XOR-based compression (aka "Gorilla" compression), dictionary compression [2] - Adds other capabilities like interpolation, LOCF, data retention policies, etc, that are necessary for time-series data - Scales up on a single node, or out across multiple nodes There's more, but I'll stop because I don't want this to sound like marketing. You are welcome to implement this yourself (and our docs explain how if you'd like), but most find it easier to use the database we already provide for free[3]. :-) [1] https://docs.timescale.com/latest/using-timescaledb/continuous-aggregates https://docs.timescale.com/latest/using-timescaledb/continuo... [2] https://docs.timescale.com/latest/using-timescaledb/compression https://docs.timescale.com/latest/using-timescaledb/compress... [3] https://blog.timescale.com/blog/multi-node-petabyte-scale-time-series-database-postgresql-free-tsdb/ https://blog.timescale.com/blog/multi-node-petabyte-scale-ti...
- mulmen 6y ago
  Thanks for the detailed explanation, that helps a lot. It sounds like a time-series database does in fact include some elements of ETL at the database layer. I can see how that would be helpful.
  - akulkarni 6y ago
    Glad to hear. I'm also available via email ajay (at) timescale.com if you have additional questions We also have a pretty active community on Slack (4,000+ members): https://slack.timescale.com/ https://slack.timescale.com/