DuckDB Spatial Analytical SQL for GIS

Hive Partitioning GeoParquet by Region

A partitioned write that nobody verified is a directory tree that looks organised and prunes nothing — this walkthrough, part of the partitioning and file layout reference, covers the specific operation of writing a regional GeoParquet lake with PARTITION_BY, reading it back with hive_partitioning enabled, and proving from the scan node that files were skipped rather than merely filtered.

Root-Cause Analysis: why a partitioned read still reads everything

Four causes account for nearly every case of a partitioned dataset that does not prune, and they are distinguishable from the query text alone.

  • hive_partitioning not enabled. Without it the directory names are just path segments; the partition columns do not exist as columns, and a predicate on them either errors or matches nothing. This is the only one of the four that usually announces itself.
  • The predicate hides the column. WHERE region = 'west' prunes. WHERE upper(region) = 'WEST' does not, because the reader compares directory names literally and cannot see through a function. The same applies to a cast: year::VARCHAR = '2024' defeats what year = 2024 would have done.
  • The filter is on a column that is not a partition key. Filtering by land_use over a lake partitioned by region prunes nothing at the directory level — that filter can only be served by row-group statistics, and only if the files were sorted by it.
  • The glob does not descend. read_parquet('s3://lake/parcels/*.parquet') matches nothing in a partitioned tree because the files are two levels down. **/*.parquet is what walks the tree, and getting this wrong tends to produce an empty result rather than a slow one.

The distinguishing question is what the scan node reports: a file count equal to the whole lake means the pruning never happened, while a reduced file count with a still-slow query means pruning worked and the cost is elsewhere.

Four reasons a partitioned read does not prune Hive partitioning disabled, a function-wrapped predicate, a filter on a non-partition column, and a glob that does not descend — with the signal that identifies each. CAUSE SIGNAL FIX hive_partitioning off the column does not exist enable it — it announces itself predicate wraps the column full file count in the scan unwrap it; compare bare filter is on a non-key column full file count in the scan sort on write; use statistics glob does not descend an empty result use ** to walk the tree

The two middle rows look identical from the scan node. Knowing which columns are partition keys is what separates them.

Deterministic Configuration

The write is the expensive half, and its peak memory is set by the thread count multiplied by the row-group buffer rather than by the size of the input.

INSTALL spatial; LOAD spatial;
INSTALL httpfs; LOAD httpfs;          -- only needed for object storage

-- Each writer thread buffers a whole row group before flushing. On dense
-- geometry that buffer is the peak, so threads × row_group_size is the
-- number to size against — not the table.
SET memory_limit = '12GB';
SET threads = 6;

-- Sorting on write is an external sort and will spill on any dataset worth
-- partitioning. Local disk only; never a network mount.
SET temp_directory = '/var/tmp/duckdb_partition';
SET max_temp_directory_size = '80GB';

Optimized Execution Pattern

The anti-pattern is a write with no ordering and a read with no partition awareness. Both look correct and together they produce a lake with the storage cost of partitioning and none of the benefit.

-- ANTI-PATTERN: directories exist, nothing inside them is ordered, and the
-- read never learns the directories mean anything.
COPY parcels TO 's3://lake/parcels' (FORMAT PARQUET, PARTITION_BY (region));
SELECT count(*) FROM read_parquet('s3://lake/parcels/**/*.parquet')
WHERE region = 'west';                 -- region is not a column here
-- PATTERN: sort within the partition on the next most common filter, and
-- read with hive_partitioning so the directory names become real columns.
COPY (
    SELECT * FROM parcels
    ORDER BY region, year, land_use
) TO 's3://lake/parcels'
  (FORMAT PARQUET, PARTITION_BY (region, year), ROW_GROUP_SIZE 65536);

SELECT count(*), sum(ST_Area(geometry)) AS area
FROM read_parquet('s3://lake/parcels/**/*.parquet', hive_partitioning = true)
WHERE region = 'west'                  -- directory pruning
  AND year = 2024                      -- directory pruning
  AND land_use = 'residential';        -- row-group statistics, thanks to the sort

The ORDER BY includes the partition columns even though the partitioning already groups by them. That is deliberate: it costs nothing extra, since the sort has to happen anyway for the third key, and it keeps the statement readable as a single statement of intended order rather than as two mechanisms that happen to agree.

Diagnostic Queries & Plan Validation

The verification is a comparison between the number of files in the lake and the number the scan actually opened.

-- How many files exist at all?
SELECT count(*) AS total_files FROM glob('s3://lake/parcels/**/*.parquet');

-- How many did the query touch? Look for the files-read count on the
-- PARQUET_SCAN node. If it equals total_files, no directory pruning happened.
EXPLAIN ANALYZE
SELECT count(*)
FROM read_parquet('s3://lake/parcels/**/*.parquet', hive_partitioning = true)
WHERE region = 'west' AND year = 2024;

