Loading…
Clickhouse
High-performance open-source columnar database management system designed for real-time analytics and data warehousing at scale
- 236 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
July 2026 newsletter
The July 2026 newsletter rounds up ClickHouse releases, community projects, benchmarks, engineering work, and upcoming events in real-time data warehousing. Version 26.6 adds hypothetical skip indexes, cascading refreshable materialized-view dependencies, experimental continuous queries, AI embedding functions, PNG output, CLI documentation, and geospatial improvements. A CostBench comparison running the full analytics path over 28 hours found better cost-performance for ClickHouse Cloud than Snowflake Interactive Tables, which required a continuously running refresh warehouse for pre-aggregated data. Other features cover conversational analytics with ClickHouse Cloud, LibreChat, and MCP; Postgres change-data capture through Debezium and Kafka; local agent queries with chDB; high-concurrency tuning; and Silk, whose fiber runtime targets lower I/O tail latency. Quick reads report ingestion, caching, security, and multi-tenant SQL developments.
Why Trainy migrated from Amazon RDS Postgres to ClickHouse Managed Postgres
Trainy’s Pluto is an open-source experiment tracker that separates analytics metrics in ClickHouse Cloud from relational run metadata in Postgres. Its original metadata store, Amazon RDS Postgres, was too expensive for the volume observed and contributed to slower initial page loads, so Trainy migrated roughly 40–50 GB to ClickHouse Managed Postgres, which uses local NVMe storage and keeps both databases in one account. After migration, initial page loads became 2–3 times faster, while a complex query reported as taking 10–15 minutes previously returned in five seconds; visualization queries over runs with hundreds of thousands of steps typically return in 50–100 milliseconds at the P50. Trainy reports 100% reliable ingestion with zero dropped data points and says the unified stack could support future joins between GPU metrics and run metadata, as well as governed agent access.
Replacing the HDB: ClickHouse for historical ticker data
Historical market-data systems separate a durable archive from the real-time trading path, but the archive still must support research, backtesting, transaction-cost analysis, and regulatory reconstruction. The worked example uses Binance’s public archive to load trades and quotes for five USD-M perpetual-futures symbols into ClickHouse, with a MergeTree table ordered by (symbol, ts) and codecs including DoubleDelta and ZSTD. SQL queries calculate VWAP, hourly OHLC candles, and as-of joins, while ClickHouse’s sparse primary index skips most granules for symbol-and-time filters. Across ten months, 16.2 billion rows compress to 76 GiB on disk; the post reports a ClickHouse Cloud cost of $1.88 per month and argues that typical symbol-and-time queries retain low latency at that scale, while broader scans read more data.
How Bullet uses ClickHouse Cloud to give DeFi's fastest exchange real-time analytics
Bullet operates a DeFi exchange for perpetuals, spot trading, and lending, with trades settled on a blockchain while users retain custody of their assets. Its sub-millisecond trading system produces more than 150 million event rows per hour, creating a need for historical indexing, real-time analytics, and operational monitoring beyond the blockchain’s state-machine model. ClickHouse Cloud consumes events from Kafka, stores them in dedicated tables, and supports user features, leaderboard and reward calculations, P&L charts, audit logs, and Grafana-backed alerts. Replacing Databricks reduced query latency from 10–15 seconds to milliseconds, improved data freshness from 1–2 hours to under 5 seconds, and removed a separate DynamoDB serving layer at comparable cost while handling 1,000 times more data across staging, testnet, and mainnet.
@clickhouse/rowbinary: when your library is also a parser compiler
ClickHouse's @clickhouse/rowbinary is a Node.js reader and writer for the RowBinary, RowBinaryWithNames, and RowBinaryWithNamesAndTypes formats, offering both a generic parser and an accompanying Agent Skill. RowBinary avoids per-row overhead but is difficult to consume in JavaScript because nested types, LEB128 varints, precision-sensitive DateTime64 values, and recursive self-describing types require extensive runtime dispatch; JSON alternatives can also round large UInt64 values. The package provides small, composable, monomorphizable reading primitives, while SKILL.md instructs a coding agent to assemble query-specific parsers with inlined operations and selected policies such as BigInt handling and array materialization. Generated parsers run 1.5–3.4x faster than composing library functions in loops, with a fixed-width benchmark parser reaching 3.41x faster, and produce reviewable source that can be tested and benchmarked like ordinary application code.
How we configure huge pages in ClickHouse Managed Postgres
ClickHouse Managed Postgres configures Linux huge pages to reduce the per-connection page-table cost and translation overhead of a large PostgreSQL shared_buffers cache. The configuration reserves a quarter of machine memory before PostgreSQL starts, verifies HugePages_Total, sets huge_pages = 'on' to prevent silent fallback, and sizes shared_buffers against the complete shared-memory segment, including buffer descriptors, WAL buffers, and lock tables. On an r7i.4xlarge with 32GB shared_buffers, 4KB pages added 31.1MB of page tables per connection and reached 6.12GB at 200 connections, while 2MB huge pages reached 111MB. A select-only pgbench test at 100 clients also rose from 373,083 TPS to 418,087 TPS, a 12% increase, while startup failure without a sufficient pool made misconfiguration explicit.
How Insider One uses ClickHouse Cloud to power real-time customer engagement at scale
Insider One’s Unified Customer Database (UCD) unifies event data, user attributes, identity resolution, and profiles across channels to support real-time segmentation, personalization, and analytics. The platform initially ran ClickHouse in a self-managed AWS deployment, but growth brought larger datasets, more concurrent queries, and increasing work tuning clusters and balancing performance against cost. In a proof of concept, the team migrated an anonymized dataset from one of its largest customers to ClickHouse Cloud and replayed UI segmentation, audience-count, and scheduled-campaign workloads. Average query latency fell 50–60% without significant changes, reaching 70–75% for the largest customers, while latency percentiles became more predictable; the managed service also reduced operational burden and gave the team more capacity for product development and its AI-oriented roadmap.
ClickHouse on Docker Hardened Images
ClickHouse is now available as Docker Hardened Images (DHI), stripped-down builds for the ClickHouse server, ClickHouse Keeper, Kubernetes Operator and Helm chart, and metrics exporter. The standard image’s Ubuntu 22.04 base includes unused packages such as Perl, wget, and apt, whose known CVEs can block enterprise deployments even though ClickHouse does not use them. DHI replaces that base with a minimal image that has no package manager or network tools, runs as non-root, provides SLSA Level 3 provenance, and is maintained for CVE patching; Docker Scout reported 0 medium and 14 low findings for DHI, versus 8 medium and 11 low across 111 packages in the standard image. Migration preserves volumes and configuration, while development tooling remains available through a dev variant or temporary docker debug attachment.
One Driver, One Format, Every Language: ADBC
ClickHouse announces an official ADBC driver, a modern Arrow-native alternative to ODBC and JDBC for analytics and AI applications. It provides end-to-end columnar movement: query results leave ClickHouse as Apache Arrow and arrive in applications without a row-oriented conversion or staging step. A single Rust-built implementation connects through ClickHouse's HTTP interface, supports TLS, and serves languages and tools with ADBC bindings, including Ruby, R, and C. Users can install precompiled, signed binaries through the ADBC Driver Foundry and connect with a clickhouse:// URI; dbt Fusion distributes the driver directly. The driver also supports streaming Arrow inserts, while schema discovery, improved bulk ingest, usage examples, and JSON support remain on the roadmap.
How Trigger.dev is using ClickHouse to scale observability for long-running AI workflows
Trigger.dev, an open-source platform for serverless asynchronous tasks, needed to scale observability for increasingly complex, long-running AI workflows spanning thousands of tenants. Its initial Postgres design stored logs, traces, and events in a growing table, making indexes, analytics, and operational reliability increasingly difficult to manage. The team moved observability workloads to ClickHouse after evaluating performance, scalability, open-source compatibility, deployment flexibility, and support for dynamic telemetry, including its native JSON data type. Migration occurred gradually at the task-execution level, routing each run’s logs to either database without downtime or customer intervention. ClickHouse now supports real-time telemetry analysis, approximately 200-millisecond P95 latency for queries returning tens of thousands of rows, and a dramatically smaller telemetry storage footprint.
Introducing pg_re2, fast, RE2-powered regular expressions in Postgres
pg_re2 is a Postgres extension that brings RE2-powered regular expression functions and operators to Postgres, designed to address both execution speed and syntax compatibility with ClickHouse. It uses finite-automata-based RE2 rather than Postgres's backtracking POSIX engine, and version 0.4.0 adds the @~ operator plus btree and GIN index support. Benchmarks on an AWS m6g.2xlarge with PostgreSQL 18 report RE2's functions running at 1.8x to 8.6x the speed of native equivalents, while indexed operations are 1.1x to 1.8x faster. With pg_clickhouse, using RE2-backed functions such as re2match() keeps regex behavior consistent between Postgres execution and ClickHouse pushdown, including patterns such as Quake\b. The extension duplicates ClickHouse's suite for string search, replacement, extraction, and splitting.
AI needs the best-of-breed data stack: Postgres and ClickHouse
AI-native applications generate large volumes of operational data, while agents and users increasingly require real-time answers from that data. This pressure raises both query concurrency and reliability requirements, with a cited sample of companies using Postgres and ClickHouse showing more than 1,000% data growth in six months and over 85 TB added. The proposed stack assigns Postgres low-latency transactional workloads and ClickHouse real-time analytics, connecting them through PeerDB and ClickPipes CDC with single-digit-second replication, pg_clickhouse query pushdown, and pg_stat_ch telemetry streaming. The conclusion is that AI makes the OLTP/OLAP distinction more important, favoring an open best-of-breed stack over a single platform while preserving each database's specialized role.
chDB as the Agent's Local Data Engine
Agents often make 5 to 20 tool calls per turn, and many of those calls retrieve data such as prior memory, recent events, or catalog records. When data is remote, round-trip latency compounds across sequential calls, while packet loss can trigger retries that increase both task time and token usage. The article presents chDB, which embeds a full ClickHouse query engine in the agent process, as a way to make data access local and CPU-bound rather than network-bound. For local memory, it combines structured, time-series, vector, and text-index capabilities with local storage and an embedding model, while append-only MergeTree rows preserve belief revisions, deletion markers, and point-in-time history. It also describes syncing trickle-written local memory to a warehouse on a slower cadence and using a materialized-view mirror for frequently accessed current state.
98% faster queries, 50% lower cloud costs: Verihubs’ journey from Postgres to ClickHouse
Verihubs rebuilt its data warehouse after a Postgres-based, daily-batch pipeline became slow, unreliable, and difficult to correct when late vendor callbacks required backfills. The new architecture uses Kafka, Kafka Connect, and Debezium for real-time ingestion into ClickHouse, with MergeTree for append-only logs and ReplacingMergeTree for datasets updated through change data capture. Materialized views create daily and monthly rollups, projections accelerate existing query patterns, and Dagster handles complex detection workflows that can notify Slack. The migration, completed in about six months, reduced query times from 20–30 minutes to two or three seconds on datasets up to 18 million rows, representing as much as 98% faster queries and 50% lower infrastructure costs. Verihubs says the resulting real-time OLAP system gives finance and operations fresher data for traffic analysis, reconciliation, performance reporting, and invoicing.
Introducing AI dashboard and workflow generation in ClickStack
ClickStack now supports AI-generated dashboards and linked investigative workflows from natural-language prompts, targeting the effort required to understand telemetry, write queries, choose visualizations, and iterate. Rather than asking an LLM to return a finished artifact, ClickStack uses an AI Notebook with the same MCP tools available to external agents, where queries, visualizations, findings, and reasoning are captured in a persistent, editable investigation. The system explores telemetry, validates assumptions, assembles dashboards, and can connect overview and service-level dashboards through dashboard actions; generated charts remain editable. The same MCP server exposes these capabilities to agents such as Claude, Cursor, and Codex, so users can create, refine, and extend dashboards through one toolchain.
ClickHouse Release 26.6
ClickHouse 26.6 is a release marking the 10-year anniversary of ClickHouse becoming open source, with 56 new features, 79 performance optimizations, and 366 bug fixes. Hypothetical skip indexes let users evaluate set indexes in a session without building them; on a 487.24-million-row UK property dataset, the example estimated skip ratios of 77.0% at granularity 128 and 92.2% at granularity 1. The release overhauls dependencies between refreshable materialized views, addressing schedule drift that could make dependent stages lag or miss a refresh cycle. It also introduces experimental continuous queries, the aiEmbed function, embedding quantization to 8 bits and below, and workload-level memory reservations.
How we made ClickStack 5x faster for ClickHouse observability
ClickStack’s inherited OpenTelemetry Collector schema was struggling with slow searches, Top N queries, and aggregations that could time out on large observability workloads. The team prioritized common log and trace access patterns, including exact-attribute searches, body-text queries, histograms, individual log lookups, and distinct-key or value introspection. They used fixed production-like workloads and iterative testing with ClickCannon, Kubernetes-managed environments, ephemeral ClickHouse Cloud instances, and controlled schema, query, and version comparisons. The resulting redesign combined primary-key changes, text indexes, query rewrites, alias columns, materialized views, and newer ClickHouse features, reducing representative query latency by more than 5× while balancing ingestion, storage, and operational efficiency.
ClickHouse Agents is now available for Managed Postgres
ClickHouse Agents is now available for all Managed Postgres services in ClickHouse Cloud, extending its plain-English agent interface to operational data. The beta service, powered by Claude and built on LibreChat, combines an agent builder and chat interface with a sandboxed code interpreter; it is read-only by default and supports MCP-compatible systems. Agents can inspect Postgres schemas, run read-only queries, return the SQL used, and reason across Postgres and ClickHouse by querying each engine independently before merging results. Additional skills guide migration from Postgres to ClickHouse through discovery, access-pattern analysis, schema design, type mapping, CDC ingestion, and validation, while other workflows use metrics, Query Insights, and EXPLAIN to rank performance fixes. Planned extensions include scheduled recommendations and safe automated remediation, but the current release focuses on exploration, analysis, monitoring, and guided database work.
How we scale PgBouncer in ClickHouse Managed Postgres
PgBouncer is single-threaded, so a single process can bottleneck connection pooling on multi-vCPU machines even when Postgres has capacity. ClickHouse Managed Postgres runs a fleet of PgBouncer processes sized to available cores, sharing one port through so_reuseport; peering forwards query-cancellation requests to the process that owns the session, while transaction pooling and per-process connection-budget splits limit Postgres connections. In tests on identical AWS EC2 c7i.4xlarge pooler instances, a 16-process fleet reached roughly 336,000 transactions per second at 256 clients, compared with 76,893 for one process, while using about 49% versus 8% in-guest CPU. The fleet's advantage emerged under concurrency, whereas the single process was slightly faster at eight clients, and the described setup ships by default with every ClickHouse Managed Postgres server.
How Artemis Security runs 69x faster detection queries with ClickHouse Cloud
Artemis Security uses ClickHouse Cloud on AWS for real-time threat detection across many terabytes of compressed logs, tens of billions of daily rows, and queries over trillions of rows. To reduce repeated scans, its engineers coalesce hundreds of detection-rule predicates into one query, tagging matching rows by rule ID; batches of more than 100 rules now run in under a second, with a measured 69x speedup. A Claude Code skill called /poke connects ClickHouse system tables, CloudWatch, Datadog, application data, and source code to investigate infrastructure issues through natural-language prompts. For investigative queries over semi-structured JSON, Artemis extracts frequently queried fields into a parallel materialized table and uses a hybrid two-step lookup, producing 30–60x faster queries and up to 400x lower CPU consumption.
Topics they write about
All topics →Weekly digest
The best engineering reads, once a week.