4 ms·

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

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...

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...