Clickhouse works exceptionally well as a TSDB.
If you're building it with a specific application and a concrete schema you can create which will result in fast queries and don't have requirements for arbitrary dimensions being specified for lookup, then yes it's great as a TSDB.
Prometheus, M3DB, etc all use an inverted index alongside the column store TSDB to help with metrics workloads.
With regards to trie vs inverted index for Graphite data, I'd actually still be inclined to say inverted index is better based on the amount of queries I saw at Uber with Graphite where people did `servers.*.disk.bytes-used` type queries which is way faster to do using an inverted index since you have a postings list for each part of the dot-separated metric name, rather than traversing a trie with thousands to tens of thousands of entries in index 1 host part of the Graphite name. This is what M3DB does[0].
[0]: https://github.com/m3db/m3/blob/b2f5b55e8313eb48f023e08f6d53...
Regarding the auto-rebalance feature, I cannot much more agree with you. It's something that clickhouse definitely need to handle internally.
I'm assuming this is an out of process inverted index used alongside ClickHouse? Or is it more of a secondary table contained by ClickHouse which can be searched to find the metrics, then the data is looked up?
The latter scales not as well with billions of unique metrics since it's always a scan across the unique metrics stored in the time window your query searches for (since any arbitrary dimensions can be specified, all must be evaluated). This is the drawback of PromHouse which is an implementation of Prometheus remote storage on top of ClickHouse - and the major reason why PromHouse was only ever a proof of concept rather than a production offering.
I agree that python implementation of graphite was not particularly fast but there was faster implementation in C that companies used first to significantly increase performance. Then coordination of storage backend becomes complex when you try to scale the initial design. This is where clickhouse really shine. It provide out of the box distributed storage with compaction, rollup and fast querying. The other layers are stateless, which means that they'll scale with your computing ressources.
M3DB is roughly doing the same thing as clickhouse but clickhouse is much more advanced database that has proven records of running at petabyte scale without a sweat. For example they now have tiered storage which means that you can store recent event in nvme and rollup to standard HDD...
[1]https://medium.com/avitotech/metrics-storage-how-we-migrated...