---
title: "Clickhouse"
description: "High-performance open-source columnar database management system designed for real-time analytics and data warehousing at scale"
---

# Clickhouse
> High-performance open-source columnar database management system designed for real-time analytics and data warehousing at scale

## Articles

### [Smarter Auto-Scaling for ClickHouse: The Two-Window Approach](https://yomu.fyi/post/smarter-auto-scaling-for-clickhouse-the-two-window-approach.md)
- Company: [Clickhouse](https://yomu.fyi/company/clickhouse.md)
- Author: Ashwath Singh, Manas Alekar
- Published: Mar 24, 2026
- Type: Problem & solution

ClickHouse Cloud required a vertical auto-scaling mechanism that preserves rapid scale-ups while avoiding prolonged over-provisioning after traffic drops. Operating on a single 30-hour lookback window forced scale-downs to take up to 30 hours, whereas an earlier fixed-factor CPU algorithm caused severe oscillations. The engineering team deployed a dual-window recommender pairing a 3-hour window for responsive scale-downs with a 30-hour window for single-step scale-ups. Alongside a trend-based hunting check to resolve conflicting window recommendations, the team introduced target-tracking CPU scaling using geometric mean watermarks and 10-minute median smoothing. This dual-window architecture reduced scale-down latency to 3 hours, prevented scaling oscillations, and significantly reduced infrastructure costs for variable database workloads.


### [Building high-performance full-text search for object storage](https://yomu.fyi/post/building-high-performance-full-text-search-for-object-storage.md)
- Company: [Clickhouse](https://yomu.fyi/company/clickhouse.md)
- Author: Elmi Ahmadov, Jimmy Aguilar, and George Larionov
- Published: Mar 24, 2026
- Type: Problem & solution

ClickHouse redesigned its native full-text index to address latency bottlenecks caused by random read patterns on remote object storage. Instead of using a Finite State Transducer dictionary, the new architecture organizes indexed tokens into fixed-size sorted blocks compressed with front-coding. Each data part separates index data across a small in-memory sparse index file, a sequential dictionary file, and a posting list file. This layout allows the query engine to pinpoint token locations with minimal random I/O and resolve multiple full-text functions directly from index files. Consequently, ClickHouse Cloud maintains predictable search latency across shared object storage while matching local disk performance and scaling through parallel query execution.


### [Structured Logging in .NET with Serilog and ClickHouse](https://yomu.fyi/post/structured-logging-in-net-with-serilog-and-clickhouse.md)
- Company: [Clickhouse](https://yomu.fyi/company/clickhouse.md)
- Author: Alex Soffronow Pagonidis
- Published: Mar 23, 2026
- Type: Tutorial

Traditional logging solutions like Seq, ELK, and cloud platforms often face scaling bottlenecks, including slower queries, higher operational costs, and complex pipeline management. To address this, an ASP.NET service can write structured logs directly to ClickHouse using Serilog and the Serilog.Sinks.ClickHouse library. The sink transmits data over HTTP and provides a fluent C# builder to configure table schemas, typed JSON columns, full-text indexes, and MergeTree engine parameters. ClickHouse stores log data in a columnar format that delivers 10 to 20 times compression while enabling direct SQL queries, JSON property filtering, and real-time aggregations through materialized views. Furthermore, the sink includes an in-memory buffer to handle transient network drops with configurable batching, retry mechanisms, and callback hooks for failure alerting.


### [Querying DateTimes in ClickHouse](https://yomu.fyi/post/querying-datetimes-in-clickhouse.md)
- Company: [Clickhouse](https://yomu.fyi/company/clickhouse.md)
- Author: Mark Needham
- Published: Mar 20, 2026
- Type: Tutorial

ClickHouse provides specialized functions to filter, bucket, and compute durations on native DateTime columns without requiring external processing tools. Using the New York City taxi dataset imported into a MergeTree table, queries bucket pickup timestamps into hour and 15-minute intervals using toStartOfHour and toStartOfFifteenMinutes while casting values to the Time type to group across dates. The dateDiff function calculates journey durations in minutes, which enables trip speed calculations across time windows and powers inline ASCII visualizations generated by the bar function. By combining toDayOfWeek with conditional aggregations like countIf and the lag window function, queries uncover distinct morning surge trajectories between weekdays and weekends. The analysis demonstrates that weekday trip volumes peak around 8:45 AM alongside dropping speeds, whereas weekend volumes increase steadily across the morning.


### [March 2026 newsletter](https://yomu.fyi/post/march-2026-newsletter.md)
- Company: [Clickhouse](https://yomu.fyi/company/clickhouse.md)
- Author: Mark Needham
- Published: Mar 19, 2026
- Type: Announcement

The March 2026 ClickHouse newsletter details major platform updates, community deployments, and architectural practices for high-throughput analytics. Recent product announcements include ClickHouse 26.2 featuring time-based block flushing for streaming feeds, general availability of native inverted indexes for full-text search, and the release of chDB 4 with a Pandas-compatible DataStore API. Community implementations show Cloudflare managing 1.61 quadrillion daily events, while Trigger.dev isolated multi-tenant SQL execution using a domain-specific query language that compiles safe ClickHouse queries. In data optimization techniques, Hookdeck accelerated webhook payload search from over 30 seconds to under 400 milliseconds by partitioning payload hashes into typed bucket columns and scanning iterative time windows. Additional operational guidance emphasizes setting the ttl\_only\_drop\_parts parameter and aligning table partitioning with time units so background merges drop entire data parts cleanly without triggering mutation rewrites.


### [How ClickStack makes ClickHouse faster for observability](https://yomu.fyi/post/how-clickstack-makes-clickhouse-faster-for-observability.md)
- Company: [Clickhouse](https://yomu.fyi/company/clickhouse.md)
- Author: Mike Shi
- Published: Mar 18, 2026
- Type: Problem & solution

ClickHouse delivers high performance for telemetry data, but unoptimized queries on high-cardinality observability workloads can bypass pruning, inflate intermediate state, and exhaust cluster resources. ClickStack addresses this challenge by embedding engine-specific optimization best practices directly into how queries are generated, structured, and executed. For search workflows, the platform executes progressive queries across bounded time windows using optimize\_read\_in\_order to return initial rows rapidly without scanning entire historical ranges. ClickStack also segments large chart aggregations into parallel bucket-aligned queries, automatically redirects map attribute filters to pre-materialized columns, and extends lazy materialization thresholds to minimize runtime input-output overhead. These query formulation strategies are being packaged into specialized analytical endpoints to power notebooks and external tools without requiring manual SQL tuning or exposing raw database interfaces.


### [Introducing AI observability notebooks for Managed ClickStack in Private Preview](https://yomu.fyi/post/introducing-ai-observability-notebooks-for-managed-clickstack-in-priva.md)
- Company: [Clickhouse](https://yomu.fyi/company/clickhouse.md)
- Author: Mike Shi
- Published: Mar 18, 2026
- Type: Announcement

ClickHouse has launched AI Notebooks for Managed ClickStack in private preview to assist site reliability engineers during system investigations. Instead of isolating interactions in a standard chat window, the platform embeds Anthropic's Claude models directly into a cell-based notebook canvas. The system connects the model to internal ClickStack APIs, translating natural language questions into structured queries that leverage ClickHouse optimizations like materialized views and query chunking. Engineers can inspect reasoning, edit generated SQL queries, add manual visualizations, and branch investigations to evaluate multiple hypotheses independently. While currently utilizing a last-write-wins model for shared canvases, the roadmap includes true concurrent editing, customizable context, and external tool integration via an MCP server.


### [How Socialpruf built a faster, more reliable data stack by replacing Neon with Postgres managed by ClickHouse](https://yomu.fyi/post/how-socialpruf-built-a-faster-more-reliable-data-stack-by-replacing-ne.md)
- Company: [Clickhouse](https://yomu.fyi/company/clickhouse.md)
- Author: ClickHouse
- Published: Mar 17, 2026
- Type: Problem & solution

Socialpruf, a social media performance tracking platform ingesting hundreds of posts per second, experienced analytics latency, connection dropouts, and high network transfer costs while running Postgres on Neon alongside ClickHouse Cloud. To resolve these issues, the team migrated their transactional system of record to NVMe-backed Postgres managed by ClickHouse, colocating transactional and analytical workloads. Although standard Postgres logical replication failed on their 0.5 TB dataset, they achieved continuous synchronization using PeerDB before conducting a production cutover. The migration eliminated connection dropouts, reduced network transfer fees, and improved overall Postgres query performance by approximately 30 percent. To manage thousands of concurrent connections generated by ingestion workers, Socialpruf implemented ClickHouse's bundled, horizontally scalable PgBouncer instances.


### [AI is redrawing the database market](https://yomu.fyi/post/ai-is-redrawing-the-database-market.md)
- Company: [Clickhouse](https://yomu.fyi/company/clickhouse.md)
- Author: Tanya Bragin
- Published: Mar 17, 2026
- Type: Explainer

Emerging artificial intelligence workloads are fundamentally transforming data platform requirements across real-time analytics, data warehousing, and observability. Conversational analytics and autonomous agents replace periodic batch queries with rapid bursts of concurrent, low-latency requests, exposing the architectural limits of traditional cloud data warehouses and standalone transactional databases. To resolve these constraints, architectures are converging on engine-level integrations between Postgres for transactions and ClickHouse for analytical scale, while unifying operational observability and business event storage. ClickHouse also incorporates turnkey agent interfaces through its acquisition of LibreChat alongside dedicated execution tracing from tools like Langfuse. These combined capabilities ensure organizations can reliably support interactive agentic workflows without incurring severe latency penalties or compounding infrastructure costs.


### [Designing the new async-native ClickHouse Python client](https://yomu.fyi/post/designing-the-new-async-native-clickhouse-python-client.md)
- Company: [Clickhouse](https://yomu.fyi/company/clickhouse.md)
- Author: Joe Spadola
- Published: Mar 16, 2026
- Type: Problem & solution

The official ClickHouse Python client, clickhouse-connect, originally addressed asynchronous execution by wrapping its synchronous HTTP client in a thread pool executor. However, this executor model experienced thread exhaustion, GIL contention, and severe tail latency variance under heavy concurrent workloads. To resolve these scalability bottlenecks, the developers redesigned the client around a half-sync/half-async pattern utilizing aiohttp for async network I/O. A custom bounded queue bridges streaming socket reads on the event loop with synchronous, CPU-intensive binary parsing running inside background threads. Benchmarks demonstrate that this architecture stabilizes tail latencies, delivering an average P95 latency of 556 milliseconds compared to 869 milliseconds for the legacy executor client.


### [ClickHouse Release 26.2](https://yomu.fyi/post/clickhouse-release-26-2.md)
- Company: [Clickhouse](https://yomu.fyi/company/clickhouse.md)
- Author: ClickHouse
- Published: Mar 16, 2026
- Type: Announcement

ClickHouse version 26.2 introduces twenty-five new features, forty-three performance optimizations, and one hundred eighty-three bug fixes across the database engine. This release graduates both the text-index and the QBit vector embedding data type to production-ready status for search and retrieval workloads. For streaming ingestion pipelines where row arrival rates are low, the engine adds time-based block flushing configurations to write data parts before hitting default row or byte thresholds. The update also embeds the ClickStack observability user interface directly into the standard binary distribution, accessible through the local HTTP server port. Further enhancements include automatic min-max indexing for temporal columns in MergeTree tables, interactive time-based one-time password authentication in the client, and a high-performance prime number generator function.


### [Unordered mode for GCS ClickPipes is now available](https://yomu.fyi/post/unordered-mode-for-gcs-clickpipes-is-now-available.md)
- Company: [Clickhouse](https://yomu.fyi/company/clickhouse.md)
- Author: Marta Paes
- Published: Mar 16, 2026
- Type: Announcement

ClickHouse Cloud has added unordered mode to its Google Cloud Storage (GCS) ClickPipes connector, enabling continuous data ingestion regardless of file naming or arrival sequence. Standard GCS ClickPipes rely on polling every 30 seconds and require files to arrive in strict lexicographical order, which causes out-of-order data and backfills to be ignored. Unordered mode replaces polling by consuming OBJECT\_FINALIZE notifications from Google Cloud Pub/Sub whenever objects land in a bucket. State tracking is managed through a metadata store, and the connector automatically reprocesses batches upon failure while maintaining exactly-once insertion guarantees. Setup requires configuring a Pub/Sub topic and bucket notifications via tools like gcloud, followed by configuring the ClickPipe via the console or Terraform provider.


### [AI doesn’t always generate perfect ClickHouse schemas (yet)](https://yomu.fyi/post/ai-doesn-t-always-generate-perfect-clickhouse-schemas-yet.md)
- Company: [Clickhouse](https://yomu.fyi/company/clickhouse.md)
- Author: Al Brown
- Published: Mar 13, 2026
- Type: Explainer

LLMs can generate syntactically valid ClickHouse schemas whose advanced choices are poorly matched to a workload. The post examines recurring pitfalls including query-driven partitioning, per-column codecs, premature projections, materialized-view sprawl, JSON configurations, and forced deduplication with OPTIMIZE TABLE ... FINAL. It explains that partitions primarily support data management, incremental materialized views add work to every insert, and ReplacingMergeTree deduplicates during background merges rather than at insertion. The recommended method is to begin with a simple MergeTree or ReplacingMergeTree, a well-chosen ORDER BY, default compression, and no optional structures, then measure production behavior before adding complexity or seeking human expertise for high-scale, interconnected designs.


### [5 ways to parse Dates and DateTimes in ClickHouse](https://yomu.fyi/post/5-ways-to-parse-dates-and-datetimes-in-clickhouse.md)
- Company: [Clickhouse](https://yomu.fyi/company/clickhouse.md)
- Author: Mark Needham
- Published: Mar 12, 2026
- Type: Tutorial

ClickHouse provides several ways to convert dates and datetimes from Unix timestamps, packed numeric values, known-format strings, mixed inputs, and file data. The examples use fromUnixTimestamp and its millisecond and microsecond variants, YYYYMMDDToDate and YYYYMMDDhhmmssToDateTime, plus parseDateTime or parseDateTimeInJodaSyntax when the input format is known. For unknown or mixed formats, parseDateTimeBestEffort and its DateTime64 counterpart accept inputs such as ISO 8601 strings, dates, and timestamp strings; invalid values otherwise raise an exception, while OrNull returns NULL and OrZero returns January 1, 1970 at midnight. The post also shows direct ::DateTime casting, controlled by cast\_string\_to\_date\_time\_mode: basic supports standard forms, whereas best\_effort broadens parsing but still throws for completely invalid dates. An inline or session setting lets the same casting approach parse rows from a CSV file.


### [What's new in ClickStack. February '26.](https://yomu.fyi/post/what-s-new-in-clickstack-february-26.md)
- Company: [Clickhouse](https://yomu.fyi/company/clickhouse.md)
- Author: The ClickStack Team
- Published: Mar 12, 2026
- Type: Announcement

ClickStack introduced several platform updates and query performance enhancements across both open-source and Cloud environments for February 2026. ClickHouse Cloud OpenAPI now exposes ClickStack resources including dashboards, alerts, sources, and webhooks to support automated configuration-as-code workflows. In addition, ClickHouse 26.2 embeds the ClickStack UI directly into the database binary with a 4.1 MB footprint for local exploration and diagnostics. Visualizations now support native SQL mode for tables, allowing users to join disparate datasets like logs and traces across injected dynamic time variables. Finally, query execution speeds improve through streaming skip indexes, index pruning on disjunctions, and an expanded 100,000-row lazy materialization limit alongside new threshold alerting on number charts.


### [Announcing chDB 4: write Pandas, run ClickHouse, now on Hex](https://yomu.fyi/post/announcing-chdb-4-write-pandas-run-clickhouse-now-on-hex.md)
- Company: [Clickhouse](https://yomu.fyi/company/clickhouse.md)
- Author: Ryadh Dahimene, Auxten Wang
- Published: Mar 11, 2026
- Type: Announcement

chDB 4 introduces Data Store, a native component that allows users to write Pandas-like code that executes on the ClickHouse OLAP SQL engine. Standard Pandas workflows suffer from eager execution, excessive memory materialization, and lack of automatic query optimizations. To solve this, Data Store implements a four-layer architecture that lazily records operations as a chain of LazyOp objects and compiles them into optimized ClickHouse SQL plans upon evaluation triggers. When encountering operations unsupported by ClickHouse, the QueryPlanner splits the pipeline into segments, routing tasks between ClickHouse and Pandas while using Python's memoryview to minimize data copying overhead. This release also integrates natively into Hex notebooks.


### [GitTrends: A Google Trends style view of the GitHub ecosystem](https://yomu.fyi/post/gittrends-a-google-trends-style-view-of-the-github-ecosystem.md)
- Company: [Clickhouse](https://yomu.fyi/company/clickhouse.md)
- Author: Lionel Palacin
- Published: Mar 10, 2026
- Type: Announcement

GitHub produces billions of events across issues, pull requests, and comments, but turning this vast stream into a real-time trends analyzer presents significant technical challenges. GitTrends is an open-source demo application designed to search and compare technology keywords across nearly ten billion GitHub events. Built to showcase the full-text search capabilities recently released in ClickHouse, the application indexes raw text without pre-computed answers or intermediate data transformation. ClickHouse executes full-text searches and aggregations within a single engine in one pass, eliminating cross-system joins and data movement. The demo includes live comparisons toggling between full-text search, bloom filters, and full table scans to illustrate query performance at scale.


### [Announcing General Availability of ClickHouse Full-text Search](https://yomu.fyi/post/announcing-general-availability-of-clickhouse-full-text-search.md)
- Company: [Clickhouse](https://yomu.fyi/company/clickhouse.md)
- Author: Melvyn Peignon
- Published: Mar 10, 2026
- Type: Announcement

ClickHouse has reached general availability for its native Full-text Search feature, designed to accelerate token-based text filtering across large datasets. The capability relies on deterministic inverted indexes rather than probabilistic Bloom filter skip indexes, mapping individual tokens directly to row numbers. It supports plain strings, string arrays, and map keys or values while allowing configurable pre-processing and tokenization using standard SQL expressions. The system does not implement relevance scoring or store positional data for ranking, focusing instead on rapid filtering paired with large-scale analytical aggregations. Testing shows significant reductions in scanned granules alongside substantial latency improvements, though text indexes require more storage than Bloom filters.


### [How Critical Manufacturing uses ClickHouse to bring real-time intelligence to the factory floor](https://yomu.fyi/post/how-critical-manufacturing-uses-clickhouse-to-bring-real-time-intellig.md)
- Company: [Clickhouse](https://yomu.fyi/company/clickhouse.md)
- Author: ClickHouse
- Published: Mar 10, 2026
- Type: Problem & solution

Critical Manufacturing faced performance and scaling bottlenecks while processing millions of factory floor events using a legacy Microsoft SQL Server analytics stack. Increasing data volumes caused historical queries to take hours, straining real-time dashboards and escalating operational maintenance. The engineering team replaced this architecture by migrating analytics workloads to ClickHouse connected directly to streaming Kafka topics. They implemented an ELT pattern alongside ReplacingMergeTree engines to deduplicate late-arriving sensor records and utilized TTL policies for automated lifecycle management. The resulting platform delivers sub-second analytical queries across historical and real-time operational data, expands into OpenTelemetry observability storage, and maintains embeddings for retrieval-augmented generation workloads.


### [What Replo learned optimizing 100+ billion events in ClickHouse](https://yomu.fyi/post/what-replo-learned-optimizing-100-billion-events-in-clickhouse.md)
- Company: [Clickhouse](https://yomu.fyi/company/clickhouse.md)
- Author: ClickHouse
- Published: Mar 9, 2026
- Type: Problem & solution

Replo built an analytics pipeline on ClickHouse to serve more than 4,000 Shopify merchants processing 3,000 to 5,000 events per second. The initial single-table architecture recalculated session metrics on the fly, leading to poor query efficiency as volume scaled. To speed up dashboard queries, the team implemented a mark-and-unmark pattern with SummingMergeTree and refreshable materialized views, but real-time deduplication and fractional attribution caused runaway query backlog loops. Replo resolved the issue by limiting real-time recomputations to a specialized table holding only the last 40 minutes of purchase-related events. This architecture allowed processing over 100 billion records while keeping query times fast and ingestion lag to approximately one minute.


[Newer posts](https://yomu.fyi/company/clickhouse/page/11.md) · [Older posts](https://yomu.fyi/company/clickhouse/page/13.md)