May 2026 Summaries

"Big data" often refers to datasets so large that they surpass a single server's memory capacity, requiring specialized systems like ClickHouse® for efficient handling. ClickHouse, an open-source OLAP database, is tailored for high-performance analytics over petabyte-scale datasets, employing a columnar storage format, vectorized query engine, and distributed execution model for scalability. It utilizes compute-storage separation in ClickHouse Cloud, allowing stateless compute nodes to access shared storage without fixed shard ownership, leading to efficient scaling and query execution. To manage massive datasets, ClickHouse introduces index sharding to distribute primary and secondary index analysis across replicas, minimizing memory usage and enhancing query speed. For join-heavy queries and large-scale aggregations, multi-stage distributed execution improves performance by parallelizing query stages across nodes. Schema design emphasizes optimal partition and sort key selection to minimize data scanned during queries, and secondary indexes like bloom filters enhance high-cardinality lookups. ClickHouse also incorporates tiered storage with TTL rules for efficient data management, sampling for exploratory queries, and specific ingestion techniques for handling large-scale streaming and historical data. Approximate aggregations using functions like uniq() and quantile() support most analytics needs efficiently, while pre-aggregated rollup tables facilitate fast dashboard queries. PREWHERE optimizations and monitoring merge health ensure system performance at scale. Managed solutions like Tinybird offer ClickHouse's capabilities without the complexity of self-hosting, simplifying operations while maintaining scalability and performance.

Security operations rely heavily on log data from various sources such as firewalls, authentication systems, and cloud audits, requiring rapid ingestion, long-term storage, and efficient query capabilities. Traditional SIEM platforms use costly proprietary technologies, whereas ClickHouse offers an open-source, columnar storage solution optimized for high-volume data ingestion and fast query performance, as demonstrated by its deployment in companies like IBM and Exabeam. The text details how ClickHouse's schema patterns, such as the use of LowCardinality and IPv4 data types, enhance performance for security logs by enabling efficient filtering, aggregation, and compression. It highlights the use of detection queries for identifying security threats, such as brute force attacks and multi-stage attacks, emphasizing the benefits of using materialized views and windowFunnel functions to streamline data processing. Furthermore, it discusses log enrichment techniques using GeoIP data, strategies for cost-effective storage with tiered retention policies, and the option of using managed ClickHouse services like Tinybird for scalable, real-time analytics without the overhead of managing a cluster.

ClickHouse is an analytical database designed for real-time workloads, contrasting with traditional BI tools that are optimized for batch queries on older data, leading to dashboard performance issues. Many users experience bottlenecks not due to ClickHouse itself but because of schema design, connection methods, or the query patterns generated by BI tools. The text outlines how to effectively connect and optimize BI tools like Grafana, Metabase, Looker Studio, Tableau, and Power BI with ClickHouse to leverage its real-time capabilities, emphasizing proper schema design and query patterns to enhance performance. ClickHouse excels in handling high query concurrency with its columnar storage, vectorized execution, and built-in caching features, which are particularly beneficial for dashboards that require fast, parallel queries. Additionally, Tinybird offers a managed ClickHouse solution with an API layer that simplifies embedding analytics directly into products, eliminating the need for separate BI layers and improving performance for customer-facing dashboards.

ClickHouse presents a unique storage model that diverges from traditional relational databases, emphasizing the importance of initial schema decisions, such as partition key and table engine selection, which are challenging to modify later. Its architecture provides exceptional ingestion throughput and query speed when configured correctly, but poor initial decisions can lead to performance issues. Key considerations for data engineers include choosing the right table engine—like MergeTree for append-only data or ReplacingMergeTree for mutable records—and ensuring efficient data ingestion by batching inserts to avoid overwhelming the merge scheduler. The use of materialized views facilitates real-time transformations, distinguishing ClickHouse from batch-oriented systems like dbt. Schema evolution is supported but requires careful planning, particularly for partition and sort keys. Monitoring insert throughput, merge backlog, and query latency is essential to maintaining pipeline health. Tinybird offers a managed ClickHouse solution that incorporates data engineering workflows, automating parts management and simplifying the integration with Kafka for real-time data streams, allowing engineers to deploy pipelines swiftly without the need for intensive cluster operations.

