DuckDB Storage Strategy: Choosing Between Parquet, Native Files, and In-Memory Tables

The Storage Decision Problem

When integrating DuckDB into an analytical pipeline, the primary architectural decision is where the data lives. Because DuckDB can query external files directly or manage its own internal storage, developers often struggle to decide if they should treat DuckDB as a query engine (reading Parquet/CSV) or a database (using .duckdb files). Choosing the wrong approach leads to either excessive I/O overhead or unnecessary ETL complexity.

Storage Option Comparison

The following table compares the three primary data residency patterns in DuckDB (based on version 1.1.x).

Feature	In-Place Files (Parquet/CSV)	Native .duckdb File	In-Memory (:memory:)
Data Freshness	Immediate (reads disk)	Requires Load/Import	Ephemeral
Query Speed	Moderate (Pushdown dependent)	Fastest (Optimized metadata)	Extreme (No disk I/O)
Portability	High (Open Standard)	Medium (DuckDB format)	None
ACID/Updates	No (Read-only/Overwrite)	Yes (Full Transactions)	Yes (Session-based)
Persistence	External	Internal	None

Evaluating the Trade-offs

Querying Files In-Place

Using functions like read_parquet() allows DuckDB to act as a compute layer over a data lake. The critical advantage here is projection and filter pushdown. Projection pushdown ensures only the requested columns are read from disk; filter pushdown allows DuckDB to skip entire row groups in a Parquet file if the metadata indicates they don't contain the requested values.

This is the ideal choice for raw landing zones or data shared across different tools (e.g., Spark, Pandas, and DuckDB).

Native .duckdb Persistence

A native database file stores data in a highly optimized columnar format tailored specifically for the DuckDB engine. Unlike Parquet, which is designed for archival and interchange, the native format maintains internal statistics and indexes that accelerate repeated queries.

Concurrency Constraint: DuckDB uses a single-writer/multiple-reader model. Only one process can hold a read-write connection to a .duckdb file. Other processes must connect in read_only = true mode. If your application requires multiple concurrent writers, DuckDB is not a replacement for a client-server DB like PostgreSQL.

In-Memory Tables

In-memory mode is best for ephemeral transformations or unit tests. While it is the fastest option, it is bounded by available RAM. For large datasets, DuckDB can spill to disk (out-of-core processing), but the primary benefit of :memory: is the elimination of filesystem latency for temporary scratch-work.

Implementation and Validation

To determine which method fits your workload, you can benchmark the same query across all three modes. This example assumes a dataset of sales records partitioned by year.

Step 1: Querying Parquet In-Place

Run this from the DuckDB CLI or a Python script. This avoids any data loading phase.

-- Run in DuckDB CLI
-- This leverages pushdown to only read the 'region' and 'amount' columns
-- and only the files matching the year 2025 filter.
SELECT region, sum(amount) 
FROM read_parquet('data/sales/*.parquet') 
WHERE year = 2025 
GROUP BY region;

Step 2: Persisting to Native Storage

Convert the external files into a native table to test the performance gain from internalized metadata.

-- Create a persistent database file
-- Command: duckdb sales_analytics.duckdb

CREATE TABLE sales AS 
SELECT * FROM read_parquet('data/sales/*.parquet');

-- Run the same aggregate
SELECT region, sum(amount) 
FROM sales 
WHERE year = 2025 
GROUP BY region;

Step 3: Validation with EXPLAIN ANALYZE

To verify that DuckDB is actually optimizing the Parquet read (and not just scanning everything), use EXPLAIN ANALYZE.

EXPLAIN ANALYZE 
SELECT region, sum(amount) 
FROM read_parquet('data/sales/*.parquet') 
WHERE year = 2025 
GROUP BY region;

What to look for: In the output, check the PARQUET_SCAN operator. You should see a reduced number of columns being read and a filter being applied during the scan phase rather than after the data has been loaded into memory.

Practical Verification Checklist

• Wall-clock timing: Compare the execution time of the Parquet scan vs. the native table query. Native tables typically win on repeated queries.
• Locking test: Try opening sales_analytics.duckdb in two separate terminal windows. Attempting to run a CREATE TABLE in the second window while the first is active should trigger a connection error, confirming the single-writer constraint.
• Memory check: For in-memory tables, monitor system RAM. If the dataset exceeds available memory, DuckDB will use temporary files in the temp_directory.

Rollback and Cleanup

Because the native storage operation creates a physical file on disk, you can revert the state by deleting the database file:

# Run in your system shell
rm sales_analytics.duckdb