4 ms·

We touched on this a bit elsewhere when someone asked about InfluxDB. One of the biggest advantages is that by being built on Postgres, we are able to support t

We touched on this a bit elsewhere when someone asked about InfluxDB. One of the biggest advantages is that by being built on Postgres, we are able to support the full range of SQL/RDBMS features including secondary indices, complex WHERE predicates, CTEs, and more. Time-series data can also be easily stored alongside relational data, which makes it easier to simplify your stack.

ddorian43 9y ago
He is asking, why didn't you build a columnar-store extension and just partition by time ? This way you also have columnar-storage features/customers.
georgewfraser 9y ago
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.

ddorian43 9y ago

He is asking, why didn't you build a columnar-store extension and just partition by time ? This way you also have columnar-storage features/customers.

georgewfraser 9y ago

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.