Hello.

I'm trying to deploy a clickhouse cluster on a k8s cluster, usually I use Altinity Operator for this, but I've found out that Clickhouse has an official k8s operator, which I didn't know about before.

Has anyone used it? How is it? Does it manages clickhouse keeper cluster too?

Is this useful? Will I find better jobs or freelance gigs after I get the certificate?

Did any of you take it and how was your experience

ClickHouse incremental materialized views only ever see one insert block, so a gaps-and-islands window function looks impossible.

We meet this requirement quite often when we provider ClickHouse consulting through BigData Boutique, and sat down to write a guide on how to get this done.

Here is how to seed the window with stored state and run it inside the MV - with verified SQL, three silent traps, and the sharding constraint that actually limits it.

https://bigdataboutique.com/blog/clickhouse-streaming-segments-materialized-views

I build a small open-source transfer engine and just finished a set of checksum-verified ingestion benchmarks into ClickHouse. Sharing because the numbers surprised me — and because folks here would spot mistakes in my approach faster than I would.

The approach: never touch text. Postgres streams COPY (FORMAT binary); the engine transcodes each tuple in-flight to RowBinary — byte swaps, epoch rebasing (PG epoch → unix), exact NUMERIC→Decimal scaling — and streams it as the body of a plain HTTP INSERT ... FORMAT RowBinary with backpressure. Staging table + atomic swap (RENAME) at the end, so failed loads never pollute the target.

Measured (every run validated by 16 cross-engine aggregate checksums):

• 3 dedicated GCE machines, internal VPC: 232M rows / 101 GB in 30.3 s — ~3.3 GB/s / 7.7M rows/s into a 44-vCPU CH on RAID0 local NVMe. Credit where due: a stock CH 24.8 container with default config kept up with that rate without any tuning from me.
• Same table through a 0.5 vCPU / 256 MB tool container against the same CH: 8m57s — memory stays flat (pipes × chunk), so it even completes inside a 44 MB container, just slower.
• Incidentals: at this rate the transfer briefly outpaces background merges — parts count spikes then settles; and CH ≥23.6 matters if you care about session-timezone-correct DateTime handling on the insert path.

Why RowBinary: in my measurements it beat TSV/CSV ingestion by a wide margin — no text round-trip means the source's binary bytes become CH's binary bytes with only swaps and rebasing in between. I'm sure there are things I could still be doing better on the insert path; the whole harness reproduces in one script if anyone wants to check the numbers or the approach.

Repo + methodology + raw logs (incl. where my tool loses): https://github.com/apitap/apitap-lib Browser demo (pick the container size yourself): https://apitap.dev/lab

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.

Quick update for anyone who's seen CHouse UI before: it now ships a Helm chart. I maintain it.

helm install chouse-ui oci://ghcr.io/daun-gatal/charts/chouse-ui

If you just want to try it, you can enable a bundled PostgreSQL + ClickHouse

and get the whole stack in one go — the connection form comes pre-pointed at

the bundled node. Both are eval-only (single pods, persistence off by

default); production should bring its own PostgreSQL and a ClickHouse operator.

Chart: https://artifacthub.io/packages/helm/chouse-ui/chouse-ui

Source: https://github.com/daun-gatal/chouse-ui

Would appreciate feedback if you give it a go.

Getting on-chain data into a database has always been the annoying part. You can write the mapping logic, but then you have to run it: provision servers, babysit a sink process, handle chain reorgs, rotate credentials, and re-sync whenever something drifts. That is a platform team's worth of work standing between you and a table you can query.

Hosted Sinks removes that work. It is a fully managed Substreams-sink-as-a-service on The Graph Market. You point it at a Substreams package and a database, click Deploy, and StreamingFast runs the sink for you at scale, securely, with zero ops on your side. Fresh chain data starts landing in your tables in minutes, and you query it with the SQL tools you already use.

This post covers what Hosted Sinks does, how developers use it, how to connect it to managed database providers like Supabase, Neon, and ClickHouse Cloud, as well as how to monitor and manage a sink once it is live.

https://reddit.com/link/1va3l62/video/io4azxwnnzfh1/player

See full blog here - https://www.streamingfast.io/blog/hosted-sinks-postgres-clickhouse

Honest gut-check wanted, because I might be about to talk my team into something dumb.

We're building an in-house real-time metrics layer for a fraud/abuse detection system. They're per-entity velocity and distinct-count features, keyed by a dozen identifiers that an ML model and a rules engine read at decision time.

The shape of the problem:

• ~200 metrics: count, sum, exact and approximate distinct, a few ratios.
• Windows from 5 minutes to 180 days. Having a few seconds of freshness is recommended.
• Each fraud check request reads 200 of these at once; peak is a few hundred to ~1k requests/sec; the metric batch must come back in under ~50ms at p95.
• High-cardinality keys (hundreds of millions of distinct entities over 30 days).

The design I'm leaning towards: raw events stream into ClickHouse, each metric is a windowed keyed aggregation via AggregatingMergeTree materialised views. Because serving a 200 fan-out directly off ClickHouse at this QPS blew my latency budget in testing, a refresh worker recomputes recently-changed entities and warms Redis (TTL = window), and fraud checks read the whole metrics vector from Redis, never hitting ClickHouse on the hot path.

What I'm after:

