Partition pruning
Partition pruning is a query optimization that skips reading entire partitions of a dataset when a query's filters make it impossible for those partitions to contain matching rows.
Overview
Large datasets are frequently partitioned — physically split into separate files or directories based on the value of one or more columns, commonly a date. A common convention, popularized by Hive, lays out partitions as directories like year=2024/month=06/. Partition pruning uses a query's WHERE clause to figure out which partitions could possibly contain matching rows and skips reading every other partition entirely, without even opening those files.
How Pruning Works
If a table is partitioned by year and a query filters WHERE year = 2024, a pruning-aware engine only reads files under the year=2024/ partition and never touches year=2023/, year=2022/, and so on. This is far cheaper than filtering after reading, because the pruned partitions' data — and their I/O cost — are avoided completely rather than read and discarded.
DuckDB and Partition Pruning
DuckDB supports Hive-style partition pruning when reading partitioned Parquet or CSV datasets with hive_partitioning enabled (DuckDB also auto-detects Hive partitioning for many layouts):
Copy code
SELECT *
FROM read_parquet('s3://bucket/events/*/*.parquet', hive_partitioning = true)
WHERE year = 2024 AND month = 6;
DuckDB extracts year and month as virtual columns from the directory structure and prunes any file paths whose partition values can't satisfy the filter before ever opening them. When writing data out, COPY ... TO 'path' (FORMAT parquet, PARTITION_BY (year, month)) produces the same Hive-style layout, making downstream partition pruning by DuckDB or other engines possible.
Related terms
Hive partitioning is a directory-naming convention, originally from Apache Hive, that encodes column values in folder paths (like year=2026/month=01/) so query engines can skip irrelevant files without reading them.
PARTITION BY →PARTITION BY divides rows into independent groups for a window function or an output operation, so calculations reset for each group without collapsing rows the way GROUP BY does.
partitions →Partitions in data systems refer to the logical or physical division of large datasets into smaller, more manageable segments.
Apache Hive →Apache Hive is a data warehouse system built on Hadoop that lets users query large datasets stored in HDFS or object storage using HiveQL, a SQL-like language, which Hive translates into distributed jobs.
OVER clause →The OVER clause turns an aggregate or ranking function into a window function by defining the partition, order, and frame it operates on, without collapsing the result set.
COPY statement →The COPY statement bulk-transfers data between a table and an external file, exporting query results to formats like CSV or Parquet, or loading files directly into a table.
FAQS
Partition pruning skips whole files or directories based on values encoded in the file path (like a Hive-style year=2024/ directory). A zone map skips smaller blocks within a file based on stored min/max statistics. Both avoid reading data that can't match, at different granularities.
With hive_partitioning enabled (or auto-detected), DuckDB parses key=value segments from the file path as virtual partition columns, which it can then use to prune non-matching files based on query filters.
