3 ms·

Column store data warehouses have all those features, except indices which don’t make sense in the context of a columnar architecture. It seems to me that if yo

Column store data warehouses have all those features, except indices which don’t make sense in the context of a columnar architecture. It seems to me that if you create a table in a conventional data warehouse with a partition key on a time column, you have all the advantages of TimescaleDB, plus all the advantages of an MPP columnar data warehouse. Am I missing something? Why should anyone use TimescaleDB instead of just a specific configuration of a data warehouse?

cevian 9y ago
A lot of time-series data has complex relations and cross-correlations that make it unsuitable for column stores. Take for example a device that reports temperature and humidity. You may want to enforce foreign key relations between devices and readings, or you may want to do analysis for device in a particular location (which may be part of readings or devices table) secondary indexes help here. Or you may want to easy access to all data where temperature is above 90 degrees (partial indexes help here). You often want this in a real-time dashboard where the kind of latencies you see with MPP are problematic. Pretty much the columnar/MPP stuff is good for simple aggregates for ad-hoc non-real-time queries but TimescaleDB is geared to more complex analysis to power dashboards. Plus, when you know which particular aggregates you want to regularly query, TimescaleDB makes it easily to perform continuous roll-ups so you can store in a separate (hyper)table.
- ddorian43 9y ago
  See Clickhouse for `oltp` columnar store (built for powering dashboards).
  - cevian 9y ago
    Clickhouse is a very cool project but is more analytical than OLTP. Percona had a nice writeup[1] (the comparison to mysql is especially apt -- note the lack of real-time updates and deletes in Clickhouse). [1] https://www.percona.com/blog/2017/02/13/clickhouse-new-opensource-columnar-database/ https://www.percona.com/blog/2017/02/13/clickhouse-new-opens...
- manigandham 9y ago
  > Pretty much the columnar/mpp stuff is good for simple aggregates for ad-hoc non-real-time queries How is a column-oriented database which is primarily designed for fast performance across large data not good for real-time queries? Memsql, clickhouse, vertica, druid, etc are all fast systems that can scan terabytes in milliseconds with complex queries. It's great that timescale provides new options and works with postgres but let's keep things accurate in a field that has plenty of confusion already.

cevian 9y ago

A lot of time-series data has complex relations and cross-correlations that make it unsuitable for column stores. Take for example a device that reports temperature and humidity. You may want to enforce foreign key relations between devices and readings, or you may want to do analysis for device in a particular location (which may be part of readings or devices table) secondary indexes help here. Or you may want to easy access to all data where temperature is above 90 degrees (partial indexes help here). You often want this in a real-time dashboard where the kind of latencies you see with MPP are problematic. Pretty much the columnar/MPP stuff is good for simple aggregates for ad-hoc non-real-time queries but TimescaleDB is geared to more complex analysis to power dashboards. Plus, when you know which particular aggregates you want to regularly query, TimescaleDB makes it easily to perform continuous roll-ups so you can store in a separate (hyper)table.

ddorian43 9y ago
See Clickhouse for `oltp` columnar store (built for powering dashboards).
- cevian 9y ago
  Clickhouse is a very cool project but is more analytical than OLTP. Percona had a nice writeup[1] (the comparison to mysql is especially apt -- note the lack of real-time updates and deletes in Clickhouse). [1] https://www.percona.com/blog/2017/02/13/clickhouse-new-opensource-columnar-database/ https://www.percona.com/blog/2017/02/13/clickhouse-new-opens...
manigandham 9y ago
> Pretty much the columnar/mpp stuff is good for simple aggregates for ad-hoc non-real-time queries How is a column-oriented database which is primarily designed for fast performance across large data not good for real-time queries? Memsql, clickhouse, vertica, druid, etc are all fast systems that can scan terabytes in milliseconds with complex queries. It's great that timescale provides new options and works with postgres but let's keep things accurate in a field that has plenty of confusion already.