Forwarding Fly.io Logs to ClickHouse

It is a database specifically designed for analytical queries (think: GROUP BYs against hundreds of millions of rows.) These are queries that are untenable in Postgres.

It stores data on disk in a structure which is optimized in two ways: allows for very fast aggregations, and makes compression significantly easier. Each column is stored in a separate file, and that file is a tree data structure which is continuously rebalanced.

The trade off is that querying all columns of a single row (SELECT * WHERE user_id=1) is slower. But querying an aggregation (SELECT avg(age)) is much much faster.

The name clickhouse literally means "a data warehouse for click stream data".

Thus, clickhouse is a good choice for log aggregation: there is a lot of data, it is compressible, and often we want to create reports ("how many HTTP 400 requests per day") on logs. This blog post is how to use my tool to ingest logs into clickhouse.