Understanding ST_Geometry vs WKB

Choosing between DuckDB’s native GEOMETRY type and raw Well-Known Binary (WKB) stored in a BLOB column decides whether the planner can push a bounding-box filter into the scan or must decode every row before it can evaluate a single predicate — the difference that, inside spatial indexing internals, turns a sub-second lookup into a full-table topology scan.

GEOMETRY is DuckDB’s strongly-typed spatial object: the engine recognizes it during predicate pushdown, R-tree index construction, and SIMD-accelerated minimum-bounding-rectangle (MBR) evaluation. A BLOB column holding WKB bytes is opaque to the planner — it requires an explicit ST_GeomFromWKB() decode before any spatial operation, allocating a temporary coordinate array on the heap per row.

Root-Cause Analysis #

The performance gap between the two encodings is not a single penalty but a taxonomy of distinct failure modes, each triggered by the planner’s inability to reason about an opaque BLOB:

• No MBR pushdown. Exact predicates such as ST_Intersects and ST_Contains over a BLOB column cannot be pre-filtered by bounding box; the engine materializes full rows and evaluates exact topology on each, forcing $O(N \times M)$ vertex comparisons instead of cheap rectangle overlap.
• Per-row decode allocation. Every ST_GeomFromWKB() call deserializes bytes into a fresh coordinate struct. In high-cardinality joins this produces sustained heap churn and SIMD pipeline stalls that scale linearly with row count.
• Index cannot be built. The R-tree builder only accepts a GEOMETRY column. A BLOB column is simply not indexable — CREATE INDEX ... USING RTREE against it fails, so the most important optimization is unavailable until the column is promoted.
• Storage engine opacity. GEOMETRY vectors participate in columnar compression and direct memory mapping during spill-to-disk under memory pressure; BLOB columns force full-page reads during out-of-core execution, inflating I/O latency by 40–70% when memory_limit is constrained.
• Silent CRS drift. Neither encoding carries an inline SRID. DuckDB tracks no per-geometry coordinate system, so mixing a layer in EPSG:3857 with one in EPSG:4326 yields silently wrong results rather than an error — a failure mode shared with every coordinate-system transformation path.

For datasets above 10M rows, the combined effect of the first two items alone is a measured 3–5× increase in peak memory and sustained CPU utilization versus the native GEOMETRY path.

Deterministic Configuration #

The patterns below need only the spatial extension and a few session knobs. Each setting carries a trade-off relevant to promoting and indexing geometry columns:

INSTALL spatial; LOAD spatial;

-- Cap the working set so a large UPDATE/materialize spills predictably
-- to temp_directory instead of aborting the session with an OOM.
SET memory_limit = '8GB';

-- Match physical cores. Coordinate decode and MBR evaluation are SIMD-bound;
-- hyperthreaded oversubscription only adds scheduling contention.
SET threads = 8;

-- Unlock parallel, out-of-order scans. Safe here because geometry promotion
-- and index builds do not depend on row order.
SET preserve_insertion_order = false;

Optimized Execution Pattern #

The core fix is to stop decoding WKB inside the predicate and instead materialize a native GEOMETRY column once, then index it. Compare the two predicate forms first:

-- BEFORE: WKB path — ST_GeomFromWKB runs once per row, no MBR pruning possible
SELECT id FROM parcels
WHERE ST_Intersects(ST_GeomFromWKB(wkb_col), ST_MakePoint(-73.98, 40.75));

-- AFTER: native GEOMETRY path — planner applies bounding-box pruning in the scan
SELECT id FROM parcels
WHERE ST_Intersects(geom_col, ST_MakePoint(-73.98, 40.75));

The behavioral change is entirely in the column type the predicate sees: in the AFTER form the optimizer recognizes geom_col as GEOMETRY and pushes the MBR test ahead of exact topology, so vertex math runs only on rows whose envelope already overlaps the probe point.

To get there, promote the raw WKB BLOB to a native column and build the R-tree. DuckDB has no ALTER TABLE ... ADD GENERATED column, so add a plain column and populate it:

-- Promote WKB BLOB → native GEOMETRY, then index it.
ALTER TABLE parcels ADD COLUMN geom_col GEOMETRY;
UPDATE parcels SET geom_col = ST_GeomFromWKB(wkb_col);
CREATE INDEX idx_parcels_geom ON parcels USING RTREE (geom_col);

When the source is a fresh ingest rather than an existing table, materialize the native column in a single pass and drop the opaque BLOB entirely:

-- One-pass materialization: exclude the WKB blob, keep only native GEOMETRY.
CREATE TABLE parcels_opt AS
SELECT * EXCLUDE (wkb_col), ST_GeomFromWKB(wkb_col) AS geom_col
FROM parcels;

Most analytical formats avoid the decode step altogether. A GeoParquet file written by a compliant writer already stores geometry as the GEOMETRY extension type, and GeoJSON ingestion via st_read() produces GEOMETRY columns directly — only a generic JSON column needs an explicit ST_GeomFromGeoJSON() call:

