The Problem: Why Is Your DuckDB Query So Slow?

When querying Parquet files with DuckDB, the most common performance trap is scanning too many irrelevant files.

Many users write queries like this:

SELECT count(*) FROM 'orders/*.parquet'
WHERE order_date >= '2026-05-01';

This SQL looks correct: read files, then filter by date. But if you have 365 daily-partitioned files, DuckDB will read all 365 files into memory, then discard 364 of them. That’s massive I/O waste.

Worse case: If you use Hive-style partitioned directories (e.g., orders/year=2026/month=05/day=01/), querying without partition pruning means reading the entire dataset from disk even if you only need 1 day.

Real-world benchmark: 100 million order records, daily-partitioned into 365 Parquet files (~35 MB each, ~12 GB total):

Note the asymmetry: data volume dropped 343x but query time only improved 30x. This is because DuckDB has parallel I/O and caching mechanisms that partially mitigate the overhead. Still, in practice, 30x means the difference between “waiting for coffee” and “instant results.”

Core Principle: The Filesystem Is Your Filter

DuckDB’s read_parquet function supports glob path patterns — file wildcard syntax similar to shell *, ?, and [] operators. When you use glob to constrain the file range, DuckDB only touches matching files and never even looks at the rest.

The key insight: Let the filesystem do the work for you.

Traditional WHERE filtering happens in memory — read all data first, then check each row against the condition. With glob patterns, DuckDB excludes non-matching file paths from the scan plan at the optimizer level. It never reads them at all.

This is especially impactful for aggregate queries like count(*), sum(), and avg(), because you don’t care about the contents of excluded partitions.

Basic Usage: Glob Path Patterns

Single glob pattern

-- Query only May 2026 data
SELECT count(*) 
FROM read_parquet('orders/order_date=2026-05-*/*.parquet');

-- Query only May 1, 2026
SELECT count(*) 
FROM read_parquet('orders/order_date=2026-05-01/*.parquet');

-- Query all of 2026
SELECT count(*) 
FROM read_parquet('orders/order_date=2026-*/*.parquet');

Multiple glob patterns (array parameter)

read_parquet accepts an array of strings as its first argument. You can pass multiple glob paths, and DuckDB will merge them while still only scanning those paths:

-- Query first two weeks of May
SELECT count(*)
FROM read_parquet([
  'orders/order_date=2026-05-0[1-9]/*.parquet',
  'orders/order_date=2026-05-1[0-4]/*.parquet'
]);

This is significantly faster than WHERE order_date BETWEEN '2026-05-01' AND '2026-05-14' — the latter would still scan the entire partition directory.

Exclusion patterns

While glob doesn’t natively support exclusion, you can achieve “exclude” effects by combining inclusive globs:

-- Query all May data except May 1st
SELECT count(*)
FROM read_parquet([
  'orders/order_date=2026-05-0[2-9]/*.parquet',
  'orders/order_date=2026-05-1*/*.parquet',
  'orders/order_date=2026-05-2*/*.parquet',
  'orders/order_date=2026-05-3*/*.parquet'
]);

Monthly/quarterly aggregation

Glob patterns shine in reporting scenarios:

-- 2026 Q2 (April-June)
SELECT 
  date_trunc('month', order_date) AS month,
  count(*) AS orders,
  round(sum(total_amount), 2) AS revenue
FROM read_parquet([
  'orders/order_date=2026-04-*/*.parquet',
  'orders/order_date=2026-05-*/*.parquet',
  'orders/order_date=2026-06-*/*.parquet'
])
GROUP BY month
ORDER BY month;

Advanced Usage: Hive-Style Partitioning & Auto Pruning

If your data directory follows the Hive partition naming convention (column_name=value/), DuckDB can automatically detect partition columns and prune at query planning time.

What is Hive-style partitioning?

Hive-style partitioning organizes directories like this:

orders/
├── year=2025/
│   ├── month=01/
│   │   ├── day=01/
│   │   │   ├── part_000.parquet
│   │   │   └── part_001.parquet
│   │   ├── day=02/
│   │   └── ...
│   ├── month=02/
│   └── ...
├── year=2026/
│   ├── month=01/
│   └── ...
└── year=2027/
    └── ...

