Loading…
Clickhouse
High-performance open-source columnar database management system designed for real-time analytics and data warehousing at scale
- 241 posts summarised
- latest
The Good Stuff
Paid picks we use ourselves. Links carry a referral code — a subscription through one earns us a commission at no extra cost to you.
From their blog
Latest articles
OLTP is expensive, you can ClickHouse it.
PostgreSQL’s ACID guarantees cause substantial write amplification: each write flushes WAL for durability, copies rows via MVCC for isolation, and updates every affected index, amplifying 100 bytes of row change into tens of kilobytes of I/O. Costs scale linearly with data size, and even sharded Postgres offers only linear scaling with added coordination overhead, so growing datasets drive rising bills. The post proposes offloading settled, rarely-changing data to ClickHouse, whose columnar compression shrinks datasets 10-30x and whose cloud offering costs about 5x less than comparable Postgres infrastructure while also reducing Postgres compute load through fewer indexes. ClickHouse builds indexes in background batches and runs aggregations at roughly 100x lower cost than Postgres. ClickHouse Cloud provides WalShadow for millisecond-latency CDC and pg_clickhouse FDW, enabling an application pattern that cuts the cost of serving settled data by an order of magnitude.
October 2026 newsletter
The October 2026 ClickHouse newsletter announces CostBench results where ClickHouse Cloud delivered 752× better performance per dollar than Databricks and 438× to 512× better than BigQuery on real-time analytics workloads. The 26.9 release extends LIMIT with AFTER and UNTIL boundary conditions, adds incremental refresh for materialized views, and introduces CREATE TOKEN for time-limited credentials. Executable user-defined functions are now generally available on ClickHouse Cloud, supporting precompiled Rust, Go, C++, and JavaScript binaries with outbound network access and a deterministic flag. Featured community member Hoang Minh V has had over 100 pull requests merged since May, including a fast path for PromQL aggregations and fixes to TimeSeries reads. The newsletter also covers the official Apache Airflow provider for ClickHouse, the chDB durable layer for agent memory, and a Postgres round-up on memory safety and local NVMe storage.
How Ridgeline unified Iceberg and ClickHouse into one analytics layer for investment management
Ridgeline, a cloud-native investment management platform, rebuilt its performance analytics layer after Postgres could not handle the scale of larger customers. The team evaluated Redshift, SingleStore, Trino over Iceberg, and a custom in-memory engine before choosing ClickHouse for its analytical performance and lower cost. They adopted ClickHouse Cloud on AWS, using separate organizations for staging and production and per-customer services, with assistance from ClickHouse engineers. Historical data lands in Apache Iceberg via a Spark pipeline and is queried through ClickHouse, while recent data is written directly into ClickHouse. The architecture enabled Ridgeline to serve customers 10x larger than before and more than double its live customer count.
Why Redshift Serverless can’t match ClickHouse Cloud for real-time analytics
CostBench streamed 113.2 billion stock-market quotes into both systems while four aggregate queries ran every ten minutes and two drill-down queries ran every hour. ClickHouse asynchronous inserts order raw data and build pre-aggregations during ingestion, while Redshift relies on separate background sorting and materialized view refreshes. ClickHouse Cloud led in preparation cost ($28.69 versus 522.03), normalized query cost ( 0.05 versus $24.97), and accumulated query runtime (58.12 seconds versus 1.69 hours). The combined score shows ClickHouse Cloud delivered 1,996× better end-to-end performance per dollar in this continuous-ingestion comparison. The difference originates in the preparation path: ClickHouse makes data query-ready during inserts, whereas Redshift’s streaming, sorting, and refresh steps advance independently.
Postgres at Open House NYC
At ClickHouse Open House NYC, the team announced WalShadow, an open-source engine that replicates Postgres into ClickHouse from the physical WAL, achieving sub-second latency and high throughput. They also launched ClickHouse Managed Postgres on Google Cloud in private preview, offering Postgres on local NVMe storage with native CDC into ClickHouse. The chdb Postgres extension was released, enabling fast imports from cloud storage using the embeddable ClickHouse engine. A live demo showed WalShadow keeping ClickHouse under 400 ms behind during a simulated trading platform with 50,000 trades per second, and detecting a fraud burst in under 500 ms compared to over 15 seconds with ClickPipes. The integrated stack aims to provide world-class Postgres for transactions and ClickHouse for analytics, with WalShadow as a key step toward unifying OLTP and OLAP.
Chasing that 32 ms tail latency in ClickHouse
A customer observed an extra ~32 ms tail latency on simple SELECT queries after idle periods in ClickHouse’s Native TCP protocol. The root cause was that after a TCP idle period (RTO), the congestion window resets to initcwnd (8 MSS), and with TCP_NODELAY enabled, each metadata packet (ProfileEvents, Logs, Progress) becomes a separate TCP segment, exceeding the small window and forcing an additional round trip. The ClickHouse Native protocol sends these metadata packets between Data and EndOfStream, and the unbatched writes caused the problem. The team reproduced the issue deterministically using Linux network namespaces and tc to simulate 32 ms RTT and initcwnd 8. They fixed it by batching those metadata packets into a single buffer write at the application layer, reducing tail latency from ~32 ms to ~1 ms, as confirmed by a Go client program that measured the time between the last Data packet and EndOfStream.
How iSAM Funds built an options research platform for a 10,000x data problem with ClickHouse Cloud
iSAM Funds, a UK-based systematic asset manager, built an options research platform on ClickHouse Cloud to handle the 10,000x data fan-out from underlying instruments to related options. Migrating from Postgres, the team achieved 35-50x compression and increased single-core ingestion from 8,000 to 800,000 rows per second, while ClickHouse’s native Apache Arrow support eliminated encoding reshuffling. ClickHouse’s columnar storage and vectorized execution made queries fast enough to run on demand, allowing the team to drop pre-computed rollups and materialized views and remove a layer of pipeline complexity. The managed service integrates with iSAM’s AWS footprint and enterprise authentication, pushes backups to iSAM’s own buckets for developer use, and lets them scale instances up for large backfills then down to control costs. The platform gives researchers fast, interactive access to full options history, shortening the time from hypothesis to tested strategy.
How we migrated Clera’s 500 GB production database to ClickHouse Managed Postgres overnight
Clera, an AI talent agent representing 200,000+ candidates, migrated its 500 GB production Postgres database to ClickHouse Managed Postgres after outgrowing its previous provider. The previous database suffered from point lookups taking multiple seconds, 100% CPU for six straight days, and timeouts. Clera evaluated Supabase, Neon, PlanetScale, and ClickHouse, choosing ClickHouse for its local NVMe storage that avoids network-attached disk latency. In sysbench TPC-C testing, ClickHouse showed 16–42% higher throughput than the runner-up. The team migrated 500+ tables from the EU to the US overnight using ClickPipes and manual replay, with production traffic still hitting the database. After migration, CPU usage dropped to 10–20%, and no settings have been changed since the cutover.
Introducing JWT authentication in ClickHouse Cloud
ClickHouse Cloud now supports JWT authentication for services running version 26.4 or later, allowing short-lived tokens from OpenID Connect providers such as Okta and Microsoft Entra to replace long-lived passwords. When a client presents a token, ClickHouse verifies its signature and claims, then creates an ephemeral in-memory user with the roles and grants specified in the token. The user disappears after the token’s expiration, removing the need for CREATE USER or DROP USER commands. Roles are managed once in ClickHouse and assigned via token claims; identity and group membership are governed by the provider. The feature also handles definer views by writing persistent shadow users, and it supports RS256 keys from version 26.4 and EC keys from version 26.8.
Why BigQuery can’t match ClickHouse Cloud for real-time analytics
ClickHouse Cloud delivered 438× better end-to-end performance per dollar than BigQuery under Capacity pricing, and 512× under On-demand pricing, while continuously ingesting 113.2 billion stock-market quotes. The CostBench methodology measures preparation cost, query cost, and query runtime as data arrives. ClickHouse Cloud led in all three components: preparation cost was 8.5× lower than BigQuery Capacity, normalized query cost was 94× lower, and accumulated query runtime was 50.8× lower. The difference originates in how each system handles incoming data: ClickHouse orders raw data and builds current summaries during inserts, while BigQuery makes new rows queryable before background clustering and materialized view refresh complete. This leaves BigQuery aggregate queries to include rows that refresh has not yet summarized, increasing cost and runtime.
🗽 Postgres Summit US 2026 retrospective
ClickHouse sponsored Postgres Summit US 2026 in New York City, where David Wheeler presented “Postgres in da ClickHouse” on integrating Postgres and ClickHouse for OLTP and OLAP workloads. He demoed four technologies: pg_stat_ch for query metrics, pg_clickhouse for querying ClickHouse from Postgres (pushing down 16 of 22 TPC-H queries and supporting nearly all Postgres data types), chdb_hook for efficient data lake file import/export via COPY (outperforming similar extensions), and WalShadow for sub-second physical replication. Other sessions covered pg_plan_advice in Postgres 19, index prefetching for Postgres 20, open source CDC plugins, and a graveyard of technologies that failed to “kill” SQL. Wheeler also attended talks on Postgres replication, African Postgres communities, and CloudNativePG extensions. The conference marked ClickHouse’s first Postgres conference sponsorship, with plans to sponsor PGConf.EU in Valencia.
Why Databricks can’t match ClickHouse Cloud for real-time analytics
The CostBench benchmark streamed 113.2 billion stock-market quotes at one million rows per second while running aggregate and drill-down queries to compare ClickHouse Cloud and Databricks for real-time analytics under continuous ingestion. ClickHouse Cloud delivered 752× better end-to-end performance per dollar than the Databricks stack using Serverless SQL, Zerobus Ingest, liquid clustering, and incremental materialized views. ClickHouse Cloud led in all three components: preparation cost ($28.69 versus 695.60), normalized query cost ( 0.05 versus $2.65), and accumulated query runtime (56.39 seconds versus 29.10 minutes). The gap stems from ClickHouse preparing ordered raw data and current summaries on the insert path, while Databricks requires separate clustering and refresh services that cost more and can leave queries with unfinished layout work. Databricks’ new Lakehouse//RT, still in beta, was not included in this comparison.
Executable UDFs are now generally available on ClickHouse Cloud
Executable user-defined functions (UDFs) have moved from public beta to general availability on ClickHouse Cloud across AWS, GCP, and Azure. The GA release adds a Native runtime for precompiled statically linked binaries in Rust, Go, C++, and JavaScript, requiring both amd64 and arm64 architectures, alongside the existing Python support. UDFs now have network access to public endpoints, a per-process memory limit, a deterministic flag that enables query caching, and are not billed separately, running inside service pods. ClickHouse 26.6+ provides eight ProfileEvents counters and two asynchronous metrics for per-query UDF cost monitoring, and the Cloud API and Terraform provider support UDF management with per-service version pinning. The post demonstrates counting LLM tokens and grading completions using these UDFs, showing how to put tokenization libraries next to the data in ClickHouse.
What is direct I/O, and why does ClickHouse Managed Postgres use it for backups?
A base backup reads every byte of a live database off the same NVMe drives that serve queries. Buffered reads go through the kernel page cache, evicting warm database pages and adding a kernel copy per byte. Direct I/O skips the cache, leaving Postgres’s warm data in place, but also disables kernel readahead. On a RAID0 array, small direct reads hit only one drive, so ClickHouse Managed Postgres sizes each direct read to span the whole stripe and scales the reader count to the hardware. On a 48 vCPU box with four NVMe drives and a 467 GB database, the direct I/O backup evicted nothing, cut query latency impact by about two thirds, used about 14% less CPU, and finished in 71 seconds.
How LinkedIn extended ClickHouse from distributed tracing to metric discovery and analytics
LinkedIn’s observability team first deployed ClickHouse for distributed tracing, where even at 1% head sampling the system generates 0.8 trillion spans and 200 TB of uncompressed data per day across three data centers. They then extended ClickHouse to consolidate a legacy metric metadata system built on RRDtool semantics, which had grown into three separate services that had reached their scaling limits. The new single-shard ClickHouse index serves over 150,000 queries per minute with an average latency of 68 milliseconds across 13 billion-plus metrics with 30% daily turnover. Migrating off the legacy stack reduced memory use to roughly one-fifth and compute to about two-thirds, with enough headroom to double the metric volume. By exposing individual dimensions as columns, the index enables engineers to replace regex-heavy queries with efficient native ones, supporting the larger migration away from RRD semantics.
Introducing SCIM Provisioning in ClickHouse Cloud
ClickHouse Cloud introduces SCIM 2.0 provisioning, making the identity provider the single source of truth for user membership and role assignments. Previously, SAML SSO handled authentication but left user invites and role updates as manual tasks, creating security risks. SCIM provisioning automatically syncs user create, update, and delete operations from the IdP using standard SCIM 2.0 endpoints, maps IdP groups to custom ClickHouse roles via stable external IDs, supports up to two API keys for rotation, and provides audit logging for all provisioning actions. The feature requires an active SAML connection, is limited to verified domain SSO users, excludes system roles like Admin, and revokes all keys if SAML is disabled. It is generally available for Enterprise and BYOC organizations, validated with Okta and Microsoft Entra ID, and eliminates manual invite and deprovisioning tasks.
pg_clickhouse & chdb updates: Encoding, nesting, and types
pg_clickhouse v0.11.0 and the chdb extension v0.1.2 continue their focus on cross-database compatibility between ClickHouse and PostgreSQL with several enhancements derived from header-only C libraries. A new check_encoding server option provides error handlers for invalid byte sequences in text columns: fail, remove, replace, and truncate, with replace displaying the Unicode replacement character for UTF-8 databases. ClickHouse Interval types such as IntervalMillisecond are now imported as PostgreSQL interval columns, and expressions like start_at + duration are pushed down to ClickHouse. Unflattened Nested columns are mapped to two-dimensional text arrays by default, or to arrays of composite types when a structure is specified; parameterized JSON columns are mapped to jsonb with proper ORDER BY pushdown. Additional type mappings include Int128 and UInt256 to numeric, BFloat16 to float4, and spatial types; the deprecated clickhouse_raw_query function was removed, and PostgreSQL 13 support was dropped.
Memory safety for Postgres extensions in C/C++
Postgres MemoryContext allocation combined with setjmp/longjmp error handling bypasses C++ destructors, leading to undefined behavior when C++ code runs inside a Postgres extension. C++ exceptions also bypass PG_CATCH, and an uncaught exception in a background worker aborts the process, forcing the postmaster to restart everything due to potential shared memory corruption. ClickHouse’s four Postgres extensions each used a different approach: pg_clickhouse replaced clickhouse-cpp with a new C library, pg_re2 scoped C++ to a single file, pg_chdb isolated libchdb in a helper executable, and pg_stat_ch uses a terminate handler. pg_chdb’s helper process runs outside the Postgres backend, so a libchdb crash fails only the calling operation without triggering cluster crash recovery. A long-term plan for pg_stat_ch moves its C++ dependencies into a helper executable, with PR #129 as the first step to isolate C++ from Postgres code.
ClickHouse expands collaboration with Microsoft, bringing Fabric integration, deeper OneLake interoperability, and enterprise deployment flexibility
ClickHouse announced a significant expansion of its strategic collaboration with Microsoft at The European Microsoft Fabric + SQL Community Conference, including the public preview of a native ClickHouse workload for Microsoft Fabric that brings sub-second analytical query latency to OneLake. The company also made generally available the ability to read Apache Iceberg tables from OneLake using OneLake Table APIs, and introduced a public preview of writing Iceberg tables back to OneLake. These capabilities eliminate data duplication and ingestion steps, allowing teams to keep OneLake as the governed single source of truth while leveraging ClickHouse’s speed. ClickHouse Bring Your Own Cloud (BYOC) is now available in the Microsoft Marketplace, enabling customers to run ClickHouse Cloud inside their own Azure tenant with full control over IAM, networking, and encryption. The integrations aim to serve real-time agentic workloads, interactive dashboards, and observability without separate cluster provisioning or ETL pipelines.
ClickHouse Workload for Microsoft Fabric: sub-second analytics on OneLake, now in public preview
The ClickHouse workload for Microsoft Fabric brings ClickHouse as a native acceleration layer for OneLake, now in public preview. It provisions dedicated ClickHouse Cloud services on Azure via the Fabric Extensibility Toolkit, using a ClickHouse item in the workspace. Users sync OneLake tables through ClickPipes as point-in-time snapshots with no ingestion charges. The embedded SQL console and external tools (Power BI, Copilot Studio, GitHub Copilot) can then query billions of rows with sub-second response times at high concurrency. Planned future features include write-back to OneLake, continuous sync, permission sync, and deeper Power BI integration.
Topics they write about
All topics →Weekly digest
The best engineering reads, once a week.