clickhouse

Bilingual

Migrating 6.5B Rows from MongoDB
2 months ago
- Acquired Gauges analytics platform and promised to migrate historical data without requiring customers to change tracking code or API integrations.
- Replaced the Ruby on Rails application with a Laravel-based system to handle ingest and API compatibility, avoiding long-term reliance on an unfamiliar language.
- Built a secure, read-only migration API server to interface with the legacy MongoDB database, keeping the main Fathom application isolated from MongoDB.
- Mapped Gauges sites to Fathom accounts, handling both new and pre-existing customers, and migrated historical breakdown data as monthly aggregates to reduce request volume.
- Overcame bottlenecks including underpowered migration server, local ClickHouse memory limits, and AWS Lambda timeouts by moving to production and simplifying the migration interface.
- Successfully cut over tracking endpoints and API, imported 6.5 billion rows of data, and retired the old infrastructure, resulting in significant cost savings.
Clickhouse is winning the Observability Wars
2 months ago
- Logs are consistently the worst part of observability work due to volume, schema drift, and conflicting expectations between developers and non-technical consumers.
- ClickHouse's columnar storage allows queries to read only relevant columns, significantly reducing I/O and enabling 10–14x compression on observability data.
- At 1 TB/day, all major observability stacks (Elasticsearch, LGTM, Datadog, ClickHouse) are comparable in complexity and cost.
- At 5–10 TB/day, Elasticsearch requires Kafka, shard management, and multiple clusters; LGTM demands 180+ pods and dedicated teams; Datadog becomes financially untenable with six- to seven-figure monthly bills.
- ClickHouse scales linearly by simply adding shards, maintaining the same architecture, query language, and operational model regardless of data volume.
- The trade-off for ClickHouse is upfront schema design and query complexity, but this investment pays off as data grows by orders of magnitude without architectural shifts.
Helicone allows users to write SQL directly to a shared ClickHouse
3 months ago
- Introducing HQL (Helicone Query Language), a SQL editor for customers to run queries directly against a ClickHouse cluster using a shared table.
- Ensuring security by implementing row policies and custom settings in ClickHouse to enforce data isolation between organizations at the database level.
- Creating a dedicated user and attaching a row policy to filter rows based on organization ID via custom settings, preventing cross-tenant data access.
- Using ClickHouse Cloud's fixed custom setting prefix (SQL_) to set organization-specific query parameters securely via the HTTP interface.
- Enhancing security with additional settings (e.g., readonly, timeouts) to prevent misuse and parsing queries for ergonomics rather than security.
- Revoking unnecessary privileges from the dedicated user to restrict access and relying on database-enforced security over application-side parsing.
- Conducting thorough testing, including subqueries and adversarial queries, to validate the row policy's effectiveness without app-side filtering.
- Achieving fail-closed behavior where missing settings cause errors instead of data leaks, with admin tools using separate connections to bypass policies.
Pg_re2: Postgres extension for fast, RE2-powered regular expressions in Postgres
3 months ago
- ClickHouse introduces pg_re2, a Postgres extension for fast RE2-powered regular expressions, emphasizing speed and compatibility.
- RE2 uses finite automata for faster, more predictable performance compared to Postgres's backtracking POSIX regex, with benchmarks showing 1.8x to 8.6x speedups.
- pg_re2 adds index support (@~ operator) with btree and GIN indexes, providing 1.1x to 1.8x acceleration and better trigram handling for patterns.
- Compatibility with ClickHouse regex functions is key, as pg_re2 addresses syntax differences (e.g., \b meaning word boundary vs. backspace) to ensure consistent results when using pg_clickhouse.
- pg_re2 functions push down to ClickHouse, eliminating discrepancies and improving performance for analytical queries, and is recommended for a unified high-performance data platform.
Have you heard? ClickHouse is winning the observability wars
3 months ago
- ClickHouse excels in observability at large scales (e.g., 10TB/day) while maintaining consistency and performance, unlike other tools like Elastic, LGTM, and Datadog that struggle.
- Observability built on columnar storage represents a fundamental improvement over the traditional three pillars model, offering better handling of high cardinality data and scalability.
- Many newer observability vendors use columnar storage but market themselves as cheaper alternatives to Datadog without highlighting the architectural advantages, obscuring innovation.
- The author argues that the value in observability comes from unified data (like traces or structured logs) rather than segmented pillars, which is threatened by Datadog's business model.
- ClickHouse's willingness to align with 'Observability 2.0' contrasts with other vendors, as they focus on solving customer problems rather than just competing on price.

clickhouse

Migrating 6.5B Rows from MongoDB

Clickhouse is winning the Observability Wars

Helicone allows users to write SQL directly to a shared ClickHouse

Pg_re2: Postgres extension for fast, RE2-powered regular expressions in Postgres

Have you heard? ClickHouse is winning the observability wars