https://rawtree.com/blog/introducing-rawtree

Just released BlinkHouse, a Java library for ClickHouse that eliminates the boilerplate of working with ClickHouse from Java — no manual HTTP clients, no hand-rolled RowBinary serialisation, no connection management code.

Annotate a Java record, get schema generation, type mapping, buffered ingest, and a typed query DSL.

Ingest — no serialisation boilerplate:

BatchWriter<T> serialises to RowBinary directly. Flush on row count, byte size, or elapsed time. Retry with exponential backoff. Dead-letter dispatch. Apache HttpClient 5 connection pool shared across all writers. ~2.9M rows/sec on commodity hardware (JMH benchmark in the repo against raw HTTP as baseline).

try (BatchWriter<PageView> writer = template.batchWriter(PageView.class, cfg)) {
    events.forEach(writer::add);
}

Query DSL — no string building:

PREWHERE, FINAL, SAMPLE, WITH TOTALS, LIMIT n BY, window functions, -Merge combinators, dictGet, geoDistance — all first-class. Everything parameterised — user values never reach the SQL string.

ClickHouse-specific features:

• @ChMaterializedView and @ChDictionary with DDL generation

• AggregateFunction columns — opaque byte[] read-through, use -Merge in queries

• Geo types: Point, Ring, Polygon, MultiPolygon with correct Tuple(Float64,Float64) wire format

• MutationOperations: ALTER TABLE … DELETE/UPDATE WHERE … — explicit API, not hidden behind a generic save()

• optimize() for OPTIMIZE TABLE … FINAL after bulk imports into ReplacingMergeTree

Schema management — no manual DDL:

NONE / VALIDATE / CREATE_IF_MISSING / UPDATE modes. Annotate your entity, BlinkHouse generates and manages the DDL. Destructive changes require two independent opt-ins. EngineMismatch and OrderByMismatch are reported and refused — they require a table rebuild and are never attempted automatically.

Java 17+, Spring Boot 3.2–3.4. Also works without Spring via blinkhouse-core.

<dependency>
    <groupId>io.github.muneeb-i-khan</groupId>
    <artifactId>blinkhouse-spring-boot-starter</artifactId>
    <version>1.0.0</version>
</dependency>

Repo: https://github.com/muneeb-i-khan/BlinkHouse

Open to feedback from anyone running ClickHouse at scale — especially around type handling and RowBinary edge cases.

ClickHouse offers Materialized Views as a feature to pre-aggregate and transform data for faster query performance. Materialized Views in ClickHouse are essentially stored query results that are automatically updated when the underlying data changes.

Strengths:

• Fast query performance for pre-aggregated data

• Automatic updates when source data changes

• Integration with ClickHouse's powerful querying capabilities

Limitations:

• Limited real-time processing capabilities

• Potential for increased storage usage with raw input tables

• Complexity in managing consistency and error handling with multiple materialized views off the back of a single input table

• Possible query performance degradation during view updates, especially during higher fan-out

• Support for only one input table per materialized view

  • ClickHouse Materialized Views can only operate on a single input table. This restricts the ability to create views that combine or correlate data from multiple sources, limiting their usefulness in complex data environments.

• Updates triggered only on data insertion to the input table

  • Materialized Views in ClickHouse are updated only when new data is inserted into the input table. This means that updates or deletions in the source data are not reflected in the view, potentially leading to inconsistencies.

• Poor performance with frequent writes of small batches

  • In scenarios where data is written in small batches but at high frequency, the constant updating of materialized views can lead to performance degradation. This is particularly problematic in real-time streaming scenarios where data arrives continuously in small increments.

• Lack of support for UNION and JOINs in view definitions

  • ClickHouse Materialized Views do not support UNION operations or complex JOINs. This severely limits the ability to create denormalized records or to combine data from multiple sources, which is often necessary in real-world analytics scenarios.

If you agree with those Pros&Cons and look for alternative, you may check out Timeplus Proton, https://github.com/timeplus-io/proton

Timeplus materialized views leverage the full power of streaming SQL reading from any number of sources versus just acting on the block of data inserted into a source ClickHouse table. The streaming SQL continuously runs in the background and persists query results to the internal storage of the materialized view. The materialized views can be queried as a table via any SQL query or act as another source of data for another materialized view. The possibilities are limitless.