-- Direct GeoParquet scan — geometry is already GEOMETRY, no decode
SELECT id, geom FROM read_parquet('s3://bucket/data.parquet');

-- st_read produces GEOMETRY directly from well-formed GeoJSON
SELECT id, geom FROM st_read('s3://bucket/data.geojson');

-- Only a generic JSON column needs an explicit decode + JSON tokenization
COPY (
    SELECT id, ST_GeomFromGeoJSON(geojson_col) AS geom_col
    FROM read_json_auto('s3://bucket/data.json',
                        columns={'id': 'INTEGER', 'geojson_col': 'VARCHAR'})
) TO 's3://bucket/data_optimized.parquet' (FORMAT PARQUET);

The knobs below are the ones worth tuning for the promote-and-index workload specifically:

Diagnostic Queries & Plan Validation #

Before deploying, prove that the column is the type you think it is and that the index is actually traversed. The first diagnostic flags BLOB columns that should have been promoted:

-- Detect BLOB columns that should be native GEOMETRY
SELECT table_name, column_name, data_type
FROM duckdb_columns()
WHERE data_type = 'BLOB' AND column_name ILIKE '%wkb%';

Then confirm the predicate uses the R-tree rather than a sequential scan. An index that exists but is never traversed is the most common invisible regression here:

EXPLAIN ANALYZE
SELECT id FROM parcels
WHERE ST_Intersects(geom_col, ST_MakePoint(-73.98, 40.75));

In the plan output, look for an RTREE_INDEX_SCAN (or an INDEX_SCAN feeding the spatial filter) rather than a SEQ_SCAN over parcels. Key thresholds to watch: if the operator’s actual cardinality is within a few percent of total table rows, the MBR filter is not pruning and the predicate is effectively running against every row — treat that as a failed optimization even if timing looks acceptable on a small sample. The same plan-fingerprint discipline used across spatial joins and proximity filters applies: capture the operator set once and fail the build if a query that previously used the index stops doing so.

Geometry Validation & Fallback Routing #

Promotion only helps if the geometries are valid. Self-intersecting rings and unclosed polygons survive the WKB round-trip and then crash exact predicates downstream, so guard the materialize step with ST_IsValid and repair in place:

-- Only promote valid geometries; repair the rest with ST_MakeValid
INSERT INTO parcels_clean (id, geom_col)
SELECT id,
       CASE WHEN ST_IsValid(g) THEN g ELSE ST_MakeValid(g) END
FROM (SELECT id, ST_GeomFromWKB(wkb_col) AS g FROM parcels_raw);

Because there is no ST_SRID, detect CRS drift by coordinate range before any join — geographic lon/lat data sits within ±180/±90, projected metric data does not:

-- Bucket rows by whether their envelope looks geographic vs projected
SELECT
    ST_XMin(geom_col) BETWEEN -180 AND 180
      AND ST_YMin(geom_col) BETWEEN -90 AND 90 AS looks_geographic,
    COUNT(*) AS row_count,
    MIN(ST_XMin(geom_col)) AS min_x, MAX(ST_XMax(geom_col)) AS max_x
FROM parcels
GROUP BY looks_geographic;

When a layer arrives in the wrong projection, standardize it to a single CRS during ingestion rather than mixing units at query time:

-- Transform a known source CRS to EPSG:4326, validity-guarded
INSERT INTO parcels_clean (id, geom_col)
SELECT id, ST_Transform(geom_col, 'EPSG:3857', 'EPSG:4326')
FROM parcels_raw
WHERE ST_IsValid(geom_col);

If the promote step itself hits memory pressure on a very large table, fall back to chunked execution: partition by a coordinate-derived grid cell and materialize one partition at a time so the working set stays under memory_limit.

-- Process one grid cell at a time to cap peak memory during promotion
INSERT INTO parcels_opt
SELECT * EXCLUDE (wkb_col), ST_GeomFromWKB(wkb_col) AS geom_col
FROM parcels
WHERE floor(ST_X(ST_GeomFromWKB(wkb_col)) / 1.0) = :cell_x
  AND floor(ST_Y(ST_GeomFromWKB(wkb_col)) / 1.0) = :cell_y;

Since DuckDB has no GRANT/role system, enforce access at the boundary once the curated GEOMETRY column exists: expose a view that drops the raw wkb_col and attach the database read-only for analysts.

CREATE VIEW parcels_secured AS SELECT id, geom_col FROM parcels;
ATTACH 'production.duckdb' AS prod (READ_ONLY);

Related #

See also

• Spatial indexing internals — how the R-tree reads the MBR without decoding vertices.
• In-memory vs disk storage — the memory budget that promotion and index builds compete for.
• GeoParquet parsing in DuckDB — the format that skips the WKB decode entirely.

Up: DuckDB Spatial Architecture & Fundamentals