Hacker News Reader

Comment by fnord123 | Hacker News Reader

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

View on HN

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.

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.

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.

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.

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

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/

2. http://opentsdb.net/docs/build/html/user_guide/definitions.h...

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?

Reply on news.ycombinator.com