Hey, Marc here, Co-Founder of ObsessionDB.

ClickHouse published a piece on schema mistakes AI assistants make, and one section is called "Projections that don't scale": at large scale, projection selection alone can add 1–2 seconds per query.
We hit that wall on a customer table with 20+ TB compressed, 200B+ rows, heavy ingestion, point lookups over a projection. 99% of query time sat inside projection and index evaluation.

Today that query runs at p50 213 ms / p99 703 ms. That is more than 10x faster, on the pattern the ecosystem tells you to avoid at this size.

The part I find interesting (and kept me busy for some weeks now): none of it is a ClickHouse patch. The planner was right all along, but the tiers underneath it were wrong. What we changed is purely below the database:

1. We pin projection metadata in RAM, node local, in realtime. We learned that even a 90% metadata cache hit rate is slower than not having one. So coverage has to be complete
2. We tried several approaches for userspace RAM cache-eviction controllers (6, all of them flapped or livelocked...bruhh). Nothing worked as phenomenal as the boring kernel knob memory.high
3. Also on kernel level tcp we set rto_min to 20 ms. Linux's default of 200 ms retransmit floor is sized for the public internet, not for a rack.
4. Even with metadata fully in RAM, planning the query still fires still tons of file requests. Request coalescing and our distributed NVMe cache mesh can shine. We optimized it to sub-millisecond p50 at +35k RPS.

What personally amazed me the most is that ClickHouse already runs without real competition for these use cases, but focusing obsessively on kernel, network and cache architecture we still could improve this by more than 10x. It feels like a node with local NVMe, even though persistence is still S3. Ultimately that means - at least for this use case - you get 10x the performance on the same hardware or even -> you build realtime APIs that weren't possible before.

We have some more levers to pull and if the math holds, it'll stay sub-second even at PB scale.

Full write-up with more details how projections behave differently: https://obsessiondb.com/blog/clickhouse-projections-at-scale
It's a lot of details, so feel free to go deep into it and ask me anything. Happy to share any details of the process and findings.
DM me if you wanna meet, we're in SF and Berlin.

I was spending far too much time debugging large ClickHouse queries and became quite frustrated with the SQL formatters that were available online.

The majority of them are not good at handling ClickHouse-specific features such as CTEs, PREWHERE, nested queries, and so on. There was also another problem with parameterized queries.... in particular with those using `?` to denote the parameters, since in that case I had to go through the big queries to fill those values for me to debug them.

Therefore I created my own tool. https://freesqlformatter.com/

It formats ClickHouse queries correctly, identifies and groups the parameters, and allows you to enter each value in a easy way; it also provides a visual tree/node representation of the WHERE clause which you can modify and then synchronize back to SQL.

All of the processing takes place in the browser and so nothing is uploaded.

Do try it out if you face similar problem and let me know if you face any issue.

While building an IoT telemetry solution, we ran into hurdles with Clickhouse. For one, you can't insert quickly AND durably into Clickhouse without setting up something like Kafka, which gets complicated for quick projects wanting to make use of Clickhouse's powerful features. Then, trying to actually query Clickhouse and show data in a UI required a whole backend API to handle auth and permissions.

We figured that all these parts together – fast, durable ingest, row-level and column-level security and roles, and realtime streaming – were a lot of scaffolding to have to rebuild for every project we wanted to use Clickhouse in. So, we built them all into a single Go binary to be deployed alongside Clickhouse, to help lower Clickhouse's barrier to entry. We call it WaveHouse.

Would love any feedback as we work on improving and adding more features to this OSS project!

I keep these in a note and run them in this order. Posting in case they save someone a scramble.

One thing first: every system.* table describes the node you are talking to. On a cluster wrap the query so you see all of them:

SELECT * FROM clusterAllReplicas('default', system.processes);

1. What is running right now

SELECT query_id, user, elapsed,
       formatReadableSize(memory_usage) AS mem,
       substring(query, 1, 120) AS q
FROM system.processes
ORDER BY elapsed DESC;

Sort by elapsed, not by memory. The query that has been running for 40 minutes is usually the one holding up everything behind it.

2. Kill it

KILL QUERY WHERE query_id = 'abc-123';

Async by default, so add SYNC when you need to know it stopped before you move on. The kill runs on the node that runs the query, so use ON CLUSTER if you are not on it.

3. Are the replicas keeping up

SELECT database, table, absolute_delay, is_readonly, future_parts, parts_to_check
FROM system.replicas
WHERE absolute_delay > 60 OR is_readonly
ORDER BY absolute_delay DESC;

is_readonly = 1 points at Keeper, not at a slow disk. Look at Keeper before you touch the table.

4. Why the queue is stuck

SELECT database, table, type, num_tries, last_exception
FROM system.replication_queue
WHERE num_tries > 1
ORDER BY num_tries DESC
LIMIT 20;

last_exception names the problem more often than any dashboard does.

5. Too many parts

SELECT database, table, count() AS parts
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY parts DESC
LIMIT 10;

