Experiment: querying large Zarr datasets directly from PostgreSQL/PostGIS without ETL

News & Announcements
1

Experiment: querying Zarr directly from PostgreSQL/PostGIS

I’ve been working on a Zarr FDW for PostgreSQL and I’d like to get feedback from people who work with real Zarr datasets.

The idea came from a workflow I keep seeing:

PostgreSQL / PostGIS
        ↓
query IDs, geometries, metadata
        ↓
Python
        ↓
xarray / Zarr
        ↓
subset, decode, aggregate
        ↓
return results to the application

I wanted to see how much of that bridge could disappear if PostgreSQL could query the Zarr store directly.

So the experiment is pretty simple:

Can PostgreSQL/PostGIS act as a query layer over large Zarr datasets while the arrays stay in object storage?

I’m building this as a native FDW in a fork of Supabase Wrappers. It is still experimental and the upstream PR is not merged.

What works so far

The implementation can currently read Zarr v2 and a bounded subset of Zarr v3, including indexed v3 sharding.

Storage support currently includes S3-compatible stores, anonymous HTTP(S), and a restricted local filesystem backend.

It also has:

  • named N-dimensional arrays

  • dimension selection by coordinate value or physical index

  • value/index lists and ranges

  • coordinate and chunk pruning

  • lazy chunk iteration

  • bounded parallel reads

  • query-local compressed chunk caching

  • missing chunk and fill-value handling

  • common v2/v3 codec pipelines

  • CF decoding for packed value arrays

  • CF time decoding for the currently supported calendar

  • CRS discovery

  • aggregate pushdown for count, sum, avg, min, and max

  • PostGIS point sampling and zonal statistics

  • initial OME-Zarr 0.5 multiscale support

  • Zarr-specific I/O metrics in EXPLAIN ANALYZE

The main thing I’m trying to avoid is treating every array cell as a PostgreSQL row.

For example, if a query asks for a small spatial and temporal subset, the execution path is roughly:

SQL predicates / selectors
        ↓
dimension selection
        ↓
chunk pruning
        ↓
read only the relevant chunks
        ↓
decode
        ↓
exact predicate checks
        ↓
return rows or update an aggregate

For something like AVG(), the FDW can aggregate while scanning the chunks instead of sending millions of values back to PostgreSQL just to reduce them there.

Why PostgreSQL?

I’m not trying to replace xarray. If I’m doing general array analysis, I’d still reach for xarray first.

What I’m interested in is the point where Zarr data needs to meet data that already lives in PostgreSQL or PostGIS.

For example:

  • rainfall over watersheds stored in PostGIS

  • model values near weather stations stored in PostgreSQL

  • climate exposure for infrastructure or customer assets

  • EO statistics over farm polygons

  • spatial and temporal aggregation over a remote climate cube

Something like:

SELECT
    region.id,
    stats.mean
FROM regions region
CROSS JOIN LATERAL zarr_zonal_stats(
    ...,
    geometry => region.geom
) stats;

The source array stays in object storage. The FDW figures out which chunks are needed and reads those.

Where it still breaks

There are still some important limitations.

Ordinary arrays currently expect numeric rank-1 coordinates. String or categorical coordinates such as band labels are not supported yet.

Curvilinear coordinates, multidimensional coordinates, rotated grids, interpolation, and multi-variable scans are also not there yet.

Spatial operations currently target rectilinear rank-2 grids, and polygon statistics use cell-center inclusion rather than fractional cell coverage.

Coordinate CF packing is not decoded yet, and the time model currently supports one temporal dimension with a limited calendar implementation.

OME-Zarr support is still intentionally narrow.

For unsupported scientific semantics, I’m trying to make the FDW fail explicitly rather than quietly return something that looks reasonable but is wrong.

What I’d like to test next

At this point I’m much more interested in breaking this against real datasets than adding features based on guesses.

If you work with a public or shareable Zarr dataset that has any of the following, I’d be interested in trying it:

  • lots of chunks

  • Zarr v3 sharding

  • pressure, depth, ensemble, or band dimensions

  • non-Gregorian calendars

  • categorical coordinates

  • curvilinear grids

  • CF-packed coordinates

  • large public S3 or HTTP stores

  • workflows where you currently have custom glue between Postgres/PostGIS and xarray

I’m especially interested in two questions:

What query would actually make Zarr access from PostgreSQL useful in your work?

What assumption in this implementation would immediately break on your datasets?

The branch is here:

I’d appreciate dataset suggestions, weird edge cases, performance problems, or reasons this approach would not fit the way you use Zarr. That’s the kind of feedback I’m looking for now.

2

Curious if you were aware of the efforts in GitHub - xqlsystems/duckdb-zarr: Query Zarr stores with SQL directly from DuckDB · GitHub