r/Clickhouse

When running a test suite of 250 concurrent users we identified clickhouse as a bottleneck due to the nature of our queries. It’s a simple select query with multiple where clauses. The problem is that the table itself contains 200+ million records and our goals is to optimise the query in such a way that we get results under 1 seconds.

Things we have tried out

1. Projections
2. Skinny materialized view (this was working fine but was showing stale data in UI because of the nature of mv and using refreshable mv was cpu intensive process)
3. Partitioning of data
4. Horizontal scaling

PS : we are using a OLAP as OLTP (ik it’s wrong). Problem is happening when we are trying to performs a search it’s scanning all the 200 million records.

Is there any way to optimise this ?

I am trying to install RITA and Clickhouse is a dependency for the container to run. I noticed Clickhouse uses ports 8123 and 9000. I tried running the container but i get an error because i believe another container is also using 9000...which is my keycloak application.

The main question is can i set Clickhouse to run on a different port than 9000? Only asking because keycloak is already on that port and it might be a hassle changing on that end.

I wrote a query last week that ran fine on Postgres and DuckDB, and hard-errored on ClickHouse and BigQuery - this sent me down a rabbit hole for most of the day.

Here's what I had:
```
SELECT term, MAX(ranking_page_count) AS ranking_page_count
FROM ranked
GROUP BY term
HAVING MAX(ranking_page_count) >= 2
```

The CTE already had a column called ranking_page_count. I aliased MAX() of it to the same name, because why not, and then used that name again in HAVING.

So which one does HAVING actually filter by? Turns out that's a matter of opinion.

In Postgres, HAVING can’t see SELECT aliases at all. So it reads the column directly and lands on the same max anyway - no error, right answer.

DuckDB does let you use aliases in HAVING, but only as a fallback, and it won't put one inside an aggregate, so this also runs. This is the one that got me, since DuckDB is where I test locally.

BigQuery gives the alias priority over the column. So it read my query as MAX(MAX(...)) and gave the error "aggregations of aggregations are not allowed"

ClickHouse just swaps aliases in everywhere, so it gave code 184 illegal aggregation. it even fails when the alias isn't shadowing anything.

The thing that finally made it click for me was processing order. FROM, WHERE, GROUP BY, HAVING, then SELECT, then ORDER BY. Aliases get created in SELECT, so when HAVING runs the alias doesn't exist yet. That's why Postgres says no, and why everything else here is a vendor extension rather than four equally valid readings.

ORDER BY is the only clause that runs after SELECT, which is why it's the only clause where nobody argues.

What actually worries me is that it can go completely silent. Drop the aggregate from the alias and the loud error disappears:
```
SELECT term, ranking_page_count * 10 AS ranking_page_count
FROM ranked
GROUP BY term, ranking_page_count
HAVING MAX(ranking_page_count) > 4
```

Postgres and DuckDB filter on `ranking_page_count`
BigQuery and ClickHouse filter on `ranking_page_count * 10`
I get 1 row from the first two and 4 rows from the other two, and not one of them raises an error about it.

That's the version that ends up on a dashboard.

ok fine, I learned my lesson and won't name an aggregate after the column it aggregates...

If you work across different engines, this is your reminder to go check 🥲

Hi all, I'm looking to explore ClickHouse through a personal POC. What would be the best hands-on project to understand its strengths, especially for Observability use cases?

Also, does ClickHouse offer any free trial, learning credits, or evaluation program for individuals interested in trying it?

We’re planning to use ClickHouse as the backend for a multi-tenant observability/tracing platform.

What is the recommended approach for multi-tenancy in ClickHouse?

Specifically, would you recommend:

A shared database/table with tenant_id as a column?

A separate database per tenant?

Separate tables for each tenant?

Using ClickHouse RBAC/row policies to enforce tenant-level data isolation?

We expect potentially many tenants, with high-volume trace/span data and queries frequently filtered by tenant_id.

What approach has worked well in production, and what are the main scalability, performance, and operational trade-offs we should consider?

Hi everyone,

I'm able to use Clickhouse on an ARMv8-A system with this image:

altinity/clickhouse-server:22.8.15.25.altinitystable

Problem is: the version is to old for a software I use that needs Clickhouse. Is there any newer version to just use out of the box as a Docker image?

All versions from different distributors I've tested deliver different errors while trying to start it. Different error codes.

Thanks!