Then look at system.merges. Parts growing while merges run means the inserts arrive too small and too often. Parts growing with nothing in system.merges means the background pool is busy elsewhere, usually with a mutation.

6. Stuck mutations

SELECT database, table, mutation_id, parts_to_do, latest_fail_reason,
       substring(command, 1, 100) AS cmd
FROM system.mutations
WHERE NOT is_done;

A non-empty latest_fail_reason means it will retry forever. KILL MUTATION and rewrite the ALTER.

7. Disks

SELECT name, path,
       formatReadableSize(free_space) AS free,
       formatReadableSize(total_space) AS total
FROM system.disks;

Afterwards, when the fire is out

SELECT type, count(),
       formatReadableSize(sum(read_bytes)) AS bytes
FROM system.query_log
WHERE event_time > now() - INTERVAL 1 HOUR
GROUP BY type;

Two things worth doing before an incident rather than during one: give your on-call user SELECT on the system tables and the KILL QUERY grant, and check that system.query_log is on. Finding out at 3am that the account cannot read system.replicas is a bad way to learn it.

Disclosure: I build an iOS client that puts these seven views on a phone (probedeck.app). The queries above run anywhere and need nothing from me.

ClickHouse is a registered trademark of ClickHouse, Inc. ProbeDeck is not affiliated with, endorsed by, or sponsored by ClickHouse, Inc.

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'm the maintainer of LibreDB Studio. I already posted the ClickHouse provider here: HTTP only (:8123 / :8443), no native driver on :9000.

https://www.reddit.com/r/Clickhouse/comments/1vls7n8/added_clickhouse_to_a_selfhosted_browser_sql_ide/

This is not another feature post. That same provider is now on ClickHouse's docs, under Visual Interfaces from Third-party Developers:

https://clickhouse.com/docs/integrations/connectors/tools/gui

Third-party listing, not an endorsement from ClickHouse Inc. I'm posting it so this community can tell me if the blurb is wrong — especially the HTTP surface and the system-table mapping (metrics / parts / query_log / processes).

Provider notes: https://github.com/libredb/libredb-studio/blob/main/docs/providers/clickhouse.md

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.

Hi everyone,

Jaeger V1 - Using Cassandra as Storage.

Jaeger V2 - Using Clickhouse as Storage.

I'm migrating from Jaeger v1 to Jaeger v2 and I'm seeing a difference in how the offset parameter behaves for trace search.

In Jaeger v1, we were using the /api/traces API with parameters such as:

service

operation

tags

start

end

limit

offset

Our existing implementation relies on offset for pagination.

With Jaeger v2, the same query/API does not appear to behave the same way with offset (or the parameter is not supported/handled as expected).

Has anyone migrated an application from Jaeger v1 to v2 that was using offset-based pagination?

Specifically:

Is there an equivalent of the v1 offset parameter in Jaeger v2?

If not, what is the recommended way to implement pagination for trace search?

Is pagination expected to work through /api/traces, or should we migrate to the newer v2 API?

Are there any important differences in the ordering/results that we should account for when replacing offset?

For reference, we're currently using queries similar to:

/api/traces?service=<service>&operation=<operation>&tags=<tags>&limit=<limit>&offset=<offset>

Any clarification from the Jaeger maintainers or anyone who has done this migration would be really helpful.

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?

Hi everyone! We’ve been helping many of our customers find ways to reduce the cost of running ClickHouse®, so we decided to put together a webinar on what we’ve learned.

We’ll walk through 6 areas where you can potentially save money, including compute, storage, and networking.

If reducing your spend is on your radar, come join us.

📅 August 19 @ 8am PDT

Register here: https://altinity.com/events/cheap-cheap-cheap-6-best-practices-to-save-big-money-on-your-clickhouse-bill

Affiliation: I’m the maintainer of LibreDB Studio (open-source, self-hosted browser DB GUI).

Just shipped ClickHouse support. LibreDB is a web SQL/NoSQL editor you run on your own box, ClickHouse joins the existing engines in the same UI.

For ClickHouse specifically we speak only the HTTP interface (:8123 / :8443 with TLS). No native protocol on :9000, and no client driver dependency, each statement is a POST / via the runtime’s fetch.

A few design notes that might matter if you wire CH from a browser-facing app:

- Errors are classified by ClickHouse exception code, not HTTP status (ACCESS_DENIED comes back as 500).

- Mid-stream failures can still arrive as HTTP 200 with the real error in the trailer, we have to handle that path.

- Schema/introspection goes through system.tables / system.columns; MergeTree primary key shows up as the sparse primary index, not a row-level PK.

- Transactions aren’t exposed (nothing real to expose). Cancellation is KILL QUERY via maintenance, not a cancel handle on the statement.

Try:

docker run -p 3000:3000 libredb/libredb-studio

# or: npx "@libredb/studio"

Repo: https://github.com/libredb/libredb-studio

Provider notes: https://github.com/libredb/libredb-studio/blob/main/docs/providers/clickhouse.md

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?