HN Explorer

Ask HN: Suitable database to store web analytics data for million insertion

1 points by r0ckysharma ↗ HN
Hi Hackers,

I am looking for an experience-based comment for cost-effectively storing millions of web analytics data in a database. What database have you chosen, and if possible, answer why as well!

I have a few timeseries databases in mind, but apart from that - if anyone else has some other solution which has worked them in a highly cost-effective way for extremely high insertion and low read, especially for storing web analytics data.

Thanks

10 comments

[ 3.3 ms ] story [ 27.9 ms ] thread
iknownothow
1. How many insertions do you expect per second or per minute?

2. What's the size of each insert?

3. At the end of one year, what's the total size of your dataset?

4. How long can your largest and most complex analytical query take to finish? Should it finish in a minute? Is it okay if it takes an hour? Is it okay if it takes upto 24 hours?

r0ckysharma
1. 80,000 - 125,000 ( on peak per minute ) - expecting 5-6 times in increase in coming few months.

2. Size of each insert ( approx 1 KB )

3. Year end datasize = Not available ( too early to guess, but average 600-700 GB )

4. Query must finish in around a minute or around.

iknownothow
The total dataset size doesn't seem much at ~1TB. So you can use Clickhouse for fast analytics like others have suggested. Consider hosting on Hetzner since they have cheap Nvme disks.

The biggest problem you're going to face is ingestion of these events during peaks at 500k events per minute. You can't ingest them individually into Clickhouse or most other databases. So unfortunately you will have to add one additional streaming layer to cache these events so you can create batches of events once every few seconds and ingest a big batch of 1k-10k events into Clickhouse. AWS API Gateway + Kinesis is operationally easy to set up and quite cheap and should be able to handle your peak load. Afterwards use a Lamda to batch >1000 events from Kinesis and insert into Clickhouse. I've never tested this last part so I'm not sure how it will work out.

It'd be nice to know what you eventually go with. Please send me a message if you can of what you've finally chosen.

richraposa
ingestion of these events during peaks at 500k events per minute. You can't ingest them individually into Clickhouse or most other databases.

Turn on async_insert or use a Buffer table engine and you can easily insert them individually into ClickHouse

iknownothow
That's interesting! I don't have much experience with Clickhouse, especially not in the last two years. I'll have to try this out myself. That's a pretty incredible if it can handle batching internally.
openplatypus
Depends on the use case.

You will find folks recommending Clickhouse.

We use Kafka and Elasticsearch with Wide Angle Analytics.

Kafka gives us scalable and cheap storage potential. Kafka Streams means we can easily create "live" aggregates.

Elastic search gives us fast data discoverability.

We chose our stack because of existing expertise in the team.

Is this the easiest setup? No.

Is it scalable? Yes.

Is it cheap? Can be.

openplatypus
A word of warning. Running managed Kafka and Elasticsearch quickly become expensive. The worst (limited and expensive) is AWS.

That's why our in-house solution, combined with our expertise gives us a very cost-effective formula.