5 ms·

ClickHouse (the analytics DMBS by Yandex), while not explicitly designed as such, is a fantastic time series database. There's even a special backend, the Grap

by lima 9y ago

ClickHouse (the analytics DMBS by Yandex), while not explicitly designed as such, is a fantastic time series database. There's even a special backend, the GraphiteMergeTree, which does staggered downsampling, something most TSDBs aren't able to. It's the most promising development in this space I've seen in a long time. https://clickhouse.yandex/ https://clickhouse.yandex/ https://clickhouse.yandex/docs/en/table_engines/graphitemergetree.html https://clickhouse.yandex/docs/en/table_engines/graphitemerg...

betaby 9y ago
Direct link how to use as a graphite whisper replacement https://github.com/yandex/graphouse https://github.com/yandex/graphouse Telegram channel https://t.me/clickhouse_en https://t.me/clickhouse_en
nawgszy 9y ago
Completely unrelated question, but how on earth is 'clickhouse.yandex' a valid web address?
- betaby 9y ago
  The same way https://domains.google https://domains.google is, you pay - you get your own TLD
manigandham 9y ago
I'd also recommend Druid, MemSQL, SnappyData, MapD and other column-oriented databases. Any of them can partition on a time column with full SQL and extremely fast aggregations and high compression that come from columnar storage.
- cevian 9y ago
  Hi, you've posted this notion that partitioning a column store by time would yield the same result as TimescaleDB a few times, so thought we'd jump in and clear things up. We fully agree that column stores have their place, particularly if you have a massive number of metrics, and all you care are roll-ups on single column axes. There are some major differences between TimescaleDB and column stores. Namely, TimescaleDB supports a lot of features that column stores in general do not. - Secondary Indexes. - Transactional semantics. - Can operate on data sets greater than available memory (doesn't have to be all in-memory unlike memSQL and some others). Time-series data is voluminous. - A whole bunch of specialized time-based optimizations that optimize query plans when working with time-based indexes and data. - Constraints - Including foreign keys. - Triggers - Joins with relational data. - Full SQL - allowing you to use complex queries and window functions - Compatible with data tools that use SQL - which gets you gets you the richest ecosystem of tools in the data world - The full gamut of Postgres datatypes including JSON/B and GIS location data - 20+ years of reliability, tested backups, live streaming replication, etc. - Geospatial support through best-in-class PostGIS And of course, we're only getting started :)
  - neildahlke 9y ago
    Point of clarification - MemSQL does not need to be all in-memory, there is also a columnstore that is on-disk and only leverages memory for indexes / column segment information.
  - manigandham 9y ago
    It depends on the queries but columnstores would yield a faster result. We're not new to this and have used ClickHouse, MemSQL, SQL Server, and Druid extensively. Columnstores just store data by column, they do not have any inherent limitations because of it. They all support SQL and compatible tools (although Druid is experimental SQL using apache calcite). They all store columnstore tables on disk (memsql uses rowstores in memory, sql server can optionally run columnstores in-memory using its hekaton engine, and they all use in-memory buffers for rapid ingest). They can all do geospatial queries, support JSON columns and some can handle nested/repeated structures. Indexes are available but unnecessary when you can prune partitions based on what's contained in each segment, especially when using a primary sort key (like a timestamp column in your case). SnappyData has a unique statistical engine to tradeoff query precision for much faster results (like HLL+ algorithms applied to the entire dataset). MemSQL will do OLTP access with full transactions across both rowstore and columnstore data. Congrats on the VC funding, I'm always happy to see new projects and building on Postgres does give you a solid base with triggers and foreign keys (which come with their own scaling issues), and extending time-based functions will be useful -- however my issue is the marketing spin where you claim to be better than everything else. Columnstores are very fast, efficient, performant, and time as a dimension is not a new challenge. That's before considering the bigquery/snowflake superscale options or specialized databases like kdb+ which have served the financial industry for decades. Approaching the field with a single-node automatic partitioning extension (as of today) for a rowstore RDMS and saying you're better than the rest on features that they already have just strikes me as insincere. It would be better to recognize the competition and focus on what you're good at instead.
    - mfreed 9y ago
      “my issue is the marketing spin where you claim to be better than everything else” I’m sorry that was your impression and it’s certainly not our intent to mislead, although I’m not really sure why/where you think we claimed this. Indeed, the quoted article even says that "Timescale is not trying to take on Kx Systems directly in this core market.", and that such organizations have different needs for different use cases. Technology choices are all about trade-offs, and databases are no different. In fact, our docs have a page describing exactly when not to use TimescaleDB compared to other options: http://docs.timescale.com/v0.8/introduction/timescaledb-vs-nosql http://docs.timescale.com/v0.8/introduction/timescaledb-vs-n... Cheers :)