Diagnostic — the partition census: a partition scheme that looked balanced at design time is often badly skewed in practice, and the check is one aggregate.

-- Rows and files per partition. One partition holding most of the rows means
-- the parallelism the layout was meant to enable never materialises; a
-- partition holding a handful of rows means the cardinality is too high.
SELECT region, year, count(*) AS rows
FROM read_parquet('s3://lake/parcels/**/*.parquet', hive_partitioning = true)
GROUP BY region, year
ORDER BY rows DESC;
Reading a partition census An even distribution is healthy; one dominant partition means skew; many tiny partitions mean the cardinality is too high; empty partitions point at a filter that leaked into the write. WHAT THE CENSUS SHOWS WHAT TO DO roughly even distribution nothing — this is the healthy case one partition holds over half the rows split it on a second key many partitions of a few thousand rows coarsen the scheme — small-file problem a partition with no rows harmless — but a filter leaked into the write Three safe ways to re-run a partitioned write, and one to avoid Fresh prefix and swap, delete-then-rewrite, or a run identifier in the path — and never a plain re-run, which leaves both file sets and double-counts. APPROACH READER SEES COST fresh prefix, then swap old or new, never a mixture both sets held briefly delete partitions, then rewrite nothing, during the window a visible gap run id in the path, filtered on read whatever it asks for complexity in every query plain re-run into the same tree both sets at once silent double-counting

The fourth row is what happens by default, which is why the first three have to be chosen deliberately.

Geometry Validation & Fallback Routing

Two properties of a partitioned write should be verified before the lake is used, because both fail silently. The first is that the geometry column survived as geometry rather than as an opaque blob, which depends on the GeoParquet metadata having been written. The second is that the row count across all partitions matches the source, which catches a partition that was skipped or a filter that leaked into the write.

-- Verification, both properties in one pass. A mismatch in rows_total names
-- the load; a geometry column reported as BLOB means the metadata is missing.
SELECT count(*) AS rows_total,
       count(*) FILTER (WHERE geometry IS NULL) AS rows_null_geom,
       count(DISTINCT region) AS regions
FROM read_parquet('s3://lake/parcels/**/*.parquet', hive_partitioning = true);

DESCRIBE SELECT * FROM read_parquet('s3://lake/parcels/**/*.parquet') LIMIT 1;

If the write has to be re-run, remember that PARTITION_BY is additive rather than replacing: a second write into a populated tree leaves both sets of files, and every reader sees the union. Clear the target or write to a fresh prefix and swap.

What the directory names cost you

Hive partitioning encodes a column’s value in a path, which has one consequence worth planning around: the value becomes part of the object key, and object keys are immutable. Renaming a region, correcting a mis-spelled code, or changing a year’s representation from two digits to four means rewriting every file under that prefix, because there is no way to rename a directory in object storage — only to copy and delete.

That makes the choice of encoding as durable a decision as the choice of column. Prefer stable codes over display names, prefer a fixed width where the value is numeric, and avoid anything that a downstream system might one day want spelled differently. A partition key that has to be corrected is a full rewrite of the affected prefix, which is exactly the operation partitioning was supposed to let you avoid.

-- Verify what the writer actually produced before the tree hardens: one row
-- per distinct partition path, with its file count and total size.
SELECT regexp_extract(file, 'region=[^/]+/year=[^/]+') AS partition,
       count(*)                                        AS files,
       sum(size) / 1024 / 1024                         AS mb
FROM glob('s3://lake/parcels/**/*.parquet')
GROUP BY 1
ORDER BY mb DESC;

Frequently Asked Questions

Why does my WHERE clause not prune directories?

Almost always because the partition column is wrapped in something. The reader matches directory names literally, so a cast or a function on the column hides it — year = 2024 prunes, year::VARCHAR = '2024' does not. Check that the partition column appears bare on one side of a simple comparison.

Do I need hive_partitioning = true?

Yes, if you want the directory names to be usable as columns. Without it they are inert path segments: the partition columns do not exist in the result, and there is nothing to filter on. It is not a performance flag, it is what makes the layout mean anything.

Can I write into an existing partitioned dataset?

Yes, and that is what makes the layout convenient for incremental loads — a new directory or a new file inside an existing one is picked up on the next read. What it does not do is replace: re-running a write over the same partitions leaves both sets of files and every reader sees duplicates. Clear the partition or write to a new prefix and swap.

How many partition levels should I use?

One, usually; two if the second is genuinely coarse and genuinely filtered by. Each level multiplies the directory count, so three levels of ten values each is a thousand directories before any data exists. If a third level seems necessary, it is usually a sign that the thing you want is sort order rather than partitioning.

Up: Partitioning and File Layout