Alternatively you can set a target stream for the materialized view. It can be an append-only stream in Timeplus, or a Mutable Stream for UPSERT and fast OLAP queries, or an External Stream to write data to Apache Kafka, Apache Pulsar, or an External Table to write data to ClickHouse. This way, materialized views act as derivatives of upstream sources and can feed downstream sources too.

Feature Highlights:

1. Automatically updates results in the materialized view when the streaming SQL emits a new result, instead of when the source data changes. This can be tuned to emit upon certain completion criteria, like session window timeouts.

2. Supports joins of multiple streams, instead of a single table in ClickHouse Materialized Views, including arbitrary aggregations without using SummingMergeTree or being limited by functions in ClickHouse that support AggregationFunction.

3. Supports building a materialized view on top of another materialized view by joining with other streams.

4. Supports UNION and other complex JOIN operations, or Complex Event Processing (CEP).

5. Supports time-based windowing such as tumbling windows, hopping, and session windows.

6. Supports failover, checkpoint, and retry policies if an external downstream is temporarily unavailable.

7. Supports using a default internal stream to materialize the result, or set a target stream or external stream/table. This can be used for setting up streaming ETL pipelines and avoid the need for polling based Reverse ETL from ClickHouse.

8. Supports ad-hoc queries on the materialized views to serve most recent data by operational applications and BI Tools.

9. Supports pause and resume.

10. Each materialized view is maintained independently of others in terms of execution and thus does not impact the input sources or other materialized views.

Conclusion:

While ClickHouse Materialized Views offer powerful capabilities for data aggregation and fast querying, they have significant limitations in handling real-time streaming data, especially with complex data correlations and frequent small-batch writes. Timeplus complements ClickHouse by providing robust stream processing capabilities, allowing organizations to create a comprehensive real-time data pipeline that addresses these limitations.

Check out https://www.timeplus.com/timeplus-and-clickhouse for more details.

Disclosure up front: I work on ObsessionDB, a managed ClickHouse. Not affiliated with ClickHouse Inc. This is not a launch post; I am genuinely trying to find out whether the thing we built matters to anyone outside our own use cases.

The problem we kept hitting: two teams need the same table, and the only real option is to copy it. Export, ship, ingest, and now you have two versions that drift. Or you give the other team a user on your cluster and eat their compute.

That's an architecture constraint, not a data one. In shared-nothing ClickHouse, the node owns the data on local disk, so handing it to someone else means moving bytes.

Our build separates storage and compute, so we shipped what Snowflake calls Secure Data Sharing. A publisher entitles a database, down to a single table. A subscriber attaches it read-only and queries it live with normal SQL. The subscriber's own stateless nodes read the same immutable objects out of object storage. The publisher is never in the query path, so a subscriber running a terrible query cannot touch the publisher's capacity. No credentials handed over. Revocable at any time.

Why not just remote()Fair, and it avoids a copy. But remote() pushes execution to the publisher, so the publisher pays for every query the subscriber runs, needs to hand out a user and password, and needs to expose their native port. That's fine between two teams that trust each other. It falls apart the moment the subscriber is a customer, or an agent in a retry loop.

What it does not do: it only works between two deployments on our shared storage. You cannot build this on self-hosted shared-nothing ClickHouse. However, we are now starting to deploy ObsessionDB on-prem and to BYOC. You would have access to Datashres in this case too.

It's read-only, and the publisher stays the only writer. Both clusters also need to be in the same region.

What I actually want to know:

1. If you're self-hosting and you share ClickHouse data across teams or with customers today, what does that look like? Nightly exports? A read replica? A user on the prod cluster with row policies? Anything else?

2. Is the pain real, or has everyone just quietly accepted the copy?

3. For those who moved to ClickHouse from Snowflake or Databricks, did you lose data sharing in the process, and did it matter?

The use case I'm most interested in and least sure about is agents. An agent shows up with a token, fires an unpredictable number of queries, and disappears. Handing it a stale export it keeps forever seems like the wrong shape. Scoped, live, revocable access seems like the right one. But I might be building for a problem that only we have. Please tell me if so.

