3

A lot of people don't want to use ClickHouse just to do analytics for their company or project. They want to use it as the backbone for SaaS data/analytics projects. Which most of the time would require supporting semi-structured json data, which could result in creating a lot of columns for each user you have.

Now, some experinced ClickHouse users say less tables means more performance. So having a seperate table for each user is not an option.

Also, having the data of too many users into the same database will result in a very large number of columns, which some experiments say it could make CH unresponsive.

So what about something like 20 users per database having each user limited to 50 columns. But what if you got thousands of users? Should you create thousands of databases?

What is the best solution possible to this problem?

Note: In our case, data isolation is not an issue, we are solving it on the application level.

Marwan abdel moneim
  • 33
  • 4

1 Answers1

0

There is no difference between 1000 tables in a single database and 1000 databases with a single table.

There is ALMOST no difference between 1000 tables and a table with *1000 partitions partition by (tenant_id, .some_expression_from_datetime.)

The problem is in overhead from MergeTree and ReplicatedMergeTree Engines. And is in number of files you need to create / read (data locality problem, not related to files, will be the same without a filesystem).

If you have 1000 tenants, the only way is to use order by (tenant_id,..) + restrictions using row policies or on application level.

I have an experience with customers who have 700 Replicated tables -- it's constant straggle with the replication, need to adjust background pools, the problem with Zookeeper (huge DB size, enormous network traffic between CH and ZK). Clickhouse starts for 4 hours because it needs to read metadata from all 1000000 parts. Partition pruning works slower because it iterates through all parts during query analysis for every query.

The source of the issue is the original design, they had about 3 tables in metrika i guess.

Check this for example https://github.com/ClickHouse/ClickHouse/issues/31919

Luc
  • 5,339
  • 2
  • 48
  • 48
Denny Crane
  • 11,574
  • 2
  • 19
  • 30
  • 1
    I am (and other people too) struggling to understand why a big number of tables or databases would be an issue. Maybe I need to read more about MergeTrees. But my understanding is ClickHouse must be handling those databases or tables in separate concurrent processes. So maybe startup time will be huge, but why would that affect querying time, if the VM had enough resources? -- Also, I understand your solution, that is exactly what we are doing. But supporting semi-structured data (for a lot of tenants) using arrays or usual columns will result in a huge number of [materialized] columns. – Marwan abdel moneim Jan 14 '22 at 23:14
  • Note: We could have many clusters, and maybe many ClickHouse installations inside each cluster (still not sure how to do it exactly). So I am not talking about 1K databases or tables per installation. I don't even want to have 1K databases spread across the clusters, we want a small number, but somehow the math doesn't work. -- 20 Users limited to 50 columns means 1K columns, that table is ruled out now. So what happens when you have thousands or maybe millions of users? – Marwan abdel moneim Jan 14 '22 at 23:21
  • I am also struggling with the same issue – Apurv Gupta Jun 01 '22 at 12:08
  • Multitenancy I think is a half-solved problem in CH – danthegoodman Jul 26 '23 at 22:26