Partitioning and File Layout for Spatial Lakes
How a spatial dataset is laid out on disk decides more about query time than any session setting will, and the decision is made once, at write time, by whoever produced the files. This page sits inside the DuckDB Spatial architecture and fundamentals reference and covers the three layers of that decision — directory partitioning, row-group sizing, and write order — as one problem, because they interact: a perfect partition scheme with random write order prunes at the directory level and nowhere else, and a beautifully sorted file split into one giant row group prunes nowhere at all.
The unifying idea is that DuckDB never reads a spatial lake; it reads a subset of one, and every layer of the layout is a mechanism for making that subset smaller. Directory partitioning eliminates whole files before any of them is opened. Row-group statistics eliminate blocks inside the files that survive. Column projection eliminates the columns inside the blocks that survive that. What is left is what the query actually pays for, and a badly laid out lake is one where each of those mechanisms had nothing to work with.
Execution Model & Core Concepts
A partitioned GeoParquet dataset is a directory tree in which each level encodes one column’s value in the directory name — region=west/year=2024/part-0.parquet — and DuckDB reads that structure directly. When a query filters on region, the reader never lists, opens or fetches the footers of the directories that cannot match. This is a categorically stronger form of pruning than statistics, because it happens before any I/O against the file at all, which matters enormously when the lake is on object storage and every file open is a network round trip.
The reduction is several orders of magnitude and none of it comes from a setting.
The interaction between the layers is what makes this a single decision rather than three. Directory partitioning on region and sorting within each file by region is redundant — the second buys nothing the first has not already done. Partitioning on region and sorting by date is complementary, because the two prune on different predicates. And partitioning on a high-cardinality column produces thousands of tiny files, at which point per-file overhead dominates and the layer that was meant to save the most has cost the most.
Configuration Reference
Writing a partitioned dataset needs three settings decided together: the partition columns, the row-group size, and the write order. DuckDB’s COPY ... TO ... (PARTITION_BY ...) handles the directory structure, and the rest is expressed in the query that feeds it.
INSTALL spatial; LOAD spatial;
-- The write is the memory-hungry step: a row group is buffered whole before
-- it is flushed, and a row group of dense geometry is far larger than the
-- row count suggests. Size the ceiling for the buffer, not the input.
SET memory_limit = '12GB';
-- Sorting on write is an external sort. Give it somewhere to spill or it
-- will fail on any dataset worth partitioning.
SET temp_directory = '/var/tmp/duckdb_write';
SET max_temp_directory_size = '80GB';
-- Threads matter more on the write than the read here: each writes its own
-- buffer, so the peak is roughly threads × row-group size.
SET threads = 6;Trade-off Analysis: Raising threads speeds the sort and the encode but multiplies the write-side buffer, because each thread holds a row group in flight. On a geometry-heavy write, six threads at a 122,880-row group can easily hold twenty gigabytes between them. If a write is spilling or being OOM-killed, lowering threads is usually a better first move than raising memory_limit, because it reduces the peak rather than accommodating it.
Choosing partition columns
The rule is short: partition on a low-cardinality column that queries actually filter by, and never on more than two levels unless the second is genuinely coarse. Everything difficult about partitioning is a consequence of violating one half of that.
Tens of values, not tens of thousands, and only on columns queries genuinely filter by.
The failure mode in the third row deserves its own name because it is so common: the small-file problem. Each Parquet file carries a footer that must be read before anything else, so a query over ten thousand small files pays ten thousand footer reads before it has touched a single value. On local disk that is merely wasteful; on object storage, where each read is a network round trip with tens of milliseconds of latency, it is the dominant cost of the entire query and no amount of parallelism hides it.
-- Two partition levels, both low-cardinality and both filtered by real
-- queries. Sorting inside each partition by the *next* most common filter
-- is what makes row-group statistics selective on top of the directories.
COPY (
SELECT * FROM parcels
ORDER BY region, year, land_use -- directories, then within-file order
) TO 's3://lake/parcels'
(FORMAT PARQUET, PARTITION_BY (region, year), ROW_GROUP_SIZE 122880);Sizing row groups for geometry
The default row-group size is a row count, and a row count is a poor proxy for size when the payload is geometry. A row group of 122,880 points is a few megabytes; the same row group of coastline polygons can be several gigabytes. That difference shows up twice — as a write-side buffer that decides whether the write completes, and as a read-side granularity that decides how finely statistics can prune.
The workable heuristic is to target a byte size rather than a row count: aim for roughly 64–256 MB per row group, and derive the row count from the average serialised geometry size. On a table where geometries average 4 KB, that is roughly 16,000–64,000 rows, which is well below the default.
-- Derive the row count from the payload rather than accepting the default.
-- avg_bytes is the average serialised geometry size; the target is a
-- 128 MB row group.
SELECT
avg(octet_length(ST_AsWKB(geom))) AS avg_bytes,
(128 * 1024 * 1024 / avg(octet_length(ST_AsWKB(geom))))::BIGINT AS suggested_rows
FROM parcels;Query Planning & Optimization
The confirmation that a layout is working is a plan that reads fewer files and fewer row groups than exist, and the numbers are visible in EXPLAIN ANALYZE output for the Parquet scan.
-- Look for the file count and the row-group count in the scan node. If the
-- file count equals the total number of files in the lake, directory pruning
-- did not happen — usually because the predicate is on a column that is not
-- a partition key, or is wrapped in a function.
EXPLAIN ANALYZE
SELECT count(*)
FROM read_parquet('s3://lake/parcels/**/*.parquet', hive_partitioning = true)
WHERE region = 'west' AND year = 2024 AND land_use = 'residential';Diagnostic — pruning that silently stopped: the most common cause is a predicate the reader cannot match against a directory name. WHERE year = 2024 prunes; WHERE year::VARCHAR = '2024' does not, because the cast hides the column. WHERE region IN ('west','east') prunes; WHERE upper(region) = 'WEST' does not. The rule is that the partition column must appear bare on one side of a simple comparison.
A layout tuned on a laptop regresses in the cloud for all four reasons at once.
Production Deployment Boundaries
A spatial lake on object storage has a different cost model from one on local disk, and the layout that is optimal for each differs. Object storage charges per request and has high per-request latency, which pushes toward fewer, larger files. Local disk has negligible per-request cost, which permits smaller files and finer partitioning. A layout tuned on a laptop and deployed to S3 frequently regresses for exactly this reason.
The second boundary is write concurrency. A partitioned write from several processes into the same directory tree works only if each process owns distinct partitions — two processes writing region=west will produce files that both exist and neither knows about, which is fine for a reader (it sees both) and disastrous for a re-run (it sees duplicates). The safe arrangement is one writer per partition, with the partition list computed up front, which is the same discipline the batch processing pipelines reference applies at job scale.
Failure Modes & Diagnostics
Diagnostic — the small-file problem: count files and average their size before assuming a slow scan is a compute problem.
-- A lake whose average file is under about 16 MB is paying more in per-file
-- overhead than it saves in pruning. Compaction is the fix, not tuning.
SELECT count(*) AS files, avg(size) / 1024 / 1024 AS avg_mb
FROM glob('s3://lake/parcels/**/*.parquet');Diagnostic — write order lost: statistics prune only when values inside a row group are clustered. A file written without an ORDER BY has every group spanning the full range, and the symptom is that adding a selective predicate changes the runtime not at all.
Diagnostic — partition skew: partitioning by region on a dataset where one region holds 60% of the rows produces one enormous partition and eleven small ones, so the parallelism the layout was meant to enable never materialises. Check the row count per partition, and split the dominant one on a second key if it is badly out of balance.
Diagnostic — the geometry column in the partition key: partitioning on anything derived from geometry per row — a grid cell, a geohash prefix — is tempting and usually produces the small-file problem at scale. Spatial locality belongs in the sort order, where a space-filling curve expresses it without creating a directory per cell.
The cost of getting the scheme wrong
Partitioning is unusual among layout decisions in that it is expensive to change and cheap to get wrong. A row-group size can be corrected by rewriting one file; a sort order by rewriting one partition. A partition scheme is encoded in every object key in the lake, and changing it means reading and rewriting the whole dataset — which is precisely the operation the scheme was meant to make unnecessary.
That asymmetry argues for a conservative first choice. One level, on the coarsest column that queries reliably filter by, is almost never wrong: it prunes usefully, it produces few enough directories that listing stays cheap, and it leaves room to add a second level later without disturbing the first. The schemes that have to be undone are nearly always the ambitious ones — three levels chosen because all three columns appeared in some query, or a level on a column whose cardinality was estimated rather than counted.
-- Count before you commit. Cardinality is the whole decision, and it is one
-- query. Anything above a few hundred distinct values is not a partition key.
SELECT
count(DISTINCT region) AS regions,
count(DISTINCT year) AS years,
count(DISTINCT municipality) AS municipalities,
count(DISTINCT region) * count(DISTINCT year) AS directories_if_both
FROM parcels;The last column is the one to look at, because levels multiply. Twelve regions and ten years is a hundred and twenty directories, which is comfortable. Add a third level of thirty land-use codes and it is three thousand six hundred, most of which will hold a handful of rows — and the lake now has the small-file problem by construction rather than by drift.
Compaction and the Life of a Lake
A partitioned lake fed by incremental writes degrades in a predictable direction: files get smaller and more numerous, and within each file the write order reflects the order of one increment rather than of the dataset. Both effects are gradual, neither errors, and together they undo most of what the original layout was designed to achieve. Compaction is the maintenance job that reverses them, and it is one of the few maintenance jobs whose benefit is straightforward to measure.
The operation is simply a read and a rewrite of one partition, with the sort re-applied and the row-group size re-derived. Doing it per partition rather than over the whole lake keeps each run bounded, restartable, and safe to interleave with reads — the new files are written to a fresh location and swapped in, so readers see one complete version or the other.
-- Compact one partition: read everything under it, re-sort, re-write with a
-- derived row-group size, then swap. Per-partition keeps each run bounded.
COPY (
SELECT * EXCLUDE (region, year)
FROM read_parquet('s3://lake/parcels/region=west/year=2024/*.parquet')
ORDER BY land_use, ST_XMin(geometry)
) TO 's3://lake/_compact/parcels/region=west/year=2024'
(FORMAT PARQUET, ROW_GROUP_SIZE 30000);Two signals decide when a partition is worth compacting, and both are cheap to compute. The first is the file count: a partition holding dozens of files where it should hold a few has accumulated increments. The second is the average file size: anything well below the target means the per-file overhead is now a meaningful share of every scan against it. Neither number needs a threshold argued from first principles — a partition that has drifted an order of magnitude from its designed shape is worth rewriting, and one that has drifted by twenty per cent is not.
Trade-off Analysis: Compaction reads and rewrites data that has not changed, so its cost is proportional to the partition rather than to the increment that triggered it. On a lake with a long tail of rarely-touched partitions, compacting everything on a schedule wastes most of the work; compacting on the two signals above touches only what has drifted. The scheduled form is simpler to operate and is the right default until the waste is measurable.
Frequently Asked Questions
Should I partition by geography?
By a coarse geographic key such as region or country, yes, if queries filter by it. By anything finer — a grid cell, a geohash prefix, a tile — almost never, because the cardinality explodes and you get the small-file problem. Fine-grained spatial locality belongs in the write order, expressed as a space-filling curve, not in the directory structure.
How large should each file be?
Large enough that per-file overhead is amortised, which on object storage means at least 64 MB and comfortably more. Files below about 16 MB spend more time being opened than read. If a partition scheme produces files smaller than that, the scheme has too many levels or too high a cardinality.
Does DuckDB read Hive-partitioned directories automatically?
With hive_partitioning = true it reads the directory names as columns and uses them for pruning. Without it, the directories are just paths and the partition columns are not available to filter on at all — so the setting is not an optimisation, it is what makes the layout mean anything.
Can I add a partition without rewriting the dataset?
Yes — a new directory with correctly named levels is picked up by the next read, which is what makes partitioned layouts convenient for incremental loads. What you cannot do cheaply is change the partition scheme, since that means rewriting every file. Choosing the scheme is therefore a decision worth taking slowly.
How do row groups and partitions interact?
They prune on different predicates and compose. Directories eliminate files on the partition columns; statistics eliminate row groups on whatever the file was sorted by. Using the same column for both is redundant. The productive arrangement is to partition on the coarsest common filter and sort on the next one.
Is compaction worth running?
On any lake fed by frequent incremental writes, yes, because those writes produce small files by construction. A periodic pass that reads a partition and rewrites it as a few large, sorted files restores both the file size and the write order, and it is one of the few maintenance jobs on a lake whose benefit is easy to measure.
Related
See also
- Hive partitioning GeoParquet by region — the write, the read, and how to verify pruning happened.
- Row-group sizing for spatial scans — deriving the row count from the payload.
- Sorting writes with Hilbert curves — spatial locality inside a one-dimensional order.
- GeoParquet parsing — the metadata that makes any of this prunable.
- Batch processing pipelines — one writer per partition, and why.