DuckDB Zero-Copy SQL on Pandas and Polars

This article explores how DuckDB runs high-performance SQL queries directly on in-memory Pandas and Polars DataFrames without duplicating data in RAM. By leveraging standard memory layouts, pointer sharing, and the Apache Arrow format, DuckDB accesses existing memory buffers in place. Readers will learn the mechanics behind DuckDB's pointer-level scanning, its interaction with the Python buffer protocol and Arrow C Data Interface, and how this architecture eliminates serialization overhead during analytical query execution.

The Foundation: Shared Memory Architecture

Traditional analytical pipelines incur substantial overhead when moving data between Python libraries and external database engines. Standard processes involve serializing data to disk or streaming it over inter-process communication (IPC) channels, which doubles memory footprint and consumes CPU cycles.

DuckDB overcomes this limitation by acting as an in-process columnar database. Because DuckDB executes within the exact same process and address space as the host Python environment, it can directly read memory addresses allocated by other Python libraries. To query a DataFrame without copying it, DuckDB relies on two mechanisms: the Python Buffer Protocol (for NumPy-backed data) and the Apache Arrow standard (for Arrow-backed data).

How DuckDB Queries Polars DataFrames

Polars organizes its data natively using the Apache Arrow memory specification. Because Apache Arrow standardizes columnar in-memory data representations, DuckDB and Polars inherently share the same logical layout for contiguous arrays, validity bitmaps, and nested types.

When a SQL query targets a Polars DataFrame, DuckDB executes the following sequence:

1. Scope Inspection and Pointer Handshake: When executing a query such as duckdb.sql("SELECT * FROM polars_df"), DuckDB looks up the variable polars_df within the local Python frame.
2. Arrow C Data Interface: DuckDB requests the Arrow export of the DataFrame. Polars implements the Arrow C Data Interface and the Arrow PyCapsule protocol (__arrow_c_stream__). This interface exports C-level pointers to the array structures (data buffers, null bitmaps, and type metadata) without cloning the data.
3. Internal Arrow Scan: DuckDB maps these shared pointers directly into its physical execution plan using an internal Arrow scanner operator.
4. Chunk-by-Chunk Vectorized Execution: DuckDB pulls vectors of data directly from Polars' memory chunks into its vectorized execution engine. DuckDB processes vectors (typically 2,048 tuples at a time) in CPU cache without writing intermediate data back to RAM.

Because no memory allocation or deep copy takes place, query latency matches the raw speed of memory reads, and memory usage remains flat.

How DuckDB Queries Pandas DataFrames

Pandas data structures are historically backed by contiguous NumPy arrays, though modern versions can also use PyArrow backends. DuckDB handles both variants using tailored zero-copy scanning strategies.

NumPy-Backed Pandas DataFrames

For standard Pandas DataFrames backed by NumPy:

• Buffer Protocol Access: DuckDB inspects the underlying NumPy 1D arrays forming each column. Using Python's internal C-API and Buffer Protocol, DuckDB extracts raw memory pointers pointing to the array's data buffer.
• Contiguity Checks: If the memory layout is contiguous (C-style) and the data type is a primitive numeric type (e.g., int64, float64), DuckDB maps the memory region directly to a DuckDB vector.
• Exceptions Requiring Masking/Casting: If a column uses Pandas object types (e.g., Python strings) or non-contiguous data slices, a pure pointer-level read is not feasible. In these instances, DuckDB must construct native string vectors or resolve Python object pointers, which introduces minimal conversion overhead compared to primitive columns.

Arrow-Backed Pandas DataFrames

When Pandas utilizes the ArrowDtype engine (or when converted via PyArrow), DuckDB treats the DataFrame similarly to a Polars DataFrame. It consumes the underlying Arrow RecordBatch references via the Arrow C Data Interface, enabling complete zero-copy reads across all supported datatypes, including strings and nested structures.

Filter and Projection Pushdown

DuckDB does not need to scan an entire DataFrame into its internal structures before evaluating SQL statements. Instead, its optimizer pushes query operations directly into the memory scanning phase:

• Projection Pushdown: If a query requests only three columns out of a hundred (SELECT a, b, c FROM df), DuckDB retrieves pointers only for those three columns, leaving the remaining ninety-seven untouched in memory.
• Filter Pushdown: DuckDB evaluates predicates (WHERE x > 100) directly on the source memory buffers during the chunked scan. Rows that fail the condition are dropped immediately before entering subsequent pipeline stages like joins or aggregations.

Summary of Zero-Copy Query Mechanics

The zero-copy pipeline operates through a tightly coupled execution sequence:

[ Polars / Pandas (Arrow) ] ──(Arrow C Data Interface)──> [ Memory Pointers ]
                                                                   │
                                                                   ▼
[ DuckDB SQL Query ] ───────> [ Vectorized Engine ] <──── [ Direct In-Memory Read ]
                                       │
                                       ▼
                              [ Result / Aggregation ]

By executing inside the Python process, leveraging standardized memory formats like Apache Arrow, and using C-level pointer references, DuckDB eliminates data duplication and serialization overhead, allowing instant, memory-efficient SQL analysis over active DataFrames.