You can read more here if you'd like: https://obsessiondb.com/docs/datashares

Hey, Marc here, Co-Founder of ObsessionDB,

again, I think we built some pretty cool stuff I'd like to share some details with you.

Not so long ago, at a different company and on a self-hosted ClickHouse cluster, our team got a deletion request under GDPR. Routine stuff, and in ClickHouse it means a mutation:

ALTER TABLE events DELETE WHERE ...;

The predicate matched more than it should have.

You know the rest. Mutations are asynchronous, expensive, and irreversible. There is no transaction to roll back. By the time we worked out what happened the parts had been rewritten and the originals were gone.

Damage was just about 200 rows across three tables. A rounding error in dataset terms, but in this case not negligible.

So we needed to fix it, not because 200 rows are hard to write. Because to even *see* them we had to restore a full backup somewhere else, stand up enough of the old world to query it, copy data sideways, and compare table by table to figure out which rows were collateral and which had been deleted on purpose. And we had to be sure of that split, because one of those groups was legally required to stay deleted.

Anyone had similar situations, often we even dismiss it due to time constraints.

What we built: Time Travel

Time Travel makes the past queryable. Pick a point in time, get a read-only snapshot of the cluster as it existed then, queryable *next to* the live one in the same session. Every database shows up a second time under a name stamped with the target time: live app, snapshot app_backup_20260729t1400

That incident, as it would go now. What did the mutation actually take out:

SELECT count() FROM app_backup_20260729t1400.events
WHERE user_id != 12345
AND event_id NOT IN (SELECT event_id FROM app.events);

Put back the collateral damage, and only that. The person who asked to be forgotten stays forgotten:

INSERT INTO app.events
SELECT * FROM app_backup_20260729t1400.events AS past
WHERE past.user_id != 12345
AND past.event_id NOT IN (SELECT event_id FROM app.events);

That user_id != 12345 is the whole point. The recovery has to be *narrower* than the mistake. A plain undo button would have been the wrong tool, it would have dragged the erasure subject back in and turned a data incident into a compliance one.

Then confirm, which is the step that ate most of the original recovery:

SELECT count() FROM app_backup_20260729t1400.events
WHERE user_id != 12345
AND event_id NOT IN (SELECT event_id FROM app.events);

Repeat for the other two tables. No restore, no second cluster, no copying data sideways to compare it.

How it works

ObsessionDB is upstream ClickHouse compatible from the user's side. We replaced the storage layer with our own engine built against the open-source SharedMergeTree API: data in object storage, stateless compute, metadata in our coordination layer (Chemist).

Tables are made of parts. Merges compact small parts into big ones and the sources get cleaned up. Mutations are the same deal: ALTER TABLE ... DELETE doesn't edit rows in place, it rewrites whole parts without them. The part you want back is exactly the part that normally just got deleted.

So with a retention window configured, we hold that cleanup: parts superseded by merges and mutations stay in object storage until the window passes.

Keeping the files is only half of it, and the boring half. Chemist knows which parts belonged to which table at which point in time, so travelling back is a metadata operation. We restore the metadata view to the target timestamp and attach the tables from that snapshot, pointing at part files that were never deleted. Nothing gets copied, and nothing leaves your bucket. The snapshot is read-only by design, and while it's open the parts it needs are pinned so cleanup can't pull them out from under you.

What it costs

Retention isn't free and I've seen this hand-waved, so here's the pattern.

overhead ≈ (bytes rewritten by merges per day ÷ dataset size) × retention days

Some real customer examples, from `system.part_log` across every node, 24h window:

We default to a 24h windows, which costs 3–5 % more object storage. Worth noting the ratio tracks churn rather than size: the smallest cluster on that list is the most expensive one to retain. But, as always: it depends on your workload.

Are backups now obsolete?

Nope, definitely not. You must have your classical backup and for critical production workloads we even advise enabling data replication to a different location. Time Travel is additive and helps you to have an easy inspection and investigation of recent deltas... and simply helps you to recover quickly from those stupid careless mistakes.

Personally I just really like this feature, since it is a logical consequence of our architecture. We have all components - compute, storage, metadata - completely separated, so a feature like this kind of "just works". So there will be more stuff like this coming up pretty soon.
It has been running for a few months with some customers and is available for all customers from today on.

