⚠️ Version Note: This article is based on DuckDB v1.5.x. DuckDB v2.0 Preview introduces more complete native partition table support (PARTITION BY syntax), but v1.5.x primarily achieves similar effects through directory organization and predicate pushdown.

DuckDB Partition Tables: Understanding Partition Pruning and Data Management

Have you ever encountered this scenario?

Everything was running fine, until you loaded tens of millions of rows of log data. After that, every aggregation query takes dozens of seconds or even minutes.

You’ve tried adding indexes, tuning parameters, and upgrading hardware, but the data keeps growing.

Understanding partition pruning principles can help you organize data more efficiently.

DuckDB’s Partitioning Mechanism

DuckDB v1.5.x does not natively support PARTITION BY table syntax (introduced in v2.0 Preview). However, in production environments, we can leverage partitioning benefits through the following approaches:

Method 1: Hive-Style Partitioned Directories

Organize data into partitioned directories by time/region dimensions. DuckDB automatically utilizes the directory structure when reading:

import duckdb

# Directory structure:
# /data/orders/year=2023/month=01/
# /data/orders/year=2023/month=02/
# ...

conn = duckdb.connect('orders.db')

# Create external table pointing to partitioned directory
conn.execute("""
 CREATE VIEW orders_view AS
 SELECT * FROM read_parquet('/data/orders/*.parquet')
""")

# DuckDB will automatically optimize path scanning
result = conn.execute("""
 SELECT month, SUM(amount) as total
 FROM orders_view
 WHERE month >= '2023-06'
 GROUP BY month
""").fetchdf()

Method 2: CREATE TABLE AS for Partitioned Data

import duckdb

conn = duckdb.connect('sales.db')

# Create base table
conn.execute("""
 CREATE TABLE orders (
 order_id INTEGER,
 customer_id INTEGER,
 amount DECIMAL(10,2),
 order_date DATE,
 region VARCHAR
 )
""")

# Insert data
conn.execute("""
 INSERT INTO orders
 SELECT 
 gen,
 (random() * 10000)::INTEGER,
 ROUND(random() * 1000, 2),
 DATE '2023-01-01' + (random() * 365)::INTEGER,
 CASE random() * 4
 WHEN 0 THEN 'East'
 WHEN 1 THEN 'South'
 WHEN 2 THEN 'North'
 WHEN 3 THEN 'West'
 ELSE 'Central'
 END
 FROM generate_series(1, 1000000) AS t(gen)
""")

# Check data distribution
print(conn.execute("SELECT count(*) FROM orders").fetchone()[0])
print(conn.execute("SELECT region, count(*) FROM orders GROUP BY region").fetchdf())

Partition Pruning Principle

When queries include filter conditions, DuckDB’s optimizer attempts to push conditions into the data source scan phase, reading only matching data blocks:

-- DuckDB automatically pushes date filter into Parquet file scan
-- Utilizing Parquet zone maps to skip non-matching column chunks
SELECT region, SUM(amount)
FROM read_parquet('/data/sales/*.parquet')
WHERE order_date >= '2023-06-01'
GROUP BY region;

Verifying Optimizer Behavior

Use EXPLAIN to view the query plan:

EXPLAIN
SELECT region, SUM(amount)
FROM read_parquet('/data/sales/*.parquet')
WHERE order_date >= '2023-06-01'
GROUP BY region;

If you see Filter Predicate: order_date >= 2023-06-01 at the scan phase, predicate pushdown is working.

Production Best Practices

1. Use Parquet Format

Parquet files come with built-in zone maps that DuckDB leverages for efficient block skipping:

# Export as Parquet (with partitioning)
conn.execute("""
 COPY (
 SELECT * FROM orders
 WHERE order_date >= '2023-01-01'
 ) TO '/data/export/orders_2023.parquet' (FORMAT PARQUET)
""")

2. Appropriate Partition Granularity

3. Avoid Too Many Small Files

# Merge small files for better read efficiency
conn.execute("""
 CREATE TABLE merged_orders AS
 SELECT * FROM read_parquet('/data/small_partitions/*.parquet')
""")

v2.0 Preview: Native Partition Table Support

DuckDB v2.0 Preview (Alpha) has introduced native PARTITION BY syntax. Future article updates will cover:

• CREATE TABLE ... PARTITION BY RANGE/LIST/HASH
• Automatic partition pruning optimization
• Partition maintenance commands

⚠️ v2.0 is still in Alpha stage; APIs may change. Consider waiting for the official release before using in production.

Summary

Key recommendations:

1. In v1.5.x, achieve partition-like effects through Parquet + predicate pushdown
2. Watch for native partition table features in v2.0 official release
3. Always use EXPLAIN to verify optimizer behavior

DuckDB Official Documentation: https://duckdb.org/docs/ Last verified: 2026-09-12, DuckDB v1.5.2

本文信息

如发现错误，欢迎通过 GitHub Issue 或邮件 contact@duckdblab.org 反馈。