ClickHouse on a live Iceberg copy of MySQL: exact rows, slower reads, an Athena fix

DBTrail is an open source tool that follows MySQL’s binary log and keeps an analytical copy of your tables. It can export that copy as Apache Iceberg tables every minute. We wanted to know if ClickHouse could read those tables, so reports run on data a minute old and MySQL never sees the query.

It can, and the rows come out exact. The catch is compaction, which means rewriting a table’s many small files into a few, with the deleted rows already removed. ClickHouse can’t compact these tables, so every export leaves more files for every query to read. In half an hour our report query went from 0.18 s to 8.1 s. ClickHouse’s caches make it about four times faster, but it keeps climbing. Altinity’s Antalya build, which we tested alongside, behaves the same way. What fixed it was compacting with Amazon Athena: one statement, 4 seconds, and the query was fast again.

The setup

One m7g.xlarge on AWS (4 cores, 16 GB) running MySQL 8.0.46, DBTrail 0.125.3 and ClickHouse 26.10.3.7. The orders table had 100,000 rows and took around 15 changes a second: updates, deletes, inserts, text going to NULL and back, and every 10 seconds an UPDATE that changes the primary key. A second table held one row whose timestamp MySQL rewrote every second, to measure how far behind ClickHouse was.

DBTrail exported both tables once a minute, and we ran three tests:

1. Local disk for an hour (60 exports), with ClickHouse reading the files on the same machine.
2. S3 plus the AWS Glue Data Catalog (the AWS service that records where each table’s current version lives), which is what you’d run in production. After 30 exports we compacted with Athena and let the exports keep going.
3. The same S3 setup, with a second machine running Project Antalya 26.6.8.20002 against the same tables at the same time. Both machines ran the report query every 20 seconds, once with default settings and once with every cache on.

Every command, the IAM role and the load script are in the benchmark procedure in our public repository.

What worked

You create the table in ClickHouse once and it picks up each export by itself. With one export a minute, ClickHouse was 7 to 71 seconds behind on S3 and 3 to 70 seconds behind on local disk.

And the data is right. At the end of each run we stopped the writes and compared both sides row by row. Every row matched in all three runs (103,447 on local disk, 101,949 on S3, 101,964 in the third run on ClickHouse and Antalya, with and without caches), including the rows whose primary key had changed.

Reads get slower with every export

Every export adds a few files to the Iceberg table: the new versions of the rows that changed, plus delete files that mark the old versions as gone. Readers must open all of those files and apply the deletes, and nothing rewrites the table, so each export adds work.

On S3 the report query (a count and a sum per status over the whole table) was 45 times slower after 29 exports. Each number is the median of the measurements at that many exports, from the first two runs:

On S3 each extra file is another round trip. Local disk holds up longer, but still bends upward.

Iceberg keeps past versions of a table, called snapshots, until someone expires them. In the third run we pointed the same query at the snapshot the 30th export wrote, the last one before Athena compacted the table. This is a single run in ClickHouse’s web UI:

What about caches?

ClickHouse and Antalya can cache Iceberg metadata, Parquet metadata and the files themselves on local disk. In the third run we turned them all on and kept running the query with defaults too. These default columns come from the third run, so they differ a little from the S3 column above (the second run).

Caches made the query about four times faster, but it still grew 16 times in half an hour. Medians:

(Antalya started reading after the 8th export.) The cache saves the trip to S3, but the query still opens the same files and applies the same deletes. A single run with every cache on, same snapshot:

ClickHouse 26.10 can’t compact these tables yet

On the local disk run, after 60 exports, we tried the Iceberg settings the ClickHouse 26.10 docs describe for read performance and compaction.

None of them brought the query down, and the two that rewrite data refused the table:

The first three settings help ClickHouse find new files, but the time goes into reading them. Merging index files doesn’t touch the data files. Data compaction is experimental in ClickHouse, and it refused our table with Unsupported snapshot's operation type OVERWRITE.

