Scaling Request Logging with Clickhouse, Kafka, and Vector

Great write-up!

I had a similar project back in August when I realised my DB's performance (Postgres) was blocking me from implementing features users commonly ask for (querying out to 30 days of historical uptime data).

I was already blown away at the performance (200ms to query what Postgres was doing in 500-600ms), but then I realized I hadn't put an index on the Clickhouse table. Now the query returns in 50-70ms, and that includes network time.

BTW you could've used e.g. kittenhouse (https://github.com/YuriyNasretdinov/kittenhouse, my fork) or just a simpler buffer table, with 2 layers and a larger aggregation period than in the example.

Alternatively, you could've used async insert functionality built into ClickHouse: https://clickhouse.com/docs/optimize/asynchronous-inserts . All of these solutions are operationally simpler than Kafka + Vector, although obviously it's all tradeoffs.

Seems weird not to use Redis as the buffering layer + minutely cron job. Seems a lot simpler than installing Kafka + Vector.

How does Clickhouse compare to Druid, Pinot or Star Tree?

1) clickhouse async_insert would have solved all your issues: https://clickhouse.com/docs/optimize/asynchronous-inserts

1a) If you’re still having too many files/parts, then fix your partition by, and mergetree primary key.

2) why are you writing to kafka when vector dev does buffering / batching?

3) if you insist on kafka, https://clickhouse.com/docs/engines/table-engines/integratio... consumes directly from kafka (or since you’re on CHC, use clickhouse pipes) — what’s the point of vector here?

Your current solution is unnecessarily complex. I’m guessing the core problem is your merge tree primary key is wrong.

can you just buffer some writes in Vector and eliminate Kafka?

I setup some Vector to buffer ElasticSearch writes years ago, also for logs, it ran so well without any problems that I almost fogot about it.

weird you have to adopt Kafka AND Vector just to batch a bit of writes into Clickhouse...

Thanks for sharing. I really enjoyed the breakdown, and great to see small tech companies helping each other out!

  Geocodio offers a pay-as-you-go metered plan where users get 2,500 free geocoding lookups per day. This means we need to:
  Track the 2,500 free tier requests
  Continue tracking above that threshold for billing
  Let users view their usage in real-time on their dashboard
  Give admins the ability to query this data for support and debugging
  Store request details so we can replay customer requests when debugging issues

Just on the basis of what you wrote here, I'm not convinced ClickHouse is the right tool. ClickHouse very much would help with helping you crunch statistics for latencies etc., but just for billing and getting individual query data? 1) push the request to Kafka/Pub Sub/etc. 2) one consumer pushing to TigerBeetle for tracking request usage within the free tier and other billing 3) one consumer to push individual requests to object storage, which scales out infinitely-ish, allows you to get full request details for an individual request, lifecycle rules will automatically async delete old requests for you. If request statistics is important for business analysis, then instead of (boring) object storage you could look at one of the newer Iceberg-based options on top of object storage, e.g. S3 tables; as long as querying an individual request remains fast and getting statistics can be generated, say, for a nightly report. Another cheap approach could hook up another consumer to the PubSub, any request with too-high latency above a reasonable threshold, dump it into a Slack channel with a reference to the request ID so someone can look into debugging it.

Currently at the millions stage with https://mailpace.com relying mostly on Postgres

Tbh this terrifies me! We don’t just have to log the requests but also store the full emails for a few days, and they can be up to 50 mib in total size.

But it will be exciting when we get there!

Thanks for sharing I enjoyed reading this.

Sai from ClickHouse here. Very compelling story! Really love your emphasis on using the right tool for the right job - power of row vs column stores.

We recently added a MySQL/MariaDB CDC connector in ClickPipes on ClickHouse Cloud. This would have simplified your migration from MariaDB.

https://clickhouse.com/docs/integrations/clickpipes/mysql https://clickhouse.com/docs/integrations/clickpipes/mysql/so...

I shared this article internally and my peers were impressed about how similar it is to our final implementation. (It differs in the fact that we use Redis as queue.)

Happy to exchange notes about our journey too.

Cheers

I shimmed vector in my log pipeline recently and it really is a wonderfully simple and powerful tool. It's where I transform logs of software I don't own in to prometheus metrics and drop useless logs from making it to loki.