MergeTree Parts Not Merging Automatically with Frequent Inserts, Seeking Optimization Methods

[yidigo]

Hello chdb team,

I am using chdb for a use case that involves periodically inserting small batches of data into a MergeTree table. I have noticed that over time, each insert creates a new, small data part (a separate file/directory on disk).

The Problem:
Unlike a standard ClickHouse server, these small parts do not seem to be automatically merged in the background. This results in a large number of small data parts for a single table. Consequently, the query performance on this table degrades significantly, as the query engine has to read from many different files.

My Questions
Does chdb have an automatic background merge process for MergeTree tables like a standard ClickHouse server? If so, is there any configuration required to enable or tune it?

Or is there a way to manually trigger a merge for all the data parts in a table? Are there any recommended best practices or MergeTree settings for write-intensive scenarios with frequent, small inserts in chdb?

The follows are some screenshots

[yidigo]

I use sql

select table ,partition ,name ,active from system.parts

find

but in data path, there are 7841 directories

[yidigo]

when use sql below some details

SELECT
    part_type,
    path,
    formatReadableQuantity(rows) AS rows,
    formatReadableSize(data_uncompressed_bytes) AS data_uncompressed_bytes,
    formatReadableSize(data_compressed_bytes) AS data_compressed_bytes,
    formatReadableSize(primary_key_bytes_in_memory) AS primary_key_bytes_in_memory,
    marks,
    formatReadableSize(bytes_on_disk) AS bytes_on_disk
FROM system.parts
FORMAT Vertical;

i find 22 partitions and some partition only have one row

[wudidapaopao]

Hi @yidigo, chdb does not modify ClickHouse's compaction behavior, the background thread will automatically compact MergeTree data parts. I conducted tests locally and observed the phenomenon of automatic compaction. The difference is that chdb limits the number of background threads, so compaction will be relatively slow—especially during frequent writes. Therefore, it is recommended to make each write batch as large as possible and reduce the write frequency.

In addition, you can use OPTIMIZE TABLE or OPTIMIZE TABLE FINAL to manually trigger compaction for MergeTree table. For details, please refer to https://clickhouse.com/docs/sql-reference/statements/optimize.