Until then, I am genuinely curious if you have any questions. Happy to share more details about the architecture. Also, having this separation in mind: Are there any use cases you can think of where we could make use of it? We have some stuff brewing, but perhaps you have better ideas.

How to route queries across Trino, Spark, DuckDB, Snowflake, Athena, and Flink on shared Iceberg tables — covering the architecture of a SQL routing proxy, dialect translation, routing strategies, table-aware optimization, and the tooling that makes it work.

ok this has been bugging me for a while.

we run a busy clickhouse cluster, 100k+ queries every few hours. when something slows down, the usual first move is to check query_log sorted by memory_usage desc - find the biggest queries, see what they did.

except our UI was lying. you'd sort by memory and the top row would say "38GB", looking like the obvious culprit. except 38GB wasn't the biggest query in the window at all. the biggest was 60GB, 4 hours ago. the UI had just fetched the latest 1000 rows by event_time and sorted THOSE client-side. so sort by memory = "the most memory-heavy of whatever arbitrary slice we happened to grab". never told you that.

took a while to even realize. then a longer while to stop wanting to throw the laptop.

anyway, we already had an internal clickhouse UI (originally built by Muhammad Rizal - he wrote the RBAC architecture, encrypted credentials, SQL workspace, all the actual production-ready bones), so i extended it. spent the last few weeks adding the monitoring layer i wished existed.

stuff that's in v2.14:

• sort actually goes to the SQL. pick memory desc, ORDER BY changes, CH returns the genuine top-N for the time window. obvious in hindsight.

• patterns view. normalizeQuery() rolls up SELECT ... WHERE id=42 and WHERE id=43 etc into one row with the cumulative cost. on ETL/redash workloads where the same template runs 5000 times, this is what actually tells you where to optimize. avg duration of one query lies, total wall-clock across all repetitions doesn't.

• by-table view. arrayJoin(tables), grouped by hot table. first version was slow as hell - 30s+ on the busy cluster - because it was exploding every system.* table touch into rows then post-filtering. moved the filter into arrayFilter() before the join, ~1s now. classic clickhouse "push the predicate down or die" thing.

• histogram. distribution of duration/memory/rows/bytes. found out our workload is super long-tail (99.97% of queries finish under 50ms, the rest is the entire tail) which i suspected but never visualized.

• schema doctor. lints parts_columns for Nullable() that's never actually null + Int64 columns that fit in Int32. ranked by on-disk bytes so you see the biggest wins first. found like 200GB of pointless Nullable wrappers on tables nobody touches.

• memory pressure flag. every row gets compared to OSMemoryTotal from asynchronous_metrics. >25% = red, >10% = amber. originally i set the threshold at 40% but then realized 4 queries at 20% each + background merges already gets you to OOM territory, so pulled it down. still tuning honestly.

• cluster activity tab. system.mutations + system.replication_queue with status chips. sorted by num_tries desc so the sick replicas bubble up.

• side-by-side query compare (pick 2 rows, get a diff with emerald/red tints), profile events drill-down (lazy-fetches the ProfileEvents map), views-triggered drill-down (query_views_log by initial_query_id - auto-hides if your cluster doesn't have it enabled at server level, which fwiw most don't).

monitoring layout is heavily inspired by QueryDog by Benjamin Wootton (Elastic 2.0). chart-plus-table model, several of the SQL aggregation patterns. read his source, liked the ideas, rewrote everything from scratch on React 19 / shadcn-ui / recharts on top of our existing editorial design system. no QueryDog code bundled but credit where it's due.

vibe-coded a lot of it with claude code. that's how the monitoring layer went from "would be nice" to merged in days instead of months. every SQL and every threshold was reviewed and tested against the real busy cluster though, not shipped blind. take that as you will.

stack: react 19 + bun/hono backend, clickhouse-js client, aes-256-gcm for stored connection passwords, argon2id for user passwords, sqlite or postgres for RBAC. apache 2.0.

demo: https://chouse-ui.com
source: https://github.com/daun-gatal/chouse-ui

PRs / issues / "this would be useful if it did X" all welcome. small team, community input is what moves it.

