Grid-Native Hazard Geometry — Eliminate `hazard_centroids`¶

Status: proposed · Date: 2026-08-02 · Driver: no bulk data on the application host

The finding¶

Prod Postgres is 4.32 GB, of which hazard_centroids is 4294 MB. Every other table combined is ~15 MB (spatial_ref_sys 7 MB is a PostGIS built-in; exposures 7 MB). The database is 99.7% one table.

That table holds 7,182,016 rows across 8 datasets. Measured on prod:

7 Aqueduct Greece datasets × exactly 1,026,000 rows each.
All 7 share a byte-identical grid — same md5 dc20f9c1f0851fc2dfbabb7d979097b8 over (lat,lon) ordered by array_index, same bbox, same ordering.
pixel_index == array_index for all 7,182,016 rows, and both form a contiguous 0..1025999. The grids are perfectly dense.

A dense regular grid is fully specified by 8 numbers: the six affine coefficients plus width and height. Every column in those 4294 MB — lat, lon, geometry, pixel_index, array_index, plus a UUID PK and per-row org_id/timestamps — is derivable from those 8 numbers. We are storing 613 MB per dataset to represent a grid definition, seven times.

The arithmetic already exists: core/ingest/base.py:207 _pixel_centers(transform, width, pixel_idx) is vectorized and documented as bit-exact against rasterio.transform.xy for north-up transforms.

Why it looks like this¶

worker/ingest/aggregate_and_commit.py:437 grid_definition_for returns the grid only when the centroid set is sparse, and None when dense — because #442 reasoned that over a full regular grid PostGIS KNN is equivalent to cell membership, so dense datasets could stay on the unchanged KNN path.

That reasoning is correct about assignment behaviour and backwards about storage. The dense case is exactly the case where the grid definition lets you store zero rows. Discarding it forces the materialized point cloud that KNN then needs.

The second half is the split-brain at worker/tasks.py:396-410: the intensity CSR is loaded from the NPZ in object storage, then the lat/lon for those same columns is fetched as 1M Postgres rows on every compute job. And api/v1/results.py:119-135 fetches up to 1M centroid rows to look up a handful by array_index — for 7,631 exposures.

These are positional arrays sharing an axis with the CSR columns. Relational rows are the wrong shape for them.

Target architecture¶

Postgres stores relations and identity. Object storage stores arrays and blobs. Regular grids are stored as definitions, never as materialized points.

Three representations, chosen by dataset kind — none of them a per-point table:

Dataset kind	Geometry representation	Cost
Dense regular grid	grid definition on `hazard_datasets` (6 floats + 2 ints)	~0
Sparse regular grid	grid definition + `pixel_idx` array in the NPZ	~8 MB/1M cells
Irregular point set (e.g. TC)	explicit `lat`/`lon` arrays in the NPZ	~16 MB/1M pts

The NPZ is already the natural home. aggregate_and_commit.py:409 writes it with np.savez_compressed(data, indices, indptr, shape); consumers use plain np.load, so adding keys is backward compatible. The per-chunk intermediate NPZ (base.py:788 serialize_sparse_chunk) already carries pixel_idx, grid_transform, grid_width, grid_height, grid_crs — the final artifact is the only place the geometry gets dropped. We are deleting information we already computed, then paying 4.29 GB to reconstruct it in rows.

Object-store layout (ADR-029 conformant)¶

Unchanged prefixes; the artifact gains keys, not paths:

hazards/{dataset_id}/intensity.npz     # + pixel_idx, grid_*, format_version
processed/{org|public}/{dataset_id}/hazard.tif
tiles/{dataset_id}/{z}/{x}/{y}.{ext}

Authorization note (must not regress)¶

Postgres RLS currently enforces org isolation on hazard_centroids (migrations/versions/0022_row_level_security.py:32). hazards/{id}/intensity.npz has no org segment in its key, so isolation must be enforced at the dataset lookup before the artifact is read — which is already how npz_path is gated. Every new read path must go through the org-scoped dataset fetch, never straight to a bucket key.

Consumer migration¶

