DuckDB Spatial Analytical SQL for GIS

Reading Remote GeoParquet over HTTPS and S3

Reading GeoParquet from object storage is not a slower version of reading it locally — it is a different cost model, where latency rather than bandwidth dominates and every avoidable file open is worth more than every avoidable byte. This walkthrough, part of the GeoParquet parsing reference, covers what a remote scan actually requests, the settings that change how many round trips it makes, and how to tell a slow network from a badly laid-out lake.

Root-Cause Analysis: what makes a remote scan slow

  • Every file open is a round trip. The reader fetches the footer before it can decide anything, so a query over ten thousand small files pays ten thousand latencies before it reads a value. On local disk that cost is negligible; over a network it is the query.
  • Ranged reads are the whole mechanism. A remote Parquet scan is a footer fetch followed by ranged requests for the surviving column chunks. Anything that prevents pruning turns those ranges into the whole file.
  • Connection setup is not free. Without keep-alive, each request re-establishes TLS. On a query making hundreds of ranged reads, handshake time can exceed transfer time.
  • Credentials and region lookups add round trips. A misconfigured region causes a redirect per request; an expired credential causes a retry. Both look like slowness rather than misconfiguration.
  • Concurrency is bounded differently. Local reads are bounded by disk queue depth; remote reads by how many requests are in flight. The setting that governs throughput is therefore a different one, and its default is conservative.

The distinguishing question is whether the query is making many small requests or a few large ones. The first is a layout problem; the second is genuinely bandwidth, and only the second is fixed by a faster link.

What a remote scan actually requests List the prefix, fetch each footer, evaluate statistics, range-read the surviving chunks, decode — three of which are latency-bound. STEP COST BOUND BY list the prefix one request per page latency fetch each file footer one round trip per file latency — file count matters evaluate statistics free nothing range-read surviving chunks the large transfers bandwidth decode client CPU cores

Only one row is about bandwidth, and it is the one a faster link improves.

Deterministic Configuration 

INSTALL httpfs; LOAD httpfs;
INSTALL spatial; LOAD spatial;

-- Keep-alive matters more than it looks: without it every ranged read
-- re-establishes TLS, and a scan makes hundreds of them.
SET http_keep_alive = true;
SET http_retries = 3;
SET http_timeout = 30000;

-- Remote reads are bounded by requests in flight rather than disk queue
-- depth, so the useful concurrency setting is a different one.
SET threads = 8;

-- Credentials from the environment; never inline in a query that gets logged.
CREATE OR REPLACE SECRET s3_lake (TYPE S3, PROVIDER credential_chain, REGION 'eu-west-2');

Optimized Execution Pattern

The pattern that matters remotely is the one that reduces requests rather than bytes: prune directories, project columns, and give the scanner statistics it can act on.

-- ANTI-PATTERN: a glob with no partition awareness, selecting everything.
-- This lists the whole prefix, fetches every footer, and reads every column.
SELECT * FROM read_parquet('s3://lake/parcels/**/*.parquet');
-- PATTERN: partition-aware, projected, and filtered on columns that have
-- statistics. Listing, footers and chunks are all reduced.
SELECT parcel_id, land_use, geometry
FROM read_parquet('s3://lake/parcels/**/*.parquet', hive_partitioning = true)
WHERE region = 'west'                          -- prunes the listing itself
  AND year = 2024
  AND land_use = 'residential'                 -- prunes row groups
  AND bbox_xmax >= 2.20 AND bbox_xmin <= 2.45; -- prunes row groups, spatially

The first two predicates are worth more remotely than locally, because they eliminate work before any request is made rather than after a footer has been fetched. That asymmetry is the main way a remote layout differs from a local one.

Four rules that reverse between local and remote File size, directory depth, column cost and the parallelism bound all behave differently once the data is remote. PROPERTY LOCAL REMOTE many small files mildly wasteful the dominant cost directory listing free a paged request per level an extra column plentiful bandwidth another ranged read parallelism bound disk queue depth requests in flight

A layout tuned on a laptop is tuned against three of these four backwards.

Why file count dominates

A footer fetch is a small request with a fixed latency, typically tens of milliseconds. It is unavoidable per file, because the reader cannot decide anything about a file without it. That makes the total floor for a query roughly the number of surviving files multiplied by the round-trip time, before a single byte of data is transferred.

