Apache Druid vs. Time-Series Databases
imply.io
imply.io
Druid worked really well for almost all use cases, reliably getting sub-second results against many billions of records even on a pretty modest deployment. Being able to use arbitrary Javascript functions in queries was fantastic, so we could do things like filtering on IP subnets or case insensitive prefix/suffix matching, and the like, as needed.
The Docker setup that Druid ships with is deceptively simple - making a production installation was an effort. My thoughts are:
- Build templating and code generation for the many config files for each component early on so you can edit constants in a single place and have all of the sundry config files update to reflect them, and also manage per-host overrides in a sane, version controlled, way.
- Druid will use as much ram as you can throw at it, but in a pinch, reading directly from fast NVME storage is pretty good.
- If you have realtime data ingestion, you will also have to build tooling to re-ingest older data that has changed or needed to be amended. This will end up looking like a 'lambda architecture'
> you have realtime data ingestion, you will also have to build tooling to re-ingest older data that has changed or needed to be amended. This will end up looking like a 'lambda architecture'
I believe what GP means by Lambda is that, you'd need a system that batch processes the data to be amended / changed (reprocess older data) but stream processes whatever that's required for real-time [2].
An alternative is the Kappa architecture proposed initially by Jay Kreps [3][4], co-creator of Apache Kafka.
---
[0] https://www.amazon.com/dp/1617290343
[1] https://en.wikipedia.org/wiki/Lambda_architecture
[2] https://speakerdeck.com/druidio/real-time-analytics-with-ope...
[3] https://engineering.linkedin.com/distributed-systems/log-wha...
In theory this sounds great, but you have to account for processing capacity.
While compute is getting cheaper, one of the key reasons streaming in lambda sacrifices quality over throughput is compute capacity (as well as timing). If you have to feed already stored data through the same streaming pipe, you either have to have a lot of excess capacity, be willing to pay for that additional burst or accept latency in your results (assuming you can keep up with your incoming workload and not lose data). There is no free lunch.
An excerpt from the article:
Furthermore, the big data tools can be combined using a growing number of data processing architectures — Lambda and Kappa, among others.
Especially interested in this part:
> If you have realtime data ingestion, you will also have to build tooling to re-ingest older data that has changed or needed to be amended. This will end up looking like a 'lambda architecture'
Druid's intro flow was awesome. The tutorials worked very well. I hit hiccups with Pinot's intro flow. The Pinot team has been very responsive on Slack to issues.
Here are some notes:
https://docs.google.com/document/d/1GiB8zoiJ4Qs10A1LdIr6lJGW...
Based on our earlier benchmark, Pinot query latency without star-tree is already slightly better than Druid: https://image.slidesharecdn.com/lnkdmeetupslides0426-1705021.... Though that benchmark was done years ago, the result seems consistent with the recent test run by @quietgolfer.
would love to learn by knowing about the use-cases where I should perhaps consider such technologies..
* Analyzing netflow logs (network management, numerous users)
* Content delivery (Mux.com)
* Web analytics (ContentSquare, CloudFlare)
* Generating optimized parameters for real time ad bidding (various, not too many recent talks on this)
* Log management and system observability (surprisingly common across industries-Sentry, Cloudflare, Uber)
* Valuing assets from market data, e.g. tick data (financial services, not many companies will discuss publicly)
Druid is used for some of these. I don't know that much about Pinot, hence cannot comment there.
Druid is good if you (1) make use of native integrations like Kafka (2) Need every field indexed for fast seeking to a few rows (3) can use the JSON API to make up for the still-in-beta SQL interface (4) don't need every single event/row as they are pre-aggregated (5) always have a time-column to partition by (6) want to use the native S3 tiering for older data (7) dont need joins and complex analysis
Imply's distribution is better than core Druid but it's still more operationally complex than Clickhouse and alternatives.
2) If you're looking to provide this type of analytics in the first place, you probably do want this. Being able to execute extremely fast, granular, ad-hoc queries over highly dimensional data is very powerful. I designed a reporting frontend that really took advantage of this and I always felt guilty when people would complimented me how fast it drilled down in to 10+ dimensions.
3) There are plenty of mature libraries for the Druid API.
4) This is almost inherent with any OLAP system. Although, even with high-cardinality data, Druid performs extremely well. Either way, you should have a backing data warehouse and offline, batch jobs if you need to perform BI analytics on row-by-row / non-aggregated data. Remember, OLAP sits on top of a data warehouse.
5) Yes, but technically you just need a key that increments.
6) Not sure if this is an argument against Druid. I've found the S3 deep storage / tiering to be very efficient and powerful, especially because you can create and push segments directly to storage and not run it through an indexer. S3 is also just an object store protocol specification now. There are lots of people who run S3-compatible object stores in-house. HDFS is also natively supported and another widely used storage backend in this space. Also, there are plenty of community extensions for other object stores.
7) Again, OLAP. Data flowing in to your OLAP layer should already be denormalized and ready for indexing. Also, you can join data in to your existing indices with Hadoop, etc. Druid support joins and lookups, although I've never used them. ClickHouse and other similar systems also don't do very well with joins. Maybe we have different definitions of "complex analysis," but in my experience, you can do some pretty crazy stuff with queries including writing your own JS functions, and if you're really dedicated, you can write your own extensions.
One thing that I feel like a lot of people miss is that Druid is specifically an OLAP layer designed for large scale, and I mean beyond Netflix size scale (they use Druid). Every individual component of Druid is designed to scale out independently and play nicely with your existing infrastructure. Similar to ES, you have nodes that have specific roles such as event ingress / real-time compute, query handlers, query brokers, historical data / cache / compute, etc. Then you also have a bunch of supporting architecture provided for you for (re)indexing data, coordinating the entire cluster, etc. Druid is huge, not an AIO (OLAP, OLTP, DWH, etc) analytics solution, and it takes more than one person to run a larger cluster, even though I did it for a few years.
Druid has a slight edge in data scale-out and indexed seeks but modern data warehouses are adding similar tiering features, along with with field indexing, full-text search, nested data records, and even in-memory rowstores for OLTP support.
They're all converging on the same feature set and eventually Druid will just become another data warehouse option, although I'd still recommend Clickhouse or MemSQL at that point.
- C-Store (column-oriented database storage and processing): http://db.lcs.mit.edu/projects/cstore/vldb.pdf and https://sookocheff.com/post/databases/c-store/
- BigQuery (the original Dremel-based query engine): https://cloud.google.com/files/BigQueryTechnicalWP.pdf
- Snowflake paper: https://www.snowflake.com/resource/sigmod-2016-paper-snowfla...
- Andy Pavlo's CMU distributed OLAP databases lecture: https://www.youtube.com/watch?v=dPMc7FZ3Gqo&t=1s
- MemSQL architecture: https://www.youtube.com/watch?v=HoompXCG5Mk
Start there and just read more about architecture of the various products. You'll learn the basics of distributed nodes that store data in column-oriented tables partitioned by a key and how to run queries over them using various performance techniques. Then you can dive into the unique differences of the various databases.
1. http://highscalability.com/
2. https://softwareengineeringdaily.com/
Other than that, reading the blogs of the various vendors is how I keep up (using Feedly with RSS). The modern projects are Redshift, BigQuery, Snowflake, Azure DW, MemSQL, Clickhouse, YellowBrick with older projects being Vertica, Teradata, Greenplum. It's also useful to follow the "new" distributed SQL projects like CockroachDB, Citus, TiDB, Vitess, Yugabyte.
For example, RedShift and BigQuery cannot be put behind a user facing backend. BigQuery has a default limit of 50 concurrent queries, if that's your user limit, perfect. RedShift takes seconds for queries that Druid and Clickhouse can answer in milliseconds.
Redshift is an always running cluster of scale-out distributed postgres forked by AWS so it can and does return results in milliseconds, very similar to Clickhouse although still not as advanced in performance techniques.
Bigquery is a completely managed model that uses far greater scale-out architecture designed for througput (petabytes in seconds) rather than latency, although it has real-time streaming, BI Engine (memory cache) and materialized views so you can get pretty close today.
Snowflake is another option that runs on top of major clouds using instances to access object storage and also has low latency when your cluster is running.
BigQuery's petabyte aggregations in seconds is a false claim. I just launched a query to count the number of records of a 1.4 PB dataset and it has taken over a minute to just schedule the query, I have 1000+ compute units available too. I have been able to perform a similar query in seconds on top of Druid; the cluster had a run rate of $25k/month at the time, while I believe we pay 40k+/month just on our BQ deal. Maybe under ideal conditions BigQuery can run such a query, but I have never seen it do such things.
I have experience with Snowflake, it is great if you are in AWS, but I wouldn't choose it over BigQuery. I feel that Snowflake's strength is in their data sharing protocol, which allows us to tap into the datasets of partners without major data transfer costs. We use it for that reason only.
Redshift and BigQuery are both OLAP products and their documentation and whitepapers state the same. The performance profiles of both are very different but that's due to the architecture. Redshift is an always running cluster of EC2 instances running their forked scale-out Postgres. BigQuery is an entirely different engine based on Dremel which focuses on parallelization as much as possible along with Google's proprietary datacenters that have petabits of internal bandwidth to support it. AWS Timestream is an entirely different product for time-series specific data, and basically
The only reason Druid is faster is because it pre-aggregates and indexes data. You can aggregate tables yourself in Redshift and use the right primary keys to get most of the same performance. BigQuery just launched materialized views and is working on scheduling improvements and in-memory caching as well. Snowflake now runs in Azure and GCP and can do sub-second responses when the warehouse is running and data is cached or the tables are smaller.
Druid is not only fast because it pre-aggregates, but the memory structure is designed for scans over hundreds of dimensions.
Materializing views in BigQuery is just one DAG task. Unless you don’t have something like Airflow on your stack, I don’t see how it is worth mentioning. We are talking about denormalized data, time series data.
I am speaking from experience with each one of these products. Perhaps I did it all wrong, but we certainly achieved the objectives we were after.
Our users expect responses in the milliseconds. The user experience is seriously degraded when you approach tens of seconds or batch queries.
When this is the case, Druid needs strong arguments to secure mindshare in the future. It is crazy to write a comparison of Druid and sth else these days and not mention Clickhouse at all.
But it feels like ads when you have the same person every time telling you the same thing
It's also been a huge boon to be able to use a single system for both "normal" database stuff as well as time-series.
[1] https://github.com/VictoriaMetrics/VictoriaMetrics/wiki/Case...
I haven't actually tried stressing TimescaleDB, but I imagine it would hold up well if given 50GB of RAM to play with.
My point really though, is that TimescaleDB is a great fit for a lot of use cases, with basically no learning curve if you're already au fait with Postgres. And of course if you're already using Postgres elsewhere in your system, it's one less component to manage.
I have some industrial IoT use case in mind though, and I'll definitely look more into VictoriaMetrics, so thanks for the tip!
For context, I'm an engineer that uses Postgres heavily, with some basic BigQuery + Redshift. I don't understand what the benefit of something like Clickhouse are over a standard warehouse.
In RedShift you can perform joins, but in Druid and Clickhouse you cannot, your data has to be denormalized. There are quasi-joins but they are not the same. If you are going to swap Redshift with Druid or Clickhouse, then you need to denormalize the data or design around the specific quasi-join concepts of these databases that abstract dictionary lookups.
Druid is operations heavy compared to Clickhouse, while RedShift is just a few button presses. I started using Druid in 2014 and back then to set up a cluster you needed a decent understanding of orchestration. There are plenty of “minimal ops” solutions for Druid nowadays, but IMHO all they are doing is abstracting the problem and not really solving it. To run a Druid cluster, you need 5 or 6 node types and to arrive at the proper configuration of resources for your data needs will take you a lot of time. Clickhouse requires a single node type.
Ingesting data is easy in RedShift, but you will have to work a bit more with Druid and Clickhouse. Two of the druid node types are dedicated to ingestion, so your data pipelines should allocate these dynamically. Loading data into Clickhouse is as simple as asking it to ingest a file, but since it is big data, you need a dynamic place to stage such file right? All I am saying is that I wish there was an easier way to pick up the data from private S3 buckets.
Performance is a wash between Druid and Clickhouse for me. I never did a proper head to head between the two. However, the product I built on top of Clikhouse is a next get version of the product I built on top of Druid, and in my experience Clickhouse achieves the same performance for a third of the costs. However, you must account that cloud computing has gotten cheaper over the years and my numbers for Druid come from 2017 which is the last time I approved a bill.
I forgot to mention PG. I have put small analytic datasets, 80 million rows or so, in Postgres with good results, but even then the response time was never less than a few seconds.
For straightforward filters and aggregations, yeah, Clickhouse is faster. However I read a PR by the Druid team where they added a vector engine. If they release that, then the performance gap should be smaller or maybe in Druid’s favor depending on the dataset.
You are right about insertions up to a point.
If you consider data loads as insertions, with Druid I could scale my cluster elastically to speed them up. With Clickhouse I am bound by the query nodes.
Also, Druid can ingest data in real time using a special type of node that wasn’t part of the original distribution. I haven’t done real time data ingestion on Clickhouse. Hourly updates are good enough for my use case.
The main advantage of Druid is that it pre-aggregates and indexes everything resulting in much smaller summarized data with fast seeks to specific rows. Materialized views, stored procedures, and ETL processes can provide the same result in relational data warehouses and come with much better SQL support as you mentioned.
You should try Redshift again as it's come a long ways since the early days. Also I highly recommend MemSQL which is faster than Clickhouse with a much easier deployment, more usability and can serve OLTP workloads in the same database.
There is already a bewildering number of options in this space, but did you try TimescaleDB (a time-series extension for Postgres)?
I'm using it with 100 million plus rows, and aggregate queries return in less than 100ms, even on my laptop - it really is very impressive.
I haven't used them yet, but it also supports continuous aggregates (automatic roll ups), which would make common queries near instantaneous.