Could putting RedPanda/Kafka in front of ClickHouse make it insert benchmark winner? Of course it means operational expenses but I wonder if this route is worth exploring?
Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data
91–100 of 184 posts
Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data
#92Altinity folks have suggested number of Clickhouse optimizations for time series benchmark did you enable any of those ? https://altinity.com/blog/clickhouse-continues-to-crush-time...
Hello @PeterZaitsev! Actually Altinity is the one that contributed the bits to TSBS for benchmarking ClickHouse[1], so we are using the work that they contributed (and anyone is welcome to make a PR for updates or changes). We also had a former ClickHouse engineer look at the setup to verify it matched best practices with how CH is currently designed, given the TSBS dataset. As for the optimizations in the article yo…
Thank you for your prompt response!
I think the most important thing is Clickhouse is NOT designed for small batch insertion, if you need to do 1000s of Inserts/sec you do queue in front of clickhouse. And query speed can be impacted by batch side a lot. So have you looked at query performance with optimal batch size ?
Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data
#93It would be awesome to combine the following things:
* PostGIS
* Timescale
* Citus
* Zedstore
This truly would be the relational DB to end all relational DBs. Unfortunately, we run into a couple problems:
* Managing multiple extensions is a burdensome task, which should be in the wheelhouse of cloud providers, but...
* Timescale and Citus are are open core, holding back features for customers. Their primary revenue channels are their cloud offerings. Unfortunately you can't get Citus and Timescale in the same cloud offering, cause you're dealing with two separate companies.
* PostGIS has multiple cloud providers, but none of them have Timescale or Citus available.
* Citus only has cloud offerings on Azure, excluding the other two major players that often have exclusive relationships with companies.
* Zedstore is really cool and together with Citus could be a massive gamechanger by having columnstore and rowstores in the same distributed database. However, development has stalled, and nobody seems to be able to explain what happened.
Sigh...maybe 5 years from now.
Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data
#94ClickHouse shines at scales that timescale has no hope of ever supporting. Hence the choice of workloads in the test. Cloudflare was ingesting 6,000,000 rows per second into 36 node (dual E5-2630) ClickHouse cluster (2018) was something like 20PB of data per year.
(post author) Those are great, impressive numbers. We certainly don't claim to be all things to all people, but the benchmark was run using single instances mostly because that is what most other benchmarks published by others have done. With a multi-node TimescaleDB cluster, ingest does literally scale to millions of rows/second and we have a numerous users achieving great numbers. One Fortune 100 company has a 25+…
and then it will come down to spec of nodes actual fields and so on etc. Also batch size obviously plays a big role here as
CH is optimized for very large batch sizes and benchmark is not really using that kind of batch size. BTW. I am not involved with CH but any kind of vendor benchmarking their wears will always select params that will make their offering look good
Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data
#95That’s a really thorough comparison. Much more detailed than I expected. From what I see, the trade off in disk space usage would point me toward Timescale for most of my workloads. The insert performance tradeoff just wouldn’t justify the difference for me.
Does the disk usage go down later once the numerous parts are merged or not?
I would assume it does but reading the article implies that it does not.
Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data
#96Earlier quoted context omitted.
https://blog.cloudflare.com/http-analytics-for-6m-requests-p... has some good thoughts. The main thing you'll likely need is some sort of a buffer layer so you can do bulk inserts. Do not write a high-volume of single-row inserts into Clickhouse.
Thanks for sharing the link! I’ve heard the bulk insert thing before and to be honest I’ve always thought that RDBMSs don’t love single row inserts either. Seems clickhouse takes that to a new level. In our case we are using sqs and usually insert 20-100 rows into the db at a time so I’m going to benchmark how that does in clickhouse.
[deleted]
Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data
#97Earlier quoted context omitted.
https://blog.cloudflare.com/http-analytics-for-6m-requests-p... has some good thoughts. The main thing you'll likely need is some sort of a buffer layer so you can do bulk inserts. Do not write a high-volume of single-row inserts into Clickhouse.
Thanks for sharing the link! I’ve heard the bulk insert thing before and to be honest I’ve always thought that RDBMSs don’t love single row inserts either. Seems clickhouse takes that to a new level. In our case we are using sqs and usually insert 20-100 rows into the db at a time so I’m going to benchmark how that does in clickhouse.
With Clickhouse you can use a "buffer table", which uses just RAM and sits on top of a normal table: https://clickhouse.com/docs/en/engines/table-engines/special...
Rows inserted into the buffer table are then flushed to the normal/base table when one of the limits (defined when the buffer table is created) is reached (limits are max rows, max bytes, max time since the last flush), or when you drop the buffer table.
I'm using it and it works (performance difference can be huge compared to perform single inserts directly into a real/normal table), but be careful - the flushed rows don't give a guarantee of which row is flushed in which sequence, so using a buffer table is a very bad idea if your base table is something which relies on the correct sequences of rows that it receives.
Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data
#98Can 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.
> Can someone give me a real-world example of a scenario where they actually need a time series database
Large scale infrastructure monitoring?
If you run a data center with 10K machines in it, whitebox monitoring of these machines and what runs on it generates tons of timestamped data.
These time series can be used to inform an automated alerting system (eg using trends to forecast bad things before they happen).
They can also be analyzed in batch mode to figure out how to optimize many things (power / cooling / workload assignment / etc ...)
Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data
#99It would be awesome to combine the following things: * PostGIS * Timescale * Citus * Zedstore This truly would be the relational DB to end all relational DBs. Unfortunately, we run into a couple problems: * Managing multiple extensions is a burdensome task, which should be in the wheelhouse of cloud providers, but... * Timescale and Citus are are open core, holding back features for customers. Their primary revenue c…
If you use Aiven for a cloud PG instance you can do both Timescale + PostGIS installed.
I also really wish ClickHouse would prioritize PostGIS support - IIRC it has been on their roadmap for a while but keeps getting kicked around every year or so. Same thing with CockroachDB - PostGIS support kicked down the road every year.
Re: Comparing ClickHouse to PostgreSQL and TimescaleDB for time-series data
#100Disclaimer: I'm a co-founder of https://logtail.com, ClickHouse-based hosted log management platform.
PostgreSQL, TimescaleDB, and ClickHouse are all impressive pieces of software. We use both PostgreSQL and ClickHouse at Logtail.
ClickHouse shines for true OLAP use-cases and is very hard to beat performance-wise when configured properly.
Example:
> Poor inserts and much higher disk usage (e.g., 2.7x higher disk usage than TimescaleDB) at small batch sizes (e.g., 100-300 rows/batch).
If your consistency requirements allow, you could use the Buffer Table Engine to get blazing fast inserts: https://clickhouse.com/docs/en/engines/table-engines/special...
Horizontal scalability and compression are also unbeatable from what I've seen, to name a few.
There's a hefty price tag, however: ClickHouse is quite ops heavy and its observability has a seriously steep learning curve. Only go for ClickHouse in production if you really know what you're doing :)