Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data

Post by PeterZaitsev » Thu, Oct 21, 2021, 11:39 PM UTC

CodesInChaos wrote: post #109

PeterZaitsev wrote: post #90

Earlier quoted context omitted.

This does not answer the question - is it Open Source License or Source Available (TSL) https://www.timescale.com/legal/licenses

the original article and the parent poster say that the community edition was used: > Versions: TimescaleDB version 2.4.0, community edition, with PostgreSQL 13 and the link you posted explains that it's the non OSI license version: > TimescaleDB Community is made available under the Timescale License ("TSL")

Post by mfreed » Fri, Oct 22, 2021, 12:36 AM UTC

nojvek wrote: post #136

I’m surprised Timescale hasn’t given a comparison with SingleStoreDB. I’ve found SingleStore column scans at parity with ClickHouse in speed. At same time SingleStore uses a hybrid skip-list, columnstore data structure in their universal storage (which is default table format). So you have high throughput transactions, as well as insanely fast aggregate scans. Usually in column stores, they are great at append, not s…

Post by csdvrx » Fri, Oct 22, 2021, 1:17 AM UTC

akulkarni wrote: post #141

csdvrx wrote: post #89

Earlier quoted context omitted.

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

> 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-st…

Post by akulkarni » Fri, Oct 22, 2021, 2:13 AM UTC

csdvrx wrote: post #146

akulkarni wrote: post #141

Earlier quoted context omitted.

> 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-st…

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

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

  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.

Post by detailyang » Fri, Oct 22, 2021, 2:22 AM UTC

sam0x17 wrote: post #18

Can someone give me a real-world example of a scenario where they actually need a time series database, like an example query with the business use case / justification? Just super curious.