:memory: vs file-based DuckDB for low-traffic cost trade-off

Reducing cost for a low-traffic analytical workload in DuckDB requires a choice between an in-memory database using ":memory:" and a persistent file.

The in-memory option removes disk I/O and storage cost and can lower read latency, but data is lost on process exit and size is bounded by available RAM. A persistent file provides durability across restarts and can exceed RAM via spill-to-disk, with minimal write overhead for infrequent inserts due to DuckDB's append-only columnar format.

Capacity planning is uncertain because the exact memory threshold at which DuckDB spills intermediate results to disk is not exposed via a PRAGMA, and the interaction of memory_limit with ":memory:" for datasets near RAM limits is unclear.

What conditions trigger spill-to-disk for a file-based database under low-traffic queries? How should memory_limit be set for ":memory:" to avoid out-of-memory failures without over-provisioning RAM? Which configuration preserves durability with the lowest storage and compute cost for a read-heavy workload with occasional inserts?