4 ms·

There is some creative engineering going here :) have a look: https://github.com/timescale/tsbs/blob/master/scripts/load/load_timescaledb.sh https://github.com

There is some creative engineering going here :) have a look: https://github.com/timescale/tsbs/blob/master/scripts/load/load_timescaledb.sh https://github.com/timescale/tsbs/blob/master/scripts/load/l... vs https://github.com/timescale/tsbs/blob/master/scripts/load/load_clickhouse.sh https://github.com/timescale/tsbs/blob/master/scripts/load/l...

ryanbooz 5y ago
(Post author) I'm not sure why you think that's creative engineering. What you're pointing to is the depth of available configuration that the contributors to TSBS have exposed for each database. It's totally open source and anyone is welcome to add more configuration and options! I believe (although not totally sure) that Altinity and ClickHouse folks added their code a few years ago - at least it wasn't anyone on the Timescale team. That said, we didn't actually use those scripts to run our tests. Please join us next Wednesday (10AM ET/4PM CET) to see how we set the databases up and ran the benchmarks. We'd be delighted to have you try it on your own too!
- rkwasny 5y ago
  Ah so the tests you have used are not the ones in https://github.com/timescale/tsbs https://github.com/timescale/tsbs ?
  - ryanbooz 5y ago
    All the same tests. You simply pointed to a shell script that's configurable to run tests for each database. We provided details in the blog post of exactly what settings we used for each database (cardinality, batch size, time range, TimescaleDB chunk size, etc.) so you can use those script to configure and run the tests too.
rkwasny 5y ago
Also this queries are different? order by "time" vs order by "created_at" https://github.com/timescale/tsbs/blob/a045665d9c94426bbc4055c5b88246bd64cbd794/cmd/tsbs_generate_queries/databases/clickhouse/devops.go#L277 https://github.com/timescale/tsbs/blob/a045665d9c94426bbc405... https://github.com/timescale/tsbs/blob/a045665d9c94426bbc4055c5b88246bd64cbd794/cmd/tsbs_generate_queries/databases/timescaledb/devops.go#L225 https://github.com/timescale/tsbs/blob/a045665d9c94426bbc405...
- ryanbooz 5y ago
  We were using tags, so that "else" block isn't the one being used for ClickHouse. Regardless, the table that is created (by the community and verified by former CH engineers) orders by created_at, not time and so that query should be the "fastest" the distinct possible.
csdvrx 5y ago
> There is some creative engineering going here Agreed. At a previous work, clickhouse outperformed timescale by several orders of magnitude, under about every condition. The timescale team seems to recognize that (look for the comment about clickhouse being a bulldozer) but they seem to say timescale can be better suited. In my experience, in about 1% of the cases, yes, timescale will be a better choice (ex: if you do very small batches of insertions, if you need to remove some datapoints) but in 99% of the usecases for a time series database, clickhouse is the right answer. There seems to have been several improvements to timescale since 2018, with columnar storage, compression, etc. and that's good because more competition is always better. But in 2021, clickhouse vs timescale for a timeseries is like postgres vs mongo for a regular database: unless you have special constraints [*], the "cool" solution (timescale or mongo) is the wrong one. [*]: you may think you have a unique problem and you need unique features, but odds are, YAGNI https://en.wikipedia.org/wiki/You_aren%27t_gonna_need_it https://en.wikipedia.org/wiki/You_aren%27t_gonna_need_it
- akulkarni 5y ago
  > In my experience, in about 1% of the cases, yes, timescale will be a better choice (ex: if you do very small batches of insertions, if you need to remove some datapoints) but in 99% of the usecases for a time series database, clickhouse is the right answer. I always find comments like this interesting :-). Things are better for different use cases. If you find yourself inserting a lot of data in batches for OLAP-style analysis, then ClickHouse is a better choice today. If you find yourself performing a lot of time-series related queries, and needing to build an application on top (e.g., where you might want the OLTP features of Postgres), then Timescale is the better choice. YMMV! And that's OK :-) > But in 2021, clickhouse vs timescale for a timeseries is like postgres vs mongo for a regular database: unless you have special constraints [*], the "cool" solution (timescale or mongo) is the wrong one. This is also a funny statement, because TimescaleDB is built on PostgreSQL. We actually take great pride in being a "boring" option [0] - in fact I think TimescaleDB is many ways is more "boring" than ClickHouse (again, because of its PostgreSQL foundation). But I think that's actually a good thing - because you should want your database to be "boring" - ie you shouldn't have to worry about it! (Disclaimer: TimescaleDB co-founder) [0] https://blog.timescale.com/blog/when-boring-is-awesome-building-a-scalable-time-series-database-on-postgresql-2900ea453ee2/ https://blog.timescale.com/blog/when-boring-is-awesome-build...