ClickHouse is a high-performance, column-oriented SQL database management system designed for online analytical processing (OLAP), making it optimal for analytical queries on large datasets. Unlike traditional row-oriented databases like Postgres, ClickHouse stores data in columns, enabling faster reads for queries that require only certain columns, which significantly enhances performance for analytics tasks. It thrives on operations like aggregations and time-series rollups, executing them in milliseconds even on vast tables, but is less suited for online transaction processing (OLTP) tasks that involve frequent updates or deletions. ClickHouse’s columnar storage, vectorized execution, and data compression allow for efficient processing and storage, which, combined with its unique features like MergeTree engine, partitioning, and order sorting, make it ideal for growing data volumes and live dashboards. While ClickHouse excels in analytics, it is often used alongside Postgres, where the latter handles transactional data and ClickHouse manages analytics, providing a robust dual-system setup for comprehensive data management strategies.

ClickHouse is a high-performance, column-oriented SQL database management system designed for online analytical processing (OLAP), excelling in handling large-scale data queries efficiently. Unlike traditional row-store databases, ClickHouse stores each column independently, allowing it to perform queries such as aggregations, GROUP BY operations, and COUNT DISTINCT on high-cardinality columns with remarkable speed by reading only relevant columns. It supports standard SQL and offers extensions like uniq() for fast approximations and PREWHERE for optimized filtering, enhancing performance further. ClickHouse's architecture is particularly suited for analytics at scale rather than scheduled batch queries, making it ideal for tasks like cohort retention analysis, funnel analysis, and period-over-period comparisons. While it excels in scanning large datasets quickly, it is not optimized for point lookups or frequent row-level updates and deletes, which are more efficiently handled by databases like Postgres. The platform Tinybird integrates with ClickHouse to provide a managed environment with a SQL editor and API layer, facilitating the sharing of analytical insights through parameterized HTTP endpoints.

ClickHouse is a high-performance, column-oriented SQL database management system designed for online analytical processing (OLAP) and is particularly suited for aggregations, time-series queries, and GROUP BY operations, which traditional OLTP databases like Postgres handle inefficiently. This guide is aimed at backend engineers new to ClickHouse, covering schema design, data ingestion patterns via HTTP or Kafka, querying from application code, and the advantages of using managed ClickHouse solutions like Tinybird. ClickHouse is optimized for large data scans and aggregations, making it ideal for analytics tasks such as event tracking and time-series metrics, while Postgres remains better suited for application data requiring frequent row-level writes. The guide emphasizes the complementary nature of using ClickHouse alongside Postgres, provides practical advice on table design, batching inserts, parameterized queries, caching, and enriching analytics with OLTP data, and discusses the benefits and considerations of using managed ClickHouse to reduce operational overhead.

Adtech systems require handling vast amounts of data at high speeds, processing millions of bid requests per second, deduplicating impressions and clicks, and ensuring campaign reports are fresh enough for traders to act on. Traditional databases like Postgres struggle under such demands, leading many teams to adopt a stack comprising Kafka for ingestion, Spark for aggregation, and a custom reporting layer, though this setup requires significant operational resources. ClickHouse, a columnar database, emerges as a preferred choice for its real-time processing capabilities, exemplified by InMobi's successful migration which reduced latency and costs. The article details how ClickHouse's architecture, including its use of materialized views and efficient schema designs, supports high ingestion throughput and fast query responses. ClickHouse's integration with Kafka facilitates seamless bid stream ingestion, while Tinybird provides a managed ClickHouse solution, simplifying the ingestion, deduplication, and querying processes with an API layer, thus alleviating the operational burden on adtech teams.

Tinybird faced significant challenges with orphaned cloud storage objects in their ClickHouse clusters, leading to inefficiencies and increased costs. Over time, they accumulated petabytes of unused data, which resulted in a 45% reduction in storage costs after cleanup. While cleaning up these orphaned objects, they almost lost real data due to incomplete metadata snapshots, which misclassified some legitimate data as deletable garbage. The recovery process was complex, requiring a reconstruction of relationships between various metadata sources and backup paths. This incident prompted improvements in their system, including better snapshot validation, enhanced orchestration, and more reliable recovery procedures. Ultimately, the experience not only reduced costs but also strengthened their operational safety and recovery protocols, as they continue to refine the garbage collection process and prevent future creation of orphaned objects.

