DuckDB backend leaks native memory: execute_query never closes its cursor

[fgregg]

Symptom

warehouse-duckdb (the labordata DuckDB deployment) leaks ~5–13 MB/min of RSS and gets OOM-killed every ~2–3 hours (exit_code=137, oom_killed=true) on its 2 GB machine.

It's a native leak, not Python objects

Instrumented the live process (/proc + an in-process gc probe over 12 min):

• RSS climbed 301 → 451 MB, but tracked Python object count was flat (49,994 → 51,276, +1,282). So the growth is in native (C++) allocations gc can't see.
• Threads (10), FDs (74), and duckdb connections (56) were all flat — not a thread/FD/connection-count leak.
• The Python types that grew monotonically each sample were Statement (~45/min) and the weakrefs (ReferenceType) tracking them — i.e. DuckDB prepared statements, each a thin wrapper pinning a large native allocation (~280 KB × ~540 ≈ the ~150 MB).

Root cause

DuckDBBackend.execute_query does cursor = conn.cursor() per query and never closes it — the finally only cancels the time-limit watchdog:

cursor = conn.cursor()
...
finally:
    if timer is not None:
        timer.cancel()
# cursor never closed

conn is a long-lived pooled read connection. DuckDB tracks child cursors on the parent connection, so each unclosed cursor's prepared statement + native result buffers stay alive for the process lifetime → unbounded native RSS growth. (stream_query's cursor was effectively cleaned because Database.execute_stream closes the whole dedicated connection, but execute_query reuses the pooled connection — so that path is the leak.)

It only manifests under sustained real traffic; synthetic loads (even on the exact prod image, all 14 dbs, 2 GB cap — repeated SQL, unique SQL, streams) plateau and don't reproduce it.

Fix

cursor.close() in execute_query's finally (rows are already materialised into raw before it, so it's safe), and defensively in stream_query's finally (closes on generator exhaustion or client-disconnect GeneratorExit). Smoke-tested: queries / CSV streaming / row pages unaffected.

Fixed in cc1705c6 (duckdb-deploy). Deploying to verify the live RSS slope flattens.

[fgregg]

Root cause found via tcmalloc heap profiling (native backtrace)

cursor.close() alone does not fix this — reproduced the leak on an isolated box (exact prod image + tcmalloc + all 14 dbs) by replaying real captured traffic (synthetic load wouldn't trigger it; the real _memory/cross-db mix does). RSS climbed ~2.7 MB/request. pprof --base early late on the heap dumps (in-use diff over the run, 83 MB grown in the datasette process) is dominated entirely by DuckDB catalog construction:

28%  duckdb::DuckSchemaEntry::CreateFunction
20%  duckdb::TableMacroFunction::Copy
15%  duckdb::DuckTableEntry::DuckTableEntry
10%  duckdb::ScalarFunctionCatalogEntry / AggregateFunctionCatalogEntry
 +   CatalogSet::StartChain, CreateCollation, DependencyManager::CreateDependent/Subject,
     CatalogEntryMap::AddEntry, ART::ART, FixedSizeAllocator::Init, ...

Every one of these is what DuckDB allocates when it builds a fresh catalog — registering built-in scalar/aggregate functions, collations, table macros, and the attached tables. That work happens on new connection/instance creation and on ATTACH. The leak grows per-request because the backend is creating fresh DuckDB connections/instances per request and not releasing the catalog — worst for _memory, where each request re-ATTACHes all ~14 databases (note the DuckTableEntry + TableMacroFunction::Copy weight). It's a native (C++) leak, invisible to Python gc (object count stays flat while RSS climbs ~13 MB/min in prod → OOM every ~2–3h).

So the real fix is connection/instance lifecycle: reuse a long-lived _memory connection (and pooled per-db connections) instead of rebuilding the catalog + re-attaching per request, and/or ensure connections are actually released so DuckDB frees the catalog. cursor.close() (this PR) is still correct but is not the leak. Repro + heap dumps available.

[fgregg]

Resolution: cursor.close (this PR) IS the fix — the "residual leak" was warmup

Followed up with a controlled repro (exact prod image + tcmalloc + all 14 dbs + real-traffic replay) and longer prod observation. Correcting the earlier comment:

• The unbounded leak (DuckDB Statement accumulation from the unclosed cursor) is fixed by cursor.close().
• The catalog-construction backtrace (CreateFunction/TableMacroFunction::Copy) I attributed to a residual leak was warmup, not steady-state: every repeated query test (data, catalog-introspection, same/distinct SQL, params) plateaus. The replay's climb was first-touch warmup across many distinct URLs.
• Prod confirms it: with cursor.close deployed, RSS warmed to ~1.4GB and plateaued (1190MB@51min → 1401@109min → 1394@112min, flat), 112+ min with no OOM (pre-fix: OOM every ~2-3h).

Remaining note (not a leak): the backend opens a separate DuckDB instance per database, each defaulting memory_limit to 1.5 GiB, so on a small (2GB) box the buffer-pool working set runs hot (~1.4GB/2GB) and could OOM under load spikes. Mitigations are operational (lower per-instance memory_limit, more RAM, or fewer instances) — not a code leak. Closing the leak investigation; cursor.close is the fix.

[fgregg]

Deployed: per-instance memory_limit cap (validation in progress)

Root cause confirmed as buffer-pool oversubscription, not a code leak: each database is its own DuckDB instance with an independent buffer pool defaulting memory_limit to ~80% of host RAM (1.5GiB on the 2GB box). Under crawler load touching many databases the pools collectively climbed past ~1.8GB and OOM-wedged the box (~5h cycle), even with cursor.close (which fixed the unbounded Statement leak — that part holds; RSS now plateaus rather than growing without bound).

Fixed by reading an optional memory_limit from plugins.datasette-duckdb config and applying it to every connection (8328e7d6). Deployed at 128MB. Verified live: current_setting('memory_limit') = 122 MiB on file dbs and the _memory crossdb host; crossdb + CSV stream unaffected; 0 cap-induced 5xx over a real-traffic sample. Mechanism is eviction (reloadable cache of immutable files), not spilling. Watching prod RSS to confirm the plateau holds < ~1GB across the old OOM window. Shared-instance global-pool alternative tracked in #31.