5 ms·

> I wouldn't let my user-facing app write to Clickhouse I’ve been thinking of doing exactly that. What are your concerns?

claytonjy 5y ago
I suppose it depends what you're going to let your user do, but OLAPs in general and Clickhouse in particular don't do well under row-oriented workloads, as described in the post here. I'm imagining users primarily operating on small numbers of rows and sometimes making updates to or deleting them, a worst-case scenario for Clickhouse but best-case for an OLTP like Postgres.
- encoderer 5y ago
  Ah totally. Thanks for sharing your thoughts! In my case I’m evaluating clickhouse as a source of truth for customer telemetry data. Totally agree about the OLTP limitations.
  - willvarfar 5y ago
    (Remember that clickhouse is not reliable. It doesn’t pretend to be. Clickhouse is great for lots of common query workloads, but if losing your data would be a big deal then it makes a lot of sense to have your data in a reliable and backed up place (eg timescale or just s3 files or whatever) too. Of course lots of times people chuck stuff into clickhouse and it’s fine if they lose a bit sometimes. YMMV.)
    - RhodesianHunter 5y ago
      I have not found this to be the case. Like any system you need to take precautions (replicas and sharding) to ensure no data loss, but I didn't find that to be challenging. In what way have you found ClickHouse particularly risky in this way?
      - willvarfar 5y ago
        It’s basic computer science. Clickhouse doesn’t fsync etc. Clickhouse (and other systems with the same basic architecture, like elastic search and, shudder, mongodb) work very well on happy path. They are not advertising themselves as ACID.
        jd_mongodb 5y ago
        MongoDB has ACID support.
        qoega 5y ago
        You can enable fsync in ClickHouse. And it will not decrease bandwidth.
dreyfan 5y ago
https://blog.cloudflare.com/http-analytics-for-6m-requests-per-second-using-clickhouse/ https://blog.cloudflare.com/http-analytics-for-6m-requests-p... has some good thoughts. The main thing you'll likely need is some sort of a buffer layer so you can do bulk inserts. Do not write a high-volume of single-row inserts into Clickhouse.
- encoderer 5y ago
  Thanks for sharing the link! I’ve heard the bulk insert thing before and to be honest I’ve always thought that RDBMSs don’t love single row inserts either. Seems clickhouse takes that to a new level. In our case we are using sqs and usually insert 20-100 rows into the db at a time so I’m going to benchmark how that does in clickhouse.
  - deleted 5y ago
    [deleted]
  - zepearl 5y ago
    With Clickhouse you can use a "buffer table", which uses just RAM and sits on top of a normal table: https://clickhouse.com/docs/en/engines/table-engines/special/buffer/ https://clickhouse.com/docs/en/engines/table-engines/special... Rows inserted into the buffer table are then flushed to the normal/base table when one of the limits (defined when the buffer table is created) is reached (limits are max rows, max bytes, max time since the last flush), or when you drop the buffer table. I'm using it and it works (performance difference can be huge compared to perform single inserts directly into a real/normal table), but be careful - the flushed rows don't give a guarantee of which row is flushed in which sequence, so using a buffer table is a very bad idea if your base table is something which relies on the correct sequences of rows that it receives.
  - RhodesianHunter 5y ago
    On a project I worked on we found the sweet spot to be 20k-60k rows per insert.
- shaklee3 5y ago

claytonjy 5y ago

I suppose it depends what you're going to let your user do, but OLAPs in general and Clickhouse in particular don't do well under row-oriented workloads, as described in the post here. I'm imagining users primarily operating on small numbers of rows and sometimes making updates to or deleting them, a worst-case scenario for Clickhouse but best-case for an OLTP like Postgres.

encoderer 5y ago
Ah totally. Thanks for sharing your thoughts! In my case I’m evaluating clickhouse as a source of truth for customer telemetry data. Totally agree about the OLTP limitations.
- willvarfar 5y ago
  (Remember that clickhouse is not reliable. It doesn’t pretend to be. Clickhouse is great for lots of common query workloads, but if losing your data would be a big deal then it makes a lot of sense to have your data in a reliable and backed up place (eg timescale or just s3 files or whatever) too. Of course lots of times people chuck stuff into clickhouse and it’s fine if they lose a bit sometimes. YMMV.)
  - RhodesianHunter 5y ago
    I have not found this to be the case. Like any system you need to take precautions (replicas and sharding) to ensure no data loss, but I didn't find that to be challenging. In what way have you found ClickHouse particularly risky in this way?
    - willvarfar 5y ago
      It’s basic computer science. Clickhouse doesn’t fsync etc. Clickhouse (and other systems with the same basic architecture, like elastic search and, shudder, mongodb) work very well on happy path. They are not advertising themselves as ACID.
      - jd_mongodb 5y ago
        MongoDB has ACID support.
      - qoega 5y ago
        You can enable fsync in ClickHouse. And it will not decrease bandwidth.

dreyfan 5y ago

https://blog.cloudflare.com/http-analytics-for-6m-requests-per-second-using-clickhouse/ https://blog.cloudflare.com/http-analytics-for-6m-requests-p... has some good thoughts. The main thing you'll likely need is some sort of a buffer layer so you can do bulk inserts. Do not write a high-volume of single-row inserts into Clickhouse.

encoderer 5y ago
Thanks for sharing the link! I’ve heard the bulk insert thing before and to be honest I’ve always thought that RDBMSs don’t love single row inserts either. Seems clickhouse takes that to a new level. In our case we are using sqs and usually insert 20-100 rows into the db at a time so I’m going to benchmark how that does in clickhouse.
- deleted 5y ago
  [deleted]
- zepearl 5y ago
  With Clickhouse you can use a "buffer table", which uses just RAM and sits on top of a normal table: https://clickhouse.com/docs/en/engines/table-engines/special/buffer/ https://clickhouse.com/docs/en/engines/table-engines/special... Rows inserted into the buffer table are then flushed to the normal/base table when one of the limits (defined when the buffer table is created) is reached (limits are max rows, max bytes, max time since the last flush), or when you drop the buffer table. I'm using it and it works (performance difference can be huge compared to perform single inserts directly into a real/normal table), but be careful - the flushed rows don't give a guarantee of which row is flushed in which sequence, so using a buffer table is a very bad idea if your base table is something which relies on the correct sequences of rows that it receives.
- RhodesianHunter 5y ago
  On a project I worked on we found the sweet spot to be 20k-60k rows per insert.
shaklee3 5y ago