<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Altinity - Medium]]></title>
        <description><![CDATA[The go-to resource to optimize ClickHouse® performance, covering best practices, tips, tutorials from ClickHouse experts, community members, developers, data engineers, and more. - Medium]]></description>
        <link>https://medium.com/altinity?source=rss----fc2a2d760d0f---4</link>
        <image>
            <url>https://cdn-images-1.medium.com/proxy/1*TGH72Nnw24QL3iV9IOm4VA.png</url>
            <title>Altinity - Medium</title>
            <link>https://medium.com/altinity?source=rss----fc2a2d760d0f---4</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 20:11:35 GMT</lastBuildDate>
        <atom:link href="https://medium.com/feed/altinity" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Introducing Hybrid Tables: Transparent Query on ClickHouse® MergeTree and Iceberg Data]]></title>
            <link>https://medium.com/altinity/introducing-hybrid-tables-transparent-query-on-clickhouse-mergetree-and-iceberg-data-56ca25d605d2?source=rss----fc2a2d760d0f---4</link>
            <guid isPermaLink="false">https://medium.com/p/56ca25d605d2</guid>
            <category><![CDATA[merge-tree]]></category>
            <category><![CDATA[apache-iceberg]]></category>
            <category><![CDATA[data-lake]]></category>
            <category><![CDATA[parquet]]></category>
            <category><![CDATA[clickhouse]]></category>
            <dc:creator><![CDATA[AltinityDB]]></dc:creator>
            <pubDate>Wed, 01 Apr 2026 12:31:02 GMT</pubDate>
            <atom:updated>2026-04-01T12:31:01.879Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*56FoAhWbvhUk-FzN.png" /></figure><blockquote><em>Your tsaritsa, sire, last night</em><br><em>Was delivered of a fright –</em><br><em>Neither son nor daughter, nor</em><br><em>Have we seen its like before.</em><br>– Alexander Pushkin, “The Tale of Tsar Saltan”</blockquote><p>Project Antalya aims to extend ClickHouse with an alternative storage model based on Iceberg and Parquet. However, Iceberg is more suited for cold data, as one can guess from its name. The hot data is best served by a regular MergeTree. How to make those two work together in a convenient way for the user? This is what the new Hybrid table engine stands for. It’s a key feature of the latest <a href="https://docs.altinity.com/releasenotes/altinity-antalya-release-notes/25.8">Altinity Antalya 25.8</a> build.</p><h3>The Problem</h3><p>In our <a href="https://altinity.com/blog/project-antalya-progress-alter-table-export-and-other-cool-features">previous article</a>, we demonstrated how EXPORT PART can be used to move data from MergeTree to Iceberg. This is an important step towards the hybrid storage model. Using EXPORT PART and a bit of scripting one may implement of the following workflow:</p><ol><li>Load data into <a href="https://altinity.com/blog/clickhouse-mergetree-on-s3-intro-and-architecture">ClickHouse MergeTree</a></li><li>EXPORT PARTS for old partitions to Iceberg</li><li>DROP old parts from MergeTree (or use TTL DELETE)</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*Bti020iwvUNlYq2P.png" /></figure><p>However, it is missing one very important feature — how to query the data. We have part of the data in one table, and another part in a different table. Let’s call the “parts” segments. Segments may overlap. Therefore we need a “watermark” that divides the segments in order to avoid duplicated data. Our query can be expressed in pseudo-language as follows:</p><ul><li>IF date &gt;= <em>watermark_date</em>, get data from the MergeTree table</li><li>IF date &lt; <em>watermark_date</em>, get data from the Iceberg table</li></ul><p>But how to do it in SQL? Let’s look at possible options in ClickHouse, and explain why we had to develop a new one.</p><h4>SQL View</h4><p>The very first idea that comes to mind is using a regular VIEW and UNION ALL for two tables. For example:</p><pre>CREATE VIEW my_view AS<br>SELECT * FROM my_mergetree_table<br>WHERE date &gt;= watermark_date<br>UNION ALL<br>SELECT * FROM my_iceberg_table<br>WHERE date &lt; watermark_date</pre><p>It does not look too hard. Also it allows us to use parameterized views in order to set ‘watermark_date’ without changing the view. So it looked like an attractive prototype. However, actual tests showed unexpected problems.</p><p>As all of us know, ClickHouse is written in C++. Iceberg on the other hand is written in Java. That creates incompatibility between standard data types. For example, ClickHouse uses unsigned integers (UInt) extensively, while Iceberg only allows signed integers. There are others as well. Parquet can support unsigned data types. So when Parquet data files are loaded into Iceberg, the data types are inferred incorrectly. UInt becomes (signed) Int of the same scale. Now, if we query two tables, one having Int and another one having UInt, ClickHouse automatically does type casting to the bigger type. But only until you store UInt64. ClickHouse does not support type lifting to Int128 automatically, and throws an exception:</p><pre>Code: 386. DB::Exception: There is no supertype for types UInt64, Int64 because some of them are signed integers and some are unsigned integers, but there is no signed integer type, that can exactly represent all required unsigned integer values. (NO_COMMON_TYPE) (version 25.8.9.20238.altinityantalya (altinity build))</pre><p>Of course, this problem can be solved when writing to Parquet. But there are other data type differences that confuse ClickHouse.</p><p>The biggest problem, however, is performance of aggregation queries. When one runs any aggregate function on a view with UNION ALL, ClickHouse will not push aggregation down to view subqueries, and instead unions results before applying the aggregation. This is very inefficient. While this issue could be potentially solved by rewriting the query, it looked like a path into a thicket of optimizer problems that we did not want to follow.</p><h4>Merge Engine</h4><p>The next idea was using the Merge table engine. This can be done as follows:</p><pre>CREATE OR TABLE my_merge_table<br>AS my_mergetree_table<br>ENGINE = Merge(REGEXP(&#39;my_db|my_iceberg_db&#39;),REGEXP(&#39;my_mergetree_table|my_iceberg_table&#39;))</pre><p>This does not allow us to add watermarks directly, but we can add it with a VIEW using _table virtual column:</p><pre>CREATE VIEW my_view AS<br>SELECT * FROM my_merge_table<br>WHERE (_table = my_mergetree_table AND date &gt;= watermark_date)<br>OR (_table = my_iceberg_table AND date &lt; watermark_date)</pre><p>This approach has an advantage that we explicitly define the schema for the Merge table (in the example above using AS statement) avoiding schema inference. However, other issues persist. The main problem is bad performance. It’s once again caused by the fact that the Merge engine does not push query execution steps down — such as aggregations or limits. In essence, a Merge table simply unifies data from multiple sources and applies most transformations afterward. The underlying tables are unaware that the data will be aggregated later, so they can’t apply optimizations like projections. The result can be significant performance degradation for analytical queries.</p><p>We also realized that SQL View and Merge table approaches do not work well when Distributed tables need to be used. Both View over Distributed and Distributed over View will lose standard ClickHouse optimizations essentially converting ClickHouse cluster into a slug.</p><p>So we continued to search for a better solution.</p><h4>Distributed Engine</h4><p>After several failed attempts to emulate queries to different engines using existing ClickHouse functionality, we started to think about what exactly the query to MergeTree and Iceberg means. We defined the following requirements:</p><ul><li>It should be possible to combine data from multiple sources</li><li>No query rewrite should be needed when going from the existing table to a new one</li><li>Existing ClickHouse optimizations should keep working: pushing down conditions, aggregations, limits, joins etc.</li></ul><p>And then there was an <em>Eureka!</em> moment — this is very similar to Distributed engine query execution, especially if we consider swarm clusters. Distributed engine query works exactly as we need: it sends queries to multiple tables, does partial aggregation and merges results together in a node initiator. The only thing missing is the ability to query <em>different tables</em>, and not shards or replicas of a single table.</p><p>This is how the Hybrid table was born.</p><h3>Hybrid Table Engine</h3><p>The Hybrid Table Engine allows users to define multiple table segments that are selected by a condition. This is how we can define it for the original example:</p><pre>CREATE TABLE my_hybrid_table AS my_mergetree_table<br>ENGINE = Hybrid(<br>  cluster(&#39;{cluster}&#39;, my_mergetree_table),<br>  date &gt;= watermark_date,<br>  my_iceberg_table,<br>  date &lt; watermark_date<br>)</pre><p>Normally there are two segments for hot and cold storage respectively, but there could be more for complex use cases.</p><p>If a user queries the above Hybrid engine table, ClickHouse will send queries to both MergeTree and Iceberg tables, adding a watermark condition. It also does a trivial expression analysis, and if a query contains a WHERE clause on ‘date’ that is fully in one segment, other segments will be ignored.</p><p>Note that Hybrid tables already work with multi-shard ClickHouse clusters. Hybrid table engine requires the first argument to be a distributed table, so we wrapped the MergeTree table in a cluster() table function.</p><p>The swarm extension is trivial as well. We just add the object_storage_cluster setting and that’s it.</p><p>There is one problem that has not been fully solved yet: data type compatibility for partial aggregation. As we discussed in the <em>SQL View</em> chapter above, data types in MergeTree and Iceberg have to be different sometimes. Say, MergeTree has a UInt64 column, and the corresponding Iceberg column is extended to Int128. If we try to query it with a uniq() aggregate function, for example, aggregate states between two storage types will be incompatible. In order to fix this, an extra type conversion needs to be applied automatically when sending queries to storage tables. This will be available shortly.</p><p>The Hybrid table engine opens up a lot of new capabilities for ClickHouse users. In addition to implementing tiered hybrid storage model, we can think of the following:</p><ul><li>Easier resharding: One can define a hybrid table with old and new (extended) cluster, re-shard in the background day by day and move watermark once data is resharded</li><li>Canary deployments: One may route part of the traffic to a different cluster or sub-cluster</li><li>Cache layer: One can load some data from Iceberg or data lake back to MergeTree for faster access</li></ul><h3>A New ClickHouse Architecture Is Coming</h3><p>When designing the Hybrid engine we had a specific use case in mind. This use case is shared by many of our users who ingest a lot of data into ClickHouse but want to store it in an efficient way.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*A-Ok1mQdZYAXMqrt.png" /></figure><p>With the addition of the Hybrid table engine almost all pieces are in place. Only two features remain, but they are important:</p><ul><li>Transactional behaviour when moving parts from MergeTree to Iceberg requires more work and much more testing. Both ClickHouse and Iceberg are transactional systems, so an external mediator is required in order to keep them in sync.</li><li>There is a similar challenge to manage watermarks properly, especially on a cluster with multiple shards. We are currently working on addressing those, so this architecture will go live very soon.</li></ul><p>Important note: these features, including Hybrid tables, are not available in upstream ClickHouse builds.</p><p>The preview version of the Hybrid table engine is already available in the <a href="https://docs.altinity.com/releasenotes/altinity-antalya-release-notes/25.8">Altinity Antalya 25.8</a> version of ClickHouse. We plan a series of improvements in the minor releases to make it fully production ready.</p><h3>Conclusion</h3><p>In this article, we introduced a fundamental new feature of the Project Antalya version of ClickHouse — Hybrid table engine. Hybrid tables extend ClickHouse capabilities to seamlessly query data from multiple storage models together — native MergeTree and Iceberg. This is a big step towards the Project Antalya reference architecture that would allow users to extend their <a href="https://altinity.com/project-antalya-real-time-data-lakes/">ClickHouse with data lake capabilities</a> without compromising ClickHouse’s outstanding speed and performance.</p><p>Stay tuned, the future is here! And it’s distributed!</p><p><em>Originally published at </em><a href="https://altinity.com/blog/introducing-hybrid-tables-transparent-query-on-clickhouse-mergetree-and-iceberg-data"><em>https://altinity.com</em></a><em> on November 7, 2025.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=56ca25d605d2" width="1" height="1" alt=""><hr><p><a href="https://medium.com/altinity/introducing-hybrid-tables-transparent-query-on-clickhouse-mergetree-and-iceberg-data-56ca25d605d2">Introducing Hybrid Tables: Transparent Query on ClickHouse® MergeTree and Iceberg Data</a> was originally published in <a href="https://medium.com/altinity">Altinity</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Project Antalya Progress — ALTER TABLE EXPORT and Other Cool Features]]></title>
            <link>https://medium.com/altinity/project-antalya-progress-alter-table-export-and-other-cool-features-3d3ea01f6e33?source=rss----fc2a2d760d0f---4</link>
            <guid isPermaLink="false">https://medium.com/p/3d3ea01f6e33</guid>
            <category><![CDATA[aws-s3]]></category>
            <category><![CDATA[data-lake]]></category>
            <category><![CDATA[data-lakehouse]]></category>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[apache-iceberg]]></category>
            <dc:creator><![CDATA[AltinityDB]]></dc:creator>
            <pubDate>Tue, 24 Mar 2026 13:31:01 GMT</pubDate>
            <atom:updated>2026-03-24T13:31:01.461Z</atom:updated>
            <content:encoded><![CDATA[<h3>Project Antalya Progress — ALTER TABLE EXPORT and Other Cool Features</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*sHmefFaT-0YTjIl8.png" /></figure><blockquote><em>Aurora Borealis bent</em><br><em>Her arch around the skies,</em><br><em>And up the wondering billows gazed</em><br><em>With phosphorescent eyes</em></blockquote><blockquote>-Lydia Sigourney — Icebergs</blockquote><p>Last month we released a long-awaited update to Altinity Antalya based on ClickHouse® 25.6. The new release is an important step in the Project Antalya <a href="https://github.com/Altinity/ClickHouse/issues/804">roadmap</a> that delivers better integration with data lakes and improved performance of queries over Iceberg data. Full release notes are available <a href="https://docs.altinity.com/releasenotes/altinity-antalya-release-notes/25.6">here</a>. Let us highlight some of the important features that are now available for the users.</p><h3>MergeTree Part Export to S3</h3><p>The new SQL command is an extension of ALTER TABLE:</p><pre>ALTER TABLE &lt;MergeTree table&gt; EXPORT PART &lt;part_name&gt; TO TABLE &lt;S3 table&gt;</pre><p>The export is asynchronous, similar to mutations. It preserves MergeTree ordering and minimizes resources required for the export. As such it resembles ClickHouse moves between volumes, but the original data is not deleted. In order to track the progress, there is a new system.exports table.</p><p>Let’s try exporting the popular Star Schema Benchmark dataset from ClickHouse to Parquet using this new feature. We will later use it to run some performance tests.</p><p>Star Schema Benchmark, or SSB for short, first appeared in 2007 as a modification of the TPC-H benchmark. Vadim Tkachenko from Percona <a href="https://www.percona.com/blog/clickhouse-general-analytical-workload-based-star-schema-benchmark">used it in 2017</a> in order to run one of the first ever independent benchmarks of ClickHouse. Later on we modified it into the denormalized version, and it became our standard benchmark to compare performance of cloud VM types. See <a href="https://altinity.com/blog/ultra-fast-aws-graviton-instances-in-altinity-cloud">this</a> or <a href="https://altinity.com/blog/the-tournament-of-aws-cpus-in-altinity-cloud">this</a>, for example.</p><p>The original approach assumed data generation, but we have stored the dataset in S3 while back. In order to load the dataset into ClickHouse, we can use this simple command:</p><pre>CREATE TABLE ssb.lineorder_wide<br>ENGINE = MergeTree<br>PARTITION BY LO_ORDERMONTH<br>PRIMARY KEY (S_REGION, C_REGION, P_MFGR, S_NATION, C_NATION, P_CATEGORY)<br>ORDER BY    (S_REGION, C_REGION, P_MFGR, S_NATION, C_NATION, P_CATEGORY, LO_CUSTKEY, LO_SUPPKEY)<br>AS<br>SELECT *, toYYYYMM(LO_ORDERDATE) as LO_ORDERMONTH FROM<br>s3(&#39;s3://altinity-clickhouse-data/ssb/data/lineorder_wide_bin/*.bin.zstd&#39;, NOSIGN, &#39;Native&#39;)<br>LIMIT 10<br>SETTINGS max_threads=4, max_insert_threads=4, input_format_parallel_parsing=0;</pre><p>The table contains 600 million rows and takes 73GB of storage. Note, that toYYYYMM(LO_ORDERDATE) is materialized as a separate column. This is a limitation of export functionality that will be fixed later.</p><p>Once data is loaded, we also run OPTIMIZE FINAL in order to reduce the number of parts.</p><p>We are ready to export parts into Iceberg. Since we are testing it in Altinity.Cloud, we have a default catalog available and can create an Iceberg database like this:</p><pre>CREATE DATABASE ice<br>ENGINE = DataLakeCatalog(&#39;http://ice-rest-catalog:5000&#39;)<br>SETTINGS<br>catalog_type = &#39;rest&#39;,<br>warehouse = &#39;s3://altialya-2fv4arm7-iceberg&#39;,<br>auth_header = &#39;[HIDDEN]&#39;</pre><p>The export can be done in the following steps:</p><h4>Step 1: Create Destination S3 Table</h4><p>Antalya can not export directly to Iceberg yet, so we have to export it to an S3 table first:</p><pre>CREATE TABLE ssb.lineorder_wide_export as ssb.lineorder_wide<br>ENGINE = S3(<br>  &#39;s3://altialya-2fv4arm7-iceberg/ssb/lineorder_wide/external_data&#39;,<br>  format = &#39;Parquet&#39;,<br>  partition_strategy = &#39;hive&#39;<br>)<br>PARTITION BY LO_ORDERMONTH</pre><p>Note that we use the S3 bucket of the Iceberg warehouse catalog directly. In Altinity.Cloud, the access is granted for ClickHouse process automatically, so no extra credentials are needed. Also for convenience we use a data path that matches the table location that we are going to export.</p><p>It is important that partitioning should be exactly the same for MergeTree and <a href="https://altinity.com/blog/real-time-queries-on-aws-s3-table-buckets-in-clickhouse">S3 tables</a>. This is why we had to extract the LO_ORDERMONTH column. The S3 engine table does not support partitioning expressions for Hive tables yet. It is going to be fixed in future releases.</p><h4>Step 2: Generate EXPORT PART Commands</h4><p>It looks ugly, but until there are no EXPORT TABLE or EXPORT PARTITION commands, we will have to generate export commands. It is not that hard:</p><pre>SELECT &#39;ALTER TABLE ssb.lineorder_wide EXPORT PART \&#39;&#39; || name || &#39;\&#39; TO TABLE ssb.lineorder_wide_export settings allow_experimental_export_merge_tree_part=1;&#39; <br>FROM system.parts <br>WHERE active and (database, table) in (&#39;ssb&#39;,&#39;lineorder_wide&#39;)<br>FORMAT TSVRaw</pre><p>It will produce EXPORT PART statements for every part in the table:</p><pre>ALTER TABLE ssb.lineorder_wide EXPORT PART &#39;199201_1_81_2&#39; TO TABLE ssb.lineorder_wide_export settings allow_experimental_export_merge_tree_part=1;</pre><p>We are finally ready to run those commands and monitor progress in the system.exports table. Here is how a typical row looks like:</p><pre>source_database:               ssb<br>source_table:                  lineorder_wide<br>destination_database:          ssb<br>destination_table:             lineorder_wide_export<br>create_time:                   2025-10-15 13:34:57<br>part_name:                     199209_152_242_2<br>destination_file_path:         ssb/lineorder_wide/external_data/LO_ORDERMONTH=199209/199209_152_242_2_75F9C52EB98248D1DE79F824B4289EA7.parquet<br>elapsed:                       12.728964259<br>rows_read:                     4530176 -- 4.53 million<br>total_rows_to_read:            7470835 -- 7.47 million<br>total_size_bytes_compressed:   915958664 -- 915.96 million<br>total_size_bytes_uncompressed: 1292033922 -- 1.29 billion<br>bytes_read_uncompressed:       904616045 -- 904.62 million<br>memory_usage:                  660317081 -- 660.32 million<br>peak_memory_usage:             826682461 -- 826.68 million</pre><p>By default, it is using 8 parallel threads, and it takes about 5 minutes to export 600M rows into S3 bucket on a 8vCPU VM. Now we are ready for the final step.</p><h4>Step 3: Create Table With <a href="https://github.com/Altinity/ice">ice</a></h4><p>Since the data is already inside the warehouse location, we only need to register files without copy using <a href="https://github.com/Altinity/ice">ice</a>.</p><pre>ice insert ssb.lineorder_wide -p \<br>&#39;s3://altialya-2fv4arm7-iceberg/ssb/lineorder_wide/external_data/*&#39; \<br>--no-copy --thread-count=10 \<br> --partition=&#39;[{&quot;column&quot;:&quot;LO_ORDERMONTH&quot;}]&#39;</pre><p>It takes a few seconds, and we can see the table in ClickHouse! It is even smaller than the MergeTree source, taking only 56GB — thanks to more aggressive Parquet default compression.</p><pre>The typical query runs in half a second from Iceberg:<br><br>SELECT sum(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue<br>FROM ice.&quot;ssb.lineorder_wide&quot;<br>WHERE toYear(LO_ORDERDATE) = 1993 <br>  AND LO_DISCOUNT &gt;= 1 AND LO_DISCOUNT &lt;= 3 AND LO_QUANTITY &lt; 25</pre><p>We are going to discuss benchmarking in a separate article. Now let’s look into other new features.</p><h3>Iceberg Improvements</h3><p>We mentioned in the previous chapter that the Iceberg table takes 56GB. How did we know that? Of course from system.tables! This is yet another improvement of Altinity Antalya — now a user can see more useful information about Iceberg in system.tables.</p><p>So if we run:</p><pre>SELECT * FROM system.tables WHERE database = &#39;ice&#39; and table = &#39;ssb.lineorder_wide&#39;</pre><p>We will see row counts, size and even partition and sorting keys (the latter one is empty since we did not define it).</p><pre>Row 1:<br>──────<br>database:                         ice<br>name:                             ssb.lineorder_wide<br>engine:                           IcebergS3<br>...<br>partition_key:                    LO_ORDERMONTH<br>sorting_key:<br>...<br>total_rows:                       600038145 -- 600.04 million<br>total_bytes:                      56616254533 -- 56.62 billion<br>...</pre><p>Let’s now try this one:</p><pre>SELECT LO_ORDERMONTH, count() FROM ice.&quot;ssb.lineorder_wide&quot;<br>GROUP BY 1 ORDER BY 1<br><br>    ┌─LO_ORDERMONTH─┬─count()─┐<br> 1. │        199201 │ 7733051 │<br> 2. │        199202 │ 7233211 │<br> 3. │        199203 │ 7721819 │<br> 4. │        199204 │ 7476931 │<br> 5. │        199205 │ 7731519 │<br>...</pre><p>It runs in less than 0.3s! Thanks to count() optimization based on Iceberg metadata. It is controlled by the allow_experimental_iceberg_read_optimization setting that is enabled by default in Antalya builds. If you disable it, the query above would take a long eight seconds!</p><h3>What’s Next?</h3><p>We have shown some of the new features of Altinity Antalya. As you can see, this is a work in progress, and functionality seems incomplete. So in 25.8 we are going to release a few more important updates:</p><ul><li>EXPORT PARTITION - Allows users to export full table partition without generating part specific SQL statements</li><li>Partition expressions in export destination tables, so we could use toYYYYMM(LO_ORDERDATE) without the need for an extra column</li><li>Even faster count() queries on Iceberg tables</li></ul><p>But hold on. Why do we need to keep two versions of the data — one in MergeTree and another one in Iceberg? One of the main goals of Project Antalya is to have hot data stored in <a href="https://altinity.com/blog/clickhouse-mergetree-on-s3-intro-and-architecture">MergeTree</a>, and cold in Iceberg. The EXPORT is an important step. The ultimate goal is to query data consistently from both sources. This is already coming. Get ready for something really cool!</p><p><em>Originally published at </em><a href="https://altinity.com/blog/project-antalya-progress-alter-table-export-and-other-cool-features"><em>https://altinity.com</em></a><em> on October 22, 2025.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3d3ea01f6e33" width="1" height="1" alt=""><hr><p><a href="https://medium.com/altinity/project-antalya-progress-alter-table-export-and-other-cool-features-3d3ea01f6e33">Project Antalya Progress — ALTER TABLE EXPORT and Other Cool Features</a> was originally published in <a href="https://medium.com/altinity">Altinity</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Real-time Queries on AWS S3 Table Buckets in ClickHouse®]]></title>
            <link>https://medium.com/altinity/real-time-queries-on-aws-s3-table-buckets-in-clickhouse-77e8f2f29a6b?source=rss----fc2a2d760d0f---4</link>
            <guid isPermaLink="false">https://medium.com/p/77e8f2f29a6b</guid>
            <category><![CDATA[aws-s3]]></category>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[apache-iceberg]]></category>
            <category><![CDATA[aws]]></category>
            <dc:creator><![CDATA[AltinityDB]]></dc:creator>
            <pubDate>Tue, 17 Mar 2026 12:31:01 GMT</pubDate>
            <atom:updated>2026-03-17T12:31:01.354Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*KZ-rgLkTg-yZOdNM.png" /></figure><blockquote><em>The gifts of earth are brought and prepared, set on the table.<br>So it has been since creation, and it will go on<br></em>- Joy Hairo. Perhaps the World Ends Here</blockquote><p><a href="https://docs.aws.amazon.com/AmazonS3/latest/userguide/s3-tables-buckets.html">AWS S3 table buckets</a> are a unique new product from AWS introduced at the end of 2024. In a nutshell, table buckets organize files stored on S3 into tables. Supported file formats include Parquet. There are table management features on top that make S3 look like analytic database storage. Most importantly, S3 table buckets expose tables in Apache Iceberg format, which means that ClickHouse can query them easily.</p><p>Since Iceberg is one of the foundational features of the <a href="https://altinity.com/project-antalya-real-time-data-lakes">Altinity Antalya project</a>, we could hardly overlook this important advance in S3 technology. We built an open source integration from ClickHouse to S3 table buckets that you can access easily in Altinity.Cloud. Let’s try it out!</p><h3>The Hidden Underside of the Iceberg</h3><p>Before showing how to use S3 Table Buckets, let me remind you of one of the biggest problems with Apache Iceberg today: compaction.</p><p>Imagine a service that collects <a href="https://altinity.com/blog/altinity-loves-every-observability-vendor-especially-the-ones-that-use-clickhouse">observability data</a> and pushes a batch of rows every 10 seconds. In 24 hours it will produce 6 * 60 * 24 = 8640 batches. If there are multiple services, the number will multiply accordingly. Inserting such data into a database is not a problem. Databases are designed to process inserts at a high rate and volume. However, this is not true for S3 buckets (not tables!) nor for Iceberg.</p><p>In Iceberg, every batch is one or more new Parquet files. As the number of Parquet files increases, the performance of accessing those files for queries will degrade dramatically. Even the metadata access becomes slow! In order to keep the query performance good as new data arrives, Parquet files need to be compacted. It is similar to the merge process in ClickHouse that compacts new parts produced by inserts into bigger ones, but applied to Parquet files.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/538/0*UR_JRpa5aexUOBpv" /><figcaption><em>Picture from </em><a href="https://aws.amazon.com/ru/blogs/storage/how-amazon-s3-tables-use-compaction-to-improve-query-performance-by-up-to-3-times"><em>AWS blog</em></a></figcaption></figure><p>Apache Iceberg makes it <em>possible </em>to solve the compaction problem through features like <a href="https://iceberg.apache.org/spec/#overview">snapshots</a> and <a href="https://iceberg.apache.org/spec/#optimistic-concurrency">optimistic concurrency</a>. It leaves the actual work to external tools. Open source implementations most commonly use Apache Spark for this purpose, but many users find it heavyweight and operationally complex. Topics like automation, upgrade, monitoring, and the like are user problems.</p><p>This is one of the main reasons why users were so excited about S3 Table Buckets. AWS adds data compaction as well as other maintenance features like old snapshot removal. That makes it a ready-to-use service without a need for extra management tools.</p><h3>Connecting ClickHouse to S3 Table Buckets Using ice-rest-catalog</h3><p>S3 Table Buckets, as well as Iceberg, start with a catalog. A catalog exposes information about tables and files stored in S3 table buckets so other systems including ClickHouse can read those. ClickHouse can connect to the most common catalog types including AWS Glue and Iceberg REST. We have found that Glue requires quite a lot of management overhead, so let’s take a look at <a href="https://docs.aws.amazon.com/AmazonS3/latest/userguide/s3-tables-integrating-open-source.html">REST</a>.</p><p>Unfortunately, ClickHouse cannot connect directly to S3 table bucket REST catalogs, due to use of <a href="https://github.com/Altinity/ClickHouse/issues/777">specific authentication parameters that ClickHouse does not support yet</a>. There are also <a href="https://docs.aws.amazon.com/AmazonS3/latest/userguide/s3-tables-integrating-open-source.html">limitations</a> in the REST implementation itself. So we decided to take a different route.</p><p>Altinity.Cloud uses an <a href="https://github.com/Altinity/ice">open source ice-rest-catalog</a> that we developed to simplify Iceberg usage in Kubernetes. It allowed us to add a proxy for S3 table buckets without changing ClickHouse. The proxy connects to S3 Table Buckets using AWS Java API, which does not have any REST limitations. Problem solved!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*cjF1E94Yim2vw6hc" /></figure><p>If you want to try it out by yourself, check out this very simple example that uses <a href="https://github.com/Altinity/ice/tree/master/examples/s3tables">AWS CLI and ice</a>. In Altinity.Cloud we can deploy a catalog for a new or an existing S3 Table bucket. Please reach out to our support to configure it for your Altinity.Cloud environment.</p><p>Once the catalog is configured, ClickHouse can connect to it using the DatalakeCatalog database engine. Users may use the Altinity.Cloud Configure Data Lake Catalogs Wizard for that, or look up credentials from the environment dashboard page. At the end the CREATE DATABASE statement will look as shown below.</p><pre>CREATE DATABASE s3tables1<br>ENGINE = DataLakeCatalog(&#39;http://ice-rest-catalog-s3table1:5000&#39;)<br>SETTINGS catalog_type = &#39;rest&#39;,<br>        auth_header = &#39;[HIDDEN]&#39;,`<br>        warehouse = &#39;arn:aws:s3tables:us-east-1:313342380333:bucket/[HIDDEN]&#39;</pre><p>The database is empty. So we need to load it with some data. We will use <a href="https://github.com/Altinity/ice">ice</a> for that.</p><h3>Loading Data Into S3 Table Buckets Using Ice</h3><p>Ice has a downloadable binary; you can find install instructions on the <a href="https://github.com/Altinity/ice/releases">Ice project releases</a> page. Once downloaded, it can connect to any Iceberg REST catalog. URI for external connections and credentials to Altinity.Cloud catalog can be extracted from Altinity.Cloud and used in .ice.yaml configuration files as follows:</p><pre>.ice.yaml <br>—--------<br>uri: https://iceberg-catalog-s3table1.altinity-antalya.altinity.cloud<br>bearerToken: [YOUR_TOKEN HERE]</pre><p>Now we can run ice and confirm it can connect to catalog:</p><pre>#:/ ice check OK</pre><p>We are ready to load data. For the initial test, let’s use the BTC public AWS dataset. It can be ice’d with a one liner:</p><pre>ice insert btc.transactions -p --s3-region=us-east-2 --s3-no-sign-request s3://aws-public-blockchain/v1.0/btc/transactions/date=2025-01-*/*.parquet</pre><p>This will load 31 files for January 2025. If you want to load more data, adjust the date pattern accordingly.</p><h3>Querying S3 Table Buckets From ClickHouse</h3><p>Once the catalog is connected, query on AWS S3 tables is no different from any other query on ClickHouse tables:</p><pre>SELECT date, sum(output_count)<br>FROM s3tables1.&quot;btc.transactions&quot;<br>WHERE date between &#39;2025-01-01&#39; and &#39;2025-01-31&#39;<br>GROUP BY date ORDER BY date</pre><p>Moreover, if you have a swarm cluster available, it will nicely work as well to provide unlimited performance scalability:</p><pre>SELECT date, sum(output_count)<br>FROM s3tables1.&quot;btc.transactions&quot;<br>WHERE date between &#39;2025-01-01&#39; and &#39;2025-01-31&#39;<br>GROUP BY date ORDER BY date<br>SETTINGS object_storage_cluster=&#39;my-swarm&#39;</pre><p>Read more about <a href="https://altinity.com/blog/the-future-has-arrived-parquet-on-iceberg-finally-outperforms-mergetree">Antalya compute swarms</a> in our blog post.</p><h3>Setting Up a Real-Time Pipeline</h3><p>The example above is not very exciting since it does not allow users to benefit from the data management features of S3 Table Buckets, most notably compaction. To see compaction in action, let’s set up a real-time pipeline that constantly adds new data.</p><p>In order to set up a real-time ingestion pipeline we configured Amazon FireHose to stream CloudWatch logs into S3 tables buckets. The details of this setup are covered in <a href="https://aws.amazon.com/ru/blogs/big-data/stream-real-time-data-into-apache-iceberg-tables-in-amazon-s3-using-amazon-data-firehose">AWS blog</a> and <a href="https://docs.aws.amazon.com/firehose/latest/dev/writing-with-cloudwatch-logs.html">documentation</a>. Here is the high-level idea:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/601/0*El68TjSBzeQKK15r.png" /></figure><p>At the end, we’ve got CloudWatch metrics streamed to S3 Table Buckets under Iceberg. Let’s check it out from the ClickHouse side. The catalog with S3 Table Buckets is named ice_s3t:</p><p>Here is the table structure:</p><pre>DESCRIBE ice_s3t.&quot;cloudwatch.metrics&quot;<br><br>metric_stream_name Nullable(String)<br>account_id Nullable(String)<br>region Nullable(String)<br>namespace Nullable(String)<br>metric_name Nullable(String)<br>timestamp Nullable(Int64)<br>dimensions Map(String, Nullable(String))<br>value Tuple(<br>    min Nullable(Float64),<br>    max Nullable(Float64),<br>    count Nullable(Float64),<br>    sum Nullable(Float64))<br>unit Nullable(String)</pre><p>You can see that the timestamp is Int64 — Iceberg does not support DateTime64 datatype yet, so we had to store it as an integer. (Good news: it’s a feature in <a href="https://iceberg.apache.org/spec/#version-2-row-level-deletes">Iceberg V3</a>, so a fix is on the way.) Meanwhile, we can convert values to Date or DateTime at query time, like this:</p><pre>SELECT toDate(fromUnixTimestamp64Milli(timestamp)), count()<br>FROM ice_s3t.&quot;cloudwatch.metrics&quot;<br>GROUP BY 1 ORDER BY 1<br><br>   ┌─toDate(fromU⋯timestamp))─┬─count()─┐<br>1. │               2025-08-19 │    8519 │<br>2. │               2025-08-20 │   34560 │<br>...<br>8. │               2025-08-26 │   23003 │<br>9. │               2025-08-27 │   10421 │<br>   └──────────────────────────┴─────────┘</pre><p>With this data, we can quickly see what’s going on inside the cloud account, for example check the top failed resources:</p><pre>SELECT dimensions, metric_name, sum(value.count)<br>FROM ice_s3t.&quot;cloudwatch.metrics&quot;<br>WHERE metric_name like &#39;StatusCheckFailed%&#39;<br>GROUP BY 1,2 order by 3 desc<br>LIMIT 1<br>FORMAT Vertical<br><br>Row 1:<br>──────<br>dimensions:       {&#39;InstanceId&#39;:&#39;i-0bf059f5cd0548854&#39;}<br>metric_name:      StatusCheckFailed_AttachedEBS<br>sum(value.count): 11117</pre><p>But what about compaction? Is it really happening? We can check it by observing number of unique files for every day</p><pre>SELECT toDate(fromUnixTimestamp64Milli(timestamp)), count(), uniq(_file)<br>FROM ice_s3t.&quot;cloudwatch.metrics&quot;<br>GROUP BY 1 ORDER BY 1<br><br>   ┌─toDate(fromU⋯timestamp))─┬─count()─┬─uniq(_file)─┐<br>1. │               2025-08-19 │    8519 │           1 │<br>2. │               2025-08-20 │   34560 │           1 │<br>...<br>8. │               2025-08-26 │   23003 │           1 │<br>9. │               2025-08-27 │   10482 │          29 │<br>   └──────────────────────────┴─────────┴─────────────┘</pre><p>As you can see, all previous days are pretty much compacted. If we look into today:</p><pre>SELECT _file, arraySort(groupUniqArray(toHour(fromUnixTimestamp64Milli(timestamp))))<br>FROM ice_s3t.&quot;cloudwatch.metrics&quot;<br>WHERE toDate(fromUnixTimestamp64Milli(timestamp)) = today()<br>GROUP BY 1 ORDER BY 2<br><br>   ┌─_file───────────────────────────────────────────────────────────┬─arraySort(group⋯(timestamp))))─┐<br> 1. │ 00000-0-848d2dbe-e7b2-4661-afcd-4973893edf64-0-00001.parquet    │ [0,1,2,3,4,5,6,7,8,9,10,11,12] │<br> 2. │ 00006-159518-0eeaf657-30b3-4a31-91da-1ff1afa2f0a2-00001.parquet │ [12]                           │<br> 3. │ 00008-159627-a0a3ed77-5aee-412e-8dce-c3cece1fd5d6-00001.parquet │ [12]                           │<br> 4. │ 00004-159353-fa3a2d6c-937b-42d7-a052-b263fef3d8b0-00001.parquet │ [12]                           │<br> 5. │ 00000-159238-68f9be81-432b-4dc8-b8a7-bdc4ee6619e8-00001.parquet │ [12]                           │<br> 6. │ 00000-159573-e32986f4-9519-44c3-bd4e-71f92b47092a-00001.parquet │ [12]                           │<br>...</pre><p>We will see that there is one ‘merged’ file and a number of new files for the current hour. Voilà!</p><h3>Final Words</h3><p>The new S3 Table Buckets service is a testament to the power of Iceberg that also makes it much easier to use. Built-in compaction and catalog management are game changers. Combined with ClickHouse query performance, it allows users to build real-time data pipelines using open data formats like Parquet with minimal effort. This level of convenience is yet another step in the accelerating adoption of Apache Iceberg.</p><p>S3 Table Buckets are an AWS-only service. Start an <a href="https://altinity.com/managed-clickhouse/">Altinity.Cloud</a> trial to try them out today. If you are an existing Altinity.Cloud user, <a href="https://docs.altinity.com/support/">contact Altinity support</a> to get data lake support enabled. What if you run your data lakes on GCP, Azure or on-prem? We have a solution coming for other clouds as well, so stay tuned!</p><p><em>Originally published at </em><a href="https://altinity.com/blog/real-time-queries-on-aws-s3-table-buckets-in-clickhouse"><em>https://altinity.com</em></a><em> on August 28, 2025.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=77e8f2f29a6b" width="1" height="1" alt=""><hr><p><a href="https://medium.com/altinity/real-time-queries-on-aws-s3-table-buckets-in-clickhouse-77e8f2f29a6b">Real-time Queries on AWS S3 Table Buckets in ClickHouse®</a> was originally published in <a href="https://medium.com/altinity">Altinity</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Using Async Inserts for Peak Data Loading Rates in ClickHouse®]]></title>
            <link>https://medium.com/altinity/using-async-inserts-for-peak-data-loading-rates-in-clickhouse-6d907668d3dc?source=rss----fc2a2d760d0f---4</link>
            <guid isPermaLink="false">https://medium.com/p/6d907668d3dc</guid>
            <category><![CDATA[async-inserts]]></category>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[loading-data]]></category>
            <dc:creator><![CDATA[AltinityDB]]></dc:creator>
            <pubDate>Mon, 09 Mar 2026 14:31:01 GMT</pubDate>
            <atom:updated>2026-03-09T14:31:00.982Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*KOnVeA6i4xv1N5Ni.png" /></figure><h3>The Challenge: Too Many Small Inserts</h3><p>Picture this scenario: You’re running a monitoring platform that collects metrics from thousands of IoT devices. Each device sends a small JSON payload every few seconds-temperature readings, battery levels, network latency. You’ve chosen <a href="https://altinity.com/clickhouse-database/">ClickHouse for its incredible analytical capabilities</a>, but now you’re facing a problem that keeps many engineers up at night: how to efficiently handle thousands of tiny inserts per second.</p><p>If you’ve worked with ClickHouse before, you know the golden rule: <strong><em>batch your inserts</em></strong>. ClickHouse performs best when you insert data in larger chunks-ideally millions of rows at once. But what happens when your architecture doesn’t allow for client-side batching? What if you have legacy systems, third-party integrations, or simply too many data sources to coordinate?</p><p>This is where async inserts come to the rescue.</p><h3>Understanding <a href="https://kb.altinity.com/altinity-kb-queries-and-syntax/async-inserts/">Async Inserts</a>: Server-Side Batching Magic</h3><p>Async inserts are ClickHouse’s answer to the small insert problem. Instead of immediately writing each tiny insert to disk (which would create performance nightmares from merges), ClickHouse collects these inserts in memory and intelligently batches them together before writing.</p><p>When you enable async inserts with `SET async_insert = 1`, your inserts go into a buffer. ClickHouse then decides when to flush this buffer based on three conditions:</p><ol><li><strong>Size threshold</strong>: The buffer reaches a certain size (default: 1MB)</li><li><strong>Time threshold</strong>: A timer expires (default: 1 second)</li><li><strong>Query count</strong>: Too many queries are waiting (default: 100)</li></ol><p>Whichever condition is met first triggers the flush, ensuring your data doesn’t sit in memory indefinitely while still achieving efficient batching.</p><h3>When Async Inserts Shine (And When They Don’t)</h3><h4>Perfect Use Cases</h4><p>Async inserts are your best friend when:</p><p><strong>You have hundreds of independent data sources</strong>: Imagine a fleet management system where each vehicle sends GPS coordinates every few seconds. Coordinating client-side batching across thousands of vehicles would be a nightmare. With async inserts, each vehicle can send its data independently, and ClickHouse handles the batching.</p><p><strong>You’re dealing with legacy systems</strong>: Perhaps you’re migrating from a traditional database where applications expect to INSERT row by row. Rewriting all your applications for batching might not be feasible. Async inserts let you keep your existing code while getting better ClickHouse performance.</p><p><strong>Real-time data with relaxed latency requirements</strong>: If your dashboard updates every minute, waiting a second for data to be flushed is perfectly acceptable. The slight delay is a small price for the massive performance improvement.</p><h4>When to Think Twice</h4><p>However, async inserts aren’t always the answer:</p><p><strong>You need immediate consistency</strong>: If your application needs to read data immediately after inserting it, async inserts add complexity. You’d need to use `wait_for_async_insert = 1` (which we strongly recommend anyway), but this adds latency to your insert operations.</p><p><strong>You’re already batching effectively</strong>: If you have a proper <a href="https://altinity.com/blog/kafka-to-clickhouse-made-simple-with-glassflow-and-altinity-cloud">data pipeline with Kafka</a>, or you’re using tools like Vector or Fluentbit that batch naturally, async inserts add unnecessary complexity. Stick with your client-side batching.</p><p><strong>You’re using INSERT SELECT</strong>: Async inserts don’t work with `INSERT .. SELECT` statements. If your workflow relies heavily on transforming data during insertion, you’ll need alternative approaches.</p><h3>Configuration: Getting It Right the First Time</h3><p>Let’s walk through setting up async inserts properly. The configuration might seem simple, but the details matter enormously.</p><p>By the way, the information in this guide comes from the <a href="https://kb.altinity.com/altinity-kb-queries-and-syntax/async-inserts/">Altinity Knowledgebase for ClickHouse</a> where you can go to get the latest information on this topic or to contribute your own learnings-it’s open source!</p><pre>-- Enable async inserts for your session<br>SET async_insert = 1;<br><br>-- CRITICAL: Always wait for confirmation<br>SET wait_for_async_insert = 1;<br><br>-- Configure buffer behavior<br>SET async_insert_max_data_size = 5000000;    -- 5MB buffer<br><br>SET async_insert_busy_timeout_ms = 2000;      -- 2 second timeout<br><br>SET async_insert_max_query_number = 200;      -- Up to 200 queries</pre><p>The most critical setting here is `wait_for_async_insert = 1`. Without it, you’re in “fire-and-forget” mode, which sounds fast but is actually dangerous. Your application won’t know if inserts fail due to disk issues, table locks, or other problems. You might lose data silently, and debugging becomes a nightmare. Always wait for confirmation-the small latency cost is worth the reliability.</p><h3>A Real-World Example: Building a Metrics Collection System</h3><p>Let’s implement a practical example. Suppose you’re building a system to collect application metrics from multiple services:</p><pre>-- Create a table for metrics<br>CREATE TABLE metrics (<br>    timestamp DateTime,<br>    service String,<br>    metric String,<br>    value Float64,<br>    tags Map(String, String)<br>) ENGINE = MergeTree()<br>ORDER BY (service, metric, timestamp);<br><br>-- Configure optimal async insert settings for this use case<br>SET async_insert = 1;<br>SET wait_for_async_insert = 1;<br>SET async_insert_max_data_size = 10000000;     -- 10MB buffers for metrics<br>SET async_insert_busy_timeout_ms = 5000;       -- 5 second timeout</pre><p>Now, each service can send metrics independently:</p><pre>-- Service A sends CPU metrics<br>INSERT INTO metrics FORMAT JSONEachRow<br>{&quot;timestamp&quot;: &quot;2024-01-15 10:00:00&quot;, &quot;service&quot;: &quot;api-gateway&quot;, &quot;metric&quot;: &quot;cpu_usage&quot;, &quot;value&quot;: 45.2, &quot;tags&quot;: {&quot;host&quot;: &quot;server1&quot;, &quot;dc&quot;: &quot;us-east&quot;}}<br><br>-- Service B sends memory metrics (can execute simultaneously)<br>INSERT INTO metrics FORMAT JSONEachRow<br>{&quot;timestamp&quot;: &quot;2024-01-15 10:00:01&quot;, &quot;service&quot;: &quot;auth-service&quot;, &quot;metric&quot;: &quot;memory_usage&quot;, &quot;value&quot;: 2048.5, &quot;tags&quot;: {&quot;host&quot;: &quot;server2&quot;, &quot;dc&quot;: &quot;us-west&quot;}}</pre><p>Behind the scenes, ClickHouse collects these inserts and writes them together, dramatically reducing the overhead compared to individual inserts.</p><h3>The Adaptive Timeout Revolution (ClickHouse 24.3+)</h3><p>Starting with ClickHouse 24.3, there’s a game-changing feature: <a href="https://github.com/ClickHouse/ClickHouse/pull/58486">adaptive timeouts</a>. Instead of using fixed timeouts, ClickHouse now automatically adjusts flush timing based on server load. When your server is under pressure, it flushes more frequently to prevent memory buildup. When things are calm, it waits longer to create larger, more efficient batches.</p><p>This feature is enabled by default with `async_insert_use_adaptive_busy_timeout = 1`. While powerful, it can override your manual settings, so you might want to disable it if you’ve carefully tuned your timeouts:</p><pre>-- Disable adaptive timeout for predictable behavior<br>SET async_insert_use_adaptive_busy_timeout = 0;</pre><h3>Monitoring: Keeping an Eye on Your Async Inserts</h3><p>You can’t optimize what you can’t measure. ClickHouse provides comprehensive metrics for async inserts:</p><pre>-- Check current async insert queue status<br>SELECT <br>    metric,<br>    value,<br>    description<br>FROM system.metrics<br>WHERE metric LIKE &#39;%AsyncInsert%&#39;<br>ORDER BY metric;<br><br>-- Review async insert history<br>SELECT <br>    query_id,<br>    query,<br>    rows,<br>    bytes,<br>    exception,<br>    flush_time_microseconds<br>FROM system.asynchronous_insert_log<br>WHERE event_date = today()<br>ORDER BY event_time DESC<br>LIMIT 10;<br><br>-- Monitor for failures<br>SELECT <br>    count() as failed_inserts,<br>    exception<br>FROM system.asynchronous_insert_log<br>WHERE event_date = today()<br>    AND exception != &#39;&#39;<br>GROUP BY exception<br>ORDER BY failed_inserts DESC;</pre><p>Pay special attention to `AsynchronousInsertQueueBytes` and `PendingAsyncInsert` metrics. If these keep growing, your flushes might not be keeping up with incoming data, and you need to adjust your thresholds.</p><h3>Common Pitfalls and How to Avoid Them</h3><p>Through years of helping teams implement async inserts, we’ve seen some patterns emerge:</p><p><strong>The Deduplication Trap</strong>: By default, async inserts disable deduplication. If your application might send duplicate data and you rely on <a href="https://kb.altinity.com/altinity-kb-schema-design/insert_deduplication/">ClickHouse’s deduplication</a>, you need to explicitly enable it:</p><pre>SET async_insert_deduplicate = 1;</pre><p>But be careful-this doesn’t work well with materialized views that have their own deduplication enabled.</p><p><strong>The Silent Data Loss</strong>: Never, ever use `wait_for_async_insert = 0` in production unless you fully understand the risks and have explicit monitoring for data loss. We’ve seen teams lose millions of records because they optimized for speed without understanding this setting.</p><p><strong>The Version Mismatch</strong>: If you’re running ClickHouse versions before 23.8, be extra careful. Many critical bugfixes for async inserts landed in 23.8 and later. If you’re on an older version and experiencing issues, upgrading might solve your problems.</p><h3>Making the Decision: A Simple Framework</h3><p>Here’s a simple decision framework for whether to use async inserts:</p><ol><li><strong>Do you have many small inserts from independent sources?</strong> → Consider async inserts</li><li><strong>Can you batch on the client side reasonably?</strong> → Skip async inserts, batch yourself</li><li><strong>Do you need immediate read-after-write consistency?</strong> → Be cautious with async inserts</li><li><strong>Are you on ClickHouse 23.8 or later?</strong> → Green light for async inserts</li><li><strong>Can you accept 1–5 second delays in data availability?</strong> → Async inserts fit well</li></ol><h3>The Bottom Line</h3><p>Async inserts are a powerful tool in your ClickHouse toolkit, but they’re not a silver bullet. They excel at solving the specific problem of many small, independent inserts when client-side batching isn’t practical. When configured correctly-especially with `wait_for_async_insert = 1`-they provide a robust solution that balances performance with reliability.</p><p>Remember: the best insert strategy is still client-side batching when possible. But when life gives you thousands of tiny inserts, async inserts help you make efficient batches. Use them wisely, monitor them closely, and your ClickHouse cluster will thank you with smooth, predictable performance even under challenging insert patterns.</p><p>Whether you’re building a new system or optimizing an existing one, understanding when and how to use async inserts can be the difference between a ClickHouse deployment that struggles and one that sings. The key is knowing when this tool fits your use case and configuring it properly for your specific needs.</p><p><em>Originally published at </em><a href="https://altinity.com/blog/using-async-inserts-for-peak-data-loading-rates-in-clickhouse"><em>https://altinity.com</em></a><em> on August 20, 2025.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6d907668d3dc" width="1" height="1" alt=""><hr><p><a href="https://medium.com/altinity/using-async-inserts-for-peak-data-loading-rates-in-clickhouse-6d907668d3dc">Using Async Inserts for Peak Data Loading Rates in ClickHouse®</a> was originally published in <a href="https://medium.com/altinity">Altinity</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Altinity.Cloud Launches Managed Iceberg with Antalya Compute Swarms]]></title>
            <link>https://medium.com/altinity/altinity-cloud-launches-managed-iceberg-with-antalya-compute-swarms-26a7df1c21c4?source=rss----fc2a2d760d0f---4</link>
            <guid isPermaLink="false">https://medium.com/p/26a7df1c21c4</guid>
            <category><![CDATA[data-lakehouse]]></category>
            <category><![CDATA[apache-iceberg]]></category>
            <category><![CDATA[data-lake]]></category>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[open-source]]></category>
            <dc:creator><![CDATA[AltinityDB]]></dc:creator>
            <pubDate>Tue, 03 Mar 2026 13:01:02 GMT</pubDate>
            <atom:updated>2026-03-03T13:01:01.723Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*moVmKd6yBZAWiC-e.png" /></figure><p>The era of real-time data lakes has arrived. Today we’re proud to announce two new Altinity.Cloud features that enable users to combine sub-second ClickHouse® performance with the infinite scalability of <a href="https://iceberg.apache.org/">Apache Iceberg</a> storage. Managed Iceberg catalogs enable convenient access to Iceberg tables on cheap, infinitely scalable object storage. Compute swarms dramatically speed up queries on Parquet data stored in Iceberg tables, Apache Hive, and plain S3 files.</p><p>The new features are just the first of many emerging from <a href="https://docs.altinity.com/altinityantalya/">Project Antalya</a>, our project to eliminate the trade-offs between real-time analytic database performance and data lake scalability. Future releases will add AWS S3 table buckets, tiered Parquet storage, and many other features. It’s part of our plan to help customers build analytic systems not just for today but for the next decade.</p><p>Managed Iceberg catalogs and compute swarms are in preview and will move to production shortly. They are limited to AWS environments for now. You can <a href="https://docs.altinity.com/support/">contact Altinity support</a> to enable both features in any environment and get credits to try them out. Once enabled, customers can access capabilities with a couple of clicks in the Altinity.Cloud UI. New users can <a href="https://acm.altinity.cloud/signup?_gl=1*1hq2lc0*_gcl_au*ODM1OTUyNDU0LjE3NTE4MzE1NTY.">launch a trial</a> to get access.</p><h3>Towards Ubiquitous, Real-Time Data Lakes with Cloud Ease-of-Use</h3><p>Data growth is a critical problem for open source ClickHouse users. Storage is now the dominant cost in use cases ranging from financial market analytics to observability. Compute costs have also grown because ClickHouse forces over-provisioning of resources in large clusters to ensure performance for diverse workloads. The demand for AI access adds additional workloads and further exacerbates compute contention. Apache Iceberg theoretically offers a way forward: low-cost, high-volume storage that supports rapid compute scaling and simultaneous access to a single copy of data from many applications. The problem is integrating and managing the parts.</p><p>Altinity.Cloud is opening the door to a new architecture for real-time analytics that combines the performance of ClickHouse with the proven storage management and flexible compute access of Apache Iceberg tables. But we’re doing more than delivering real-time data lakes with cloud management. We also are giving our users the freedom to operate anywhere: from traditional SaaS model operation in the Altinity cloud account to Bring-Your-Own-Cloud models in user cloud accounts to self-managed, on-prem environments. Altinity.Cloud can now bring the power of real-time data lakes to all of them.</p><p>In doing so we’re building on our proven architecture of a centralized cloud management plane connected to decentralized data planes that operate anywhere Kubernetes runs.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*elOM2rjKM76QiUui.png" /></figure><p>This architecture extends open source ClickHouse and leverages ecosystem tools like the Altinity Operator that are already used by tens of thousands of installations. The new additions-Iceberg catalogs and compute swarms — are fully managed services that deploy easily in any existing Altinity.Cloud environment. Like all software we manage in Altinity.Cloud, they are 100% open source with Apache 2.0 licenses. This upholds our guarantee that all building blocks of analytic applications will remain open.</p><h3>Compute Swarms</h3><p>Compute swarms deliver a breakthrough in the economics of compute for analytic systems. They allow you to add stateless ClickHouse compute nodes to scan Parquet data stored in Apache Iceberg, Hive, or plain S3 files. Swarms can scale up or down easily, and, as we showed <a href="https://altinity.com/blog/the-future-has-arrived-parquet-on-iceberg-finally-outperforms-mergetree">in an earlier post</a>, match or even exceed the query performance of ClickHouse MergeTree. Swarms thus enable users to choose storage based on properties other than performance, for example ease of changing data or ability to scale.</p><p>There are two steps to enable compute swarms. First, enable swarm access for the existing ClickHouse cluster using <a href="https://docs.altinity.com/altinitycloud/userguide/cluster-actions/enabling-swarms/">Cluster-&gt;Actions-&gt;Enable Swarms</a>. This step will apply the necessary configuration and restart ClickHouse. Second, launch the swarm cluster itself using the button on the clusters’ dashboard.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/428/0*s_1xti0qKqPHB1Xb.png" /></figure><p>Since compute swarms are stateless, they can also use spot instances to reduce the cost. You can configure spot instances when <a href="https://docs.altinity.com/altinitycloud/administratorguide/working-with-environments/environment-actions/defining-custom-node-types/">defining Node Types</a>.</p><p><strong>Important note:</strong> In order to run swarm queries from your primary ClickHouse cluster, you need to use an <a href="https://docs.altinity.com/releasenotes/altinity-antalya-release-notes/">Altinity Antalya build</a> for ClickHouse. Altinity Antalya builds are fully open source and 100% compatible with corresponding upstream versions. They have additional features for better Iceberg and swarm support. See <a href="https://github.com/Altinity/ClickHouse/blob/antalya/Antalya.md">the feature matrix</a> for more detail. Altinity Antalya builds are <a href="https://altinity.com/blog/why-every-clickhouse-user-should-appreciate-altinity-stable-builds#h-does-altinity-still-support-clickhouse-inc-builds">leading edge builds</a>, which means they prioritize rapid features rather than stability. Please consult with Altinity Support before upgrading production applications.</p><p>Compute swarms transform queries on any Parquet data stored in S3-compatible storage, not just those files stored in Iceberg tables. Consider them in the following cases.</p><ul><li>High-volume analytical workloads where you need both performance and cost efficiency</li><li>Variable demand scenarios that benefit from scaling compute up and down</li><li>Historical data analysis requiring occasional fast access to massive datasets</li><li>Environments where spot instances can reduce costs</li></ul><h3>Managed Iceberg Catalogs</h3><p>Catalogs hold the description of tables in open formats like Apache Iceberg. They are a key part of the procedures that applications use to locate, read, and change table data. Managed Iceberg catalogs in Altinity.Cloud provide an easy way to set up a catalog for Iceberg tables, which in turn enables you to set up real-time data lakes in any location.</p><p>It’s easy to enable an Iceberg catalog for any Altinity.Cloud environment running on AWS. Just navigate to the environment dashboard page and click on the <a href="https://docs.altinity.com/altinitycloud/administratorguide/working-with-environments/environment-actions/enabling-an-iceberg-catalog/">Iceberg Catalog link</a>. Once enabled, you will see access credentials that can be used by external clients.</p><p>Apache Iceberg shares many characteristics with ClickHouse MergeTree. Iceberg is fully open and vendor neutral. It also includes a built-in catalog specification, known as an Iceberg REST catalog. ClickHouse can connect to the catalog using a specialized form of the CREATE DATABASE command. At that point the tables managed by the catalog appear as a normal database and are available for SQL queries. This works quite well because of the similarity between Iceberg and MergeTree tables.</p><p>Unlike MergeTree tables, however, the Iceberg catalog can be used both by ClickHouse and external systems. This is one of the main benefits of using Iceberg in the first place and allows a wide spectrum of integrations, where different applications share the data. In order to use the catalog from ClickHouse in Altinity.Cloud, navigate to “ <a href="https://docs.altinity.com/altinitycloud/userguide/configuring-a-cluster/configuring-data-lake-catalogs/">Cluster-&gt;Configure-&gt;Data Lake Catalog</a> “. That will list the available catalog options, including the Altinity.Cloud managed REST catalog. Once selected, it will automatically create a database in ClickHouse cluster that connects to the catalog.</p><p>Of course, the newly created catalog is empty. ClickHouse can not write data to Iceberg yet. You will need to use external tools such as Apache Spark or PyIceberg, or <a href="https://github.com/Altinity/ice">Altinity ice</a> for that purpose. We are working on enabling writes, which will be available soon.</p><p>Managed Iceberg catalogs are only supported in AWS so far. Please reach out to us if you need them in other clouds.</p><h3>What’s Next</h3><p>Today’s launch is just the beginning of a new approach to analytics infrastructure. With managed Iceberg and compute swarms, Altinity is solving the integration complexity that has kept real-time data lakes at the idea stage for many organizations. You can now start to design production systems that help your organizations gain differentiation from fast insights on large datasets. The key is <a href="https://altinity.com/project-antalya/">managed, real-time data lakes</a> that combine the strengths of ClickHouse and Iceberg in any location.</p><p>The next releases will include managing multiple catalogs in the environment as well as AWS S3 Tables integration. We also keep working on adding more features into ClickHouse itself for better performance and Iceberg compatibility. Lastly, we’re actively working on adding support for writing data into Iceberg. Stay tuned and reach out to us if you want to learn more about how to apply these capabilities to your use cases.</p><p><strong>Ready to build real-time data lakes?</strong> <a href="https://docs.altinity.com/support/">Contact Altinity support</a> to enable preview features, receive testing credits, and start your evaluation. Don’t have an account yet? <a href="https://acm.altinity.cloud/signup?_gl=1*1hq2lc0*_gcl_au*ODM1OTUyNDU0LjE3NTE4MzE1NTY.">Start a trial</a> today!</p><p><em>Originally published at </em><a href="https://altinity.com/blog/altinity-cloud-launches-managed-iceberg-with-antalya-compute-swarms"><em>https://altinity.com</em></a><em> on July 7, 2025.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=26a7df1c21c4" width="1" height="1" alt=""><hr><p><a href="https://medium.com/altinity/altinity-cloud-launches-managed-iceberg-with-antalya-compute-swarms-26a7df1c21c4">Altinity.Cloud Launches Managed Iceberg with Antalya Compute Swarms</a> was originally published in <a href="https://medium.com/altinity">Altinity</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Understanding Detached Parts in ClickHouse®]]></title>
            <link>https://medium.com/altinity/understanding-detached-parts-in-clickhouse-2354bd78a667?source=rss----fc2a2d760d0f---4</link>
            <guid isPermaLink="false">https://medium.com/p/2354bd78a667</guid>
            <category><![CDATA[database-administration]]></category>
            <category><![CDATA[troubleshooting]]></category>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[open-source]]></category>
            <dc:creator><![CDATA[AltinityDB]]></dc:creator>
            <pubDate>Sat, 28 Feb 2026 14:31:00 GMT</pubDate>
            <atom:updated>2026-02-28T14:31:00.720Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*-fIMwl9-eVgAld5X.png" /></figure><p>If you’ve ever worked with ClickHouse, you’ve probably encountered its unique way of handling data. One of the more intriguing concepts in ClickHouse is <strong>detached parts</strong>. While they might sound like a minor technical detail, understanding detached parts is essential. ClickHouse is designed to fully utilize available system resources. However, when the system becomes overloaded and resources such as memory, CPU, and I/O are maxed out, there is a high risk of <a href="https://altinity.com/blog/rescuing-clickhouse-from-the-linux-oom-killer">out-of-memory (OOM) errors</a> or unresponsive pods being restarted by the kubelet due to failed liveness probes. These abrupt or unexpected restarts can result in the appearance of detached parts.</p><p>In this blog post, we’ll break down what detached parts are, why they exist, and how to manage them effectively.</p><h3>What Are Detached Parts?</h3><p>Let’s first go to the basics. In ClickHouse, data is stored in <strong>parts</strong>. Each part is an immutable chunk of data, organized in a columnar format. These parts are the building blocks of ClickHouse tables, and they allow the database to achieve its famous performance for analytical queries.</p><p>ClickHouse <em>merges</em> consolidate multiple smaller data parts within a table into fewer, larger parts to improve query performance and reduce storage overhead. When data is inserted into a MergeTree table, it’s initially written as small, immutable parts. Over time, the system automatically merges these parts based on certain rules and heuristics (like size and age) to keep the number of parts manageable and maintain optimal read efficiency. While merges improve performance in the long run, they consume CPU, disk I/O, and memory, so their behavior can be fine-tuned or throttled for low-resource environments.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*XW-sCRrmnveSLy71.png" /><figcaption>Figure 1: Merging smaller parts into a bigger one</figcaption></figure><p>But what happens when data changes? Since ClickHouse doesn’t modify existing parts directly (they are immutable, remember?), merges also handle data cleanup for deleted or updated rows. These types of merges are called mutations. If a part needs to be mutated, ClickHouse will create a new part with the changes, mark the old one as inactive, and the new one as active, so the cleaning background process can delete those and reclaim space.</p><p>Inactive parts are essentially data segments that are no longer part of the active dataset but still exist on disk. They’re like old snapshots of your data, waiting to be cleaned up.</p><p>Ok, so what is the difference between inactive parts and detached parts? Think of <strong>detached parts</strong> as data chunks that are still physically present on your disk but are intentionally excluded from the table’s active data set. They can be explicitly detached by a ClickHouse command or can be automatically detached by Clickhouse, which is the most common case.</p><p>In contrast to detached parts, <strong>inactive parts</strong> are not explicitly put aside by an administrator. Instead, they represent an internal, transient state of data parts that are no longer considered the “current” version and are slated for eventual removal.</p><p>In the next image, you can see the building blocks of a part and how it is named. The naming is important, and I will explain it in a later section.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*CIu7i22CiEZwQ0Vw.png" /><figcaption>Figure 2: The basic building blocks of a part</figcaption></figure><h3>Why Do I Get Detached Parts?</h3><p>Detached parts are a byproduct of ClickHouse’s design philosophy, which prioritizes immutability and performance. Here are some common scenarios where detached parts come into play:</p><h4>1. Failed Inserts/Merges/Mutations</h4><p>ClickHouse doesn’t modify the existing data. Instead, it creates new parts with the updated data, and after mutation is finished, simply marks the old ones as inactive. This ensures that queries can continue running without interruption while the merge/mutation is in progress.</p><p>If a data ingestion or merge/mutation operation fails because of:</p><ul><li>Unexpected/hard restart</li><li>A bug in ClickHouse leading to damage of mutated/merged data</li><li>Misconfiguration of macros/volumes or similar user mistakes</li><li>Partial merge due to lack of <em>fsyncs</em></li></ul><p>ClickHouse might leave behind detached parts as a safety measure. This prevents data corruption and allows you to recover from the failure if possible.</p><p>For example, a merge/mutation failed because of a hard restart and we have the source parts, which were merged, but the merge was half-flushed at the moment of the hard restart. ClickHouse will detach the source parts as ‘covered’, and the new part, which has some columns unreadable, will produce some errors during queries. So what can we do? We can recover from old covered parts in the detached directory and drop the corrupted half-merged part. We will talk about this in the section <a href="https://altinity.com/blog/understanding-detached-parts-in-clickhouse#2-reattaching-detached-parts">Reattaching detached parts</a>.</p><h4>2. Manual Detachment</h4><p>You can manually detach parts using the ALTER TABLE ... DETACH PART command. This is useful for troubleshooting or temporarily removing data without deleting it permanently. This procedure will help in the above example. We would detach the corrupted part and attach the older parts.</p><h4>3. Replication and Merges</h4><p>In replicated tables, detached parts can occur during merge operations or when there are inconsistencies between replicas. ClickHouse detaches parts to ensure data consistency across the cluster.</p><h3>Where Are Detached Parts Stored?</h3><p>Detached parts live in a special subdirectory called detached within the table’s data directory. For example, if your table is located at:</p><p>/var/lib/clickhouse/data/database_name/table_name/</p><p>You’ll find the detached parts in:</p><p>/var/lib/clickhouse/data/database_name/table_name/detached/</p><p>Each detached part is stored in its own directory, named after the part’s identifier. This makes it easy to identify and manage them.</p><h4>Prefixes for Internally Detached Parts</h4><p>When ClickHouse moves a part to the detached folder, it records a <strong>reason prefix</strong> on the directory name and in system.detached_parts.reason. Some prefixes are safe to clean up after validation; others indicate corruption or consistency issues. The sections below summarize each prefix and what to do next.</p><p>Here are the most common prefixes and their meanings:</p><p>ignored_</p><ul><li><strong>Meaning:</strong> ignored_ <strong>marks inactive parts superseded by a successful merge</strong>. If the merged part is valid, the older inactive parts are renamed with ignored_ and moved to detached.<br>- <strong>Note (simple rule of thumb):</strong> unexpected_ is like a “we found this in the attic” tag, while ignored_ is like “we already replaced this, keep it aside.” In ReplicatedMergeTree startup sanity checks, parts that are unexpected relative to ZooKeeper are typically renamed to ignored_. So a part found on disk but missing in ZooKeeper will usually appear as ignored_, not unexpected_<strong>.</strong></li><li><strong>Action:</strong> These are generally safe to delete <em>after</em> you confirm the merged data is intact.</li></ul><p>covered_by_broken_</p><ul><li><strong>Meaning:</strong> covered-by-broken_ indicates parts that are <strong>covered by a broken part</strong> discovered during replicated table initialization. The broken part is detached as broken_, and older generations are marked covered-by-broken_.</li><li><strong>Action:</strong> Investigate the broken part first. Once a healthy replacement is restored (for example, fetched from another replica), covered-by-broken_ parts are safe to remove.</li></ul><p>broken_ / broken-on-start_</p><ul><li><strong>Meaning:</strong> broken_ indicates a part that failed to load <strong>during runtime operations</strong> (for example, during ATTACH PART). broken-on-start_ indicates a part that failed to load <strong>during table startup</strong> and counts toward max_suspicious_broken_parts (which can block startup if too many parts are broken).</li><li><strong>Action:</strong> Treat both as high‑severity. Inspect clickhouse-server.log, confirm data availability from replicas or backups, and only then decide to detach/delete.</li></ul><p>cloned_</p><ul><li><strong>Meaning:</strong> The actual prefix is clone_ (singular), used when ClickHouse® detaches local parts while <strong>cloning/repairing</strong> a replica to avoid conflicts with incoming parts.</li><li><strong>Action:</strong> clone_ parts are generally safe to delete once the replica is fully synced and consistent.</li></ul><p>noquorum_</p><ul><li><strong>Meaning:</strong> noquorum_ indicates parts created by inserts that <strong>failed quorum requirements</strong> in replicated setups.</li><li><strong>Action:</strong> Investigate replica health and quorum configuration before cleanup.</li></ul><p>merge-not-byte-identical_ / mutate-not-byte-identical_</p><ul><li><strong>Meaning:</strong> These indicate <strong>replica consistency issues</strong> where the resulting part is logically equivalent but <strong>not byte‑identical</strong> across replicas.</li><li><strong>Action:</strong> Treat as high‑priority; investigate root cause (hardware, filesystem, mixed CPU architectures, or version drift) before deleting.</li></ul><p>attaching_, deleting_, tmp-fetch_</p><ul><li><strong>Meaning:</strong> These are <strong>temporary prefixes</strong> used during ATTACH PART, DROP DETACHED, and replication fetch operations.</li><li><strong>Action:</strong> Do not delete these while the operation is in progress. If they persist, check server logs and background tasks.</li></ul><p>broken-from-backup_</p><ul><li><strong>Meaning:</strong> A part failed to restore during RESTORE and was moved to detached with this prefix.</li><li><strong>Action:</strong> Treat as a restore failure and investigate backup integrity and disk health before cleanup.</li></ul><h3>How to Manage Detached Parts in ClickHouse</h3><p>While detached parts are a normal part of ClickHouse’s operation, they can accumulate over time and consume disk space. Here’s how you can manage them effectively.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*-W-Qtt91VocfEVAg.png" /></figure><h4>1. Investigating Detached Parts</h4><p>Let’s explain what you might’ve seen while exploring ClickHouse files: the first number in a part’s name it’s the table’s partition-think of it like the shelf where that chunk of data lives. For most tables, it might look like a date in YYYYMMDD format, which makes scanning part names surprisingly intuitive. For example, the part 20240601_10_20_2 tells us this part holds data in the 20240601 partition (say, &quot;June 1, 2024&quot;), spans block numbers 10 through 20, and sits at merge level 2. (If you&#39;d like to know more, the Altinity Knowledge Base has <a href="https://kb.altinity.com/engines/mergetree-table-engine-family/part-naming-and-mvcc">a great article explaining part names and multiversion concurrency control</a>.)</p><p>As you see, this naming pattern is more than just pretty labeling-it’s ClickHouse’s way of quickly checking if data overlaps, is duplicate, or is safely covered by already active parts in the table. When dealing with detached parts, ClickHouse reads this naming structure to know whether the data they’re holding is already there. Next time you’re poking around the /detached folder, remember: read those part names from left to right-they tell you the full story, starting with the partition, not just where the data begins.</p><p>The knowledge base also has <a href="https://kb.altinity.com/altinity-kb-useful-queries/detached-parts">an article with a query that lets you see if detached parts are covered or not</a>. For example, you can run the query and get something like this:</p><pre>┌─database───────┬─table────────────┬─partition_id─┬─name────────────────────┐<br>│ test           │ mt4              │ all          │ ignored_all_0_3407_746  │<br>│ test           │ mt4              │ all          │ ignored_all_3408_3408_0 │<br>└────────────────┴──────────────────┴──────────────┴─────────────────────────┘</pre><p>Then because the query will check min_block_number and max_block_number and check if those blocks are covered by active healthy parts, then you know that those ignored parts could be dropped. If the query does not return any row but you see detached parts in system.detached_parts table then most probably those parts are not covered and you need to investigate.</p><h4>2. Reattaching Detached Parts</h4><p>Attaching a problematic or overlapping part could cause unexpected issues. A much safer workflow is to create a clone of your original table’s structure, and then play with the detached parts in isolation. Here’s how you can do that:</p><ul><li>Create a new table with the same structure as the original:<br>CREATE TABLE visits_test AS SELECT * FROM visits</li><li>Copy the detached part(s) from the original table’s detached/ directory to the same directory in the new table. You’ll find the detached directory at a path like /var/lib/clickhouse/data/[db]/[table]/detached/. Copy the relevant part folders directly (be sure ClickHouse isn&#39;t running critical operations while you do this!).</li><li>Attach the part in your new table:<br>ALTER TABLE visits_test ATTACH PART &#39;20240601_10_20_2&#39;;</li></ul><p>Now, you can freely query and inspect this part’s data in the visits_test table, without any risk to your production data or consistency. If something goes wrong, your original table remains untouched. This method is excellent for data forensics, debugging, or simply making sure the content of a detached part is what you expect before taking further action.</p><p>In short: using a scratch/test table is the safest way to handle and explore detached parts in ClickHouse. It’s quick, it’s easy, and it gives you peace of mind when working around lower-level storage operations.</p><h4>3. Deleting Detached Parts</h4><p>To free up disk space, you can permanently delete detached parts. Use the following command:</p><p>ALTER TABLE table_name DROP DETACHED PART &#39;part_name&#39;;</p><p>Alternatively, you can manually delete the directories from the detached folder on disk. However, this approach is riskier and should only be used if you&#39;re confident about what you&#39;re doing.</p><h4>4. Monitoring Detached Parts</h4><p>This table is your first stop for understanding why parts are in the detached directory. To get a list of all detached parts for a table, you can query the system.detached_parts table:</p><p>SELECT * FROM system.detached_parts WHERE table = &#39;table_name&#39;;</p><p>This query provides detailed information about each detached part, including its name, size, and creation time.</p><h3>Best Practices for Handling Detached Parts</h3><ol><li><strong>Schedule Regular Cleanups</strong>: Detached parts can accumulate over time, so it’s a good idea to periodically check for and remove unnecessary parts.</li><li><strong>Backup Before Deletion</strong>: Before deleting detached parts, make sure you have backups or are certain the data is no longer needed.</li><li><strong>Optimize Mutations</strong>: Use mutations sparingly. Consider using table engines like ReplacingMergeTree or CollapsingMergeTree to avoid frequent updates and deletes.</li><li><strong>Monitor Disk Usage</strong>: Keep an eye on disk usage, especially if you’re working with large datasets or performing frequent mutations.</li><li><strong>Provision adequately your clusters</strong>: If you have an underprovisioned cluster that is hit with tons of unoptimized queries, then there is a high chance of hard restarts and getting detached parts.</li></ol><h3>Final Thoughts</h3><p>Detached parts are a fascinating aspect of ClickHouse’s architecture, reflecting its commitment to immutability and performance. While they might seem like a nuisance at times, they play a crucial role in ensuring data consistency and reliability.</p><p>By following the best practices outlined in this post, you can keep your ClickHouse cluster in top shape and avoid the pitfalls of unmanaged detached parts. Whether you’re a database administrator, a data engineer, or just a curious tech enthusiast, understanding detached parts is a small but important step in mastering ClickHouse.</p><p><em>Have you encountered detached parts in your ClickHouse journey? Share your experiences, tips, or questions in the comments below! Let’s keep the conversation going.</em></p><p><em>Originally published at </em><a href="https://altinity.com/blog/understanding-detached-parts-in-clickhouse"><em>https://altinity.com</em></a><em> on July 2, 2025.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2354bd78a667" width="1" height="1" alt=""><hr><p><a href="https://medium.com/altinity/understanding-detached-parts-in-clickhouse-2354bd78a667">Understanding Detached Parts in ClickHouse®</a> was originally published in <a href="https://medium.com/altinity">Altinity</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The Future Has Arrived: Parquet on Iceberg Finally Outperforms MergeTree]]></title>
            <link>https://medium.com/altinity/the-future-has-arrived-parquet-on-iceberg-finally-outperforms-mergetree-69b2491f5ca5?source=rss----fc2a2d760d0f---4</link>
            <guid isPermaLink="false">https://medium.com/p/69b2491f5ca5</guid>
            <category><![CDATA[parquet]]></category>
            <category><![CDATA[data-lakehouse]]></category>
            <category><![CDATA[benchmark]]></category>
            <category><![CDATA[apache-iceberg]]></category>
            <category><![CDATA[clickhouse]]></category>
            <dc:creator><![CDATA[AltinityDB]]></dc:creator>
            <pubDate>Fri, 27 Feb 2026 10:14:16 GMT</pubDate>
            <atom:updated>2026-02-27T10:14:42.218Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*1cPmrsGtVBXyQ7DO.png" /></figure><p><em>Alien they seemed to be:<br>No mortal eye could see<br>The intimate welding of their later history.</em></p><p>-The Convergence of the Twain. Thomas Hardy</p><p><a href="https://altinity.com/blog/announcing-project-antalya-infinitely-scalable-clickhouse-query-on-10x-cheaper-iceberg-storage">The Altinity Antalya project</a> that was introduced last month started from our work on Parquet performance a year ago after <a href="https://altinity.com/blog/reading-external-parquet-data-from-clickhouse">“Reading External Data Parquet Data from ClickHouse</a>®” research. It quickly became apparent that Parquet combined with an open Iceberg table format is a good alternative to ClickHouse native MergeTree when stored on object storage. To prove the point and open up usage in real applications, we needed performance data. We now have the first results. They look very promising.</p><p>Iceberg and Parquet allow us to implement the main performance features of MergeTree, but also give additional benefits. It provides the foundation for compute-storage separation that allows us to apply cheap <a href="https://altinity.com/blog/using-swarms-of-clickhouse-servers-for-high-performance-reads-of-parquet">external swarms of compute for query processing</a>. In this article, we dive deeper into Altinity Antalya foundation features and demonstrate that performance of queries on Iceberg data can already match or even outperform ClickHouse MergeTree!</p><h3>From MergeTree to Iceberg</h3><p>ClickHouse MergeTree used to be fundamental for ClickHouse performance over the years. It has been originally designed for block storage where file operations are almost free, and therefore has some shortcomings that are apparent once data moves to the object storage.</p><p>To make it short, every MergeTree table consists of lots of files, sometimes thousands, and a reasonably big ClickHouse installation may have hundreds of thousands of data files that sometimes even reaches the inode limit of the file system! When running a query, ClickHouse typically opens and reads many files.</p><p>When MergeTree data is stored in object storage like S3, every file is stored as an object. So instead of hundreds of thousands of files we end up with hundreds of thousands of objects. This is a big problem. For instance, in order to access an object, ClickHouse has to perform an HTTPS call. This is slow. Even on the fastest network, the latency of accessing S3 objects is orders of magnitude bigger than the latency accessing the file on a block storage. The icing on the cake is that AWS gladly charges users for such calls! There are other problems as well; if we start digging deeper we may never get to the performance results.</p><p>The ClickHouse development team put a lot of effort into adapting MergeTree to be efficiently used with object storage. Some features like compact parts, compact metadata, various caches, lazy table loading, parallel reads, and so on are available in upstream ClickHouse versions. Others, like SharedMergeTree or distributed cache are closed source ClickHouse Cloud features only. This helps to mitigate shortcomings of original MergeTree design to some extent. Unfortunately, the most useful features are not available for open source ClickHouse users.</p><p>There is also an important limitation that can not be addressed by improving MergeTree at all. Once the data is on object storage it can be shared, and it is tempting to access it from other systems, not only from ClickHouse. Unfortunately, MergeTree is understood only by ClickHouse itself, which greatly restricts sharing options.</p><p>This is why we have chosen the Iceberg table format combined with Parquet files as a storage for our Antalya project. The combo shares a lot of attractive design and performance properties with ClickHouse MergeTree tables whilst addressing MergeTree shortcomings. It is fully open and can be read and written by any external system! There is also a big community and industry leaders backing it, including AWS, <a href="https://www.databricks.com/blog/apache-icebergtm-v3-moving-ecosystem-towards-unification">DataBricks</a>, and <a href="https://blog.cloudflare.com/r2-data-catalog-public-beta/">Cloudflare</a>.</p><p>We’ve spent the last year improving query performance for Parquet. ClickHouse developers added support for Iceberg and other catalogs. The combined efforts over very good results already. Let’s see how ClickHouse over Iceberg performs. But first we need to set up an Iceberg catalog.</p><h3>Iceberg Catalog</h3><p>Setting up an Iceberg Catalog has never been that easy. In Altinity.Cloud it is just a single button click. If you are running ClickHouse by yourself, we have released an <a href="https://github.com/Altinity/ice/tree/master/ice-rest-catalog">open source Iceberg REST catalog</a> for Kubernetes that makes it almost as easy.</p><p>Once you have a catalog enabled in Altinity.Cloud, there is a wizard that helps to create a catalog connection in ClickHouse. It ends up with SQL CREATE DATABASE statement with all connection parameters already defined.</p><pre>CREATE DATABASE &quot;ice&quot;<br>  ENGINE = DataLakeCatalog(&#39;https://&lt;iceberg_catalog_url&gt;&#39;)<br>  SETTINGS catalog_type = &#39;rest&#39;,<br>    auth_header = &#39;Authorization: Bearer [TOKEN]&#39;,<br>    warehouse = &#39;s3://&lt;catalog_warehouse_bucket&gt;&#39;;</pre><p>If you are running a catalog by yourself, it is pretty much the same but you will need to specify connection parameters manually.</p><p><strong>Note:</strong> ClickHouse versions 25.2 and earlier used ‘Iceberg’ database engine, but in 25.3 it was renamed to ‘DataLakeCatalog’. Direct upgrade from 25.2 to 25.3 is not possible without fixing the schema files directly. A lot of Iceberg performance features were added in 25.3 and 25.4 ClickHouse versions and backported to the latest Altinity Antalya Build, so we recommend using the latest upstream ClickHouse version or latest Altinity Antalya build for the best results.</p><h3>Icing the Test Data</h3><p>We start with the popular New York Taxi Rides dataset first introduced by Mark Litwintschik a few years ago. There is an excellent <a href="https://tech.marksblogg.com/benchmarks.html">summary page</a> that compares multiple databases including ClickHouse. The dataset is available in multiple variants, we will use the one that is stored in s3://altinity-clickhouse-data public bucket. It is described in <a href="https://altinity.com/blog/ultra-fast-data-loading-and-testing-in-altinity-cloud">Ultra-Fast Data Loading and Testing in Altinity.Cloud</a>, for example.</p><p>Here is a ClickHouse table:</p><pre>CREATE TABLE tripdata (<br>  pickup_date Date DEFAULT toDate(pickup_datetime) CODEC(Delta, LZ4),<br>  id UInt64,<br>  vendor_id String,<br>  pickup_datetime DateTime CODEC(Delta, LZ4),<br>  dropoff_datetime DateTime,<br>  passenger_count UInt8,<br>  trip_distance Float32,<br>  pickup_longitude Float32,<br>  pickup_latitude Float32,<br>  rate_code_id String,<br>  store_and_fwd_flag String,<br>  dropoff_longitude Float32,<br>  dropoff_latitude Float32,<br>  payment_type LowCardinality(String),<br>  fare_amount Float32,<br>  extra String,<br>  mta_tax Float32,<br>  tip_amount Float32,<br>  tolls_amount Float32,<br>  improvement_surcharge Float32,<br>  total_amount Float32,<br>  pickup_location_id UInt16,<br>  dropoff_location_id UInt16,<br>  junk1 String,<br>  junk2 String)<br>ENGINE = MergeTree()<br>PARTITION BY toYYYYMM(pickup_date)<br>ORDER BY (vendor_id, pickup_location_id, pickup_datetime)</pre><p>It can be loaded with the following query:</p><pre>INSERT INTO tripdata<br>SELECT * FROM s3(&#39;s3://altinity-clickhouse-data/nyc_taxi_rides/data/tripdata_parquet/*.parquet&#39;, NOSIGN)<br>SETTINGS max_threads=32, max_insert_threads=32, input_format_parallel_parsing=0;</pre><p>The table contains 1.3B rows and takes up 37GB of local storage.</p><p>Now we need to load it into Iceberg. Since ClickHouse does not support direct writes to Iceberg yet, we will use <a href="https://github.com/Altinity/ice">ice</a>. This is an open source client for Iceberg REST catalogs that we developed in order to load tables into Iceberg until native ClickHouse support is available. It can also load data from existing S3 buckets. If you are interested in learning more, read <a href="https://altinity.com/blog/introducing-altinity-ice-a-simple-toolset-for-iceberg-rest-catalogs">the Introduction to Ice</a> article.</p><p>First, let’s write data from ClickHouse into an S3 bucket managed by Iceberg catalog, preserving the path structure that catalog would create:</p><pre>INSERT INTO FUNCTION s3(&#39;s3://&lt;catalog_warehouse_bucket&gt;/nyc/tripdata/data/{_partition_id}/data.parquet&#39;)<br>PARTITION BY toYYYYMM(pickup_date)<br>SELECT * FROM tripdata<br>ORDER BY vendor_id, pickup_location_id, pickup_datetime</pre><p>Then run ice with the --no-copy flag:</p><pre>ice insert nyc.tripdata -p --no-copy s3://&lt;catalog_warehouse_bucket&gt;/nyc/tripdata/data/*/data.parquet \<br>  --partition=&#39;[{&quot;column&quot;:&quot;pickup_date&quot;,&quot;transform&quot;:&quot;month&quot;}]&#39; \<br>  --sort=&#39;[{&quot;column&quot;:&quot;vendor_id&quot;,&quot;column&quot;:&quot;pickup_location_id&quot;,&quot;column&quot;:&quot;pickup_datetime&quot;}]&#39;</pre><p>The ice insert command takes data ‘as-is’, registering it properly in the catalog. If you are using different table names, make sure that the table name corresponds to the path inside the warehouse. Note, that we also specify partitioning and table sort order that matches MergeTree one.</p><p>We can now run a count query to confirm row count:</p><pre>select count() from ice.&quot;nyc.tripdata&quot;<br><br>   ┌────count()─┐<br>1. │ 1310903963 │ -- 1.31 billion<br>   └────────────┘</pre><p>The count query is almost instant. So let’s try something a little more “stressful.”</p><h3>Climbing the Iceberg with Test Queries</h3><p>We will use 5 test queries:</p><p>Q1. Group by a single column.</p><pre>SELECT <br>    passenger_count,<br>    avg(total_amount)<br>FROM tripdata<br>GROUP BY passenger_count</pre><p>Q2. Group by two columns.</p><pre>SELECT <br>    passenger_count,<br>    toYear(pickup_date) AS year,<br>    count(*)<br>FROM tripdata<br>GROUP BY passenger_count, year</pre><p>Q3. Group by three columns.</p><pre>SELECT <br>    passenger_count,<br>    toYear(pickup_date) AS year,<br>    round(trip_distance) AS distance,<br>    count(*)<br>FROM tripdata<br>GROUP BY passenger_count, year, distance<br>ORDER BY year, count(*) DESC</pre><p>Q4. Query with a JOIN to taxi_zones table</p><pre>SELECT <br>    tz.zone AS zone,<br>    count() AS c<br>FROM tripdata AS td<br>LEFT JOIN taxi_zones AS tz ON td.pickup_location_id = tz.location_id<br>GROUP BY zone<br>ORDER BY c DESC LIMIT 10</pre><p>Q5. Where condition on the joined table.</p><pre>SELECT count(*)<br>FROM tripdata AS td<br>INNER JOIN taxi_zones AS tz ON td.pickup_location_id = tz.location_id<br>WHERE tz.zone = &#39;Midtown East&#39;</pre><p>The Iceberg queries differ only by the table name: ice.&quot;nyc.tripdata&quot; instead of tripdata. Table taxi_zones is always local; it is a small one and there is no reason to put it into an Iceberg table.</p><p>Finally, we enabled the following optimizations:</p><ul><li><strong>Iceberg metadata files cache </strong>(use_iceberg_metadata_files_cache=1) - this cache stores the internal representation of Iceberg metadata, which contains a list of files representing an Iceberg table. Otherwise we would need to read from object storage and parse a huge JSON on every query. This can add 10s of milliseconds (or even 100s of milliseconds) for large tables.</li><li><strong>Parquet metadata cache </strong>(input_format_parquet_use_metadata_cache=1) - this cache stores Parquet metadata for row groups. It helps to skip filtered Parquet files without reading them.</li><li><strong>S3 List cache </strong>(use_object_storage_list_objects_cache=1) - this is essential when working with Hive-partitioned S3 buckets, but may reduce a few network calls when working with Iceberg as well</li><li><strong>Filesystem cache </strong>(enable_filesystem_cache=1)- this is a standard ClickHouse local storage cache for external data.</li><li><strong>Iceberg partitioning pruning</strong> (use_iceberg_partition_pruning=1) - this is essential for efficient filtering of Iceberg data.</li></ul><p>Every query was executed 3–5 times, and we recorded the shortest response. We used c7g.8xlarge AWS instances with 32vCPUs:</p><p>Results are shown in the table below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*96U6dzmMse9Shx1c_AXoxg.png" /></figure><p>The results were quite surprising! Iceberg queries were constantly outperforming MergeTree! Why is that? Experienced ClickHouse users might suppose that the MergeTree table is fragmented with several parts per partition, while the Iceberg table stores one Parquet file per partition. We executed OPTIMIZE TABLE FINAL on the MergeTree table and re-ran the queries - there was no difference.</p><p>Even if we disable data cache, which can be done with min_bytes_to_use_direct_io=1 for MergeTree, and enable_filesystem_cache=0 for Iceberg, Iceberg queries were still two times faster.</p><p>Iceberg query results demonstrate a huge improvement over what ClickHouse could do with Parquet a year ago, when we were puzzled by ‘ <a href="https://altinity.com/blog/whats-up-with-parquet-performance-in-clickhouse">What’s Up With Parquet Performance in ClickHouse?</a>’ Queries Q1, Q2 and Q3 correspond to queries 2, 3 and 4 in Mark Litwitschik’s tests. So if we compare it against other DBs in the <a href="https://tech.marksblogg.com/benchmarks.html">summary table</a>, ClickHouse Iceberg queries would be at the top 10 performers!</p><p>But hold on, what’s up with MergeTree? Isn’t MergeTree the fastest native storage format for analytics?! We were very puzzled and decided to take a look at why MergeTree was slow in initial tests.</p><h3>Looking Back to MergeTree</h3><p>We were especially surprised by the slow MergeTree query times Q4 and Q5, so we tried to disable the analyzer with enable_analyzer=0 setting. That helped dramatically. Q4 MergeTree query time went down to 1.2s, and Q5 ran in 0.5s. While the new analyzer has been production ready for many months already, there are still cases when it fails to plan queries properly. Unfortunately, disabling analyzer did not improve results for the first three queries.</p><p>Our next theory was that the difference in query response was caused by better data compression in Parquet compared to MergeTree. Parquet uses per-column codecs that are adjusted for every RowGroup automatically. Additionally, it is compressed by ZSTD, which is configured using the output_format_parquet_compression_method when writing Parquet from ClickHouse. On the other hand, ClickHouse with default settings uses LZ4 compression for MergeTree tables. We can prove the difference in compressed data size by looking into Parquet metadata. Don&#39;t be frightened by this complex query below, it just does the job of collecting statistics:</p><pre>SELECT column, type, parquet_compressed, ch_compressed, round(ch_compressed / parquet_compressed, 2) as ratio<br>FROM<br>(<br>SELECT column, sum(num_rows), sum(column_uncompressed_size) parquet_uncompressed, sum(column_compressed_size) as parquet_compressed FROM<br>(WITH arrayJoin(columns) as c<br>SELECT _path, num_rows, metadata_size, total_uncompressed_size, total_compressed_size, tupleElement(c, &#39;name&#39;) as column, tupleElement(c, &#39;total_uncompressed_size&#39;) as column_uncompressed_size, tupleElement(c, &#39;total_compressed_size&#39;) as column_compressed_size<br>FROM s3(&#39;s3:///nyc/tripdata/data/*/data.parquet&#39;, &#39;ParquetMetadata&#39;)) t<br>GROUP BY column<br>) a<br>LEFT JOIN (SELECT name as column, type, data_compressed_bytes ch_compressed, data_uncompressed_bytes ch_uncompressed FROM system.columns WHERE table=&#39;tripdata&#39;) b<br>USING (column)<br>ORDER BY ratio<br>FORMAT PrettyCompactNoEscapesMonoBlock<br><br>    ┌─column────────────────┬─type───────────────────┬─parquet_compressed─┬─ch_compressed─┬───ratio───┐<br> 1. │ pickup_datetime       │ DateTime               │          728003899 │         14876 │         0 │<br> 2. │ dropoff_datetime      │ DateTime               │         2815366997 │         19662 │         0 │<br> 3. │ id                    │ UInt64                 │             132763 │         19662 │      0.15 │<br> 4. │ junk2                 │ String                 │             106863 │         19662 │      0.18 │<br> 5. │ junk1                 │ String                 │             106863 │         19662 │      0.18 │<br> 6. │ tolls_amount          │ Float32                │          100761469 │      86897084 │      0.86 │<br> 7. │ dropoff_location_id   │ UInt16                 │           59835410 │      90676470 │      1.52 │<br> 8. │ dropoff_longitude     │ Float32                │         2436064306 │    4062369546 │      1.67 │<br> 9. │ pickup_longitude      │ Float32                │         2380018092 │    4002029096 │      1.68 │<br>10. │ dropoff_latitude      │ Float32                │         2616572327 │    4545198969 │      1.74 │<br>11. │ pickup_latitude       │ Float32                │         2549710411 │    4492131060 │      1.76 │<br>12. │ total_amount          │ Float32                │         1631431696 │    3640707576 │      2.23 │<br>13. │ trip_distance         │ Float32                │         1564198984 │    3762184282 │      2.41 │<br>14. │ tip_amount            │ Float32                │          952820724 │    2316893979 │      2.43 │<br>15. │ passenger_count       │ UInt8                  │          293373924 │     730827155 │      2.49 │<br>16. │ fare_amount           │ Float32                │         1238104795 │    3640683882 │      2.94 │<br>17. │ rate_code_id          │ String                 │           44627379 │     138066010 │      3.09 │<br>18. │ payment_type          │ LowCardinality(String) │          180132201 │     638007985 │      3.54 │<br>19. │ extra                 │ String                 │           29879569 │     108049577 │      3.62 │<br>20. │ store_and_fwd_flag    │ String                 │           17538744 │      66921895 │      3.82 │<br>21. │ pickup_location_id    │ UInt16                 │             134890 │        579807 │       4.3 │<br>22. │ mta_tax               │ Float32                │            9102651 │      58587786 │      6.44 │<br>23. │ improvement_surcharge │ Float32                │             602351 │       6201352 │      10.3 │<br>24. │ pickup_date           │ Date                   │             331723 │      13049273 │     39.34 │<br>25. │ vendor_id             │ String                 │             117996 │      21085689 │     178.7 │<br>    └───────────────────────┴────────────────────────┴────────────────────┴───────────────┴───────────┘</pre><p>As you can see, columns that were used in the queries above — pickup_date, passenger_count, trip_distance, total_amount, pickup_location_id — all those consume much less space in Parquet compared to MergeTree. That plays an important role in query performance.</p><p>So, let’s change compression for the MergeTree table to ZSTD. Starting in ClickHouse version 25.4 it is possible to modify default compression at the table level. In earlier versions we can add a configuration snippet:</p><pre>&lt;clickhouse&gt;<br>  &lt;compression&gt;<br>    &lt;case&gt;<br>      &lt;method&gt;zstd&lt;/method&gt;<br>      &lt;level&gt;1&lt;/level&gt;<br>    &lt;/case&gt;<br>  &lt;/compression&gt;<br>&lt;/clickhouse&gt;</pre><p>Once applied, we need to run OPTIMIZE TABLE tripdata FINAL again. Let&#39;s check the data sizes now:</p><p>MergeTree:</p><pre>select total_bytes from system.tables where name = &#39;tripdata&#39;<br><br>   ┌─total_bytes─┐<br>1. │ 22069192362 │ -- 22.07 billion<br>   └─────────────┘</pre><p>Parquet in Iceberg:</p><pre>select sum(size) from (select _path, any(_size) as size from ice.&quot;nyc.tripdata&quot; group by 1)<br><br>   ┌───sum(size)─┐<br>1. │ 21132090256 │ -- 21.13 billion<br>   └─────────────┘</pre><p>Data sizes are now very close. Let’s re-run test queries on MergeTree and compare results:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GJbv3NnbLr6J6qLkVHGhuw.png" /></figure><p>Results are slightly better, but MergeTree is still slower than Iceberg for most queries.</p><p>We had to call our brilliant support engineers that pointed out the local_filesystem_read_method setting. If we change it to &#39;pread&#39;, then MergeTree finally gets the lead.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vr0eWCX_O70ZsDcpjEpbOA.png" /></figure><p>While we made MergeTree fast again, it was not the best user experience. Most users know very little about compression and analyzer, and very few have ever heard about the magical local_filesystem_read_method setting. ClickHouse was known to deliver the best performance out of the box, and it is thought-provoking that it is not the case anymore. (At least in this particular benchmark.)</p><p>So far we just used a single node ClickHouse. One of the main features of the Antalya project is swarm execution that allows users to add external compute easily to an existing ClickHouse cluster. Let’s take a minute to review how swarms work. (You can skip the next section if you already know this.)</p><h3>Swarm Execution Model</h3><p>Antalya swarms are separate ClickHouse clusters of stateless compute-only nodes that existing ClickHouse clusters can use to read data stored in object storage directly or in Iceberg tables. Swarms can be scaled up and down independently thus fulfilling the dream of compute and storage separation. Since swarm nodes are stateless, you can also apply cheap spot instances. This makes the swarm very cost effective as well.</p><p>The swarm execution model is designed around two distinct features: swarm discovery, and query execution delegation.</p><p>Swarm discovery allows ClickHouse clusters to connect to swarms with minimum configuration. We have improved the existing ClickHouse cluster auto-discovery feature combining it with auxiliary Keeper to accommodate it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*ir8YZUZpSE_31Xno" /></figure><p>Swarms register themselves in a dedicated Registry Keeper instance using a unique swarm name. ClickHouse clusters that need to run queries with swarm are configured to use the Registry as an auxiliary Keeper. The auxiliary Keeper extends any existing configuration stored in other Keeper servers. With the Registry properly configured, ClickHouse clusters will see swarms in the system.clusters table and will be able to connect and run queries. If swarm nodes are added or removed, ClickHouse handles [de-]registriation automatically, without any external configuration.</p><p>In order to implement the swarm execution model we had to re-think how ClickHouse runs distributed queries on externally stored data in general. The traditional ClickHouse approach required every table function that worked with object storage to have a “-Cluster” version, e.g.: s3 — s3Cluster, iceberg — icebergCluster etc. This is not convenient since it obliges a user to change queries when moving from a single node to a cluster. Instead, we have moved the cluster delegation instruction to the settings, so it could be defined in a profile or a query itself. Where ClickHouse would normally write:</p><pre>SELECT ... FROM s3Cluster(&#39;swarm1&#39;, ...)</pre><p>In Antalya we write it as:</p><pre>SELECT ... FROM s3(...) settings object_storage_cluster=&#39;swarm1&#39;</pre><p>If object_storage_cluster setting is not set, the query runs locally. If it is set to a particular swarm, the query is executed on that swarm! This is much more convenient.</p><p>Using query settings as an interface for query routing instructions allows us to add other features keeping the regular SQL syntax, for example:</p><ul><li>specify number of swarm nodes to use (object_storage_max_nodes) - this is similar to max_threads but applied to limit number of nodes</li><li>Use swarm node as a query initiator (object_storage_remote_initiator)</li></ul><p>Going forward, this will allow us to offload heavy system workloads completely to the swarm. The obvious example is running OPTIMIZE for Parquet data.</p><p>But that’s enough theory. Let’s see it in practice.</p><h3>Swarm Lands on Iceberg</h3><p>For the next test we will downscale our initial ClickHouse to a 4vCPU instance, and start a single-node swarm extension using a 32vCPU machine. In Altinity.Cloud this can be easily done from the UI. If you are running ClickHouse by yourself, refer to the <a href="https://github.com/Altinity/antalya-examples">Antalya Examples</a> repository to see how to do it in Kubernetes.</p><p>Once the swarm is configured, we can run queries providing swarm name as a query setting, like this:</p><pre>SELECT <br>    passenger_count,<br>    avg(total_amount)<br>FROM ice.&quot;nyc.tripdata&quot;<br>GROUP BY passenger_count<br>SETTINGS object_storage_cluster=&#39;my-swarm&#39;</pre><p>Here are the query times:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KeW-5L5HW85jtzdky41Wqg.png" /></figure><p>So the use of initiator gives just a small overhead; the rest of the workload is effectively offloaded to the swarm.</p><p>*) Note Q4 and Q5. Those queries did not work out of the box, since as of writing this article swarm queries do not support joins properly. Here is a <a href="https://github.com/Altinity/ClickHouse/issues/819">bug report</a>; it will be fixed shortly. So we rewrite the queries as follows in order to move JOIN operations to the node initiator.</p><p>Q4.</p><pre>SELECT zone, sum(c) c<br>FROM (<br>  SELECT <br>    pickup_location_id,<br>    count() AS c<br>  FROM ice.&quot;nyc.tripdata&quot;<br>  GROUP BY pickup_location_id<br>) td<br>LEFT JOIN taxi_zones AS tz ON td.pickup_location_id = tz.location_id<br>GROUP BY zone <br>ORDER BY c DESC LIMIT 10<br>SETTINGS object_storage_cluster=&#39;my-swarm&#39;</pre><p>Q5.</p><pre>SELECT sum(c) c<br>FROM (<br>  SELECT pickup_location_id, count(*) c<br>  FROM ice.&quot;nyc.tripdata&quot;<br>GROUP by pickup_location_id<br>) td<br>INNER JOIN taxi_zones AS tz ON td.pickup_location_id = tz.location_id<br>WHERE tz.zone = &#39;Midtown East&#39;<br>SETTINGS object_storage_cluster=&#39;my-swarm&#39;</pre><p>We already tested a single node swarm, so let’s see if we can improve performance by adding 3 extra nodes. As soon as new nodes are up, they register automatically. No changes are needed to queries.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZaFS-p0EZglowenynBtO4g.png" /></figure><p>As you can see, the performance scales when there are more swarm resources. If we remove 0.2s overhead on the node initiator, then the remaining part of the query scales almost linearly.</p><p>Attaching an extra compute allows us to outperform MergeTree again!</p><h3>The Tip of the Iceberg</h3><p>These simple results are very promising. That said, we foresee many improvements to speed up performance.</p><ol><li>Some datatypes supported by ClickHouse are not properly supported by Parquet implementation in ClickHouse, or in Iceberg itself. For example, Enums do not work currently, and unsigned ints cannot be natively inserted using Iceberg libraries.</li><li>There is unnecessary overhead both on query initiator and swarm nodes when planning a query. The list of files is cached in Iceberg metadata file cache already, but ClickHouse still “checks” that files are there with an extra S3 Head request. While this makes sense for s3 table function queries, it is not needed for Iceberg. Iceberg catalog metadata is the point of truth.</li><li>The algorithm for maintaining cache locality for swarm nodes can be improved. Currently if one node is faster than others on a particular query it may start processing files that are cached on other nodes. This often results in unstable query times.</li><li>Finally, joins are still a work in progress.</li></ol><p>Our engineers are working on addressing all of these. If you find any other problem or have a performance improvement idea — please <a href="https://github.com/Altinity/ClickHouse/issues">file an issue on GitHub.</a> Or better yet, submit a PR. We work on this together!</p><p>In the end it’s likely that Iceberg will be just as fast as MergeTree and faster in cases where you can trade off cost for performance by applying a lot of compute. However, MergeTree still has many advantages:</p><ul><li>MergeTree is instantly queryable without compaction</li><li>MergeTree compacts automatically over time</li><li>MergeTree can be changed in ways that are difficult to replicate in Parquet. For example, many ALTER TABLE operations use hard links in order to avoid rewrites of unchanged data. This is not possible with Parquet.</li><li>It is ideal for downsampling/indexing parquet sources using materialized views.</li></ul><p>Our goal is to make these two storage types work smoothly together, leveraging their respective strengths.</p><p>The next big step is to take the proven design of ClickHouse TTL moves and extend it with an option to move data to Parquet and Iceberg. Once fully implemented it will allow users to work transparently with hot data in local MergeTree, and historical data in Parquet and Iceberg, maintaining reasonable query performance and allowing this data to be accessed by other systems, e.g. AI. Since Iceberg tables can be queried with swarms, it also allows users to manage compute for historical data independently. That way historical queries do not not affect performance on the main ClickHouse clusters.</p><h3>Conclusion</h3><p>The recent developments in ClickHouse and <a href="https://altinity.com/project-antalya/">Project Antalya bring Iceberg support in ClickHouse to the next level</a>. It is becoming a viable alternative to MergeTree, matching MergeTree performance characteristics but also adding cheap object storage and shared data capabilities. It opens the door to build systems that combine the strengths of MergeTree and Iceberg that are far more scalable and cost-performant than possible.</p><p>We’ve known for some time that ClickHouse overall has become much more performant on Iceberg/Parquet data and that Project Antalya features accelerate that performance. That said, the results presented in this article were a pleasant surprise. Our next step is to test against the ClickBench dataset, which has a more representative set of queries that include use of primary key indexes. We believe this will probe additional query paths and expand understanding of differences in performance. Stay tuned, the future has just arrived!</p><p><em>Originally published at </em><a href="https://altinity.com/blog/the-future-has-arrived-parquet-on-iceberg-finally-outperforms-mergetree"><em>https://altinity.com</em></a><em> on June 5, 2025.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=69b2491f5ca5" width="1" height="1" alt=""><hr><p><a href="https://medium.com/altinity/the-future-has-arrived-parquet-on-iceberg-finally-outperforms-mergetree-69b2491f5ca5">The Future Has Arrived: Parquet on Iceberg Finally Outperforms MergeTree</a> was originally published in <a href="https://medium.com/altinity">Altinity</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ClickHouse® TTLs in Practice: A Handbook for Developers]]></title>
            <link>https://medium.com/altinity/clickhouse-ttls-in-practice-a-handbook-for-developers-edff0f773c66?source=rss----fc2a2d760d0f---4</link>
            <guid isPermaLink="false">https://medium.com/p/edff0f773c66</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[database-performance]]></category>
            <category><![CDATA[database-design]]></category>
            <category><![CDATA[big-data]]></category>
            <dc:creator><![CDATA[AltinityDB]]></dc:creator>
            <pubDate>Tue, 13 Jan 2026 12:28:14 GMT</pubDate>
            <atom:updated>2026-01-14T09:29:17.158Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/793/0*5S0ycT7JIyU5nOnv.png" /></figure><p>If you’re using ClickHouse for serious workloads, TTLs are one of the most important features to get right.</p><p>We recently wrote a practical guide on ClickHouse TTLs from a real-world point of view, so you can keep data lifecycle, performance, and storage under control at scale.</p><p>Grab the handbook to:<br> • Learn how to design tables for effective TTL policies<br> • Understand how TTL moves data across hot, warm, and cold storage<br> • Discover why TTL deletions sometimes fail and how to fix them</p><p>Download for free: <a href="https://altinity.com/clickhouse-ttl/?utm_campaign=34417448-CH_TTL_eBook&amp;utm_source=Medium&amp;utm_medium=blog">https://altinity.com/clickhouse-ttl/</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=edff0f773c66" width="1" height="1" alt=""><hr><p><a href="https://medium.com/altinity/clickhouse-ttls-in-practice-a-handbook-for-developers-edff0f773c66">ClickHouse® TTLs in Practice: A Handbook for Developers</a> was originally published in <a href="https://medium.com/altinity">Altinity</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Join OSA CON 2025: Two Days of Open‑Source Analytics and AI (Nov. 4–5)]]></title>
            <link>https://medium.com/altinity/join-osa-con-2025-two-days-of-open-source-analytics-and-ai-nov-4-5-393191f307d2?source=rss----fc2a2d760d0f---4</link>
            <guid isPermaLink="false">https://medium.com/p/393191f307d2</guid>
            <category><![CDATA[open-source-event]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[devops]]></category>
            <category><![CDATA[ai]]></category>
            <dc:creator><![CDATA[AltinityDB]]></dc:creator>
            <pubDate>Thu, 23 Oct 2025 12:44:46 GMT</pubDate>
            <atom:updated>2025-10-23T12:44:42.723Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/0*Jd9-BowTvwh6lOeG.png" /></figure><p>The <a href="https://osacon.io/schedule/">Open Source Analytics Conference</a> (OSA CON) brings together data engineers, analysts, product managers, and builders shaping the future of open-source analytics.</p><p>It’s all about accelerating AI while celebrating the tools and communities that make modern data stacks possible. Expect candid talks from open‑source innovators rather than product pitches.</p><h3>When &amp; Where</h3><p>OSA CON 2025 takes place <strong>online on November 4–5</strong>. It’s completely <strong>free</strong> to attend. Sessions run in your local timezone on Airmeet, and full replays will be available right after the event.</p><h3>Who It’s For</h3><p>If you work with data, whether as a data scientist, engineer, analyst, designer, or product manager, OSA CON is for you.</p><p>It’s also a great space for open‑source maintainers, contributors, and decision‑makers to connect.</p><h3>What to Expect</h3><p>This year’s line‑up covers AI, databases, streaming, data lakehouses, ingestion, orchestration, governance, visualization, and more. Here are some of the standout sessions:</p><ul><li><strong>[Keynote] Open Source’s Massive Unfair Advantage in the AI Era</strong>: Maxime Beauchemin (Apache Superset &amp; Airflow Creator) explains why open source is positioned to dominate AI and why communities, not corporations, are the new centers of gravity.</li><li><strong>[Keynote] Don’t Fire Your Developers and Other Lessons for the AI Revolution</strong>: Robert Hodges (Altinity CEO) explores how AI will change developer workflows and why humans still matter.</li><li><strong>[Keynote] How Open Source Businesses Will Thrive in the Age of AI</strong>: Heather Meeker (Chinstrap Community Co-Founder) dives into commercial open source (COSS) models and how generative AI will reshape them.</li><li><strong>Smarter Analytics: AI‑Driven Intelligence in Modern Databases</strong>: Peter Zaitsev (Percona &amp; Coroot Founder) shows how AI can turn databases into context‑aware engines, from schema optimization to self‑driving databases and multimodal analytics.</li><li><strong>What the Spec?!: New Features in Apache Iceberg™ Table Format V3</strong>: Danica Fine (Snowflake Lead Developer Advocate for open source) highlights the leap from Iceberg V2 to V3, walking through user‑facing improvements such as variant types for semi‑structured data and row‑lineage to enhance CDC.</li><li><strong>Streaming Analytics in Action: Real-World Case Studies from Uber, Razorpay, and Stripe</strong>: Jayesh Asrani (StarTree Principal Solutions Architect) shares how three high‑growth companies use next‑gen streaming architectures for real‑time decision‑making and discover reference designs you can apply.</li><li><strong>Agnostic is the Only Constant: Embracing the Lakehouse Paradigm Without Lock‑In</strong>: Viktor Kessler (Vakamo Co‑Founder &amp; CEO) introduces Lakekeeper and shows how Iceberg‑based lakehouses remain portable across clouds and compute engines.</li><li><strong>ClickHouse® Chronicles: Real‑World War Rooms with Human and AI Agents</strong>: Hear battle‑tested stories of running ClickHouse at scale and solving production incidents from Shivji Kumar Jha (Nutanix Staff Engineer) and Anurag Pandey (Incerto Co-Founder). If you’re into ClickHouse internals, this is a must‑attend.</li><li><strong>Why Your FAQ Search Sucks and How I Fixed It</strong>: Chris Dabatos (TiDB Developer Advocate) shows how he built a lightning‑fast semantic FAQ search in ~300 lines of Python using TiDB and Amazon Bedrock embeddings.</li><li><strong>Open Source Healthcare Analytics: 20 % ICU Transfer Reduction at Scale</strong>: Rakshit Khare (AWS Sr. Global Specialist Solutions Architect) shares an open‑source platform that reduced ICU transfers by 20 %, built with Kafka, Spark, and TimescaleDB, and discusses privacy‑preserving analytics and federated learning.</li></ul><h3>Backed by the Community</h3><p>OSA CON is powered by the teams who build and maintain the tools developers rely on daily.</p><p><a href="https://altinity.com/">Altinity</a>, the second largest contributor to the ClickHouse project, and <a href="https://preset.io/">Preset</a> (the SaaS version of Apache Superset) headline as Platinum Sponsors.</p><p>VELODB, DataKitchen, Fiveonefour are sponsors, and Open Source Observability Day, Packt, Clowder Space, Airflow Summit, The Untitled Data Company, Abracadata, and Beam Summit are community partners.</p><h3>Ready to Join?</h3><p><a href="https://osacon.io/schedule/">Registration is open</a>! Mark your calendar for November 4–5, 2025, and get ready to learn, connect, and contribute to the open-source analytics movement.</p><p><em>Originally published at </em><a href="https://dev.to/altinity/join-osa-con-2025-two-days-of-open-source-analytics-and-ai-nov-4-5-5503"><em>https://dev.to</em></a><em> on October 20, 2025.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=393191f307d2" width="1" height="1" alt=""><hr><p><a href="https://medium.com/altinity/join-osa-con-2025-two-days-of-open-source-analytics-and-ai-nov-4-5-393191f307d2">Join OSA CON 2025: Two Days of Open‑Source Analytics and AI (Nov. 4–5)</a> was originally published in <a href="https://medium.com/altinity">Altinity</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Introducing Altinity Ice — A Simple Toolset for Iceberg REST Catalogs]]></title>
            <link>https://medium.com/altinity/introducing-altinity-ice-a-simple-toolset-for-iceberg-rest-catalogs-c0d1b9de7e60?source=rss----fc2a2d760d0f---4</link>
            <guid isPermaLink="false">https://medium.com/p/c0d1b9de7e60</guid>
            <category><![CDATA[data-lake]]></category>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[apache-iceberg]]></category>
            <category><![CDATA[open-source]]></category>
            <category><![CDATA[data-lakehouse]]></category>
            <dc:creator><![CDATA[AltinityDB]]></dc:creator>
            <pubDate>Sat, 06 Sep 2025 09:31:42 GMT</pubDate>
            <atom:updated>2025-09-06T09:31:42.272Z</atom:updated>
            <content:encoded><![CDATA[<h3>Introducing Altinity Ice — A Simple Toolset for Iceberg REST Catalogs</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/768/0*_R9nu5XF76sWc4nl.png" /></figure><p>As readers of this blog know, Altinity’s <a href="https://altinity.com/project-antalya/">Project Antalya adapts ClickHouse</a>® <a href="https://altinity.com/project-antalya/">to use Apache Iceberg tables</a> as native storage. We use Apache Iceberg tables extensively, which also means we depend on Iceberg REST catalogs. Along the way, we discovered something surprising: There aren’t a lot of simple tools to start Iceberg catalogs and load Parquet files into them.</p><p>So…we wrote the tools ourselves. Today we’re proud to introduce <a href="https://github.com/Altinity/ice">Altinity ice</a>, an open source project to set up Iceberg REST catalogs and ingest data using two simple commands. The <strong>ice-rest-catalog</strong> command runs an Iceberg REST catalog, and the <strong>ice</strong> command loads data into Iceberg. With these two commands and a bit of scripting you can set up an Iceberg data lake in practically any location. It will soon be a part of Altinity.Cloud, which will help ensure project ice is robust no matter where you run.</p><p>This blog explains ice and how to use it with Project Antalya as well as upstream ClickHouse. We invite you to try it out and help us make it better.</p><h3>What exactly is an Iceberg REST catalog?</h3><p>Catalogs are a key part of managing tables in data lakes. To understand what an Iceberg Rest Catalog is, let’s start by splitting up the name into its constituent parts.</p><ul><li>Apache <strong>Iceberg</strong> is an <a href="https://delta.io/blog/open-table-formats/">open table format</a>, which is a protocol for managing tables on object storage. Iceberg tables consist of data, usually Parquet files, and metadata, which describes tables and identifies which data files belong to each.</li><li>An <strong>Iceberg Catalog</strong> is a service that lets users find the metadata for Iceberg tables. It corresponds to a DATABASE in ClickHouse, which is a list of tables.</li><li>An <strong>Iceberg REST Catalog</strong> is a catalog that has a REST interface, so that clients can call it easily across the network.</li></ul><p>Libraries that access Iceberg files use the catalog to manage table metadata, which they read to find table files and query them. Here’s a simple diagram that shows the steps to update a table and record the change in the catalog. These steps happen under the covers in most Iceberg libraries, so you don’t have to worry about the exact details of what’s happening.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/767/0*OtnYaf3ibB0G_-UU.png" /></figure><p>Reading Iceberg tables works in essentially the opposite order. Clients fetch the current table metadata location from the catalog, read the metadata files, and then fetch table data. For more details look at the Apache Iceberg <a href="https://iceberg.apache.org/spec/#overview">Table Spec</a> and <a href="https://editor-next.swagger.io/?url=https://raw.githubusercontent.com/apache/iceberg/main/open-api/rest-catalog-open-api.yaml">REST Catalog Spec</a>.</p><h3>Why Is Altinity Creating a New Catalog Implementation?</h3><p>There are plenty of catalogs already for Iceberg, so we weren’t looking to create yet another. That said, we needed a portable toolset to set up Iceberg catalogs-and more generally Iceberg <em>data lakes</em> -in any location: from laptops to CI/CD pipelines to cloud platforms. In addition to other uses we will host it in Altinity.Cloud so that our users can easily use Iceberg tables for shared storage.</p><p>We’re also big fans of simple CLIs with minimal dependencies. The alternative is to write custom programs with low-level calls and complex dependencies each time you want to manage Iceberg tables or load data. It’s far more productive to use a generic tool that executes these operations in any location with single line commands.</p><p>Our implementation is a wrapper over the Iceberg Java API classes that implement the Iceberg REST catalog service and Iceberg APIs. This has one more benefit. The Java API is effectively the reference implementation of the Apache Iceberg specification. Ice is a convenient educational tool for learning about Iceberg itself.</p><h3>Installing ice</h3><p>Ice builds generate two executables: <strong>ice-rest-catalog</strong> and <strong>ice</strong>. Here’s how to install them on Linux.</p><p>First, ensure you have installed Java 21 or greater. Here’s the installation command for recent Ubuntu versions. (For a more general cross platform installation <a href="https://adoptium.net/installation/">check out Temurin</a>.)</p><pre>sudo apt install openjdk-21-jdk</pre><p>Next, install the latest ice and ice-catalog-builds from the ice <a href="https://github.com/Altinity/ice/releases">GitHub releases page</a> using the handy commands shown there. Find the latest version and use that. Here are examples for ice 0.2.0.</p><pre>curl -sSL https://github.com/altinity/ice/releases/download/v0.2.0/ice-0.2.0 \<br>  -o ice &amp;&amp; chmod a+x ice &amp;&amp; sudo mv ice /usr/local/bin/<br>curl -sSL https://github.com/altinity/ice/releases/download/v0.2.0/ice-rest-catalog-0.2.0 \<br>  -o ice-rest-catalog &amp;&amp; chmod a+x ice-rest-catalog \<br>  &amp;&amp; sudo mv ice-rest-catalog /usr/local/bin/</pre><p>Check the installation of your treasures using the handy -V option.</p><pre>$ ice -V<br>0.2.0<br>$ ice-rest-catalog -V<br>0.2.0</pre><p>There are no other dependencies. You are ready to start using ice.</p><h3>Trying ice Out Using Docker</h3><p>The ice project includes easy-to-follow examples for a variety of environments. We’ll work through examples using docker compose followed by a command line setup using devbox.</p><p>If you just want a quick tour that works practically anywhere, <a href="https://github.com/Altinity/ice/tree/master/examples/docker-compose">the docker example</a> is for you. It sets up an Apache Iceberg data lake using docker compose. The topology looks like the following. Ports are mapped externally so you can connect to containers from outside.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/745/0*3-U_HmS7NbhnNnSQ.png" /></figure><p>This example depends on docker and docker-compose. If you don’t have them already, installation instructions are <a href="https://docs.docker.com/desktop/">here</a>.</p><p>Next, clone the ice project code and cd to the <a href="https://github.com/Altinity/ice/tree/master/examples/docker-compose">docker-compose example directory</a>.</p><pre>git clone https://github.com/Altinity/ice <br>cd ice/examples/docker-compose</pre><p>Bring up the example cluster with docker compose. If you are curious about what’s being installed, have a look in the docker-compose.yaml file beforehand. It’s short and easy to read.</p><pre>docker compose up</pre><p>Once the cluster is up, invoke ice to add a Parquet file to the nyc.taxis table in Iceberg. The <strong>ice insert</strong> commands creates the table automatically if it does not already exist.</p><pre>ice insert nyc.taxis -p \<br>https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2025-01.parquet</pre><p>The docker-compose.yaml file thoughtfully configures a prewired database named ice that connects ClickHouse to the REST catalog. You can use docker exec to run queries directly in the ClickHouse container as shown in the following example. The queries count the rows and show the ice database definition.</p><pre>docker exec -it docker-compose-clickhouse-1 clickhouse client<br>...<br>SELECT count(*) FROM ice.`nyc.taxis`;<br><br>SHOW CREATE DATABASE ice FORMAT Raw;</pre><p>Clean up by bringing docker compose down and deleting log files. Sudo is necessary due to root ownership of logs generated by docker containers.</p><pre>docker compose down -v &amp;&amp; sudo rm -rf data/</pre><p>The docker example is great because it runs practically anywhere. On the other hand, it hides important details like how ClickHouse connects to the Iceberg REST catalog, which is important to understand what’s actually happening with your data. It’s time to understand how things are wired together under the covers.</p><h3>Digging Further Into Iceberg With ice And Project Antalya</h3><p>For deeper exploration let’s run another example at the Linux command line. Running on the command line allows us to see the details of setting up an Iceberg data lake and accessing it, including logs and configuration files.</p><p>We are going to set up an Iceberg REST Catalog and an S3 compatible object store, load data, and query it using Altinity’s project Antalya. It’s a variation on <a href="https://github.com/Altinity/ice/tree/master/examples/scratch">the ice scratch example</a> with slightly more flowery language. Here’s a picture of what the configuration will look like.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/644/0*v8u56OSOhboT2dFd.png" /></figure><p>Let’s start by cd’ing to the scratch examples directory and starting a devbox shell. If you don’t have devbox already, check out the <a href="https://github.com/jetify-com/devbox">devbox GitHub project</a> for instructions on installation. (Devbox is kind of like npm for the Linux shell. The packages to install are defined in the devbox.json file.)</p><pre>cd ../scratch<br>devbox shell</pre><p>Invoke commands to bring up S3-compatible object storage (minio) with a bucket to hold data and start the Iceberg REST catalog. <strong>Important note</strong>: local-minio and local-mc are wrapper scripts. They are handy but are not guaranteed to remain exactly the same over time. Check the <a href="http://readme.md/">README.md</a> for current instructions if you run into problems.</p><pre>local-minio &amp;<br>local-mc mb --ignore-existing local/bucket1<br>ice-rest-catalog &amp;</pre><p>Congratulations! You have a fully functional Iceberg data lake running from the command line. We can now study its habits.</p><h4>Loading and Describing Table Data</h4><p>Let’s use the same command as in the docker example to load in data from the famous NYC taxi dataset. It’s best to run it in a different window so you can see the log messages easily.</p><pre>ice insert nyc.taxis -p \<br>https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2025-01.parquet<br>…<br>2025-05-30 10:25:20 [main] INFO o.a.i.SnapshotProducer &gt; Committed snapshot 3714649723353136168 (MergeAppend)</pre><p>Use <strong>ice describe</strong> to get information about the data you just loaded.</p><pre>ice describe<br>- kind: &quot;Table&quot;<br>  metadata:<br>    id: &quot;nyc.taxis&quot;<br>  data:<br>    location: &quot;s3://bucket1/nyc/taxis&quot;<br>…</pre><p>The output shows a table. We mentioned earlier that <strong>ice insert</strong> automatically creates a table definition if it does not already exist. It does so by introspecting the Parquet schema of the first file loaded and converting it to an Iceberg schema definition.</p><h4>Accessing The Data Using Project Antalya Binary</h4><p>Let’s count the data and have a look at some stats using a Project Antalya binary. If you don’t have Project Antalya packages already installed, look up the latest binary from the Altinity Project Antalya repo at <a href="https://builds.altinity.com/">builds.altinity.cloud</a>. Download it by any convenient method. Here’s an example for pulling an amd64 binary using wget.</p><pre>wget https://builds.altinity.cloud/antalya-bin-repo/amd64/v25.2.2.28788.altinityantalya/self-extracting/clickhouse \<br>&amp;&amp; chmod +x clickhouse</pre><p>Now we can fire up clickhouse local to access the data we loaded using ice.</p><pre>./clickhouse local<br>Decompressing the binary..................<br>ClickHouse local version 25.2.2.28788.altinityantalya (altinity build).</pre><p>Inside clickhouse local, we’ll use CREATE DATABASE to connect to the catalog using the Iceberg engine. <strong>Note: </strong>ClickHouse version 24.4+ uses the DataBaseCatalog engine. The older Iceberg engine syntax will continue to be supported in newer versions of Project Antalya builds, so you don’t have to change your DDL commands.</p><pre>SET allow_experimental_database_iceberg = 1;<br><br>DROP DATABASE IF EXISTS ice;<br> <br>CREATE DATABASE ice<br>ENGINE = Iceberg(&#39;http://localhost:5000&#39;)<br>SETTINGS <br>  auth_header = &#39;Authorization: Bearer foo&#39;, <br>  storage_endpoint = &#39;http://localhost:9000&#39;, <br>  warehouse = &#39;s3://bucket1&#39;;</pre><p>You can now start to explore data in Iceberg.</p><pre>SHOW TABLES FROM ice;<br><br>SELECT count() FROM ice.`nyc.taxis`;</pre><p>That’s it. We’ve set up object storage and a Iceberg REST catalog, added Parquet data into an automagically created Iceberg table, and queried the data using clickhouse local. After conducting experiments you can clean up as follows:</p><ol><li>Terminate processes for ice-rest-catalog and minio.</li><li>rm -rf data/</li></ol><h3>Tips and Ideas for Further Exploration</h3><p>There are a number of ice features that our examples skipped over.</p><h4>Wildcards</h4><p>You can insert multiple input files using asterisk symbols (*) as wildcards. The following command loads all files for a single day from the <a href="https://registry.opendata.aws/aws-public-blockchain/">AWS BTC public dataset</a>.</p><pre>ice insert btc.transactions -p --s3-no-sign-request \<br>  s3://aws-public-blockchain/v1.0/btc/transactions/date=2025-01-01/*.parquet</pre><p>The following command loads all files for the first day of each month in 2025.</p><pre>ice insert btc.transactions -p --s3-no-sign-request \<br>  s3://aws-public-blockchain/v1.0/btc/transactions/date=2025-0*-01/*.parquet</pre><h4>Partitioning and Sorting</h4><p>Ice supports Iceberg <a href="https://iceberg.apache.org/spec/#partitioning">partition</a> and <a href="https://iceberg.apache.org/spec/#partitioning">sorting</a> specifications when inserting data. Execution engines like ClickHouse use this information to optimize queries, for example to prune partitions without looking at files. Here’s an example that provides both. You can see the partitioning at work in the logs-ice will break up the input into separate Parquet files.</p><pre>ice insert nyc.taxis_p_by_day \<br>  -p https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2025-01.parquet \<br>  --partition=&#39;[{&quot;column&quot;:&quot;tpep_pickup_datetime&quot;,&quot;transform&quot;:&quot;day&quot;}]&#39; \<br>  --sort=&#39;[{&quot;column&quot;:&quot;tpep_pickup_datetime&quot;}]&#39;</pre><p>Updates to partition specs on later inserts are not currently supported–once you pick one you have to stick with it.</p><h4>Ice Configuration Files</h4><p>You might wonder how the ice command finds the REST catalog. The connection data is in .ice.yaml, which is stored in the current directory and looks like the following for the scratch example directory.</p><pre>$ cat .ice.yaml<br>uri: http://localhost:5000<br>bearerToken: foo<br>httpCacheDir: data/ice/http/cache</pre><p>There is a similar file for the ice-rest-catalog in the same directory. It provides configuration required to run a REST catalog server.</p><pre>$ cat .ice-rest-catalog.yaml<br>uri: jdbc:sqlite:file:data/ice-rest-catalog/db.sqlite?journal_mode=WAL&amp;synchronous=OFF&amp;journal_size_limit=500<br><br># To use etcd instead of sqlite, start etcd with `etcd --data-dir=data/etcd`, then uncomment the line below<br>#uri: etcd:http://localhost:2379<br><br>warehouse: s3://bucket1<br><br>s3:<br>  endpoint: http://localhost:9000<br>  pathStyleAccess: true<br>  accessKeyID: miniouser<br>  secretAccessKey: miniopassword<br>  region: minio<br><br>bearerTokens:<br>- value: foo<br><br>anonymousAccess:<br>  enabled: true<br>  accessConfig: {}Querying table metadata data</pre><h4>Querying Table Metadata Data</h4><p>As we showed previously, you can fetch summary table metadata with ice using a command like the following.</p><pre>ice describe nyc.taxis_p_by_day</pre><p>If you want full information, you can query the REST catalog directly using <a href="https://curl.se/docs/tutorial.html">curl</a>. The output will show the table schema, partitioning, and ordering. Piping the output through <a href="https://jqlang.org/">the jq processor</a> makes it somewhat human readable.</p><pre>curl http://localhost:5000/v1/namespaces/nyc/tables/taxis_p_by_day | jq</pre><p>The HTTP response also includes the URLs for JSON metadata files. You can fetch metadata files out from object storage for closer inspection using any tool that can copy from S3. Here’s how to download a file from minio using the mc-local wrapper included in the ice scratch example.</p><pre>local-mc cp local/bucket1/nyc/taxis_p_by_day/metadata/00002-df48b90c-8ad6-4a97-8300-e53b59336ae1.metadata.json .</pre><p>Just transform the s3:// prefix to local/. This might not be super obvious if you have not used Minio previously.</p><h4>Gaining a Deeper Understanding of Apache Iceberg</h4><p>Ice lets you set up Iceberg data lakes anywhere you want. One of the key benefits for beginners is that you can now explore Apache Iceberg freely. Iceberg behavior is a lot easier to understand when you see it in action in a dev environment where you can examine the code and also access metadata and data files easily.</p><p>Speaking of code, it’s easy to bring up ice Java code in an IDE to examine and change code. I use <a href="https://code.visualstudio.com/download">VS Code</a> on WSL2 for Java work. Install the Java Extension Pack and Debugger and you should be ready to go. Here’s the command I use to get started.</p><pre>cd ice<br>code . </pre><p>That’s it. Any IDE that can read maven pom.xml files should be similarly quick to start. Have fun!</p><h3>Conclusion for Now and More to Come</h3><p>This blog introduced <a href="https://github.com/Altinity/ice">ice</a>, our new toolkit for setting up and loading data into Apache Iceberg catalogs. It is simple to set up and use. We invite you to try it out and help us make it better. Join our <a href="https://altinity.com/slack">Slack Workspace</a>, <a href="https://github.com/Altinity/ice/issues">log issues on the project</a>, and <a href="https://github.com/Altinity/ice/pulls">submit PRs on the code</a>. We welcome contributions to all Altinity projects.</p><p>To recap, we showed off a couple of toy examples to help readers get started, but Ice is not a toy. We are using it in <a href="https://altinity.com/managed-clickhouse/">Altinity.Cloud</a> to integrate Apache Iceberg tables as shared object storage for ClickHouse applications. The roadmap includes features up to and including compaction. Our next article will show you how to use Ice on Kubernetes to load large datasets that you can use for testing.</p><p>In the meantime I would like to thank the Altinity team including Stas Shyiko, Kanthi Subramanian, and Alexander Zaitsev. It’s been really fun writing this blog about your work.</p><p><em>Originally published at </em><a href="https://altinity.com/blog/introducing-altinity-ice-a-simple-toolset-for-iceberg-rest-catalogs"><em>https://altinity.com</em></a><em> on June 4, 2025.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c0d1b9de7e60" width="1" height="1" alt=""><hr><p><a href="https://medium.com/altinity/introducing-altinity-ice-a-simple-toolset-for-iceberg-rest-catalogs-c0d1b9de7e60">Introducing Altinity Ice — A Simple Toolset for Iceberg REST Catalogs</a> was originally published in <a href="https://medium.com/altinity">Altinity</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>