Using read_parquet with Hive partitioning

-- DuckDB auto-detects year/month/day as partition columns
SELECT count(*) 
FROM read_parquet('orders/*/*/*/*.parquet', hive_partitioning=true);

-- Inspect partition column values
SELECT year, month, day, count(*) AS cnt
FROM read_parquet('orders/*/*/*/*.parquet', hive_partitioning=true)
GROUP BY year, month, day
ORDER BY year, month, day;

With hive_partitioning=true, DuckDB exposes the directory-level year=, month=, and day= keys as virtual columns. When you add WHERE filters on these columns, the optimizer performs automatic partition pruning — reading only the matching directories:

-- DuckDB auto-prunes: only reads year=2026/month=05/
SELECT count(*) 
FROM read_parquet('orders/*/*/*/*.parquet', hive_partitioning=true)
WHERE year = '2026' AND month = '05';

Verify pruning with EXPLAIN:

EXPLAIN SELECT count(*) 
FROM read_parquet('orders/*/*/*/*.parquet', hive_partitioning=true)
WHERE year = '2026' AND month = '05';

Look for delim_pushdown or partition-related entries in the output — these confirm DuckDB is pruning at the file-scan level.

Combining hive_partitioning with union_by_name

When reading Parquet files with varying schemas, combine both parameters:

-- Auto-detect partition columns + auto-merge schemas
SELECT year, month, count(*) 
FROM read_parquet(
  'orders/*/*/*/*.parquet', 
  hive_partitioning=true,
  union_by_name=true
)
WHERE year = '2026'
GROUP BY year, month;

union_by_name=true lets DuckDB handle Parquet files with partially overlapping columns, filling missing columns with NULL.

Real-World Scenario: E-Commerce Query Acceleration

Imagine you run an e-commerce platform generating ~3 million order records daily, stored as daily-partitioned Parquet files.

Unoptimized query (slow)

-- Top 10 products by May sales volume
SELECT 
  product_id,
  product_name,
  sum(quantity) AS total_sold,
  round(sum(amount), 2) AS total_revenue
FROM 'orders/*.parquet'
WHERE order_date >= '2026-05-01' AND order_date < '2026-06-01'
GROUP BY product_id, product_name
ORDER BY total_sold DESC
LIMIT 10;

This query: scan 365 partitions → load 12 GB → filter for May → aggregate → sort. Runtime: ~8-15 seconds.

Optimized query (30x faster)

-- Same logic, with glob partition pruning
SELECT 
  product_id,
  product_name,
  sum(quantity) AS total_sold,
  round(sum(amount), 2) AS total_revenue
FROM read_parquet('orders/order_date=2026-05-*/*.parquet')
GROUP BY product_id, product_name
ORDER BY total_sold DESC
LIMIT 10;

This query: scan 31 partitions → load ~1 GB → aggregate → sort. Runtime: ~0.3-0.5 seconds.

Full performance comparison

The memory savings are especially critical. Loading 12 GB into memory means DuckDB needs 8+ GB at peak for aggregation. After pruning, memory drops below 1 GB — enabling the same analysis on a cheap 4 GB VPS.

Comparison: DuckDB vs Pandas vs Polars

DuckDB’s edge is zero-config + extreme simplicity: write a glob pattern, get 30x speedup. No metastore, no partition table, no complex traversal logic.

Data Production Best Practices

Partition pruning effectiveness depends on how your data files are organized. Here are production practices:

1. Python: write partitioned Parquet

import pandas as pd
import os
from datetime import datetime

def save_partitioned(df: pd.DataFrame, base_path: str, date_col: str):
    """Save DataFrame to date-partitioned Parquet"""
    df[date_col] = pd.to_datetime(df[date_col])
    for (dt,), group in df.groupby(pd.Grouper(key=date_col, freq='D')):
        date_str = dt.strftime('%Y-%m-%d')
        partition_path = f"{base_path}/order_date={date_str}"
        os.makedirs(partition_path, exist_ok=True)
        file_path = f"{partition_path}/data_{date_str}.parquet"
        group.to_parquet(file_path, index=False)
        print(f"Wrote {len(group)} rows → {file_path}")

