`ducklake_merge_adjacent_files` very slow

[alastairrushworth]

What happens?

Apologies, I can't provide a reprex for this. I've got a large (500Gb+) datalake, with postgres as the metadata catalog, and about 2k partitions. I've tried to manage writes in large chunks, to avoid lots of small parquet, but it is sometimes hard to avoid.

When I run ducklake_merge_adjacent_files it can take a very long time to run and finish - at least 3 hours, sometimes much longer. Are there ways to manage this process, or to point the compaction at specific partitions (for example). I tried max_compacted_files => 100 but it didn't appear to speed things up, and I'm a little unclear from the docs what this does to know whether it will help for my case.

While this compaction runs, I noticed that both RAM and CPU usage is very low - should I expect the process to run in parallel, respecting the SET threads TO X parameter. Any tips or pointers would be great, I suspect I might have misunderstood how some of this works!

To Reproduce

na

OS:

windows

DuckDB Version:

1.4.3

DuckLake Version:

de813ff

DuckDB Client:

python

Hardware:

No response

Full Name:

alastair rushworth

Affiliation:

creative data technolgies, ltd

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have not tested with any build

Did you include all relevant data sets for reproducing the issue?

No - I cannot share the data sets because they are confidential

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have

[Alex-Monahan]

Do you have many deletion files in your DuckLake? Maybe it would be possible to send an anonymized export from some of your DuckLake catalog tables to show how many files and what sizes, etc?

[duckdblabs-bot]

Thanks for opening this issue in the DuckLake issue tracker! To resolve this issue, our team needs a reproducible example. This includes:

• A source code snippet which reproduces the issue.
• The snippet should be self-contained, i.e., it should contain all imports and should use relative paths instead of hard coded paths (please avoid /Users/JohnDoe/...).
• A lot of issues can be reproduced with plain SQL code executed in the DuckDB command line client. If you can provide such an example, it greatly simplifies the reproduction process and likely results in a faster fix.
• If the script needs additional data, please share the data as a CSV, JSON, or Parquet file. Unfortunately, we cannot fix issues that can only be reproduced with a confidential data set. Support contracts allow sharing confidential data with the core DuckDB team under NDA.

For more detailed guidelines on how to create reproducible examples, please visit Stack Overflow's “Minimal, Reproducible Example” page.

[alastairrushworth]

I think I've made some progress on understanding why this was running slowly. I'm not able to easily produce a reprex or share any exports. But I think the issue may be straightforward to explain.

The issue I've been having comes from compaction of a single, large partitioned table.

• Pre-compaction there were around 120k files across 10k-ish partitions
• Around 5% of the files were large - in the range of [50 - 500Mb], the rest were tiny, mostly [1-100Kb]
• The large files come from a large one-off backfill, where I was careful to ensure the inserts were sorted to avoid fragmentation within partitions
• The smaller files come from incremental updates

The skewness in file sizes seemed to be slowing things down somehow. I think this because the following speeded things up:

• Updated duckdb to the pre-release version1.5.0.dev228, and updated the ducklake extension to the nightly version
• Ran ducklake_merge_adjacent_files over the table, set max_file_size => 1000000 (this arg didn't seem to be available in the ducklake version I had been using, which is why I updated)
• Targetting just the smaller files, compaction completed in 1.5hrs

I think this solves my near-term issue, and I'm happy to close the issue.

I have a couple of questions following this. It looks as though for partitioned tables, compaction is all or nothing ie. there's no way to run merge_adjacent on subsets of partitions - for example, 'compact N most fragmented partitions'. I was also wondering whether there is a way for the partitioning to print progress - on longer runs, I was left wondering if thiings might have stalled - perhaps there's a verbosity argument I should set on the duckdb connection?

Thanks again for all the hard work. ducklake is incredible, and has been phenomenal for some work I've been doing!

[YuweiXiao]

Hi @alastairrushworth Did you try inline data setup? It might help in case of large amount of small files.

the doc: https://ducklake.select/docs/stable/duckdb/advanced_features/data_inlining

[alastairrushworth]

hey @YuweiXiao yes that's 100% on my todo list. It wasn't a higher priority because I couldn't see how to control the flush threshold to suit the situation I have with a large number of partitions. Specifically, even with a high row threshold for flushing (like 40M+), writes into many partitions with inlining will still be very small (I think - please correct me if I'm wrong about how flushing works with partitioned tables).

[YuweiXiao]

Ah yes, you're right. Inline happens at the table level instead of the partition. While Postgres can handle GB-sized table, large amounts of inline data hurts both read/write performance. Also, even GB-size flushing threshold might be too small compared to your 10k partitions. This is indeed a valuable and challenging usecase.