ClickHouse Blog

Lovely! I only learnt today that clickhouse has a git-import tool from my colleagues at ClickHouse. So if you also want to give it a go:

Download clickhouse:
curl https://clickhouse.com/ | sh

Check out documentation for git-import:
./clickhouse git-import --help

Then the tool can be run directly inside the git repository.
It will collect data like commits, file changes and changes of every
line in every file for further analysis.
It works well even on largest repositories like Linux or Chromium.

Example of a trivial query:

SELECT author AS k, count() AS c FROM line_changes WHERE
file_extension IN ('h', 'cpp') GROUP BY k ORDER BY c DESC LIMIT 20

Example of some non-trivial query - a matrix of authors, how much code
of one author is removed by another:

SELECT k, written_code.c, removed_code.c,
round(removed_code.c * 100 / written_code.c) AS remove_ratio
FROM (
SELECT author AS k, count() AS c
FROM line_changes
WHERE sign = 1 AND file_extension IN ('h', 'cpp')
AND line_type NOT IN ('Punct', 'Empty')
GROUP BY k
) AS written_code
INNER JOIN (
SELECT prev_author AS k, count() AS c
FROM line_changes
WHERE sign = -1 AND file_extension IN ('h', 'cpp')
AND line_type NOT IN ('Punct', 'Empty')
AND author != prev_author
GROUP BY k
) AS removed_code USING (k)
WHERE written_code.c > 1000
ORDER BY c DESC LIMIT 500

> I can't find any other open source time series database except Mimir/Cortex which allows this much scale (clustering options in their open source version)

The following open source time series databases also can scale horizontally to many nodes:

- Thanos - https://github.com/thanos-io/thanos/

- M3 - https://github.com/m3db/m3

- Cluster version of VictoriaMetrics - https://docs.victoriametrics.com/Cluster-VictoriaMetrics.html (I'm CTO at VictoriaMetrics)

> Can we use Prometheus/Mimir as general purpose time series database?

This depends on what do you mean under "general purpose time series database". Prometheus/Mimir are optimized for storing (timestamp, value) series where timestamp is a unix timestamp in milliseconds and value is a floating-point number. Each series has a name and can have arbitrary set of additional (label=value) labels. Prometheus/Mimir aren't optimized for storing and processing series of other value types such as strings (aka logs) and complex datastructures (aka events and traces).

So, if you need storing time series with floating-point values, then Prometheus/Mimir may be a good fit. Otherwise take a look at ClickHouse [1] - it can efficiently store and process time series with values of arbitrary types.

[1] https://clickhouse.com/

ClickHouse | REMOTE| FULL-TIME| Various locations globally

ClickHouse is the fastest and most resource efficient open-source database for real-time apps and analytics. Our engineering teams invest heavily in improving the most loved ClickHouse open source project as well as building ClickHouse Cloud a fully cloud Native SAAS offering with separation of compute and storage. We are a globally diverse and distributed team, united behind a common goal of creating industry-leading, real-time analytics. Here, you will have an opportunity to solve some of the most cutting edge technical challenges and have direct ownership of your work and vision. If you are a contributor by nature, a thinker as well as a doer - we’ll definitely click!

Lot of engineering positions open across different teams- please apply on our website: https://clickhouse.com/company/careers

Yep, that trusty simple algorithm always works. I have some grafana dashboards that plot a lot of 3D data (x,y,z lines coloured by some sensor readings) using a plotly plugin. I can't pre-aggregate too much in the database, or I'll loose too much detail. So I ended up adding a little ad-hoc js implementation to reduce my number of points by a factor ten on the fly for plotting, works great.

I was just about to write down that it would be too difficult to de RDP simplification in the (ClickHouse) database, but then I recalled PostGIS has it built in and low and behold, there is also something in ClickHouse for this [0]. Back to the drawing board.

[0] https://clickhouse.com/codebrowser/ClickHouse/contrib/boost/boost/geometry/strategies/agnostic/simplify_douglas_peucker.hpp.html

There are various knobs in ClickHouse that allow you to trade memory usage for performance. ( https://clickhouse.com/docs/en/operations/settings/query-complexity#settings-max_bytes_before_external_group_by e.g.)

But yes, I've seen similar issues, running out of memory during query processing, it's a price you pay for higher performance. You need to know what's happening under the hood and do more work to make sure your queries will work well. I think postgres can be a thousand or more times slower, and doesn't have the horizontal scalability, so if you need to do complex queries/aggregations over billions of records then "return result" doesn't cut it. If postgres addresses your needs then great- you don't need to use ClickHouse...

Hi. Sorry if my query offended you.

I basically executed literally what Clickhouse recommends at their guides for deduplication https://clickhouse.com/docs/en/guides/developer/deduplication.

Of course you can also materialize with aggregations or just use a group by, or even force optimize of the table. But my point is that you don't really get exactly once guarantees. Whoever is querying that table needs to be aware than a `SELECT * FROM tb` might contain duplicates and needs to adapt their queries accordingly.