How's bigquery performance in comparison to clickhouse? When is it worth switching from one to the other?
So as an in-house solution, ClickHouse most probably would be the fastest option (if your use case suits OLAP requirements).
For clouds / PaaS - it's hard to compare directly. Do you know how many servers will process your BigQuery request? AFAIK usually BigQuery shows a bit higher performance than single mid-level ClickHouse server (but you can also have a cluster of ClickHouse servers).
Otherwise it's really difficult to compare without knowing how much data you have, how often you query, etc.
The answer depends massively on the intended use case.
If you do a "SELECT field1, field2 FROM AAA" you'll pay only for the total size of field1 and field2 rows.
So you usually want to use BigQuery in situations where you don't need to query data all the time, but rather a fixed number of times a day.
Now about performance: BigQuery queues queries, so you don't have a guaranteed time. It can take a couple of seconds before your query starts running, it can take longer. If you need something that responds in < xxx ms, BigQuery is not it.
But the queries themselves are fast. If you need to query across petabytes of data, as a simple BigQuery query will gladly run on however many dozens or hundreds of instances it needs, at no additional cost for you (since you only pay by the size of your data queried).
It's really a great example of serverless. You can run your query across 100 instances, but you only use those instances for a few seconds.
> SELECT * FROM AAA
> "SELECT field1, field2 FROM AAA" you'll pay only for the total size of field1 and field2 rows.
Another example is materialized view creation. It's common for these to scan large quantities of data to compute aggregates.
A where clause still searches the entire column, unless it is conditioned on the partition column.
"Partition your table and create clustered columns and filter on those"