Given our current scale (hundreds of millions of payments), however, we’re running into performance issues with out current ElasticSearch setup. While it still works perfect for filtering, we realized that ElasticSearch may not be the perfect tool to use for statistics as it’s becoming too resource intensive. We’re currently looking into alternatives, but we’re not sure what to use.
What tools would you suggest to use for this?
Columnar databases are awesome, for the right kinds of task the speedup can be multiple orders of magnitude. Columnar databases excel at filtering and aggregating a subset of columns, storing sparse or slowly-changing data, and timeseries operations.
Of course there's always tradeoffs. Columnar databases tend to suck at reading individual rows, reading large number of columns, and heavy writes.
Another option is of course to put a caching layer between the dashboard and ElasticSearch, and precompute common queries e.g. daily.
Feel free to message me if you want to chat, email in profile.
[1] https://github.com/ClickHouse/clickhouse-presentations/blob/...
Disclaimer: some of the "others" are customers of my employer Altinity, which offers support for ClickHouse.