4 ms·

ClickHouse is awesome, but there's a newer OLAP database in town: Apache Pinot, and it is significantly better: https://pinot.apache.org/ https://pinot.apache.o

ClickHouse is awesome, but there's a newer OLAP database in town: Apache Pinot, and it is significantly better: https://pinot.apache.org/ https://pinot.apache.org/ Here's why it is better: 1. User-facing analytics vs. business analytics. Pinot was designed for user-facing analytics (meaning analytics result is used by end-user (for example, "what is the expected delivery time for this restaurant?"). The demands are much higher, including latency, freshness, concurrency and uptime. 2. Better architecture. To scale out ClickHouse uses sharding. Which means if you want to add a node you have to bring down the database, re-partition the database and reload the data, then bring it back up. Expect downtime of 1 or 2 days at least. Pinot on the other hand uses segments, which is smaller (but self-contained) pieces of data, and there are lots of segments on each node. When you add a node, Pinot just moves around segments, no downtime needed. Furthermore, for high availability ClickHouse uses replicas. Each shard needs 1 or 2 replicas for HA. Pinot does not have shards vs replica nodes. Instead each segment is replicated to 2 to 3 nodes. This is better for hardware utilization. 3. Pre-aggregation. OLAP cubes became popular in the 1990s. They pre-aggregate data to make queries significantly faster, but the downside is high storage cost. ClickHouse doesn't have the equivalent of OLAP cubes at all. Pinot has something better than OLAP cubes: Star trees. Like cubes, star trees pre-aggregate data along multiple dimensions, but don't need as much storage.

mble_ 2y ago
Pinot is something I haven't had any personal experience with, so that's why it wasn't on the list - same with StarRocks, or Druid. Something for me to look into next year, clearly.
memset 2y ago
I’d like to learn more! Clickhouse benchmarks don’t show Pinot favorably by comparison. Also, do you have thoughts on Starrocks?
- shadow28 2y ago
  > Clickhouse benchmarks don’t show Pinot favorably by comparison Looks like they don't configure any indexes for Pinot in their benchmarks, which is one of Pinot's main selling points on the performance front - https://github.com/ClickHouse/ClickBench/issues/37 https://github.com/ClickHouse/ClickBench/issues/37.
  - PeterCorless 2y ago
    Confirmed. Also, ClickBench is working from a batch-loaded data set, which is kind of antithetical to a real-world, real-time analytical database workload. [Disclosure: I work at StarTree, and we're powered by Apache Pinot.] We are currently considering / evaluating different methodologies to benchmark more realistic situations for real-time analytics. Potential consideration for your own benchmarking / POCs, or for a future industry benchmark spec: 1. Some sort of "freshness" (data latency) measurement: time for streaming ingestion / indexing / data ready for query. Is it consistent, or are there pauses in ingestion? 2. Some sort of "ingestion scaling" measurement: how many objects per second can you get to before you choke IO? What happens to ingested objects at different payload sizes? (This interacts with "freshness" above; i.e., you might [or might not] be able to throttle ingestion to improve freshness.) 3. Query concurrency/throughput: does your query capacity scale linearly or non-linearly? What happens at 10 QPS? 100 QPS? 1000 QPS? 10000 QPS? 100000 QPS? (Or when does it top out?) 4. Data volume: Are you querying against 1TB? 10TB? 100TB? 1 PB? (More?) This interacts with query concurrency. Because driving 100 QPS against 1 PB is a totally different case than driving 100000 QPS against 1 TB. 5. Data storage type: Are you running against local NVMe, EBS, or S3 buckets? (Maybe even HDD?) Is it all uniform storage, or is it in a tiered storage topology? If tiered, what's the % mix of the different storage types? This is just an increasing reality all vendors need to deal with. Customers want to optimize their spend per use case. 6. Query complexity: Before talking simple "latencies," you have to understand what sort of queries you're running. These aren't simple atomic row CRUD operations like a Redis or a ScyllaDB. How are you doing aggregates? Are you running queries against denormalized data in a single table, or are you doing single JOINs or multiple table complex JOINs? 7. Indexing: As pointed out by shadow28, indexes are vital for best performance. Which type of index was used? (Apache Pinot supports about a dozen different types). And my personal favorite to throw into the mix: 8. Cluster resilience: Great. All of the above worked on a fully-provisioned stable cluster. Now knock out a node. Do it. See what happens. How long before the cluster rebalances and quiesces? What happens to your QPS and latencies during the rebalance, and then after quiescence? Measure that. Now knock out a 2nd node. Maybe a third. How many nodes can you bring down before performance goes non-linear, or the cluster is rendered as utterly unreliable. This latter I call the "Torpedo test;" and I've been preaching about it for years[1]. How many "torpedos" can you cluster take before it sinks under the waves. It's not specific to real-time OLAP. You can use this kind of methodology to test resilience of any distributed system. And you should probably do this before you hit production. [1] https://www.slideshare.net/slideshow/what-we-learned-about-application-resiliency-when-the-data-center-burned-down/250619480#20 https://www.slideshare.net/slideshow/what-we-learned-about-a...
hodgesrm 2y ago
> 2. Better architecture. To scale out ClickHouse uses sharding. Which means if you want to add a node you have to bring down the database, re-partition the database and reload the data, then bring it back up. This is not true in practice. In a properly designed implementation you can add new shards without downtime. ClickHouse compression is so efficient that most installations don't need to shard in the first place--everything fits on a single server with a few TB of storage. (We've put up to 50Tb on single servers.)
- breadwinner 2y ago
  If you want to scale out you have to add new nodes, and that means re-partitioning the same data (not new shards) across the new nodes. For example if you have 3 servers, each has 33% of the data, but if you add 1 more server, then each server has 25% of the data. If one server meets your scale requirements, then sure ClickHouse is a good choice.
  - hodgesrm 2y ago
    There are multiple ways to address shard rebalancing: 1. If you are loading time series data you can often just wait for the shards to rebalance automatically as new data arrives and old data drops out. 2. If you are scaling to add new tenants, just put them on a separate shard and let them fill naturally. 3. It's not hard to move table parts, if you want to redistribute data across shards. ClickHouse comes with commands to do this and it's fairly easy to script. 4. ClickHouse can scale vertically for a long way. It works fine on VMs with 256GB of RAM and 50 TB of storage. Just to be clear, it would be nice if open source ClickHouse could dispense with shards. In practice, though, it's not the biggest issue in most of systems I've seen (which is now in the hundreds), and there are several ways to design around it. Changing table partition keys is a harder issue to solve. Allocating adequate RAM for queries is another problem that's difficult to address.
maxloh 2y ago
Apache Pinot is written in Java, which is a garbage-collected language and also a rare choice for mainstream databases. Any idea if that would affect its performance? The last time I checked there are several databases written in Go, which is also garbage-collected, but never saw one in Java except Apache Derby.
- gunnarmorling 2y ago
  Concurrent garbage collectors of recent JVM versions (ZGC, Shenandoah) can give you sub-millisecond pause times, i.e. GC pauses are not really an issue any more for typical analytics query workloads. The price to pay is reduced throughput, but a scale-out architecture like Pinot makes it easy to make up for that by adding another node, if needed.
- mble_ 2y ago
  Apache Cassandra would probably be the most notable one (outside of Kafka etc).
  - negus 2y ago
    Yes. And this is the reason ScyllaDB exists
- vram22 2y ago
  I know of at least three databases written in Java: HSQLDB: https://en.m.wikipedia.org/wiki/HSQLDB https://en.m.wikipedia.org/wiki/HSQLDB H2: https://en.m.wikipedia.org/wiki/H2_(database) https://en.m.wikipedia.org/wiki/H2_(database) PointBase: https://en.m.wikipedia.org/wiki/PointBase https://en.m.wikipedia.org/wiki/PointBase I have briefly used either one of the first two, I think it was HSQLDB, with Java, years ago. PointBase was created by Bruce Scott, a co-founder of Oracle, and a few others, including an architect of Sybase. This was in the early days of Java. All three still seem to exist, according to the links above.
  - throwaway2k04 2y ago
    Neo4J
evnix 2y ago
Whenever I see a DB written Java, I run, very fast and very far away. I've never had good experience with these implementations. If you say, the DB isn't performing well, the only answer you get is, well you forgot to tune these 1000 knobs and good luck if your data doesn't fit in RAM. For some reason I feel your DB in a text file, would fare just as good as these given the same memory and resources that these Apache/java DB servers demand.
- LunaSea 2y ago
  Yes, I'm having ElasticSearch shard failures and Out Of Memory error fever every time I have to think about Java / Scala databases.

mble_ 2y ago

Pinot is something I haven't had any personal experience with, so that's why it wasn't on the list - same with StarRocks, or Druid. Something for me to look into next year, clearly.

memset 2y ago

I’d like to learn more! Clickhouse benchmarks don’t show Pinot favorably by comparison. Also, do you have thoughts on Starrocks?

shadow28 2y ago
> Clickhouse benchmarks don’t show Pinot favorably by comparison Looks like they don't configure any indexes for Pinot in their benchmarks, which is one of Pinot's main selling points on the performance front - https://github.com/ClickHouse/ClickBench/issues/37 https://github.com/ClickHouse/ClickBench/issues/37.
- PeterCorless 2y ago
  Confirmed. Also, ClickBench is working from a batch-loaded data set, which is kind of antithetical to a real-world, real-time analytical database workload. [Disclosure: I work at StarTree, and we're powered by Apache Pinot.] We are currently considering / evaluating different methodologies to benchmark more realistic situations for real-time analytics. Potential consideration for your own benchmarking / POCs, or for a future industry benchmark spec: 1. Some sort of "freshness" (data latency) measurement: time for streaming ingestion / indexing / data ready for query. Is it consistent, or are there pauses in ingestion? 2. Some sort of "ingestion scaling" measurement: how many objects per second can you get to before you choke IO? What happens to ingested objects at different payload sizes? (This interacts with "freshness" above; i.e., you might [or might not] be able to throttle ingestion to improve freshness.) 3. Query concurrency/throughput: does your query capacity scale linearly or non-linearly? What happens at 10 QPS? 100 QPS? 1000 QPS? 10000 QPS? 100000 QPS? (Or when does it top out?) 4. Data volume: Are you querying against 1TB? 10TB? 100TB? 1 PB? (More?) This interacts with query concurrency. Because driving 100 QPS against 1 PB is a totally different case than driving 100000 QPS against 1 TB. 5. Data storage type: Are you running against local NVMe, EBS, or S3 buckets? (Maybe even HDD?) Is it all uniform storage, or is it in a tiered storage topology? If tiered, what's the % mix of the different storage types? This is just an increasing reality all vendors need to deal with. Customers want to optimize their spend per use case. 6. Query complexity: Before talking simple "latencies," you have to understand what sort of queries you're running. These aren't simple atomic row CRUD operations like a Redis or a ScyllaDB. How are you doing aggregates? Are you running queries against denormalized data in a single table, or are you doing single JOINs or multiple table complex JOINs? 7. Indexing: As pointed out by shadow28, indexes are vital for best performance. Which type of index was used? (Apache Pinot supports about a dozen different types). And my personal favorite to throw into the mix: 8. Cluster resilience: Great. All of the above worked on a fully-provisioned stable cluster. Now knock out a node. Do it. See what happens. How long before the cluster rebalances and quiesces? What happens to your QPS and latencies during the rebalance, and then after quiescence? Measure that. Now knock out a 2nd node. Maybe a third. How many nodes can you bring down before performance goes non-linear, or the cluster is rendered as utterly unreliable. This latter I call the "Torpedo test;" and I've been preaching about it for years[1]. How many "torpedos" can you cluster take before it sinks under the waves. It's not specific to real-time OLAP. You can use this kind of methodology to test resilience of any distributed system. And you should probably do this before you hit production. [1] https://www.slideshare.net/slideshow/what-we-learned-about-application-resiliency-when-the-data-center-burned-down/250619480#20 https://www.slideshare.net/slideshow/what-we-learned-about-a...

hodgesrm 2y ago

> 2. Better architecture. To scale out ClickHouse uses sharding. Which means if you want to add a node you have to bring down the database, re-partition the database and reload the data, then bring it back up. This is not true in practice. In a properly designed implementation you can add new shards without downtime. ClickHouse compression is so efficient that most installations don't need to shard in the first place--everything fits on a single server with a few TB of storage. (We've put up to 50Tb on single servers.)

breadwinner 2y ago
If you want to scale out you have to add new nodes, and that means re-partitioning the same data (not new shards) across the new nodes. For example if you have 3 servers, each has 33% of the data, but if you add 1 more server, then each server has 25% of the data. If one server meets your scale requirements, then sure ClickHouse is a good choice.
- hodgesrm 2y ago
  There are multiple ways to address shard rebalancing: 1. If you are loading time series data you can often just wait for the shards to rebalance automatically as new data arrives and old data drops out. 2. If you are scaling to add new tenants, just put them on a separate shard and let them fill naturally. 3. It's not hard to move table parts, if you want to redistribute data across shards. ClickHouse comes with commands to do this and it's fairly easy to script. 4. ClickHouse can scale vertically for a long way. It works fine on VMs with 256GB of RAM and 50 TB of storage. Just to be clear, it would be nice if open source ClickHouse could dispense with shards. In practice, though, it's not the biggest issue in most of systems I've seen (which is now in the hundreds), and there are several ways to design around it. Changing table partition keys is a harder issue to solve. Allocating adequate RAM for queries is another problem that's difficult to address.

maxloh 2y ago

Apache Pinot is written in Java, which is a garbage-collected language and also a rare choice for mainstream databases. Any idea if that would affect its performance? The last time I checked there are several databases written in Go, which is also garbage-collected, but never saw one in Java except Apache Derby.

gunnarmorling 2y ago
Concurrent garbage collectors of recent JVM versions (ZGC, Shenandoah) can give you sub-millisecond pause times, i.e. GC pauses are not really an issue any more for typical analytics query workloads. The price to pay is reduced throughput, but a scale-out architecture like Pinot makes it easy to make up for that by adding another node, if needed.
mble_ 2y ago
Apache Cassandra would probably be the most notable one (outside of Kafka etc).
- negus 2y ago
  Yes. And this is the reason ScyllaDB exists
vram22 2y ago
I know of at least three databases written in Java: HSQLDB: https://en.m.wikipedia.org/wiki/HSQLDB https://en.m.wikipedia.org/wiki/HSQLDB H2: https://en.m.wikipedia.org/wiki/H2_(database) https://en.m.wikipedia.org/wiki/H2_(database) PointBase: https://en.m.wikipedia.org/wiki/PointBase https://en.m.wikipedia.org/wiki/PointBase I have briefly used either one of the first two, I think it was HSQLDB, with Java, years ago. PointBase was created by Bruce Scott, a co-founder of Oracle, and a few others, including an architect of Sybase. This was in the early days of Java. All three still seem to exist, according to the links above.
- throwaway2k04 2y ago
  Neo4J

evnix 2y ago

Whenever I see a DB written Java, I run, very fast and very far away. I've never had good experience with these implementations. If you say, the DB isn't performing well, the only answer you get is, well you forgot to tune these 1000 knobs and good luck if your data doesn't fit in RAM. For some reason I feel your DB in a text file, would fare just as good as these given the same memory and resources that these Apache/java DB servers demand.

LunaSea 2y ago
Yes, I'm having ElasticSearch shard failures and Out Of Memory error fever every time I have to think about Java / Scala databases.