cevian 9y ago
Clickhouse is very cool. But note that it does not support transactional and relational semantics and does not have real-time updates or deletes. Thus, its meant for very different applications than TimescaleDB. I would classify Clickhouse more in the data-warehouse space...

by lima 9y ago

betaby 9y ago

Direct link how to use as a graphite whisper replacement https://github.com/yandex/graphouse https://github.com/yandex/graphouse Telegram channel https://t.me/clickhouse_en https://t.me/clickhouse_en

nawgszy 9y ago

Completely unrelated question, but how on earth is 'clickhouse.yandex' a valid web address?

betaby 9y ago
The same way https://domains.google https://domains.google is, you pay - you get your own TLD

manigandham 9y ago

I'd also recommend Druid, MemSQL, SnappyData, MapD and other column-oriented databases. Any of them can partition on a time column with full SQL and extremely fast aggregations and high compression that come from columnar storage.

cevian 9y ago
Hi, you've posted this notion that partitioning a column store by time would yield the same result as TimescaleDB a few times, so thought we'd jump in and clear things up. We fully agree that column stores have their place, particularly if you have a massive number of metrics, and all you care are roll-ups on single column axes. There are some major differences between TimescaleDB and column stores. Namely, TimescaleDB supports a lot of features that column stores in general do not. - Secondary Indexes. - Transactional semantics. - Can operate on data sets greater than available memory (doesn't have to be all in-memory unlike memSQL and some others). Time-series data is voluminous. - A whole bunch of specialized time-based optimizations that optimize query plans when working with time-based indexes and data. - Constraints - Including foreign keys. - Triggers - Joins with relational data. - Full SQL - allowing you to use complex queries and window functions - Compatible with data tools that use SQL - which gets you gets you the richest ecosystem of tools in the data world - The full gamut of Postgres datatypes including JSON/B and GIS location data - 20+ years of reliability, tested backups, live streaming replication, etc. - Geospatial support through best-in-class PostGIS And of course, we're only getting started :)
- neildahlke 9y ago
  Point of clarification - MemSQL does not need to be all in-memory, there is also a columnstore that is on-disk and only leverages memory for indexes / column segment information.
- manigandham 9y ago
  It depends on the queries but columnstores would yield a faster result. We're not new to this and have used ClickHouse, MemSQL, SQL Server, and Druid extensively. Columnstores just store data by column, they do not have any inherent limitations because of it. They all support SQL and compatible tools (although Druid is experimental SQL using apache calcite). They all store columnstore tables on disk (memsql uses rowstores in memory, sql server can optionally run columnstores in-memory using its hekaton engine, and they all use in-memory buffers for rapid ingest). They can all do geospatial queries, support JSON columns and some can handle nested/repeated structures. Indexes are available but unnecessary when you can prune partitions based on what's contained in each segment, especially when using a primary sort key (like a timestamp column in your case). SnappyData has a unique statistical engine to tradeoff query precision for much faster results (like HLL+ algorithms applied to the entire dataset). MemSQL will do OLTP access with full transactions across both rowstore and columnstore data. Congrats on the VC funding, I'm always happy to see new projects and building on Postgres does give you a solid base with triggers and foreign keys (which come with their own scaling issues), and extending time-based functions will be useful -- however my issue is the marketing spin where you claim to be better than everything else. Columnstores are very fast, efficient, performant, and time as a dimension is not a new challenge. That's before considering the bigquery/snowflake superscale options or specialized databases like kdb+ which have served the financial industry for decades. Approaching the field with a single-node automatic partitioning extension (as of today) for a rowstore RDMS and saying you're better than the rest on features that they already have just strikes me as insincere. It would be better to recognize the competition and focus on what you're good at instead.
  - mfreed 9y ago
    “my issue is the marketing spin where you claim to be better than everything else” I’m sorry that was your impression and it’s certainly not our intent to mislead, although I’m not really sure why/where you think we claimed this. Indeed, the quoted article even says that "Timescale is not trying to take on Kx Systems directly in this core market.", and that such organizations have different needs for different use cases. Technology choices are all about trade-offs, and databases are no different. In fact, our docs have a page describing exactly when not to use TimescaleDB compared to other options: http://docs.timescale.com/v0.8/introduction/timescaledb-vs-nosql http://docs.timescale.com/v0.8/introduction/timescaledb-vs-n... Cheers :)

cevian 9y ago

Clickhouse is very cool. But note that it does not support transactional and relational semantics and does not have real-time updates or deletes. Thus, its meant for very different applications than TimescaleDB. I would classify Clickhouse more in the data-warehouse space...