# Usage
save_partitioned(order_df, "orders", "order_date")

2. DuckDB-native conversion

-- Read raw file, write partitioned output
COPY (
  SELECT * FROM read_parquet('raw_orders.parquet')
) TO 'orders' (
  FORMAT PARQUET,
  PARTITION_BY (order_date),
  OVERWRITE_OR_IGNORE
);

PARTITION_BY (order_date) is one of DuckDB’s most powerful COPY features. It automatically creates order_date=YYYY-MM-DD/ Hive-style directories, ready for glob pruning on reads.

3. Choosing partition granularity

• Daily: Best for incremental data and daily reports. Finest granularity, most precise pruning.
• Monthly: Best for monthly summaries and long-term trend analysis. Fewer partitions, simpler management.
• Weekly: Compromise for weekly reporting.

Guideline: Partition daily if you have 1M+ rows/day; weekly for 100K–1M/day; otherwise, partition as needed.

Common Pitfalls

Pitfall 1: Redundant WHERE + glob

-- ❌ Redundant: glob already narrowed to May 1st
SELECT count(*) 
FROM read_parquet('orders/order_date=2026-05-01/*.parquet')
WHERE order_date = '2026-05-01';

Gilding the lily. But more dangerous is this pattern:

-- ❌ Very slow: glob is too broad, WHERE is the only filter
SELECT count(*) 
FROM read_parquet('orders/*.parquet')
WHERE order_date >= '2026-05-01';

Here glob matches all files; the WHERE clause filters data in memory but doesn’t prevent scanning all files. Move the filter into the glob pattern.

Pitfall 2: ** recursive matching surprises

-- May accidentally load unexpected files
SELECT * FROM read_parquet('**/*.parquet');

** is recursive matching across all subdirectories. If your disk has other Parquet files (temp files, backups), they’ll be loaded too.

Pitfall 3: Non-Hive directories with hive_partitioning

-- ❌ Won't work: no key=value prefix
SELECT count(*) 
FROM read_parquet('orders/2026/05/01/*.parquet', hive_partitioning=true);

Paths like 2026/05/01 lack the column_name= prefix, so DuckDB can’t detect partition columns. Always use key=value/ directory structure.

Monetization Strategies

1. Performance tuning consulting

Many analytics teams running DuckDB hit performance walls because they don’t know these partition pruning techniques. Offer consulting at $200-500/hour to diagnose slow queries:

1. Collect slow queries and Parquet directory structure
2. Analyze scan scope with EXPLAIN ANALYZE
3. Optimize file organization and query patterns
4. Generate before/after performance reports

2. Automated data pipeline tool

Package these techniques into a CLI tool or Python library:

• Auto-detect directory structure and recommend optimal glob patterns
• Re-partition raw Parquet files into Hive-style directories
• Generate equivalent pruning-aware query rewrites

Monetize as a SaaS (per-API-call pricing) or open-source community edition + enterprise tier.

3. Data warehouse migration service

Many companies migrate from Snowflake/BigQuery to DuckDB for cost savings. The biggest migration risk is query performance degradation. Offer a “migration audit” service ensuring migrated queries fully leverage partition pruning, guaranteeing performance matches or exceeds the original warehouse.

4. Video course series

Expand this article into a paid video tutorial series:

• Part 1 (free): Parquet file format basics and partitioning concepts
• Part 2 (free): DuckDB glob path patterns for beginners
• Part 3 (paid): Production-grade partitioning strategy design
• Part 4 (paid): Automated partition management with Airflow/Dagster

Pricing suggestion: $29 per course, $79 for the full series.

Summary: Parquet partition pruning is the highest-ROI DuckDB performance optimization technique available — change one line of code (switch from WHERE filter to glob path) and get 30x faster queries. The core principle: Let the filesystem do the work; don’t let DuckDB filter in memory. Combined with Hive-style partitioning and hive_partitioning=true, you get zero-config automatic partition pruning.

📺 Video tutorial: youtube.com/@duckdblab