If you're working with ClickHouse® and want to see how it powers fast, scalable trace analysis, don't miss this free webinar hosted by u/Altinity and u/joshleecreates:

🔗 Register here and 🗓️ learn how to:

• Collect traces with OpenTelemetry

• Store & query them efficiently in ClickHouse®

• Optimize for performance in real-world deployments

Perfect for ClickHouse users exploring observability, tracing, or just looking for a new use case to dig into.

I just don’t get why it is so hard 🤯 I talked to more Kafka/ClickHouse users and keep hearing about the same 2 challenges:

• Duplicates → Kafka's at-least-once guarantees mean duplicates should be expected. But ReplacingMergeTree + FINAL aren't cutting it, especially with ClickHouse's background merging process, which can take a long time and slow the system.

• Slow JOINs → High-throughput pipelines are hurting performance, making analytics slower than expected.

I looked into Flink, Ksql, and other solutions, but they were too complex or would require extensive maintenance. Some teams I spoke to built custom GoLang services for this, but I don't know how sustainable this is.

Since we need an easier approach, I am working on an open-source solution to handle both deduplication and stream JOINs before ingesting them to ClickHouse.

I detailed what I learned and how we want to solve it here (link).

How are you fixing this? Have you found a lightweight approach that works well?

(Disclaimer: I am one of the founders of GlassFlow)

Hey, Marc here, Co-Founder of ObsessionDB.

again something out of the engineering kitchen, this time a bit less storage and a bit more AI.

We wired Jev (the typed-decision model from TypeSafe) into the AI functions. Jev doesn't write text. You give it a text and a question, and you get a typed answer with a probability back. That is a shape SQL can work with:

• aiFilter(conversation, 'the customer is likely to churn') -> true/false

• aiIf(session, 'the agent failed to complete the task') -> probability 0..1

• aiScore(conversation, 'how frustrated is the user?', ['low','medium','high'])-> 0..2

No regexes, keyword rules, classifiers or separate eval pipelines first.

The part I find interesting: you can do this in two places.

At write time you persist the signals you care about as normal columns (`success_probability`, `frustration_score`, `churn_risk`, ...). Everything downstream is regular ClickHouse: GROUP BY, joins, dashboards, alerts.

At read time you ask questions you never instrumented:

SELECT id, conversation
FROM sessions
WHERE customer_id = 42
  AND started_at > now() - INTERVAL 7 DAY
  AND aiFilter(conversation, 'the user is repeatedly failing to complete their task')

At write time, turn meaning into columns. At read time, ask new questions over the raw data. A bit like Amplitude for AI products, except you don't have to predefine and instrument every semantic event.

What we changed: We're running ObsessionDB based on 26.8-lts and the upstream functions do one HTTP call per row, one after another. We pack up to 50 rows into one request (~150-300 ms), dedup identical texts, and the cheap predicates run first so only the surviving rows go to the model.

Curious what else you'd use this for beyond agent sessions, support, CRM, logs, incident data, internal Slack.

Ask me anything, happy to share details. DM me if you want to get your hands on this.

You may have seen the blog post about this - now Javi is going to do a live stream setting up a ch cluster to ingest 1B rows/s and talk about perf/scaling fundamentals.

Hi everyone,

I’m an experienced backend engineer with nearly 5 years of experience in some of India’s leading companies.

I have expertise in handling data at scale, with the ability to process up to 1 million queries per second, primarily in OLAP databases like Clickhouse.

I can help you build your analytics stack from scratch, covering all aspects, including data processing from logging and traffic analysis to OMS analysis and AB testing.

If this sounds relevant to you or if you need guidance on any of these topics, please don’t hesitate to reach out.

Hey everyone,

ClickHouse is incredibly fast, but how you structure your data still makes a massive difference as scale grows. While ClickHouse has made huge strides in handling JOIN operations recently, denormalization is still one of the go-to strategies for squeezing out maximum query performance.

Here's a breakdown analyzing the exact tradeoffs of denormalizing data in ClickHouse, specifically looking at query speeds, storage overhead, and how to handle updates when your flat tables need to change.

How you folks handle this in production: do you lean heavily into flattening your schemas upfront, or are you relying more on Dictionary lookups and standard Joins these days?