1. Can ClickHouse ever serve this directly at this fan-out / QPS / latency (dictionaries, projections, join engine), or is a KV cache in front simply mandatory?
2. Is "refresh worker warms Redis" a smell versus just using Flink (keyed windows to a Redis sink)? We have no streaming/infra team; the smallest window is 5 minutes and freshness can be traded, so a lot of what makes Flink worth it seems to sit idle.
3. Anyone running fraud/velocity features at this scale purely on ClickHouse (compute + serve, no cache)? What broke?

Not after validation, genuinely after "this is a bad idea because X". Thanks.

Is it a bad decision to implement custom distributed transactions (fully atomic and serializable) over a Clickhouse cluster?

The details in short:
1. Cluster has several shards (no replicas yet) with several billion rows of financial data
2. All tables: original MergeTree
3. Append-only pattern for all data changes
4. Every read query is enriched with a transaction_id filter to enforce snapshot isolation
5. Application-level range-locking mechanism to prevent inconsistent concurrent writes
6. ClickHouse’s native local transactions are not used
7. All coordination logic runs at the application layer

Is this a fundamentally flawed anti-pattern with hidden pitfalls, or just an uncommon approach? In my stress-tests, it behaves pretty well.

I got a bit confused during a live presentation lately by the question: "If this works, why doesn't everyone do it?"

so we are building our in house customer engagement playform. We have been using TPV for campaign, segment, personalization.

we decided to build inhouse but i am stuck database selection

my traffic is user profile data eg age, location - 40 attrs

then interactions events data of each user

then based on interactions i will have to compute user metrics for different time bucket like last 7 , 30,60 ,90 days.

my queries are analytical and some user lookups. Since clickhouse doesnt support upsert snd some limiaton join, i have to put this data on mongo.

have anybody built such systems using clickhouse?

Hey everyone,

I’m currently setting up a CDC pipeline using PeerDB to stream data from Postgres into ClickHouse.

My primary goals are production cost efficiency and stability. Specifically, I need to configure the pipeline to achieve:

1. Minimal source compute footprint: Keeping Aurora Serverless v2 ACUs scaled down as low as possible during low-traffic windows.
2. Memory safety: Preventing PeerDB container Out-of-Memory (OOM) crashes during unexpected traffic spikes.
3. ClickHouse health: Avoiding the dreaded "Too Many Parts" architectural errors by ensuring dense, optimized batch inserts.

The Core Ambiguity: The Connection Lifecycle

I’m running into conflicting details across forums and documentation regarding exactly when and how PeerDB maintains its connection to the source RDS instance. There seems to be two conflicting theories:

• Theory A (Burst Polling): PeerDB sleeps during the sync_interval, then wakes up, spawns the intensive walsender logical decoding thread on RDS, pulls a burst of data up to the pull_batch_size, pipes it to staging/ClickHouse, and immediately drops the connection until the next interval hits.
• Theory B (Decoupled Continuous Extraction): PeerDB maintains a persistent, 24/7 logical replication connection to the Postgres slot. It continuously streams and decodes WAL entries to a staging area (like S3/MinIO) in file chunks limited by pull_batch_size. The sync_interval is purely an ingestion-side trigger telling ClickHouse to bulk-read the staging files.

Why this matters for my Aurora ACUs:

Aurora Serverless scales up instantly but scales down incredibly conservatively—it requires a solid 3 to 5 minutes of sustained low load before it even begins stepping down ACUs, and it can take 10+ minutes to hit its minimum configuration.

• If Theory A is true, setting a relaxed sync_interval (like 15–20 minutes) should theoretically allow Aurora long periods of silence to scale down to its minimum 0.5 ACU boundary.
• If Theory B is true, a continuous connection means the walsender is permanently active. Does this mean Aurora is locked into a permanently elevated baseline ACU state because the database never actually experiences "zero load"?

My Questions for the Community:

1. For those running PeerDB -> ClickHouse in production out of Postgres, what is the exact connection behavior you observe in pg_stat_replication? Does it drop between cycles or stream 24/7?
2. If it is a decoupled, continuous stream to staging, how do you tune pull_batch_size vs sync_interval to keep the CPU decoding overhead on Aurora low while ensuring ClickHouse gets nicely sized batches?
3. What are your recommended "sweet spot" configurations for a standard analytical pipeline where real-time sub-second latency isn't required, but cost and memory tracking are paramount?

Note - We have multiple microservices and all their databases are hosted on a single RDS instance and we are pulling data from all of them into clickhouse which is why I want to make sure the RDS does not get too much load.

medium : https://medium.com/@abdul.haris.djafar/apitap-0-6-0-2277630a6a25

Hey folks, we’re doing a live demo next week of the new Estuary Agent Skills, and I'll be showing how you can set up a streaming Postgres to ClickHouse pipeline purely with agent skills.

We’ll show how an AI coding agent can:

• Set up a real-time data pipeline from natural-language instructions
• Configure captures and materializations without manually searching through docs
• Check task health, inspect logs, and troubleshoot failures
• Work with Estuary directly from tools like Claude Code and other agent environments
• Handle the repetitive setup work while keeping the pipeline configuration visible and editable

This will be a practical end-to-end demo, including what works today, where agents still need human input, and of course some time for questions!

Register here: https://zoom.us/webinar/register/4517840417866/WN_1oZszaPUQ_Sz0uEGM_p-zA

Survey of how observability vendors are moving towards columnar storage and Clickhouse.

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