Developer Plans have transitioned from a QPS (queries per second) limit to a billing model based on actual compute usage measured in vCPU-seconds, eliminating previous restrictions and overages associated with QPS. This change responds to developer feedback that QPS limits were restrictive and instead focuses on charging for the actual computational resources consumed, making the system fairer by aligning costs with compute usage rather than arbitrary request counts. Each plan now includes built-in burst capacity, allowing temporary increases in compute usage without throttling, except under sustained heavy load where read queries may be limited. The update also removes the 24-hour lock on plan changes, permitting flexible upgrading or downgrading, with a minimum fee for changes within the first 24 hours. Existing base plan pricing remains unchanged, but the S-3 plan is discontinued for new customers, while free plan users will adopt the vCPU-based billing structure. This revised model, tested since April with select customers, aims to provide a more equitable and efficient system and is accompanied by comprehensive documentation and support resources for users.

A customer in the audience intelligence space faced the challenge of managing a vast and rapidly growing dataset of social media posts, which required efficient semantic similarity searches to find posts most relevant to user queries. Initially reliant on AWS S3 Vectors, their solution proved insufficient due to limitations on search results, prompting them to explore alternatives. The team at Tinybird demonstrated that, by optimizing their data infrastructure using ClickHouse, they could overcome previous limitations. They achieved this by consolidating fragmented data partitions into a single HNSW graph and increasing the index cache size to fit their entire dataset into RAM, drastically reducing query response times from several minutes to milliseconds. This configuration allowed for efficient retrieval of up to 1,000 results with consistent latency, transforming their data retrieval process and eliminating the need for an additional dedicated vector-search service. The successful implementation provides a streamlined approach to querying semantic similarity in Tinybird, though ongoing challenges remain in maintaining performance during continuous data ingestion, which will be addressed in subsequent efforts.

Tinybird offers a managed data platform featuring production-ready ClickHouse® capabilities, emphasizing sub-second SQL APIs and real-time analytics through a Kafka connector, allowing for seamless data ingestion and querying. The platform is designed with an AI-focused developer experience, facilitating safe schema iterations and zero-downtime migrations, and supports high availability with fault tolerance and auto failovers. Tinybird integrates with various BI tools and ORMs, ensuring robust security and compliance, including SOC 2 Type II certification. Users can access a range of resources like blogs, customer stories, video tutorials, and a comprehensive ClickHouse® course, along with community support via Slack and open-source project assistance. The platform is suitable for diverse use cases, including user-facing dashboards, real-time change data capture, gaming and web analytics, and content recommendation systems, with integrations supporting major data services like Google BigQuery, Snowflake, and Amazon S3.

Analytics queries can significantly impact transaction performance in databases like Postgres by consuming shared resources, such as buffer cache pages and connections, which transactional queries also require. The solution isn't merely to speed up analytics queries but to separate their workloads from transactional operations. Strategies for addressing this issue range from creating read replicas to offload analytics queries, using materialized views for frequently run queries, and implementing partial indexes or time-based partitioning to minimize the data touched by analytics queries. Additionally, setting a statement timeout for analytics roles can prevent long-running queries from holding connections indefinitely. However, these methods only mitigate the problem temporarily, as Postgres, being a row-store transactional database, is inherently unsuited for analytics workloads. A more permanent solution is to move analytics workloads to a columnar OLAP database like Tinybird, which is optimized for such tasks, thus allowing Postgres to focus on transactional operations while Tinybird handles analytics efficiently.

The text provides a comprehensive guide to diagnosing and addressing common SQL database performance issues, primarily within Postgres, before resorting to hardware upgrades. It highlights several key areas such as missing indexes, inefficient query patterns, and connection management as frequent culprits of slow database performance. The text emphasizes the importance of running EXPLAIN ANALYZE to understand what is causing the slowdown and suggests practical solutions like adding appropriate indexes, optimizing query structure, avoiding SELECT *, and using connection pooling. It also discusses advanced techniques like partitioning large tables, materializing expensive aggregations, and the mismatch of running analytical queries on transactional databases, suggesting the use of a columnar OLAP database like Tinybird for such workloads. Lastly, it cautions against relying solely on hardware improvements, advising that issues should be addressed at the query level first to ensure lasting performance enhancements.