ryanbooz 5y ago

(Post author) I'm not sure why you think that's creative engineering. What you're pointing to is the depth of available configuration that the contributors to TSBS have exposed for each database. It's totally open source and anyone is welcome to add more configuration and options! I believe (although not totally sure) that Altinity and ClickHouse folks added their code a few years ago - at least it wasn't anyone on the Timescale team. That said, we didn't actually use those scripts to run our tests. Please join us next Wednesday (10AM ET/4PM CET) to see how we set the databases up and ran the benchmarks. We'd be delighted to have you try it on your own too!

rkwasny 5y ago
Ah so the tests you have used are not the ones in https://github.com/timescale/tsbs https://github.com/timescale/tsbs ?
- ryanbooz 5y ago
  All the same tests. You simply pointed to a shell script that's configurable to run tests for each database. We provided details in the blog post of exactly what settings we used for each database (cardinality, batch size, time range, TimescaleDB chunk size, etc.) so you can use those script to configure and run the tests too.

rkwasny 5y ago

Also this queries are different? order by "time" vs order by "created_at" https://github.com/timescale/tsbs/blob/a045665d9c94426bbc4055c5b88246bd64cbd794/cmd/tsbs_generate_queries/databases/clickhouse/devops.go#L277 https://github.com/timescale/tsbs/blob/a045665d9c94426bbc405... https://github.com/timescale/tsbs/blob/a045665d9c94426bbc4055c5b88246bd64cbd794/cmd/tsbs_generate_queries/databases/timescaledb/devops.go#L225 https://github.com/timescale/tsbs/blob/a045665d9c94426bbc405...

ryanbooz 5y ago
We were using tags, so that "else" block isn't the one being used for ClickHouse. Regardless, the table that is created (by the community and verified by former CH engineers) orders by created_at, not time and so that query should be the "fastest" the distinct possible.

csdvrx 5y ago

> There is some creative engineering going here Agreed. At a previous work, clickhouse outperformed timescale by several orders of magnitude, under about every condition. The timescale team seems to recognize that (look for the comment about clickhouse being a bulldozer) but they seem to say timescale can be better suited. In my experience, in about 1% of the cases, yes, timescale will be a better choice (ex: if you do very small batches of insertions, if you need to remove some datapoints) but in 99% of the usecases for a time series database, clickhouse is the right answer. There seems to have been several improvements to timescale since 2018, with columnar storage, compression, etc. and that's good because more competition is always better. But in 2021, clickhouse vs timescale for a timeseries is like postgres vs mongo for a regular database: unless you have special constraints [*], the "cool" solution (timescale or mongo) is the wrong one. [*]: you may think you have a unique problem and you need unique features, but odds are, YAGNI https://en.wikipedia.org/wiki/You_aren%27t_gonna_need_it https://en.wikipedia.org/wiki/You_aren%27t_gonna_need_it

akulkarni 5y ago
> In my experience, in about 1% of the cases, yes, timescale will be a better choice (ex: if you do very small batches of insertions, if you need to remove some datapoints) but in 99% of the usecases for a time series database, clickhouse is the right answer. I always find comments like this interesting :-). Things are better for different use cases. If you find yourself inserting a lot of data in batches for OLAP-style analysis, then ClickHouse is a better choice today. If you find yourself performing a lot of time-series related queries, and needing to build an application on top (e.g., where you might want the OLTP features of Postgres), then Timescale is the better choice. YMMV! And that's OK :-) > But in 2021, clickhouse vs timescale for a timeseries is like postgres vs mongo for a regular database: unless you have special constraints [*], the "cool" solution (timescale or mongo) is the wrong one. This is also a funny statement, because TimescaleDB is built on PostgreSQL. We actually take great pride in being a "boring" option [0] - in fact I think TimescaleDB is many ways is more "boring" than ClickHouse (again, because of its PostgreSQL foundation). But I think that's actually a good thing - because you should want your database to be "boring" - ie you shouldn't have to worry about it! (Disclaimer: TimescaleDB co-founder) [0] https://blog.timescale.com/blog/when-boring-is-awesome-building-a-scalable-time-series-database-on-postgresql-2900ea453ee2/ https://blog.timescale.com/blog/when-boring-is-awesome-build...