📺 Prefer to watch? 90-second YouTube Short · 💬 Telegram
Originally published on software-engineer-blog.com.
Take one dataset — call it LandGrid, 200 million land parcels, each with a polygon and about forty attributes — and ask it two questions.
Question one, from a surveyor: "Parcel 4,182,930 got resurveyed. Move its eastern boundary two metres and save it."
Question two, from an analyst: "Total assessed value of every residential parcel in the country, grouped by county."
Same data. Same disk. One question finishes in a millisecond, the other takes six minutes and reads 400 GB. That is not a tuning problem, and no index will fix it. It's the storage layout telling you the truth: your geodata has two shapes, and you are only storing one of them.
First principles: what a row actually is on disk
Forget databases for a second and think about bytes.
In PostGIS, a parcel is a row, and a row is stored contiguously. Parcel ID, owner, county, zoning code, assessed value, thirty-five more fields, and then the polygon geometry as WKB — all glued together, one after another, in the same disk page.
That layout has one enormous virtue: everything about one feature is in one place. The surveyor's query hits an index, the index points at a page, one read pulls the whole parcel, you edit it in place, you commit. Row-oriented storage plus an R-tree index is the correct answer to "fetch this one thing and change it." This is OLTP, and PostGIS is excellent at it.
Now run the analyst's query against that same layout. It needs exactly two columns: zoning and assessed_value. But the columns are glued into the rows. To read two fields from 200 million rows you must walk 200 million rows — which means dragging all forty fields off disk, geometry included, because the polygon sits physically between the value you want on this row and the value you want on the next one.
You needed 2 of 40 columns. You read 40. And the largest of them, the geometry, was never even referenced by the query.
An index cannot rescue this. Indexes exist to avoid looking at rows. The analyst's query looks at every row on purpose. Nothing is being looked up; everything is being read. So the only thing left to change is the physical layout itself.
Flip the layout
Store the file column by column instead of row by row. All 200 million zoning codes contiguously, then all 200 million assessed values, then all the geometries in their own block at the end.
Now the analyst's query reads two contiguous runs of bytes and stops. That is column pruning, and it is not a clever optimisation — it falls straight out of the layout. The geometry column is never touched because the reader never has to step over it.
You get a second win for free. A column holds one kind of value, so a run of bytes is 200 million zoning codes rather than an alternating mess of ints, strings and blobs. Dictionary encoding, run-length encoding and general-purpose compression all work far better on homogeneous data than on interleaved records. Columnar geospatial files routinely land several times smaller than the row-oriented equivalent.
This layout is Apache Parquet. It is a file format, not a database, and it has been the standard analytical file in the data world for a decade.
GeoParquet is a convention, not a database
Here is the part people get wrong. GeoParquet is not a new format, not a fork of Parquet, not a query engine, and not "an OLAP system." It is a thin metadata convention written on top of ordinary Parquet.
A GeoParquet file is a Parquet file with a geo key in the file-level metadata. That key declares:
- the primary geometry column (and any secondary ones),
- the encoding — WKB by default, or native GeoArrow since GeoParquet 1.1,
- the CRS, as PROJJSON (not a bare EPSG integer),
- the geometry types present,
- the bbox of the data,
- and the edge semantics — planar or spherical.
That's essentially it. The consequence is the good part: any Parquet reader can still open the file. Pandas, Spark, BigQuery, a plain parquet-tools dump — none of them break. They just see a binary column. A geo-aware reader looks at the geo metadata and knows those bytes are polygons in a specific CRS. It's the same trick as WKB inside a database column, moved up to the file level.
import duckdb
con = duckdb.connect()
con.execute("INSTALL spatial; LOAD spatial;")
con.execute("INSTALL httpfs; LOAD httpfs;")
con.sql("""
SELECT county, SUM(assessed_value) AS total
FROM 'landgrid.parquet'
WHERE zoning = 'RES'
GROUP BY county
ORDER BY total DESC
""").show()
No server, no import step, no CREATE TABLE. The engine opens the file, reads the footer, and touches two columns.
Row groups, statistics, and the 1.1 bbox column
Column pruning gets you from 40 columns to 2. The next win is skipping rows — and this is where Parquet's internal structure matters.
A Parquet file is cut into row groups: horizontal slices, typically tens to hundreds of megabytes. Each row group stores each column as a separate chunk, and — crucially — each chunk carries statistics: min, max, null count.
So a reader handling WHERE assessed_value > 1000000 opens the footer, walks the row-group statistics, and discards whole row groups whose max is below the threshold without decompressing a single byte of them. This is predicate pushdown, and it happens before any real I/O.
For geometry, min/max on a WKB blob is meaningless — which is why GeoParquet 1.1 added the bbox covering column: a struct column of xmin, ymin, xmax, ymax per row, whose own Parquet statistics per row group give you the spatial extent of that row group. A bbox query can now drop row groups the same way a numeric filter does.
-- with a 1.1 bbox covering column, this prunes row groups
-- instead of decoding 200 million polygons
SELECT * FROM 'landgrid.parquet'
WHERE bbox.xmin < 8.6 AND bbox.xmax > 8.4
AND bbox.ymin < 47.5 AND bbox.ymax > 47.3;
The Hilbert trap: skipping is only as good as your sort order
Now the part nobody warns you about, and the single biggest reason a GeoParquet file underperforms in practice.
Row-group skipping only works if the rows were physically sorted before the file was written.
Write LandGrid in insertion order — the order the parcels happened to arrive, county by county over twenty years, or worse, arbitrary — and every row group ends up holding a random scattering of parcels from all over the country. Every row group's bbox is therefore roughly the whole country. Every row group overlaps your query. Nothing gets skipped. You scan the entire file and wonder why the format everyone praised is slow.
The fix is to sort on a space-filling curve — usually Hilbert — at write time, so that rows near each other in 2D end up near each other in the file:
COPY (
SELECT *, ST_Extent(geom) AS bbox
FROM parcels
ORDER BY ST_Hilbert(
geom,
ST_Extent(ST_MakeEnvelope(5.9, 45.8, 10.5, 47.8))
)
) TO 'landgrid.parquet'
(FORMAT PARQUET, COMPRESSION ZSTD, ROW_GROUP_SIZE 100000);
Now each row group covers a compact patch of ground, its bbox is tight, and a query over one city touches a handful of row groups instead of all of them.
The important property to internalise: this is a write-time decision. In a database, you can add an index to an existing table whenever you like. Here, the sort order is the index, and it is baked into the byte layout. You cannot bolt it on afterwards — you rewrite the file. Anyone handing you a GeoParquet file has already decided how fast your spatial filters will be.
Cloud-native: the index lives inside the file
Because Parquet's footer sits at a known place and the footer records the byte offset of every column chunk in every row group, a reader can work over HTTP range requests. Fetch the footer. Read the statistics. Decide which chunks you need. Fetch exactly those byte ranges from object storage.
No server. No database process. No API in front. A file on S3, R2 or Azure Blob is a queryable dataset, and the "index" is not an external structure — it is inside the file.
That's why Overture Maps distributes the entire planet as GeoParquet on object storage and lets you query a city from a laptop without downloading the world. It is the same philosophy that COPC applies to point clouds: keep the spatial organisation inside a single self-describing file, and let range requests do the rest.
The honest limits
GeoParquet is the scan shape. It is genuinely bad at the other job, and pretending otherwise leads to painful architectures.
- No in-place UPDATE. Parquet files are immutable. Changing one parcel means rewriting a file (or at best a partition). Correcting one boundary by rewriting a 40 GB file is absurd.
- No transactions, no concurrent writers, no table semantics — unless you layer Iceberg or Delta Lake on top, which add a metadata layer giving you snapshots, schema evolution and row-level updates over the same Parquet files.
- Weak at single-feature lookups. Fetching one parcel by ID means scanning row-group statistics for an ID that may be anywhere. A database index does this in microseconds.
- Not the best live bbox format. For "give me the features in this viewport, right now, over HTTP", FlatGeobuf usually wins: it carries a packed Hilbert R-tree, so a client does a couple of range requests straight to the matching features. GeoParquet's granularity stops at the row group.
| PostGIS (row-oriented) | GeoParquet (columnar) | |
|---|---|---|
| Physical layout | Row contiguous on disk | Column contiguous on disk |
| Best at | Fetch/edit one feature | Scan/aggregate everything |
| Index | B-tree + R-tree, added anytime | Sort order, fixed at write time |
| Update one feature |
UPDATE in place |
Rewrite the file |
| Transactions | Yes (ACID) | No (unless Iceberg/Delta) |
| Reads 2 of 40 columns | Reads all 40 | Reads 2 |
| Serving model | Server process + connection | Plain file + HTTP range requests |
| Live viewport fetch | Good | Weak (FlatGeobuf better) |
| Typical home | Editing systems, APIs, OLTP | Analytics, distribution, OLAP |
The verdict
If you edit it, it belongs in a database. If you only ever scan it, it belongs in a file.
That is the whole rule, and it is a statement about access pattern, not about technology preference. The surveyor's workload and the analyst's workload are different physical problems, and one byte layout cannot be optimal for both.
Mature geospatial stacks stop trying and keep both shapes: PostGIS as the system of record where features are created and edited, and a GeoParquet export — Hilbert-sorted, bbox-covered, sitting on object storage — as the analytical and distribution copy, refreshed nightly. The database stays small and fast at the thing it is good at. The analyst stops running six-minute queries against production. Nobody argues about which one "wins", because they were never doing the same job.
Top comments (0)