Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Scaling request logging with ClickHouse, Kafka, and Vector

geocod.io

Post reply on HN
Print view View original

21–29 of 29 posts

Re: Scaling request logging with ClickHouse, Kafka, and Vector

#21

Post by ch2026 »

anticodon wrote: post #20
est wrote: post #19

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.

Or vice versa: make ClickHouse ingest batches directly from Kafka. Messages are already buffered in Kafka, I don't get why Vector is necessary here.

tbh the only thing they needed was a correct schema that didn’t constantly spawn new parts and async_insert enabled.

Re: Scaling request logging with ClickHouse, Kafka, and Vector

#23

Post by solatic » 

  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.

Re: Scaling request logging with ClickHouse, Kafka, and Vector

#25

Post by est »

anticodon wrote: post #20
est wrote: post #19

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.

Or vice versa: make ClickHouse ingest batches directly from Kafka. Messages are already buffered in Kafka, I don't get why Vector is necessary here.

https://clickhouse.com/docs/knowledgebase/kafka-to-clickhous...

For anyone if curious.

Re: Scaling request logging with ClickHouse, Kafka, and Vector

#26

Post by fermuch »

rozenmd wrote: post #2

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,…

Materialized views are a great tool for aggregating data in CH since they are automatically updated on insert from the original table. I recommend you to take a look and try it out, maybe it'll go down to single digit milliseconds!

Re: Scaling request logging with ClickHouse, Kafka, and Vector

#28

Post by ansgri »

fermuch wrote: post #26
rozenmd wrote: post #2

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,…

Materialized views are a great tool for aggregating data in CH since they are automatically updated on insert from the original table. I recommend you to take a look and try it out, maybe it'll go down to single digit milliseconds!

And there are 2 kinds of those: the other is refreshable materialized views, which run on schedule, can have dependencies between them, thus can implement quite complex data transformation pipelines.
Post reply on HN