4 ms·

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 an

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.
    - fnord123 6y ago
      >And they'll be much faster than any custom compression scheme on top of another data store. As usual, it depends on the situation and what's being measured. For example, if you are able to batch a lot, then write performance of a blob will be line rate. If you need an ACK on each event then that's not possible and indeed a column store will be better. > OpenTSDB AFAICT, OpenTSDB compacts columns which is similar to what I've described. https://github.com/OpenTSDB/opentsdb/blob/14ab3ef8a865816cf920aa69f2e019b7261a7847/src/core/CompactionQueue.java#L575 https://github.com/OpenTSDB/opentsdb/blob/14ab3ef8a865816cf9... https://github.com/OpenTSDB/opentsdb/blob/14ab3ef8a865816cf920aa69f2e019b7261a7847/src/core/ByteBufferList.java#L89 https://github.com/OpenTSDB/opentsdb/blob/14ab3ef8a865816cf9... > Pinalytics This is proprietary so there's no visibility into what they do here. FWIW, KairosDB is a timeseries db build on Cassandra and uses a row with 1,814,400,000 to side step the data management overhead via bucketing. Not a binary blob but certainly not the straight forward data layout that someone asking about 'why can't I just store data in a normal database' might expect. https://kairosdb.github.io/docs/build/html/CassandraSchema.html#data-points-column-family https://kairosdb.github.io/docs/build/html/CassandraSchema.h...
      - manigandham 6y ago
        All relational columnstores use batch inserts and updates. In fact that's the recommended way to write columnstore data and SQL already supports various batch insert methods. The modern databases also use a row-store representation as a buffer for any single-row mutations before merging the columnstore segments. [1] OpenTSDB compacts columns inside a column-family. This is because Hbase/cassandra/dynamo key/value stores support arbitrary columns for each row and store a verbose identifier for the column/cell/version matrix. It's a custom data format to save disk space but the underlying compaction still refers to HBase LSM-tree compaction. [2] The rows are still stored individually and compression is enabled on HDFS. But yes, it's all highly dependent on the situation and it's the same fundamental mechanics of batching and compressing data. My point is that it's better to just use a database that is built with this architecture and can provide greater performance, usability and full querying flexibility instead of bolting it on top of a less functional data store. 1. https://docs.memsql.com/v7.1/concepts/columnstore/ https://docs.memsql.com/v7.1/concepts/columnstore/ 2. http://opentsdb.net/docs/build/html/user_guide/definitions.html http://opentsdb.net/docs/build/html/user_guide/definitions.h...
mulmen 6y ago
Almost all of our load operations are a merge. Our ETL layer takes care of that. The vacuum then deals with re-sorting rows and freeing blocks. I have never worked with something that bills itself as a "time-series" database. Does that involve pushing elements of the ETL layer down into the database? Or is it just optimizing for access by a time dimension?
cevian 6y ago
To be clear, TimescaleDB does NOT throw away and indexing capabilities or ACID features. Its only limitation is that UNIQUE constraints need to include the time column. Also, its query optimizations are far from trivial to implement.

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.
  - fnord123 6y ago
    >And they'll be much faster than any custom compression scheme on top of another data store. As usual, it depends on the situation and what's being measured. For example, if you are able to batch a lot, then write performance of a blob will be line rate. If you need an ACK on each event then that's not possible and indeed a column store will be better. > OpenTSDB AFAICT, OpenTSDB compacts columns which is similar to what I've described. https://github.com/OpenTSDB/opentsdb/blob/14ab3ef8a865816cf920aa69f2e019b7261a7847/src/core/CompactionQueue.java#L575 https://github.com/OpenTSDB/opentsdb/blob/14ab3ef8a865816cf9... https://github.com/OpenTSDB/opentsdb/blob/14ab3ef8a865816cf920aa69f2e019b7261a7847/src/core/ByteBufferList.java#L89 https://github.com/OpenTSDB/opentsdb/blob/14ab3ef8a865816cf9... > Pinalytics This is proprietary so there's no visibility into what they do here. FWIW, KairosDB is a timeseries db build on Cassandra and uses a row with 1,814,400,000 to side step the data management overhead via bucketing. Not a binary blob but certainly not the straight forward data layout that someone asking about 'why can't I just store data in a normal database' might expect. https://kairosdb.github.io/docs/build/html/CassandraSchema.html#data-points-column-family https://kairosdb.github.io/docs/build/html/CassandraSchema.h...
    - manigandham 6y ago
      All relational columnstores use batch inserts and updates. In fact that's the recommended way to write columnstore data and SQL already supports various batch insert methods. The modern databases also use a row-store representation as a buffer for any single-row mutations before merging the columnstore segments. [1] OpenTSDB compacts columns inside a column-family. This is because Hbase/cassandra/dynamo key/value stores support arbitrary columns for each row and store a verbose identifier for the column/cell/version matrix. It's a custom data format to save disk space but the underlying compaction still refers to HBase LSM-tree compaction. [2] The rows are still stored individually and compression is enabled on HDFS. But yes, it's all highly dependent on the situation and it's the same fundamental mechanics of batching and compressing data. My point is that it's better to just use a database that is built with this architecture and can provide greater performance, usability and full querying flexibility instead of bolting it on top of a less functional data store. 1. https://docs.memsql.com/v7.1/concepts/columnstore/ https://docs.memsql.com/v7.1/concepts/columnstore/ 2. http://opentsdb.net/docs/build/html/user_guide/definitions.html http://opentsdb.net/docs/build/html/user_guide/definitions.h...

mulmen 6y ago

Almost all of our load operations are a merge. Our ETL layer takes care of that. The vacuum then deals with re-sorting rows and freeing blocks. I have never worked with something that bills itself as a "time-series" database. Does that involve pushing elements of the ETL layer down into the database? Or is it just optimizing for access by a time dimension?

cevian 6y ago

To be clear, TimescaleDB does NOT throw away and indexing capabilities or ACID features. Its only limitation is that UNIQUE constraints need to include the time column. Also, its query optimizations are far from trivial to implement.