On a lake of two hundred well-sized files that floor is a few seconds at worst and invisible in practice. On a lake of twenty thousand small files it is minutes, and no amount of bandwidth, parallelism or predicate tuning removes it — the requests have to be made. This is why compaction is a more effective remote optimisation than almost anything done in SQL, and why the small-file problem is a remote problem in a way it never quite is locally.

Diagnostic Queries & Plan Validation

Two measurements separate a network problem from a layout problem, and they take a minute.

-- 1. How many files, and how large? A lake averaging under about 16 MB per
-- file is paying more in round trips than it saves in pruning.
SELECT count(*) AS files, avg(size) / 1024 / 1024 AS avg_mb, sum(size) / 1e9 AS total_gb
FROM glob('s3://lake/parcels/**/*.parquet');

-- 2. How much did the query actually need? Compare against the total above.
EXPLAIN ANALYZE
SELECT count(*) FROM read_parquet('s3://lake/parcels/**/*.parquet', hive_partitioning = true)
WHERE region = 'west' AND year = 2024;

A query that touches a small fraction of the files and is still slow is bandwidth- or latency-bound on the link. A query that touches all of them is a layout problem, and the fix is upstream of the SQL.

Four remote symptoms and what each points at Scaling with file count means footers; fast-on-second-run means cache; intermittent failures mean connection settings; uniformly slow means the link. SYMPTOM POINTS AT FIX time scales with file count footer round trips compact the lake fast on the second run only cache behaviour nothing — measure both states intermittent connection errors keep-alive or retries adjust the settings uniformly slow, any selectivity the link itself a faster connection

Only the last row is the network. The first is the one people blame it for.

Materialising a remote dataset locally

For a dataset queried repeatedly, the cheapest remote optimisation is to stop reading it remotely. A single CREATE TABLE ... AS SELECT over the remote files pulls what you need once, and every subsequent query runs against local storage with an index and no round trips at all. That is not always appropriate — the dataset may be too large, or freshness may matter — but it is under-used, because the remote read works well enough that nobody asks whether it should be happening at all.

The middle position is to materialise a projection: the columns and the extent the analysis actually uses, rather than the whole dataset. That is frequently a small fraction of the remote data and turns an hour of repeated remote scanning into one transfer and a local index.

Geometry Validation & Fallback Routing

Where the remote read must stay, the fallback for an unreliable link is to bound the blast radius of a retry rather than to raise the timeout.

-- Partition-at-a-time reads: each is small enough to retry cheaply, and a
-- failure names the partition rather than losing the whole query.
SET http_retries = 5;
SET http_timeout = 60000;

CREATE OR REPLACE TABLE parcels_west AS
SELECT * FROM read_parquet('s3://lake/parcels/region=west/**/*.parquet', hive_partitioning = true);
-- repeat per region; a failure costs one region, not the run

Frequently Asked Questions

Is reading from S3 much slower than local?

For a well-laid-out lake and a selective query, surprisingly little — the reader fetches footers, prunes, and range-reads only the surviving chunks. For a lake of many small files it is dramatically slower, because each file costs a round trip whatever its size. The layout matters far more than the medium.

What is the single most effective setting?

Keep-alive, because a scan makes hundreds of ranged reads and without it each one re-establishes TLS. After that, the region: a wrong one costs a redirect per request, which looks exactly like a slow link.

How large should remote files be?

At least 64 MB, and comfortably more is better. Below about 16 MB the round trip dominates the transfer, so the file is mostly overhead. That threshold is roughly four times the local one, which is why a layout tuned on a laptop underperforms in the cloud.

Should I download the file first?

For a dataset queried repeatedly, often yes — one transfer plus a local index beats many remote scans, and the difference grows with how selective the queries are not. For a one-off selective query over a large lake, the remote read is far cheaper, because it fetches a fraction of the data.

Do credentials affect performance?

Indirectly and sometimes substantially. An expired credential causes a retry per request and a misconfigured region causes a redirect per request; both present as uniform slowness rather than as errors. Checking them is worth doing before tuning anything else.

Does hive_partitioning help remotely more than locally?

Yes, considerably. Locally it saves opening files that are already on the disk; remotely it saves listing and round-tripping them, and the listing itself is a paged request. It is the highest-leverage single change for a remote lake.

Up: GeoParquet Parsing in DuckDB Spatial