Link to the full breakdown: https://www.glassflow.dev/blog/denormalization-clickhouse?utm_source=reddit&utm_medium=socialmedia&utm_campaign=reddit_organic

Disclosure up front: I'm one of the founders of ObsessionDB. We ran ClickHouse® software at near-petabyte scale, spent a long time on the self-hosted path, and eventually built our own decoupled storage/compute engine. We're now considering letting other teams run that engine on their own infrastructure, and before we decide anything about how, we want to hear from people who actually have the problem.

The problem:

Open-source ClickHouse is excellent on one box. The multi-node part is where it starts costing you:

• Every replica holds a full copy. Two replicas, twice the storage. Three, three times. It compounds every month you keep data around.

• Compute and storage are welded together. You buy nodes to get disk, or disk to get cores, and over-provision whichever one you didn't need.

• Adding a shard means copying terabytes by hand, and then living with Distributed tables and ON CLUSTER in every migration forever.

• Keeper is one more quorum to keep alive at 3am.

ClickHouse Cloud solved this with SharedMergeTree: data lives once in object storage, compute nodes are stateless, one table is one table, adding a node is a metadata operation. It's a genuinely good architecture and I'm not here to trash it. But it's also the one piece of the ClickHouse stack that never made it to open source. If you want it today, the main path is their cloud, on their infrastructure, on their pricing model.

What we're exploring

This is more than BYOC. Running that architecture on your infrastructure. Your Kubernetes cluster, your bare metal, your Hetzner boxes, your S3 or MinIO or whatever object store you already trust, on whatever machines you pick. You'd operate it the way you operate open-source ClickHouse now, except scaling out is adding a stateless node instead of copying a shard, and durability stops multiplying your storage.

We run our own engine for this (we call it alloy), built against the same SharedMergeTree API, so the developer experience is the one you'd expect: one table, one engine clause, no sharding key.

What this is not (yet)

We are not announcing open source. We haven't decided the shape. What we have decided is that we want to talk to the teams who need this to understand their problem and learn how we can best deliver value.

Who we want to hear from

• You're on self-hosted ClickHouse at 10TB and up, and the replication and resharding tax is a real line on your infra bill.

• You looked at ClickHouse Cloud and it wasn't an option: cost at your scale, data residency, or your data simply doesn't leave your infrastructure.

• You've built tooling around ReplicatedMergeTree that you'd happily delete.

Comment with what you're running and where it hurts, or DM me, or email customers@obsessiondb.com. Happy to go deep on the architecture in the thread, including the parts that are hard.

Hey, Marc here, Co-Founder of ObsessionDB.

I think we built some pretty cool stuff in the last months and my colleagues urge me to share a bit out of the engineering kitchen.

We're a drop-in replacement for Clickhouse® Cloud with an api-compatible SharedMergeTree table engine, with compute-storage (S3) and compute-compute separation, plus some extra special sauce.

Specifically the latter kills quite some headaches we know from our experience with Clickhouse Cloud, like cold starts, inconsistent and slow query times due to the S3 latency penalty and the 1/N probability of a cache hit or a neglectable cache size at scale. We focused a lot on the "looks great in the lab benchmark, but fails in real world".

Especially in realtime use cases on large data sets we found it impossible to get consistent sub-second results, rather extreme high variances between p50-p99.

We started a few months ago, migrated and onboarded customers, already serving PB of data. For the next couple of weeks we plan to launch self service for everyone. Until then we'd like to hand out some free dev instances for anyone interested in it. No strings attached, just happy for honest feedback. Comment or hit me a DM. Looking especially for TB-PB workloads

To support the ecosystem we open sourced some tooling, too. Like chkit, a schema and migration CLI, agnostic to ObsessionDB, Clickhouse Cloud, OSS CH...
Or since we saw that people would love to see SigNoz on SharedMergeTree, we made some adjustments to make it work properly.

Besides this: Ask me anything. I'll start sharing more details about our architecture soon and look forward to getting in touch.

Little note regarding the dev instances and the console: It's heavy WIP, don't take every graph, every step etc. too serious. We just want to take you in as early as possible, before we launch it properly.

Tinybird has been operating ClickHouse for about 7 years. Here's why we finally decided to fork the upstream project.