DELETE on a large partitioned table balloons memory (off-pool, not bounded by memory_limit)

[edsu]

Perhaps I'm doing this the wrong way round, or this is a known issue with DuckLake? I have a DuckLake table records that is partitioned by org. There can be multiple field / value per record_id.

describe records;
┌────────────────────────────────────┐
│              records               │
│                                    │
│ org           varchar default NULL │
│ record_id     varchar default NULL │
│ field         varchar default NULL │
│ value         varchar default NULL │
└────────────────────────────────────┘

I regularly get updates that I need to apply to the lake. So I am first deleting relevant rows:

DELETE FROM records
WHERE org='example'
  AND record_id = 'abc123'

... and then doing the INSERTS.

This seemed to work fine, but once my records table got big (~ 5 billion rows) the memory usage blew through the available RAM, ignoring my memory_limit.

DuckLake: d8a1881
DuckDB: v1.5.5
32 GB RAM

Does anyone have any advice for how to do this update operation more efficiently, and is this expected behavior from DuckLake (or a bug?).

[edsu]

I managed to get through this memory bloating issue by having Claude read the ducklake source code in addition to my code.

Below is a summary of the solution we ended up arriving at. I'm including it here in case someone else runs into a similar issue when doing deletes with a large lake. Also, there is a suggestion for how to tune ducklake internals that may be relevant?

---

The problem wasn't the size of the DELETE — it was the size of the accumulated delete backlog the table was carrying (my ducklake is named 'podlake'):

SELECT count(*) AS delete_files, sum(delete_count) AS tombstoned_rows
FROM __ducklake_metadata_podlake.ducklake_delete_file
WHERE end_snapshot IS NULL;
-- 1535 delete files, 1,492,158,099 tombstoned rows

~1.5B tombstoned rows against ~5B live. A DELETE appears to load the delete history of every data file it opens and hold it for the duration of the statement, so its cost tracked the backlog rather than the rows being deleted. That explains two things that had confused me: a 15k-id DELETE and a 300k-id DELETE behaved identically, and reducing batch size never helped.

Why it accumulated: I had been running expire_snapshots → merge_adjacent_files → cleanup_old_files → delete_orphaned_files to a fixed point, and it reported nothing left to reclaim. But merge_adjacent_files skips files already at the target size, so it never applies deletes on large files — and ducklake_rewrite_data_files, which does, defaults to delete_threshold => 0.95 (rewrite only a file that is already 95% deleted). So routine maintenance looked clean while the backlog grew without bound.

The fix was rewriting with a much lower threshold, stepped down so each pass commits:

SELECT * FROM ducklake_rewrite_data_files('podlake', delete_threshold => 0.5);   -- 1.49B -> 839M,  52s
SELECT * FROM ducklake_rewrite_data_files('podlake', delete_threshold => 0.25);  --       -> 558M,  2m17s
SELECT * FROM ducklake_rewrite_data_files('podlake', delete_threshold => 0.1);   --       -> 105M,  5m08s

93% of the backlog cleared in ~8 minutes. Cost per row rises steeply as the thresholds thin out, so start high and work down. (On releases without max_compacted_files the rewrite can't be bounded, so a large backlog is one all-or-nothing statement — stepping the threshold is the way to get resumable progress.)

The confirmation: the identical DELETE that had exhausted 32GB and swapped completed once the backlog was down — same ids, same partition, same memory_limit.

We now apply deletes as routine maintenance rather than only when disk gets tight, and watch sum(delete_count) as a health metric.

Two suggestions, if useful: consider a lower default for delete_threshold, and/or have the documented maintenance recipe include a rewrite step. A compaction loop that converges and reports "nothing to reclaim" while writes silently degrade is a sharp edge. I guess the trick is knowing what level of rewriting is necessary for the environment where duckdb is running?