Loading…
Clickhouse
High-performance open-source columnar database management system designed for real-time analytics and data warehousing at scale
- 235 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
Pipelined SQL in ClickHouse 26.8
ClickHouse 26.8 introduces pipelined SQL, extending the earlier ability to place FROM before SELECT with a |> operator for sequencing query transformations. Using the UK property prices dataset, the post compares conventional, FROM-first, and pipelined forms for filtering London sales, aggregating median prices by district, ordering results, and limiting output. Each pipeline stage acts as a complete-query checkpoint, while EXPLAIN SYNTAX shows that ClickHouse translates the syntax into nested standard SQL and optimizes the complete query without materializing every intermediate result. The syntax also supports EXTEND, reuse of aggregation aliases in later stages, views, and INSERT ... SELECT statements, though stage order affects which rows feed subsequent aggregations in these examples; the conclusion is that pipelines complement rather than replace conventional SQL.
Build a real-time market data app with ClickHouse and Massive
The post presents a real-time market-data application that ingests trade and quote ticks from Massive and stores them in ClickHouse for low-latency querying and visualization. It distinguishes quotes, which contain bid and ask prices and sizes, from trades, which record executed price, size, timestamps, and related fields. The implementation uses an authenticated Massive WebSocket subscription, Node.js ingestion, separate ClickHouse MergeTree tables ordered by symbol and one-minute timestamp buckets, and React charts. It also describes materialized-view aggregation for visualization and supplies a runnable repository with setup, build, start, admin, and synthetic-seed paths; the conclusion recommends durable delivery, replay, authentication, and workload-specific tuning before production use.
Introducing Scheduled Upgrades in ClickHouse Managed Postgres
ClickHouse Managed Postgres now supports scheduled maintenance windows, addressing the problem of routine platform upgrades landing at the same time for every customer, potentially during heavy workloads. Users can choose a two-hour UTC window, while Enterprise organizations can also specify the days of the week when maintenance is least disruptive. The setting covers ClickHouse-initiated Postgres minor-version upgrades, Managed Postgres feature updates, and patches to operating-system and system components, but not user-triggered changes or urgent storage scaling above 90% disk usage. Read replicas follow the schedule of their primary, and the feature is available without opt-in to Scale and Enterprise organizations; Basic users can see the setting and required plan.
MySQL CDC connector for ClickPipes is now Generally Available
MySQL CDC connector for ClickPipes is now Generally Available, enabling replication from MySQL and MariaDB into ClickHouse Cloud for analytical workloads. The release adds automatic parallel snapshotting: ClickPipes selects a logical partition key from a table’s primary key, including string and UUID types, and distributed processing reduced a benchmarked 1TB snapshot from over a day on one thread to about an hour across 32 workers. Production safeguards now require GTID-based replication and at least 72 hours of binlog retention, while new CPU, memory, network, and source, destination, and end-to-end lag metrics improve troubleshooting. MySQL CDC ClickPipes also support OpenAPI and Terraform 3.14.0+, are available across all ClickHouse Cloud service tiers, and began billing on September 1, 2026.
From Neon Postgres to ClickHouse Managed Postgres
This user story describes three teams that moved production Postgres workloads from Neon to ClickHouse Managed Postgres after encountering reliability, performance, cost, and network-transfer problems as usage grew. ClickPipes handled schema migration, a parallel initial load, and change data capture, allowing teams to test synchronized databases before cutover. Reported outcomes included zero downtime or connection restarts, faster and more predictable queries, lower CPU or transfer costs, and p99 latency improving from seconds to single-digit milliseconds in one case. The post attributes these gains to local NVMe storage and eliminating transfer fees when analytics already runs on ClickHouse, while noting differences from Neon such as slower branch creation and manual application of several DDL changes.
ClickHouse welcomes RunReveal
ClickHouse has acquired RunReveal, a company that built a security data platform on ClickHouse, and says the team’s expertise will become part of its work in security. The announcement frames security as a demanding data workload involving cloud audit logs, identity events, endpoint telemetry, and network flows, with continuous high-throughput ingest, years-long retention, and interactive query requirements. ClickHouse says the acquisition will bring sharper reference architectures, guidance on schema and retention design, and a clearer connection between customer requirements and its database roadmap, while RunReveal remains available through a bring-your-own-database model. Existing RunReveal customers will continue to receive platform support under unchanged contract terms, and the team’s work on agentic investigation is expected to inform support for analytics involving agents that hunt across sources, tune detections, and advance investigations.
The Agentic Analytics Benchmark: Measuring model accuracy and efficiency in analytical agents
The post presents data-agent-mnist, an open harness for measuring analytical agents on correctness, cost, and speed against a warehouse-shaped workload rather than a single text-to-SQL query. The authors extracted 201 production analytics questions from DWAINE, curated and anonymized them, reconstructed a deterministic synthetic warehouse with 18 tables and 865 columns, and evaluated 28 proprietary and open-weight models. The benchmark preserves schema discovery, multi-turn querying, planning, and multi-hop joins across curated marts and a dimensional CRM layer, with provider-diverse ground truth and judging described as part of the workflow. On the authors’ warehouse, Claude Fable 5.1 ranked first for correctness at 76.6%, while the full run cost $1 with DeepSeek V4 Flash versus $52 with Fable 5.1, an 11-percentage-point correctness difference. The harness is intended to identify the best production fit for a specific warehouse, not a universal winner.
New system views in PostgreSQL 19
PostgreSQL 19 adds four system views for lock contention, recovery, autovacuum priorities, and dynamic shared memory allocations. The article presents pg_stat_lock as cumulative, cluster-wide statistics with one row per lock type, reporting waits, total wait time, fastpath_exceeded, and stats_reset; waits and wait_time count only successfully acquired locks that exceeded deadlock_timeout, while fast-path overflow attempts are counted separately. Examples reset statistics, reproduce a 2.5-second table lock, and scan 140 partitions, producing 1 wait, 2201.783 milliseconds, and 422 fast-path overflows. The post describes recovery and autovacuum views, noting that autovacuum scores suggest likely priorities rather than guarantees because scoring and worker scheduling can differ. DSM registry entries expose name, type, and size; NULL means initialization failed.
How Uken Games reduces observability costs by 87% with ClickHouse
Uken Games replaced Datadog with an open-source observability stack built around ClickHouse to monitor backend services supporting mobile games with millions of players. The migration addressed rising Datadog costs and vendor lock-in while targeting pragmatic feature parity across metrics, traces, and logs. OpenTelemetry collectors run in two layers: lightweight agents on ECS instances pre-filter traces, while an autoscaling gateway batches and buffers writes to SigNoz on ClickHouse; managed Prometheus handles metrics, CloudWatch retains logs, and Grafana provides dashboards and alerts. Uken samples ordinary traces, retains errors and high-latency requests, computes alert metrics without sampling, and limits retention to two weeks. The resulting system stores all traces on one ClickHouse node using about 170 GB, operates without migration downtime, and costs about $12,000 annually—an 87% reduction.
A new getting started experience for ClickHouse Managed Postgres
ClickHouse Managed Postgres introduces a four-step onboarding experience designed to move users from provisioning a service to working with operational data and ClickHouse analytics. Existing onboarding placed new users on an empty Overview page, while the documentation quickstart required users to track progress outside the product. The new flow guides users through creating a service, migrating or ingesting data, running a query in the SQL Console, and configuring Postgres CDC to ClickHouse. Data can arrive through a managed Postgres-to-Postgres ClickPipe, a language client, a sample dataset, or a table created from scratch; pg_clickhouse provides a PostgreSQL query layer over ClickHouse foreign tables. The article reports less than a minute of replication lag and benchmarks showing five aggregations falling from 555 ms to 164 ms and a JOIN with aggregations from 1,246 ms to 170 ms on one million-row data set.
So, is ClickHouse winning the observability wars?
The article examines whether ClickHouse is “winning” observability by separating the storage-and-query question from the broader work of collection, schema design, correlation, visualization, alerting, and investigation. It argues that ClickHouse fits telemetry because its columnar layout stores fields separately, enabling compression and allowing analytical queries to read only selected columns across millions or billions of events; ordering keys, sparse primary indexes, and data-skipping indexes further reduce work. The discussion covers both rich, wide events and conventional logs, traces, and metrics, and presents ClickStack as a combination of ClickHouse, an OpenTelemetry Collector, and a UI that preserves familiar workflows. The conclusion is qualified: ClickHouse has become the de facto storage and query engine for many teams and a strong default for large telemetry workloads, but it has not won observability as a whole, while full-text indexes, PromQL support, and broader product layers remain important.
A universal interface: How QuintoAndar made ClickHouse plug-and-play with managed Postgres
QuintoAndar’s customer data platform was split between a Postgres-based hot layer for low-latency lookups and a Databricks-based cold layer for analytical processing, creating duplicate code and expensive MERGE INTO operations. The team consolidated the pipeline by having its event gateway write directly to ClickHouse Cloud, which also serves API requests and analytical queries while remaining accessible to Trino, Spark, and Superset. Hightouch’s Customer Studio did not support ClickHouse, so QuintoAndar used ClickHouse Managed Postgres and the pg_clickhouse extension to expose ClickHouse tables as Postgres tables. Queries are pushed down to ClickHouse, while Hightouch’s planner and audit tables remain in NVMe-backed Postgres, allowing the existing activation workflow to continue with negligible round-trip impact for that use case.
What's new in the ClickHouse .NET Driver: the road from 1.0 to 1.3
The ClickHouse .NET Driver moved from its 1.0 stable release to versions 1.1–1.3, changing how applications insert, read, and parameterize data while expanding type coverage, configurability, observability, and .NET ecosystem integrations. The largest ergonomic change is registered POCO support: RegisterPocoType() validates mappings up front, pre-compiles getters and setters, and enables InsertBinaryAsync with IEnumerable plus lazy QueryAsync() results through IAsyncEnumerable. Attributes customize column and ClickHouse type mappings, JSON serialization supports POCOs with paths and ignored properties when JsonWriteMode is Binary, and three optional extension points govern parameter type inference, wire formatting, and read conversion. The announced 1.4 roadmap targets allocation reductions, pluggable compression, and box-free POCO operations; version 1.5 is planned to add a Native/TCP client with columnar transfer and server progress information.
How Suprema Gaming made its data platform agent-ready with ClickHouse Cloud
Suprema Gaming migrated its analytics platform from Snowflake to ClickHouse Cloud on AWS as part of a company-wide move toward agentic operations. The previous stack delivered data about four hours late, required heavy dbt rebuilds, and often took minutes to answer queries, limiting real-time investigations and interactive analysis. Suprema rebuilt ingestion with ClickPipes for supported sources and Debezium plus Kafka for others, while dbt and GitHub Actions deploy the warehouse-layer structure without external cron jobs, Airflow, or a separate orchestrator; governed access uses role-based controls and query-time PII masking. Before cutover, every metric was validated against the reference source, and the resulting platform reduced warehouse spend by roughly 62%, improved direct-read performance 27x, and maintains about one-minute p95 freshness, supporting an early production deployment of ClickHouse Agents.
Read your writes: WAIT FOR in PostgreSQL 19
PostgreSQL 19 introduces the top-level SQL command WAIT FOR, addressing stale reads that can occur when an application writes to a primary and immediately reads from an asynchronously replicated standby. After the write, the client or pooler obtains an LSN with pg_current_wal_insert_lsn() and sends it to the reading session, where WAIT FOR blocks until the requested WAL position reaches the selected stage. The default standby_replay mode makes the change visible, while standby_write, standby_flush, primary_flush, TIMEOUT, and NO_THROW provide other durability and error-handling choices. The command waits without holding a snapshot, avoiding a replay self-deadlock, and exposes related wait events in pg_stat_activity. The feature preserves asynchronous writes by making only reads that require read-your-writes consistency wait, though timeline changes after promotion require caution.
ClickGap: Autonomous QA for ClickHouse
ClickGap addresses defects that survive ClickHouse’s extensive CI, human review, and in-house automated review, including untested behavior, silent resource waste, and performance regressions. The autonomous QA daemon examines eligible merged pull requests against verified builds, uses separate bug and coverage analysts, and requires hypotheses to become executed tests. An adversarial reviewer and ten evidence gates check consequences, source citations, complete-file review, reproducibility, and other conditions before a report can be filed. Five months after launch, ClickGap had filed roughly 500 issues and opened around 200 coverage or fix pull requests; more than half of the issues were closed, with over 200 linked to a fix PR. The system’s central design goal is to make reports trustworthy rather than merely prolific.
Ensuring reliable OpenTelemetry ingestion at scale
LogHouse’s OpenTelemetry pipeline ingests 50 million events per second and stores 177 PiB of uncompressed data, but its initial agent-to-gateway design could not reliably absorb ClickHouse backpressure. In-memory queues caused instability during short-lived spikes or database outages, while local collector write-ahead logs made gateways stateful, slowed recovery, delayed fresh telemetry behind FIFO backlogs, and took about four hours to drain 1 TiB. The team rejected adding Kafka because operating a new tier-zero service across cloud regions was not justified for this use case, then designed a custom architecture using blob storage, the OTel failover connector, overflow notifications, and a catchup collector. In staging, failover redirected 120,000 events per second to S3 without changing gateway resource usage, and recovery resumed ClickHouse writes while the catchup collector drained the queue; the design scaled from 10 million to 50 million events per second, with added regional setup and less precise recovery visibility.
What else runs on your Postgres server, and how do we stop it from taking the database down?
The Postgres VM described here runs PgBouncer instances, a backup agent, metrics exporters, a local Prometheus, a log collector, and watchdog timers alongside the database. Those processes share machine-wide resources outside Postgres’s huge-page-backed shared_buffers, so leaks, CPU saturation, or disk exhaustion can threaten database availability. ClickHouse Managed Postgres places supporting services in a cgroup v2 slice and combines Go runtime GOMEMLIMIT targets with systemd memory.high and memory.max enforcement. GOMEMLIMIT encourages earlier garbage collection, memory.high applies reclaim and throttling, and memory.max provides a hard ceiling whose OOM scope excludes Postgres because it uses a separate cgroup. CPU weights, bounded buffers, log and metric controls, disk thresholds, and exemptions for replication and monitoring sessions extend isolation across other failure modes; these controls are enabled by default.
August 2026 newsletter
The August 2026 newsletter rounds up ClickHouse product releases, research, managed services, observability, community work, and upcoming events. It introduces Andy Pavlo’s arrival to establish ClickHouse Labs, describes the 26.7 release’s join optimizations, token-position text indexes, and EXPLAIN ANALYZE, and reports a 40× phrase-search improvement on a Hacker News dataset. It also covers an interactive ClickBench playground for more than 100 database systems, running isolated workloads in Firecracker microVMs on AWS EC2, plus guidance on combining Postgres for mutable data with ClickHouse for immutable analytical workloads. Additional items address Managed Postgres notifications and backups, TTL-related disk growth, vector-search trade-offs, and hdx-evals, which found a purpose-built ClickStack MCP server scored 7–20 percentage points higher than direct SQL access in five reproducible scenarios.
POSETTE Talk Recap - Postgres Isn't Slow. Your Storage Is
At POSETTE 2026, Sai Srirampur’s talk examines how storage limits can produce slow ingestion, erratic P95 reads, lagging autovacuum, slower checkpoints, and logical replication lag in large PostgreSQL workloads. The benchmark ran the same 3.3-billion-row, random-UPDATE workload on four local-NVMe clusters and four baseline gp3 EBS clusters, using m6id.4xlarge instances, 64 clients, and 16 worker threads. NVMe reached median 16,030 TPS versus 1,734 TPS on EBS, reduced median latency from 36.9 ms to 4.0 ms, and completed the tested VACUUM and replication-backlog tasks faster. The analysis attributes most of the gap to storage waits rather than CPU work, while the proposed production model combines local NVMe with quorum-based replication, external base backups, and archived WAL for recovery.
Topics they write about
All topics →Weekly digest
The best engineering reads, once a week.