4 ms·

> There is some creative engineering going here Agreed. At a previous work, clickhouse outperformed timescale by several orders of magnitude, under about every

by 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...
- csdvrx 5y ago
  > This is also a funny statement, because TimescaleDB is built on PostgreSQL. I know, but doing timeseries with postgres is "cool", not standard, not boring. I'd even say "risky". > We actually take great pride in being a "boring" option No, you're not there yet: doing timeseries with timescale is way riskier than with clickhouse, which is both a bit older (not much) and more mature (much more), while also being more widely used (even if you are doing a lot of outreach like these posts)
  - akulkarni 5y ago
    > No, you're not there yet: doing timeseries with timescale is way riskier than with clickhouse, which is both a bit older (not much) and more mature (much more), while also being more widely used This is not true at all, and we explain why in the post: 1. TimescaleDB's reliability is PostgreSQL's reliability. ClickHouse has a lot of advantages, but "more reliable than PostgreSQL" is not one of them. From the post: PostgreSQL has the benefit for 20+ years of development and usage, which has resulted in not just a reliable database, but also a broad spectrum of rigorously tested tools: streaming replication for high availability and read-only replicas, pg_dump and pg_recovery for full database snapshots, pg_basebackup and log shipping / streaming for incremental backups and arbitrary point-in-time recovery, pgBackrest or WAL-E for continuous archiving to cloud storage, and robust COPY FROM and COPY TO tools for quickly importing/exporting data with a variety of formats. This enables PostgreSQL to offer a greater “peace of mind” - because all of the skeletons in the closet have already been found (and addressed). 2. ClickHouse, being a newer database, still has several "gotchas" with reliability: e.g., No data consistency in backups (because of its lack of support for transactions and asynchronous data modification) From the post: One last aspect to consider as part of the ClickHouse architecture and its lack of support for transactions is that there is no data consistency in backups. As we've already shown, all data modification (even sharding across a cluster) is asynchronous, therefore the only way to ensure a consistent backup would be to stop all writes to the database and then make a backup. Data recovery struggles with the same limitation. The lack of transactions and data consistency also affects other features like materialized views because the server can't atomically update multiple tables at once. If something breaks during a multi-part insert to a table with materialized views, the end result is an inconsistent state of your data. Now this trade-off - accepting less reliability for faster OLAP queries - may be fine with you. And that's OK. But stating that ClickHouse is more reliable than PostgreSQL/TimescaleDB is just not true.

by csdvrx 5y ago

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

csdvrx 5y ago
> This is also a funny statement, because TimescaleDB is built on PostgreSQL. I know, but doing timeseries with postgres is "cool", not standard, not boring. I'd even say "risky". > We actually take great pride in being a "boring" option No, you're not there yet: doing timeseries with timescale is way riskier than with clickhouse, which is both a bit older (not much) and more mature (much more), while also being more widely used (even if you are doing a lot of outreach like these posts)
- akulkarni 5y ago
  > No, you're not there yet: doing timeseries with timescale is way riskier than with clickhouse, which is both a bit older (not much) and more mature (much more), while also being more widely used This is not true at all, and we explain why in the post: 1. TimescaleDB's reliability is PostgreSQL's reliability. ClickHouse has a lot of advantages, but "more reliable than PostgreSQL" is not one of them. From the post: PostgreSQL has the benefit for 20+ years of development and usage, which has resulted in not just a reliable database, but also a broad spectrum of rigorously tested tools: streaming replication for high availability and read-only replicas, pg_dump and pg_recovery for full database snapshots, pg_basebackup and log shipping / streaming for incremental backups and arbitrary point-in-time recovery, pgBackrest or WAL-E for continuous archiving to cloud storage, and robust COPY FROM and COPY TO tools for quickly importing/exporting data with a variety of formats. This enables PostgreSQL to offer a greater “peace of mind” - because all of the skeletons in the closet have already been found (and addressed). 2. ClickHouse, being a newer database, still has several "gotchas" with reliability: e.g., No data consistency in backups (because of its lack of support for transactions and asynchronous data modification) From the post: One last aspect to consider as part of the ClickHouse architecture and its lack of support for transactions is that there is no data consistency in backups. As we've already shown, all data modification (even sharding across a cluster) is asynchronous, therefore the only way to ensure a consistent backup would be to stop all writes to the database and then make a backup. Data recovery struggles with the same limitation. The lack of transactions and data consistency also affects other features like materialized views because the server can't atomically update multiple tables at once. If something breaks during a multi-part insert to a table with materialized views, the end result is an inconsistent state of your data. Now this trade-off - accepting less reliability for faster OLAP queries - may be fine with you. And that's OK. But stating that ClickHouse is more reliable than PostgreSQL/TimescaleDB is just not true.