Is DBTrail’s exporter the cause? Each DBTrail export writes new data files plus equality delete files in one Iceberg commit with operation overwrite. That is the standard way to commit row-level changes in Iceberg, engines that run MERGE or UPDATE produce the same kind of snapshot, and Athena compacts it fine. In 26.10, ClickHouse’s experimental compaction only handles other snapshot types.

S3 gave the same result. Through the Glue catalog, ClickHouse refuses before it looks at the table (OPTIMIZE is not supported for catalog-backed Iceberg tables in this build). Pointed straight at the S3 path, it gives the overwrite error again:

Compacting with Athena

Athena’s OPTIMIZE rewrites the data files and applies the deletes:

OPTIMIZE shop.orders REWRITE DATA USING BIN_PACK;

One statement took the query from 8.1 s back under a second. In the S3 run, after 30 exports, it took 4.2 seconds on the orders table, and the report query dropped to between 0.23 and 0.76s (0.45s median). ClickHouse picked up the compacted table through Glue without us doing anything, and every export during and after the compaction committed.

The third run took the same 4.2 seconds. In single runs, the same query on the same data went from 8.58 s to 0.18 s:

You have to repeat it. The exports keep coming and the time creeps back up: in the third run the query took 0.14 to 0.20 s right after the compaction and about 0.5 s five exports later. How often depends on your export rate and table size; watch the query you care about and compact before it gets slow.

Project Antalya

Project Antalya is Altinity’s ClickHouse build for data lakes, an Apache 2.0 branch of upstream ClickHouse. It adds swarms of stateless servers that share one query’s reading, plus the caches above and AWS Glue and S3 Table Buckets as catalogs.

Antalya improves reads, but compaction, the main problem here, isn’t part of it today. In 26.6.8.20002, the current release in early November 2026, OPTIMIZE merges manifest files (release notes), which is the step that didn’t help us above.

We tried it on the same table anyway. Antalya read it about as fast as ClickHouse (single runs at 30 exports: 7.47 s with defaults, 1.61s with every cache) and refused to compact it with the same error:

The recipe

1. Export the tables to S3 and register them in Glue. You need DBTrail 0.125.3 or later (its command line tool is called bintrail), capture running, and a baseline, which is a full snapshot of each table. See Export to Iceberg:

AWS_REGION=us-west-2 bintrail export iceberg \
  --index-dsn "user:pass@tcp(index:3306)/bintrail_index" \
  --baseline-dir /data/baselines \
  --catalog glue --warehouse s3://your-bucket/iceberg \
  --every 1m

Each MySQL schema becomes a Glue database of the same name.

2. Let ClickHouse use the machine’s AWS credentials.
By default ClickHouse won’t use the server’s own credentials (instance role, environment) for S3 in user queries, and the read below fails with ACCESS_DENIED. Give it credentials or allow the server’s:

<!-- /etc/clickhouse-server/users.d/s3-server-credentials.xml -->
<clickhouse><profiles><default>
  <s3_allow_server_credentials_in_user_queries>1</s3_allow_server_credentials_in_user_queries>
</default></profiles></clickhouse>

On Antalya 26.6.8.20002, skip this. That build doesn’t have the setting, won’t start with this file in place (CANNOT_LOAD_CONFIG), and uses the machine’s role without it.

3. Point ClickHouse at the Glue catalog, so it always reads the current version of each table, compacted or not:

SET allow_experimental_database_glue_catalog = 1;
CREATE DATABASE glue ENGINE = DataLakeCatalog
  SETTINGS catalog_type = 'glue', region = 'us-west-2';

SELECT status, count(), sum(amount)
FROM glue.`shop.orders`
GROUP BY status;

4. Compact with Athena on a schedule. Run the OPTIMIZE statement above for each table from anything that can start an Athena query on a schedule, such as EventBridge Scheduler. DBTrail keeps exporting while it runs.

What we did not test

• Tables larger than 100,000 rows, and a schedule that compacts over days rather than once.
• Spark or other engines for the compaction.
• Antalya swarms, which spread one query over several machines.

If we missed a setting or got something wrong, please open an issue on the benchmark procedure. We’ll rerun the tests when ClickHouse or Antalya can compact overwrite snapshots.