Consumer	Today	Target
`tasks.py:402` engine lat/lon	1M-row SQL per job	`_pixel_centers` over NPZ geometry
`centroid_assignment.py` membership	join `hazard_centroids`	pure arithmetic; dense grid needs no join
`centroid_assignment.py` KNN	PostGIS GiST `<->`	only for irregular point sets → scipy KDTree in worker
`results.py:119`, `hazards.py:572`	fetch ≤1M rows	derive lat/lon for the requested indices, O(k)
`hazard_repository.py:296` preview bbox/count	scan rows	exact, from the grid definition
`hazard_repository.py:400` admin aggregation	`ST_Contains` join	precompute at ingest
martin `mvt_hazard_centroids`	PostGIS function	delete — no consumer exists

The MVT path is dead weight, not a dependency: models/layer_spec.py:138 never wires MVT URLs, /v1/hazards/{id}/layers never emits one, and no UI/SDK reference exists. Its removal is pure deletion (migration 0033, the martin service, /v1/tiles/mvt/..., the "mvt" TileType, and their tests).

Separately: convert_to_cog is unreachable in practice — its only caller (tasks.py:938) is gated on params["raw_raster_key"], which no API path ever sets (api/v1/hazards.py:155, :382). That is why all 8 prod datasets have cog_path NULL. Out of scope here; tracked as a follow-up.

Work breakdown¶

Dependencies: 1 → {2,3} → {4,5,6} → 8. Items 7 and 9 are independent.

NPZ v2 — carry geometry in the artifact. Add pixel_idx, grid_transform, grid_width, grid_height, grid_crs, format_version to _store_intensity_npz; add a reader returning (GridSpec | None, pixel_idx). Existing 4 keys untouched. Verify: a freshly ingested dataset's NPZ-derived lat/lon matches its hazard_centroids rows exactly; v1 artifacts still load.
Always stamp the grid definition. Invert grid_definition_for — stamp whenever a grid exists; reserve the null case for genuinely non-grid datasets. Verify: membership assignment yields junction rows identical to the KNN path on the dense reference fixture.
Backfill existing datasets. Derive GridSpec from current rows, assert _pixel_centers reproduces stored lat/lon bit-exactly, then rewrite the NPZ and stamp the grid columns. Dry-run mode; refuse to proceed on any mismatch. Verify: all 8 prod datasets pass the equality gate.
Compute path reads geometry from the artifact. Replace tasks.py:402-410; fall back to Postgres while v1 artifacts exist. Verify: impact result for the demo dataset is numerically identical before/after.
API read paths derive centroids. get_centroids, results.py, hazards.py derive only the indices requested; bbox filtering becomes arithmetic. Verify: /v1/hazards/{id}/centroids and the results GeoJSON are byte-identical.
Admin aggregation without the table. Precompute the admin-unit → array-index mapping at ingest. Verify: same output for the seeded boundary.
Retire MVT + martin. Pure deletion, no consumer.
Drop hazard_centroids. Gated on 1–6 verified against prod. Typed Alembic ops only — both raw-SQL migrations on 2026-08-01 (0063, 0065) broke trunk while the typed 0064 passed first time. Dispatch ci.yml for this merge rather than letting it ride the debounce window.
Host hygiene. Drop the /opt/climate-lama/data bind mount and directory (403 MB of re-derivable ingest cache); ship backups to the bucket and prune locally. Verify: deploy from a clean clone still works; df hits the target.

Expected end state¶

	Before	After
Postgres volume	5.65 GB	~15 MB
`hazard_centroids`	4294 MB	gone
Per-backup size	~257 MB	~2 MB
Host `/opt/.../data`	403 MB	0
Engine input fetch	1M rows/job	one cached artifact read

Rollback¶

Two-phase throughout: items 1–3 are purely additive; 4–6 keep a Postgres fallback for v1 artifacts; only item 8 is destructive, and it lands after prod verification with the existing predeploy dumps as the floor. Re-ingest from the staged sources is the last-resort path.

Grid-Native Hazard Geometry — Eliminate hazard_centroids¶