Loading…
Clickhouse
High-performance open-source columnar database management system designed for real-time analytics and data warehousing at scale
68 postsWebsite
Latest articles
Native random sampling in ClickHouse
ClickHouse native random sampling enables aggregate queries to execute against a representative fraction of data when querying full datasets is too slow. To maintain sampling accuracy, tables require a sample key derived from high-cardinality columns using hash functions like sipHash64 to ensure an even distribution. The sample key must be included in the primary key ORDER BY expression, preferably at the front to let ClickHouse quickly skip index granules. Queries can sample data deterministically by specifying a fractional ratio or a minimum row threshold, scaling aggregate counts using the virtual column _sample_factor. In benchmark tests on a 30-million-row dataset, sampling 10% of records reduced query latency by approximately 80% while retaining approximate metrics suitable for exploratory analysis.
Mark NeedhamMay 2026 newsletter
The May 2026 ClickHouse newsletter details enterprise observability migrations, ecosystem integrations, and core database enhancements. Digital banking platform Qonto migrated from Grafana Tempo to ClickHouse Cloud, compressing 231 TB of raw trace metadata to 376 GB while expanding query windows to two weeks and introducing an AI incident companion. Similarly, LINE MAN Wongnai consolidated multiple business clusters onto self-hosted ClickHouse, achieving 1.5 million rows per second peak ingest, 100% trace retention, and a 53% reduction in observability costs. Comparative log analytics benchmarks demonstrated that ClickHouse utilized 5x less disk space and achieved 4 to 6x faster cold query execution than Elasticsearch across 50 billion rows. Additional updates include the ClickHouse 26.4 release featuring improved SQL compatibility, new agent telemetry adapters in Mastra, and SQL-based alerting capabilities in ClickStack.
Mark NeedhamClickHouse vs Prometheus for High Cardinality, Part 2: Cardinality in ClickHouse
Traditional time-series databases like Prometheus experience write amplification, memory overhead, and operational challenges when high-cardinality label combinations churn rapidly. ClickHouse alters these economics by abandoning dedicated series objects in favor of a wide events model, storing telemetry as rows in columnar tables with dynamic Map attributes and metric measurements. Dynamic attributes can be handled using sharded Map types alongside materialized columns and text indexes for frequently queried labels. This design trades ingestion overhead for query-time processing, relying on columnar compression, partition pruning, and parallel scans to compute aggregations on demand. ClickHouse avoids the ingest-side bottlenecks of high cardinality, though Prometheus remains preferable for moderate cardinality, native PromQL semantics, and single-series lookups.
Rory Crispin, Dale McDiarmidClickHouse vs Prometheus for High Cardinality, Part 1: Understanding the Problem
Observability systems frequently encounter high cardinality when metrics contain numerous unique label combinations across multidimensional cloud infrastructure. In Prometheus, every unique label combination instantiates an independent time series that incurs metadata overhead, requires in-memory memSeries registration, and continually expands inverted index posting lists. The ingestion engine stores samples in two-hour Head blocks using compressed 120-sample chunks with XOR-based encodings, but rapid series creation and elevated scrape frequencies multiply chunk allocations and memory consumption. When executing read operations, broad aggregations and regular expressions require intersecting expansive posting lists and decoding entire chunks sequentially because the engine cannot push value predicates directly into compressed storage blocks. Consequently, managing high-cardinality workloads in series-oriented architectures forces operational trade-offs between system stability and granular visibility into ephemeral components.
Rory Crispin, Dale McDiarmidPostgres FDW: Pushdown is a negotiation
Postgres Foreign Data Wrapper extensions allow PostgreSQL to query external datastores like ClickHouse by delegating execution to remote engines. Engineering pg_clickhouse centers on pushdown decisions that determine whether SQL expressions run remotely or stream raw data back across the wire. Because pushdown depends on planner callbacks, deparser translations, and ClickHouse semantic compatibility, a single untranslated clause can block upper-level query pushdown entirely. Resolving pushdown barriers enables complex analytic queries to return hundreds of rows in milliseconds rather than pulling tens of millions of rows for local processing. Ultimately, pushdown development requires an iterative negotiation across differing SQL grammars, occasionally requiring the revocation of translations that fail to maintain strict equivalence.
Kaushik Iska, David Wheeler, Philip DubéClickStack SQL Charting and Alerting
ClickStack introduced SQL-based visualizations and SQL-based alerting to allow users to build charts and alerts using arbitrary ClickHouse SQL queries. Traditional query builders trade analytical flexibility for simplicity, preventing teams from easily expressing multi-stage computations like rolling baselines, anomaly detection, or dynamic SLO calculations. To maintain interactivity within dashboards, the system supports ClickHouse query parameters and macros that inject active time ranges, interval sizes, dashboard filters, and source tables. Furthermore, SQL-based alerting evaluates the numeric columns output by these queries, such as returning binary flags when values exceed statistical boundaries. By shifting computational logic directly into SQL, teams can implement complex operational checks without relying on static external thresholds.
Drew Davis, Dale McDiarmidPowering self-driving vehicle analytics at Avride with ClickHouse Cloud
Avride’s expanding autonomous passenger vehicle and delivery robot fleet generated thousands of data points per minute, overwhelming its legacy Apache Iceberg and Parquet metadata indexing architecture. Iceberg’s optimistic concurrency model struggled with parallel writes, pushing ingestion latencies to hours or days, inflating query times to 10–20 seconds, and requiring raw data duplication across storage locations. To resolve these bottlenecks, Avride built a custom indexing and metrics warehouse powered by ClickHouse Cloud on AWS backed by Amazon S3 object storage. The new architecture dropped ingestion delays to seconds, reduced worst-case lookup latencies to under one second, eliminated the need for bespoke caching layers, and removed payload duplication. Beyond operational analytics, the unified ClickHouse platform now supports internal ride-replay debugging tools, simulation workflows, and high-cardinality vehicle C++ performance trace profiling.
ClickHouseClickHouse Release 26.4
ClickHouse Release 26.4 introduces 39 new features, 45 performance optimizations, and 238 bug fixes, focusing on broader SQL compatibility and indexing improvements. The update expands standard SQL support by allowing VALUES clauses as table expressions with column aliases, PostgreSQL-style EXTRACT units, NATURAL JOIN, and SET TIME ZONE commands. For text search, LIKE and ILIKE pattern queries using splitByNonAlpha tokenizers scan inverted index dictionaries instead of executing full-table scans when the dedicated evaluation setting is active. The release also provides the JSONAllValues function to extract all leaf values from a JSON column into a string array, enabling text skip indexes that significantly reduce scanned data and improve query latency.
ClickHouseIntroducing Postgres Query Insights in ClickHouse Cloud
ClickHouse Cloud introduced Query Insights in preview for Managed Postgres to help teams diagnose slow database queries. Powered by the open-source extension pg_stat_ch, the tool streams per-statement telemetry directly into ClickHouse to enable fast aggregations across billions of execution rows. Postgres normalizes query literals into placeholders during the parse-analyze phase before telemetry leaves the database, preventing sensitive data exposure. Telemetry emission maintains around three percent producer overhead by utilizing a non-blocking try-lock on a shared-memory ring buffer. Users can examine high-level database health metrics, rank query patterns by runtime or latency percentiles, and inspect execution flyouts detailing disk spills, cache hits, and parallel worker behavior.
Amog IskaAgentic analytics starts with query-ready data: the write-side cost of Snowflake vs. ClickHouse
Agentic analytics workflows introduce bursts of concurrent, low-latency analytical queries over continuously ingested data. Analytical query efficiency relies on columnar storage and chunk-level pruning, which require contiguous, sorted data to avoid full-table scans. Rather than evaluating warehouses solely at query runtime, this benchmark compares the write-side cost of maintaining query-ready data in ClickHouse and Snowflake during continuous ingestion at approximately one million rows per second. Snowflake relies on post-write clustering to organize data, whereas ClickHouse constructs sorted layouts on the write path and refines them via background merges. Across 100B, 200B, and 300B row checkpoints on the ClickBench dataset, ClickHouse achieved a query-ready layout at 22 times lower cost and delivered 28 times better write-side cost-performance.
Tom Schreiber, Lionel PalacinStop guessing in production: Full fidelity tracing at scale with ClickHouse and Odigos
Distributed tracing frequently suffers from visibility gaps, broken context across asynchronous boundaries like Kafka, and high operational overhead from manually configuring OpenTelemetry SDKs and collectors. To resolve these issues, Odigos provides zero-code instrumentation via eBPF probes that dynamically attach to running workloads without modifying application code. An intelligent control plane within Odigos dynamically adjusts trace sampling and attribute enrichment based on real-time latency and error rates. The resulting telemetry exports over OTLP into ClickStack, an all-in-one backend featuring an OpenTelemetry Collector, ClickHouse storage, and the HyperDX query interface. This unified pipeline preserves end-to-end trace continuity, captures deep application and database context, and enables fast SQL-based trace analysis.
Will SearleGala supercharges analytics performance with ClickHouse on AWS
Blockchain gaming platform Gala struggled to ingest and query expanding volumes of telemetry and blockchain data on its Databricks-based data infrastructure, resulting in multi-minute query times and heavy engineering maintenance overhead. To solve these bottlenecks, the company migrated its data platform to ClickHouse on AWS, ingesting data through Kafka, Airbyte, Amazon S3, and Fivetran. Gala leveraged ClickHouse's indexing capabilities alongside saved queries and API endpoints to power company-wide Metabase business intelligence dashboards for non-technical teams. Following the migration completed in December 2024, data capacity expanded from 3 TB to 9 TB while query times dropped from minutes to sub-second on previously unoptimized tables. The switch also lowered initial infrastructure costs by 30 percent, with plans underway to implement ClickHouse ClickPipes for improved data processing pipelines.
ClickHouseGoodbye limitations, hello data: How Qonto is rethinking observability with ClickHouse Cloud
Qonto struggled with slow queries, system instability, and aggressive data sampling when handling distributed traces in Grafana Tempo. To enable wide-event querying without strict cardinality limits, the team migrated their telemetry storage to ClickHouse Cloud. Telemetry from applications, Kubernetes, and GitHub routes through OpenTelemetry collectors over AWS PrivateLink into ClickHouse, while Grafana remains the primary dashboard interface. By compressing 231 TB of uncompressed trace attributes down to 376 GB, Qonto achieved substantial storage savings and integrated an AI incident companion using the ClickHouse Model Context Protocol. The new architecture eliminated cardinality policing, allowed product and support teams to self-serve incident investigations, and enabled real-time streaming pre-aggregations using Apache Flink.
ClickHouse TeamComparing ClickHouse versions with clickhousectl
Evaluating query performance differences across ClickHouse versions previously required managing Docker containers or manually downloading historical binaries from GitHub releases. The clickhousectl command-line interface simplifies this process by downloading specific ClickHouse binaries, running multiple background server instances concurrently with automatic port assignment, and executing queries across local environments. Users can copy table schemas between local instances by piping raw output from SHOW CREATE TABLE queries and replicate datasets using ClickHouse's remote table function. In a benchmark comparing ClickHouse 25.12 and 26.3 on an S3-backed Parquet dataset, version 26.3 introduced a Parquet metadata cache that reduced repeat query times from roughly 9 seconds to 1–2 seconds. System tables like system.server_settings and system.metrics enable operators to inspect cache configurations, eviction policies, and memory consumption directly.
Mark NeedhamClickHouse at Google Cloud Next '26
ClickHouse announced several product updates and integrations designed to deepen native compatibility with Google Cloud. Migration of ClickHouse Cloud to Google Axion ARM-based processors demonstrated a 30 to 55 percent query speed increase and reduced data load times from 64 to 26 seconds on ClickBench benchmarks. An integration with Google Lakehouse Runtime Catalog enables querying Iceberg tables on Google Cloud Storage via the DataLakeCatalog engine without data movement. Furthermore, Bring Your Own Cloud reached general availability on Google Cloud Marketplace, and ClickPipes added an unordered mode using Pub/Sub notifications for GCS ingestion. Google Antigravity integrated ClickHouse through an MCP server to translate natural language into SQL and generate interactive React charts.
ClickHouseWhat's New in pg_clickhouse - JSONB Support, SQL value functions, Streaming, and more
Recent releases of pg_clickhouse, a Postgres foreign data wrapper for ClickHouse, introduce query pushdown enhancements and streaming result handling. Version 0.1.10 adds pushdown for JSONB accessor operators and extraction functions by mapping them to ClickHouse sub-column syntax and toJSONString calls outside SELECT clauses. In version 0.2.0, date and time functions such as CURRENT_DATE, CURRENT_TIMESTAMP, and clock_timestamp() push down to ClickHouse while honoring the active Postgres session time zone and precision settings. To address memory pressure and out-of-memory risks from large foreign queries, the extension adds query result streaming to the HTTP driver with a default batch buffer of approximately 50MB. Container benchmarking against the NYC taxi dataset showed streaming reduced peak memory consumption from over 600 MiB to under 86 MiB.
David WheelerDo you still need Elasticsearch for log analytics? ClickHouse says no.
ClickHouse introduced a redesigned full-text search powered by inverted indexes that feeds matching documents directly into its vectorized analytical engine for observability and log analytics workloads. To evaluate this capability, the open-source versions of ClickHouse and Elasticsearch were benchmarked on single-node AWS EC2 instances across synthetic OpenTelemetry log datasets scaling up to 50 billion rows. The benchmark evaluated nine representative query patterns combining text search with filtering, grouping, counting, and time-series aggregations under both cold and hot cache conditions. ClickHouse achieved a roughly fivefold smaller storage footprint on disk while running cold queries four to six times faster and hot queries approximately 1.7 to 2.6 times faster than Elasticsearch. Furthermore, ClickHouse ingested the 50-billion-row dataset in under four hours out of the box, whereas the tuned Elasticsearch setup required approximately five days.
Tom Schreiber, Lionel PalacinClickHouse integrates with Lakehouse Runtime Catalog
ClickHouse 26.2 introduced a beta integration with Google's Lakehouse Runtime Catalog, enabling direct queries on Google Cloud Lakehouse Apache Iceberg tables without data movement or metadata syncing. The system communicates with Google Cloud Storage through the Iceberg REST Catalog endpoint at BigLake using Google Application Default Credentials for OAuth authentication. Users create a database using the DataLakeCatalog engine configured either with a local credentials JSON file or with explicit inline OAuth parameters. Once connected, analysts can query external Iceberg tables directly or ingest datasets into native MergeTree tables for repeated, low-latency workloads. Upcoming enhancements will add write capabilities for Iceberg tables and dedicated connection management tools inside the ClickHouse Cloud interface.
Melvyn PeignonClickHouse Cloud on Google Cloud Now Powered by Google Axion Processors: 30–55% Faster Queries, ~15% Fewer Compute Credits
ClickHouse Cloud transitioned its Google Cloud infrastructure from x86 N2D instances to Arm-based Google Axion C4A instances to resolve CPU throttling and performance ceilings under peak query loads. The migration leverages Axion's dedicated physical cores per vCPU, higher memory bandwidth, local SSDs, and improved thermal and clock frequency stability. In automated ClickBench testing across 100 million rows, every query in the 43-query benchmark improved by 30% to 55%, while data load times decreased by approximately half. A production analysis of 38 migrated customer services demonstrated an average 15% reduction in compute credits consumed per query across diverse production workloads. ClickHouse Cloud executed this rollout using automated validation checks, scheduled maintenance windows, and capacity coordination with Google Cloud.
ClickHouseUsing the ClickHouse MCP server with Google Antigravity
Google Antigravity integrates with the ClickHouse Model Context Protocol server to provide agent-driven analytics within the developer environment. Users install the server from Antigravity's MCP Store panel and authenticate via OAuth, granting the agent read-only access to organizations, services, databases, and tables according to account permissions. When prompted with natural language, the agent uses tools like get_services_list and list_tables to inspect schemas, translating user requests into functional ClickHouse SQL executed through the run_select_query tool. Antigravity can also generate interactive React charts as Artifacts, test UI interactions with screen recordings and screenshots in an integrated browser, and refine queries based on visual area comments. This setup allows engineers and analysts to explore datasets interactively without switching between external database clients and ticketing workflows.
Dustin Healy