Nick C.

#!/usr/bin/env -S uv run --script
# /// script
# requires-python = ">=3.12"
# dependencies = ["duckdb>=1.5.2"]
# ///
"""
Bug: TRUNCATE TABLE on a large-but-ordinary table runs the duckling out of
memory, so a table can grow past the point where it can ever be truncated.

This script builds a synthetic 69-column table (the shape of our real table)
with chunked INSERT ... FROM range() statements -- which all succeed -- and
then runs TRUNCATE TABLE on it. On a Pulse duckling the TRUNCATE fails with:

    duckdb.duckdb.OutOfMemoryException: Out of Memory Error: failed to pin
    block of size 256.0 KiB (7.4 GiB/953.0 MiB used)

    Consider using a larger duckling size than Pulse or optimizing your query
    to reduce memory usage.

We originally hit this on a real 1.07-billion-row, 69-column table in
database 'fec' (TRUNCATE failed with exactly the message above, June 10-11
2026). DROP TABLE on the same table works fine, so the workaround is
DROP + CREATE, but TRUNCATE (or an unfiltered DELETE) presumably should not
need memory proportional to table size.

Usage: set the motherduck_token env var, then run this file.
The script creates database 'bug_repros' and uses ~30-40 GB of storage. The
table is left in place after the run (so it can be reused on the next run
without rebuilding); re-runs resume building from the existing row count.
Drop it manually when done: DROP TABLE bug_repros.big
"""

import os

import duckdb

DATABASE = "bug_repros"
ROWS = 400_000_000
# Each INSERT is a full MotherDuck transaction commit, so fewer/bigger chunks
# build much faster. INSERT ... FROM range() streams, so memory does not grow
# linearly with chunk size, but if a chunk still OOMs we halve and retry.
MAX_CHUNK = 32_000_000
MIN_CHUNK = 1_000_000

# 69 columns, mostly strings of varying cardinality, like a typical wide
# fact table. (Our real table mixes VARCHAR, enums, DECIMAL, and DATE.)
# The first few strings are high-entropy so the table occupies realistic
# storage (TRUNCATE's memory appetite appears to scale with the table's
# storage size, not just its row count).
N_VARCHAR = 45
N_HIGH_ENTROPY = 5
N_BIGINT = 12
N_DECIMAL = 6
N_DATE = 6


def column_expressions() -> str:
    cols = []
    for j in range(N_HIGH_ENTROPY):
        cols.append(f"to_hex(hash(i + {j})) AS s{j}")
    for j in range(N_HIGH_ENTROPY, N_VARCHAR):
        cardinality = 10 ** (1 + j % 6)  # 10 .. 1_000_000 distinct values
        cols.append(f"concat('s{j}_', i % {cardinality}) AS s{j}")
    for j in range(N_BIGINT):
        cols.append(f"(i * {j + 3}) % 1000000 AS n{j}")
    for j in range(N_DECIMAL):
        cols.append(f"((i % 100000) / 100.0)::DECIMAL(14,2) AS d{j}")
    for j in range(N_DATE):
        cols.append(f"(DATE '2000-01-01' + ((i + {j}) % 9000)::INT) AS t{j}")
    return ",\n        ".join(cols)


def main() -> None:
    md_token = os.environ.get("motherduck_token")
    if not md_token:
        raise RuntimeError("Usage: motherduck_token=... uv run 01_truncate_oom.py")
    con = duckdb.connect("md:")
    con.sql(f"CREATE DATABASE IF NOT EXISTS {DATABASE}")
    con.sql(f"USE {DATABASE}")
    sel = column_expressions()
    con.sql(
        f"CREATE TABLE IF NOT EXISTS big AS SELECT {sel} FROM range(1) t(i) LIMIT 0"
    )
    done = con.sql("SELECT count(*) FROM big").fetchone()[0]
    if done:
        print(f"reusing existing table with {done:,} rows", flush=True)
    chunk = MAX_CHUNK
    while done < ROWS:
        hi = min(done + chunk, ROWS)
        try:
            con.sql(f"INSERT INTO big SELECT {sel} FROM range({done}, {hi}) t(i)")
        except duckdb.OutOfMemoryException:
            if chunk <= MIN_CHUNK:
                raise
            chunk //= 2
            print(f"insert OOMed; retrying with chunk size {chunk:,}", flush=True)
            continue
        done = hi
        print(f"built {done:,}/{ROWS:,} rows", flush=True)
    print(con.sql("SELECT count(*) AS rows_built FROM big"))

    print("running TRUNCATE TABLE big ...", flush=True)
    con.sql("TRUNCATE TABLE big")  # <-- OOMs on a Pulse duckling
    print("TRUNCATE succeeded: bug NOT reproduced at this scale")
    print("note: table is now empty; next run will rebuild it")


if __name__ == "__main__":
    main()