Project Kijiji is an innovative real-time platform designed to address and predict inefficiencies in African internet routing by analyzing potential impacts of new Internet Exchange Point (IXP) peering agreements. Utilizing a Graph Neural Network (GNN) trained on BGP event streams and supported by Tinybird's real-time data pipelines, the platform seeks to mitigate "trombone" detours, where African internet traffic is unnecessarily routed through European hubs due to insufficient local peering agreements. By embedding a detailed data model into every event at ingestion, Project Kijiji efficiently categorizes routing events and prioritizes latency reduction in regions with lower GDP. The system's architecture allows for high-cardinality, append-only data aggregation, crucial for real-time path analysis. The project aims to transition from synthetic data to live BGP ingestion, offer APIs for ISPs to query potential latency dividends, and submit findings for academic validation, all while maintaining an open-source ethos under the guidance of AI Engineer Elvis Muchiri.

Fivetran offers three main pathways to integrate with ClickHouse® for efficient data handling and analytics: a native destination connector, a custom webhook, and an S3/GCS intermediate storage solution with Tinybird. Each option provides different levels of complexity and control, allowing data teams to choose based on their network access, schema requirements, and need for downstream API services. The native connector provides a straightforward, zero-code approach with automatic schema synchronization, suitable for teams using Fivetran's managed services. The custom webhook destination is ideal for private networks and allows for extensive customization of data ingestion logic, albeit with higher operational costs. The third option, involving Tinybird, combines Fivetran’s managed replication with REST API capabilities, offering a comprehensive solution for teams that need to serve analytical queries and user-facing analytics from a single pipeline. ClickHouse® serves as the analytical destination, known for its columnar OLAP database capabilities, providing sub-second query responses and efficient handling of operational data.

Google Data Studio, now rebranded as Looker Studio, is a free BI and data visualization platform that can integrate with ClickHouse®, a columnar OLAP database, to create interactive dashboards without needing to export to BigQuery or another data warehouse. There are three main methods for connecting these two technologies, each suited to different needs and infrastructure setups: using the Google Data Studio Community Connector for direct queries, employing the Tinybird REST API to act as an intermediary, or utilizing a JDBC bridge proxy for private ClickHouse® instances. Each method has distinct advantages and trade-offs, such as execution limits with the Community Connector or added latency with the Tinybird approach. The integration is ideal for teams whose data requirements surpass the capabilities of Google Analytics or BigQuery, offering fast query performance and scalable data management through ClickHouse®'s architecture. Designing schemas thoughtfully for Data Studio compatibility and ensuring proper security and operational monitoring are crucial for efficient integration.

A ClickHouse integration with dbt offers various pathways for data teams to enhance their analytics capabilities by combining the widely adopted SQL-based transformation framework of dbt with ClickHouse's high-performance, columnar OLAP database. This integration allows for executing dbt models against ClickHouse tables, materializing models as views or tables, and facilitating fast analytical queries. Three main options for this setup include using the dbt-clickhouse community adapter for self-managed setups or ClickHouse Cloud, connecting via the official dbt Cloud + ClickHouse Cloud connection, and utilizing Tinybird as a ClickHouse-powered transformation layer. Teams can leverage familiar dbt concepts such as models, refs, tests, and seeds while taking advantage of ClickHouse’s efficient handling of large datasets. When choosing an integration path, considerations include the need for full dbt feature parity, whether ClickHouse is self-managed or cloud-based, and whether the transformed data needs to be accessible via REST APIs or is mainly for BI tools. Each integration option provides specific configurations and model code implementations to facilitate seamless integration and optimized performance.

A ClickHouse integration with Apache Airflow enables data engineering teams to orchestrate high-performance analytical pipelines by scheduling, transforming, and querying data from ClickHouse, a columnar OLAP database, through Airflow's workflow management capabilities. This integration can be achieved through three main approaches: using the official ClickHouse Provider, leveraging Tinybird's REST API, or employing Python-level control with clickhouse-connect. Each method offers distinct advantages based on the complexity and requirements of the data pipeline, such as SQL-centric transformations, API reuse, or dynamic Python logic. The integration is particularly beneficial for scaling beyond simple cron jobs by providing Airflow's DAG-level visibility, task dependency management, and retry policies, and is ideal for complex multi-system pipelines. However, it requires careful consideration of schema design to avoid issues like non-idempotent retries and connection pool exhaustion, and it also involves managing authentication and security practices to safeguard data processes.