Skip to content

perf: replace expression-tree delete with Arrow subquery via catalog.connection #34

Description

@jghoman

Problem

_build_delete_filter builds a SQL OR-tree by materializing key columns to Python lists and constructing pyducklake expression objects row-by-row:

  • 40,000 Python objects (EqualTo/IsNull/And/Or) for a 10,000-row delete
  • 10× SQL parse round-trips (1,000 rows per chunk × 10 chunks)
  • Balanced Or-tree construction to avoid Python recursion limit

Proposed fix

catalog.connection is a public DuckDBPyConnection. Register the delete keys as an Arrow table (zero-copy) and use a single subquery:

conn = catalog.connection
conn.register("_viaduck_delete_keys", delete_rows)
try:
    cols = ", ".join(f'"{c}"' for c in key_columns)
    tbl.delete(f'({cols}) IN (SELECT {cols} FROM _viaduck_delete_keys)')
finally:
    conn.unregister("_viaduck_delete_keys")

This eliminates _build_delete_filter entirely — no chunking loop, no expression objects, no Python materialization.

Expected improvement

  • 5–20× faster for typical batch sizes (100–10,000 rows, 1–3 key columns)
  • ~95% CPU reduction on the Python side
  • Breakeven vs. expression-tree at ~10–20 rows (below that, hash join setup overhead dominates — not a practical concern)
  • No query planning regression: DuckDB uses the same zone-map scan either way

Note

Not active in the current append-only deployment. Relevant for full-CDC customers.

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions