bench_erddap_ctd.qmd

---
title: "Serving large CalCOFI CTD profiles on ERDDAP: format × schema × granularity"
subtitle: "NetCDF vs DuckDB vs Parquet vs CSV — memory, latency, and layout, for the CoastWatch ERDDAP"
author: "CalCOFI (B. Best) — for Lynn DeWitt & the ERDDAP community"
date: today
format:
  html:
    toc: true
    toc-depth: 3
    number-sections: false
    code-fold: true
    code-tools: true
    df-print: kable
calcofi:
  target_name: bench_erddap_ctd
  workflow_type: reference
  # every pipeline notebook must declare its output: build_targets_list() uses it
  # as the target's file, and a missing key resolves to NA — which fails the whole
  # run at resolution time with "missing files: NA", before anything executes.
  output: _output/bench_erddap_ctd.html
  workflow_url: https://calcofi.io/workflows/bench_erddap_ctd.html
editor_options:
  chunk_output_type: console
---

## Summary

CalCOFI's CTD profile data is large in *long* (tidy) format — one row per
cast × depth × `measurement_type` (~53 sensor types): `ctd_measurement` is
**~232 M rows / ~16 GB** of Parquet, plus an adaptively-thinned headline table
`ctd_thin` (~5.5 M rows). We want to serve it on ERDDAP without the memory
blow-up that disabled an earlier single-file attempt. To get an *apples-to-apples*
read, we benchmarked a controlled grid of **four axes** at a deliberately small
**2 GB JVM heap**:

- **format** — NetCDF (`EDDTableFromNcCFFiles`), DuckDB (`EDDTableFromDatabase`),
  Parquet (`EDDTableFromParquetFiles`), CSV (`EDDTableFromAsciiFiles`)
- **schema** — **wide** (canonical sensors as columns) vs **long** (tidy)
- **granularity** — **split** (one file per cruise, 96 files) vs **lumped** (one
  file for the whole table) — *for the file formats; DuckDB is granularity-agnostic*
- **query** — whole-dataset (load) vs subset (filtered)

::: {.callout-tip title="The recommendation, up front"}
**Serve every dataset the same way: ERDDAP `EDDTableFromDatabase` over DuckDB
views on the integrated CalCOFI release Parquet — long format, one backend, no
per-dataset variants.** Generate **NetCDF as whole-dataset files outside ERDDAP**
for the CF/modelling audience, rather than as a second live backend.

The one honest exception is scale, not format: the full-resolution
`ctd_measurement` (232 M rows) is *queryable* through this path but not
*bulk-downloadable* — an unconstrained dump OOMs at any container size we would
reasonably give ERDDAP. Bulk access to that table is what the versioned Parquet
release is for. §5 measures exactly where that boundary sits.

Serving one table in two formats (or wide in one place and long everywhere else)
costs more in user confusion than it buys in performance — so the axes below are
reported as *evidence*, not as a menu of things to deploy simultaneously.
:::

**What the grid shows (three findings, the first two mapping to the two figures):**

1. **Granularity is a memory lever only once a single file is large relative to the
   heap (Fig 1).** ERDDAP's file backends read *whole matching files onto the heap*.
   A **935 MB** single `ctd_wide.parquet` is what OOM'd the original attempt at a
   4 GB heap, and a lumped **598 MB** Parquet still loads at 1.6 GB. But the effect
   is proportional to file size, not intrinsic to lumping: after the profile-grain
   fix (§1) the lumped `ctd_measurement` NetCDF fell from 2.1 GB to **686 MB** and
   now **loads in 19 s at a 1,079 MB heap** — where the old oversized file timed
   out. Per-cruise splitting still loads lighter (66 MB) and prunes better, but
   *"lumped always fails" was an artefact of a file that should never have been
   that big.* **DuckDB is flat** (~65 MB in every config) because it streams.
2. **Split serves subsets fast; lumped re-reads everything (Fig 2).** A per-cruise
   split file answers a cruise query in well under a second (one cruise → one file);
   a lumped file has no such pruning, so *every query re-reads the whole file* — a
   cruise dump took **74 s lumped vs 1.2 s split** (even when the lumped file fits in
   memory). DuckDB's on-the-fly *wide* pivot view is the exception that proves the
   rule: cheap to load but slow on broad cross-cruise scans (76 s), so keep the tidy
   **long** view live.
3. **NetCDF, the native path, is per-cruise = one dataset — but per-cast is also
   viable.** CalCOFI has **14,290 casts** (not the 5.55 M an earlier revision
   reported — that was the depth-scan count; see the grain correction in §1). One
   file *per cast* à la Trinidad Head is therefore perfectly serveable; we still
   bundle **by cruise = 96 files** because it loads lighter and prunes
   cruise-scoped queries to a single file, but the per-cast option is a choice
   now, not an impossibility.

Live now at <https://erddap.calcofi.io> (`calcofi_ctd_thin`, `calcofi_ctd_measurement`,
DuckDB-long). Everything needed to reproduce is in §7.

```{r}
#| label: setup
#| message: false
#| warning: false
library(dplyr); library(readr); library(tidyr); library(knitr); library(stringr); library(ggplot2); library(forcats)
res <- read_csv("data/bench_erddap/results_all.csv", show_col_types = FALSE) |>
  mutate(format = factor(format, levels = c("duckdb","netcdf","parquet","csv")),
         schema = factor(schema, levels = c("wide","long")),
         granularity = factor(granularity, levels = c("na","split","lumped"),
                              labels = c("view (DB)","split (per-cruise)","lumped (1 file)")),
         table = factor(table, levels = c("thin","measurement"),
                        labels = c("ctd_thin (5.5M rows)","ctd_measurement (232M rows)")),
         loaded = load_status == "loaded")
lay_path <- "data/bench_erddap/layout_results.csv"
lay <- if (file.exists(lay_path)) read_csv(lay_path, show_col_types = FALSE) else NULL
PAL <- c("split (per-cruise)" = "#1b9e77", "lumped (1 file)" = "#d95f02", "view (DB)" = "#7570b3")
```

## 1 · Background

CalCOFI has occupied the California Current quarterly since 1949. The CTD cast
files (<https://calcofi.org/data/oceanographic-data/ctd-cast-files/>) are ~1 GB
as a single wide Parquet. We ingest them into a tidy **long** schema
(`ingest_calcofi_ctd-cast`,
<https://calcofi.io/workflows/ingest_calcofi_ctd-cast.html>): each sensor reading
on its own row keyed by `measurement_type`, convenient for analysis but
multiplying row count. Tables (release v2026.06.08 / supplemental v2026.04.08):

| table | rows | depth scans | **casts** | Parquet | note |
|---|--:|--:|--:|--:|---|
| `ctd_cast` | 5.55 M | 5.55 M | **14,290** | 128 MB | one row per depth scan; carries `time`,`lat`,`lon` |
| `ctd_thin` | 5.55 M | 0.42 M | **7,155** | 155 MB | adaptively-thinned headline table |
| `ctd_measurement` | 232 M | 5.33 M | **14,008** | ~16 GB | full long table (96 `cruise_key` partitions) |

The long tables are keyed by `ctd_cast_uuid` and carry no coordinates —
`time`/`latitude`/`longitude` live in `ctd_cast`, so every representation re-joins
them.

::: {.callout-warning title="Grain correction (2026-07-25) — read this before reusing earlier numbers"}
An earlier revision of this page reported **"~5.55 M casts"** and used it to argue
that per-cast NetCDF was infeasible. That figure was the **depth-scan** count, not
the cast count: `ctd_cast_uuid` is `md5(cruise_key|cast_key|cast_dir|datetime_utc)`
with a *per-scan* timestamp, so one station occupation carries hundreds of them.

CalCOFI actually has **14,290 casts** — 7,218 station occupations (75 per cruise,
the standard grid) across 96 cruises, each occupation contributing a down- and an
up-cast. Three consequences, all reflected below:

1. **Per-cast files are feasible after all** (§3, §5) — 14 k files is an ordinary
   ERDDAP collection, so the option Lynn asked about is genuinely on the table.
2. The **NetCDF artifacts were rebuilt**: profiles are now keyed by
   `(cruise_key, ord_occ, cast_dir)`. The old files stored one *single-point*
   profile per scan (`rowSize == 1`), which is not a profile at all. The corrected
   collections are **~3× smaller** — `ctd_measurement` split went 2.1 GB → 687 MB,
   lumped 2.24 GB → 686 MB.
3. Because the lumped file shrank below the heap, **the load-memory finding was
   re-measured rather than assumed** (§4).
:::

## 2 · How ERDDAP serves files — and the NetCDF "per-cruise = one dataset" point

A persistent point of confusion: **per-cruise files do *not* mean a dataset (or
form) per cruise.** Every ERDDAP `…FromFiles` type points at a *directory*
(`fileDir` + `fileNameRegex`) and aggregates all matching files into **one**
`datasetID` — one Data Access Form, one `.das`, with sliders/min-max **rolled up
across the whole collection**. The per-cruise split is purely *storage*: 96 files
versus 14,290 per-cast ones, while the user sees a single dataset either way.
(Both are indexable — ERDDAP routinely serves collections of that size. Per-cruise
wins on load time and on pruning a cruise query to one file, not on feasibility.) ERDDAP prunes by `cruise_key` (a
`subsetVariable`) and by each file's time/lat/lon min-max, so a cruise-scoped query
opens one file and a broad spatial query opens many.

The flip side is the memory model: for **file** backends ERDDAP reads each
*matching file fully onto the JVM heap* before filtering. That is the entire
origin of the memory problem — a single **935 MB** `ctd_wide.parquet` OOM'd a 4 GB
heap and dropped the dataset from every `LoadDatasets` run. The benchmark below
isolates that as the **granularity** axis. **DuckDB** is the exception: via JDBC it
*streams* a filtered `ResultSet` from the underlying Parquet (predicate pushdown +
partition pruning + disk spill), so the whole table is never on the heap.

::: {.callout-important title="Two DuckDB gotchas"}
Serve `time` as a real **TIMESTAMP** in the view (not epoch double) or the DuckDB
JDBC driver NPEs on every query that selects time. And `EDDTableFromDatabase` does
**not** auto-compute variable ranges — set `actual_range` (+ `geospatial_*`) or the
form shows no sliders. (NetCDF computes these from the files automatically.)
:::

## 3 · The benchmark grid

Isolated ERDDAP (custom image with the DuckDB JDBC jar) on our host, **2 GB heap**.
We serve the **same canonical data** every way and run the **same-intent** queries:
**q1** `.das`; **q2** one cruise + temperature (subset); **q3** one variable
(salinity) over depth 0–200 m across all cruises (subset, cross-file); **q4**
whole-cruise dump. In *wide* schema a query selects a named column; in *long* it
filters `measurement_type` — chosen so q2/q3 return the **same rows** either way.

```{r}
#| label: matrix
res |>
  arrange(table, schema, format, granularity) |>
  transmute(table, format, schema, granularity, load = load_status,
            `load heap MB` = load_peak_heap_mb,
            `q2 cruise ms` = na_if(q2_cruise_ms, -1),
            `q3 var+depth ms` = na_if(q3_var200_ms, -1),
            `q4 dump ms` = na_if(q4_dump_ms, -1)) |>
  kable()
```

### Finding 1 — load memory: lumped vs split (and DuckDB streams)

```{r}
#| label: fig-mem
#| fig-width: 9
#| fig-height: 5
#| fig-cap: "Peak JVM heap during dataset load at a 2 GB cap, by format and granularity. DuckDB (purple) stays ~65 MB whatever the schema/size because it streams. The file formats rise with what ERDDAP must read; the lumped 2.1 GB measurement NetCDF blows past the cap and times out (labeled). Bars are labeled with peak MB, or the failure mode."
res |>
  mutate(lab = ifelse(loaded, paste0(load_peak_heap_mb), toupper(load_status))) |>
  ggplot(aes(format, load_peak_heap_mb, fill = granularity)) +
  geom_col(position = position_dodge2(preserve = "single"), width = 0.85) +
  geom_text(aes(label = lab, colour = loaded),
            position = position_dodge2(width = 0.85, preserve = "single"),
            vjust = -0.3, size = 2.6, show.legend = FALSE) +
  geom_hline(yintercept = 2048, linetype = "dashed", colour = "grey45") +
  facet_grid(schema ~ table) +
  scale_fill_manual(values = PAL, name = "granularity") +
  scale_colour_manual(values = c(`TRUE` = "grey20", `FALSE` = "#d95f02")) +
  scale_y_continuous(expand = expansion(mult = c(0, 0.18))) +
  labs(x = NULL, y = "peak load heap (MB); dashed = 2 GB cap") +
  theme_minimal(base_size = 11) + theme(legend.position = "top")
```

### Finding 2 — query latency: split serves subsets fast, lumped re-reads everything

```{r}
#| label: fig-latency
#| fig-width: 9
#| fig-height: 4.6
#| fig-cap: "Median latency (log scale) for loaded cells. SPLIT files serve cruise-scoped queries (q2, q4) fastest — one cruise = one file. LUMPED files re-read the whole file on every query (q4 cruise-dump up to 74 s). DuckDB shows TWO purple bars — its wide-pivot and long views; the wide view is slow on the broad cross-cruise scan (q3: 76.5 s wide vs 23.9 s long) because it aggregates the whole table on the fly, while it's faster on the cruise dump (q4: 3 s wide vs 99 s long, fewer rows)."
lbl_ms <- function(x) ifelse(x >= 1000, paste0(round(x/1000, 1), "s"), paste0(round(x), "ms"))
res |>
  filter(loaded) |>
  transmute(table, format, granularity,
            `q2 cruise` = na_if(q2_cruise_ms, -1),
            `q3 var+depth` = na_if(q3_var200_ms, -1),
            `q4 cruise dump` = na_if(q4_dump_ms, -1)) |>
  pivot_longer(starts_with("q"), names_to = "query", values_to = "ms") |>
  filter(!is.na(ms)) |>
  ggplot(aes(format, ms, fill = granularity)) +
  geom_col(position = position_dodge2(preserve = "single"), width = 0.8) +
  geom_text(aes(label = lbl_ms(ms)),
            position = position_dodge2(width = 0.8, preserve = "single"),
            vjust = -0.3, size = 2.2) +
  facet_grid(query ~ table) +
  scale_fill_manual(values = PAL, name = "granularity") +
  scale_y_log10(expand = expansion(mult = c(0, 0.25))) +
  labs(x = NULL, y = "median latency (log scale)") +
  theme_minimal(base_size = 10) + theme(legend.position = "top")
```

**Reading the grid.**

- **Load memory (Fig 1).** **DuckDB ≈ 65 MB in every cell** — wide or long, thin or
  232 M rows — because it never holds a file on-heap. The file formats scale with
  what ERDDAP reads: per-cruise **split** measurement loads at ~1.1–1.8 GB (it
  indexes 96 files), and the **lumped 2.1 GB** measurement NetCDF **exceeds the cap
  and times out** (ERDDAP starts shedding requests under memory pressure). The
  smaller 598 MB lumped Parquet squeaks in at 1.6 GB — *granularity bites once the
  single file is large relative to the heap.*
- **Query latency (Fig 2).** **Split** files serve cruise-scoped q2/q4 in well under
  a second (one `cruise_key` → one file). **Lumped** files have no such pruning, so
  every query re-reads the whole file — the lumped-Parquet cruise dump took **74 s**
  vs **1.2 s** split, and lumped CSV/Parquet q2 are seconds, not milliseconds.
  **DuckDB-wide's** broad q3 is **76 s** because the pivot view aggregates the whole
  232 M-row table on the fly; the **DuckDB-long** view does the same q3 in 24 s and
  is what we serve live.
- **Apples-to-apples format (wide, split).** With schema and granularity held fixed,
  **NetCDF-split** and **Parquet-split** are the fast file formats (sub-second
  cruise queries); DuckDB is lightest on memory but slowest on broad aggregation;
  CSV works only at thin scale.
- **Schema (wide vs long).** For the materialized file formats it's close. For
  **DuckDB** the on-the-fly wide pivot is *slower* than long on cross-cruise scans —
  a reason to keep the tidy long view live and reach for wide only behind a
  materialization.

## 4 · Partitioning & ordering within the long table (Lynn's question)

> *"I wonder how organizing each sensor reading on its own row affects a query for
> data within a lat/lon/depth box."* — Lynn DeWitt

Beyond split-vs-lumped, *within* the long table we measured (in DuckDB, the serving
engine) the same bounding-box query — **`temperature_ave`, 32–34°N, 119–121°W,
0–200 m (~393 k rows)** — across four physical layouts:

- **A** raw Parquet partitioned by `cruise_key`, joined to `ctd_cast` at query time
  (live now); **B** denormalized, partitioned by `cruise_key`; **C** denormalized,
  partitioned by `measurement_type`; **D** = C **plus** rows sorted by
  `latitude,longitude,depth` within each partition.

```{r}
#| label: layout
#| eval: !expr "!is.null(lay)"
lay |>
  transmute(layout, `build s` = build_secs, size, files = nfiles,
            `query s (median)` = query_secs_median, `result rows` = result_rows) |>
  kable()
```

```{r}
#| label: fig-layout
#| eval: !expr "!is.null(lay)"
#| fig-width: 7.5
#| fig-height: 3
#| fig-cap: "The same ~393k-row bbox query across four physical layouts of the long table (DuckDB on SSD). ~12× spread, but every layout is already sub-second."
lay |>
  mutate(layout = fct_rev(factor(layout))) |>
  ggplot(aes(layout, query_secs_median)) +
  geom_col(width = 0.7, fill = "#7570b3") +
  geom_text(aes(label = paste0(formatC(query_secs_median, format="f", digits=3), " s")),
            hjust = -0.15, size = 3) +
  coord_flip() + scale_y_continuous(expand = expansion(mult = c(0, 0.18))) +
  labs(x = NULL, y = "median query (s)") + theme_minimal(base_size = 11)
```

```{r}
#| label: layout-missing
#| echo: false
#| output: asis
#| eval: !expr "is.null(lay)"
cat("*(layout_results.csv not present — run scripts/bench_parquet_layout.sh)*")
```

Decomposing the ~12× spread: removing the query-time join ≈ 4× (A→B);
partitioning by `measurement_type` ≈ 2× (B→C, a one-variable query opens 1 of 53
partitions); spatial ordering ≈ 1.4× (C→D, row-group skipping). The trade-off is
the one you raised: partitioning by `measurement_type` helps single-variable
queries but slows "all variables in a region." Every layout is already sub-second,
so for the long table this is an optimization, not a rescue.

## 5 · Download breadth and the real memory limit

Every query in §3 is *bounded* — one cruise, or a depth slice. None of them
reproduce what actually breaks for users: pressing **Submit** on the Data Access
Form with nothing constrained. This section varies **breadth alone** — same
dataset, same backend, no row filter — across four requests: metadata only
(`.das`), **one** variable, **three** variables, and **all** variables, and
sweeps the container memory limit to find where each dataset stops working.

::: {.callout-important title="The JVM heap is not the constraint — and that is the whole point"}
Across every measurement below the **JVM heap never exceeds ~1.7 GB**, including
in both out-of-memory failures. The memory that kills these requests is the
**DuckDB JDBC driver's allocation outside the Java heap**. Two consequences:

- **Raising `ERDDAP_MEMORY` (`-Xmx`) cannot fix an OOM here.** It is the knob
  that looks like it should, which is why the failure is confusing in practice.
- The bench container therefore carries a **hard cgroup cap** (`mem_limit` in
  `docker-compose.bench.yml`), not just a heap size. Without it an unconstrained
  query against `ctd_measurement` exhausted a 16 GB host and took the live
  services down — the cap turns that into a recorded `status=oom` instead.

Memory is reported as cgroup **`anon`** (true allocation), not `memory.current`:
the latter includes reclaimable page cache from reading Parquet and overstates
the requirement roughly threefold.
:::

Two methodological notes, both learned the hard way:

- **Time-to-first-byte, not total time.** A backend that *streams* emits almost
  immediately no matter how large the result; one that *materializes* first shows
  a long TTFB and then either dies or dumps at line speed.
- **Small datasets prove nothing about large ones.** The five non-CTD ERDDAP
  datasets (`casts`, `dic`, `euphausiids`, `zooplankton`, `phytoplankton`) are
  **0.4–23 MB** complete — they are event/sample tables, not measurement tables at
  CTD scale. They all serve full downloads comfortably at ~1.6 GB, but that is a
  statement about their size, not about the serving backend.

```{r}
#| label: setup-download
#| message: false
#| warning: false
dl_path <- "data/bench_erddap/download_results.csv"
dl <- if (file.exists(dl_path)) {
  read_csv(dl_path, show_col_types = FALSE) |>
    filter(query != "load") |>
    mutate(query   = factor(query, levels = c("allvars_das", "onevar_1", "onevar_3", "allvars"),
                            labels = c(".das", "1 var", "3 vars", "all vars")),
           backend = paste0(format, "/", schema),
           cap_gb  = as.numeric(sub("g$", "", mem_cap)),
           ok      = status %in% c("ok", "capped"),
           anon_gb = peak_anon_mb / 1024,
           MB      = bytes / 1048576)
} else NULL
```

**Which datasets serve a complete, unconstrained download?** One row per dataset ×
container cap; `oom` means the kernel killed the container at that cap.

```{r}
#| label: download-table
#| eval: !expr "!is.null(dl)"
dl |>
  select(table, backend, mem_cap, query, status) |>
  pivot_wider(names_from = query, values_from = status) |>
  left_join(dl |> group_by(table, mem_cap) |>
              # guard the empty case: a cell with no "all vars" row (run still in
              # flight, or skipped) must read NA, not max()'s -Inf
              summarise(`peak anon GB` = round(max(anon_gb), 2),
                        `all-vars MB`  = {v <- MB[query == "all vars"]
                                          if (length(v)) round(max(v), 0) else NA_real_},
                        .groups = "drop"),
            by = c("table", "mem_cap")) |>
  arrange(table, mem_cap) |>
  kable()
```

```{r}
#| label: fig-breadth
#| eval: !expr "!is.null(dl)"
#| fig-width: 9.2
#| fig-height: 4.8
#| fig-cap: "Peak OFF-HEAP allocation (cgroup `anon`) as a request widens from metadata to every variable. Dashed lines are the container caps tested. Bars crossing their cap are OOM (orange). The five small event tables sit flat at the ~1.5 GB JVM baseline; `ctd_thin` climbs steeply with breadth and needs >4 GB for a full dump; `ctd_measurement` exceeds even 6 GB from three variables onward. Heap is never the binding constraint."
dl |>
  filter(peak_anon_mb > 0) |>
  ggplot(aes(query, anon_gb, fill = ok)) +
  geom_hline(aes(yintercept = cap_gb), linetype = "dashed", colour = "grey55") +
  geom_col(width = 0.75) +
  geom_text(aes(label = ifelse(ok, "", "OOM")), vjust = -0.35, size = 2.3,
            colour = "#d95f02") +
  facet_grid(mem_cap ~ table) +
  scale_fill_manual(values = c(`TRUE` = "#1b9e77", `FALSE` = "#d95f02"),
                    name = NULL, labels = c(`TRUE` = "served", `FALSE` = "OOM")) +
  scale_y_continuous(expand = expansion(mult = c(0, 0.2))) +
  labs(x = NULL, y = "peak off-heap allocation (GB)") +
  theme_minimal(base_size = 9) +
  theme(legend.position = "top", axis.text.x = element_text(angle = 25, hjust = 1))
```

**Reading it.** `ctd_thin` in long format needs **between 4 and 5 GB** for a full
download (OOM at 4 GB, completes at 5 GB using ~4.1 GB, delivering ~1 GB of CSV in
~90 s). `ctd_measurement` is the wall: a single variable streams fine (309 MB in
~18 s), but three variables OOM at **4, 5 *and* 6 GB**. More memory does not
rescue it, which is why §6.2 routes bulk access to the Parquet release instead of
engineering around it.

```{r}
#| label: download-missing
#| echo: false
#| output: asis
#| eval: !expr "is.null(dl)"
cat("*(download_results.csv not present — run scripts/bench_download.sh)*")
```

## 6 · Recommendations

The benchmarks above explore a wide space, but the operational answer is
deliberately **narrow**. Every additional way of serving the same table is a
question a user has to answer before they can start ("full or thinned? DuckDB or
NetCDF? wide or long?"), and that cost is paid by every user forever, while the
performance difference is paid once by us. So the axes are evidence; the
deployment is one path.

### 6.1 · One serving path: DuckDB views over the integrated release Parquet

**Serve every dataset through `EDDTableFromDatabase` over DuckDB views on the
CalCOFI integrated release Parquet, in long format.**

To be unambiguous, since "one serving path" invites the wrong reading: this means
**one `datasetID` per source dataset**, exactly as now — CTD, bottle, zooplankton,
ichthyoplankton, DIC each keep their own ERDDAP entry with their own title,
abstract, citation, PIs and coverage. Per-dataset metadata is the point of a data
portal and is not up for consolidation. What is standardized is the *plumbing*
beneath those entries: one backend, one schema, one place the data comes from.
**Not** a single merged `obs` dataset. Concretely:

- **One backend.** A 274 KB `.db` of views on stock ERDDAP (only the DuckDB JDBC
  jar added). Nothing is copied or re-materialized — the views read the same
  release Parquet that `calcofi4r` and `calcofi.io/query` read, so ERDDAP cannot
  drift from the published release.
- **One schema — long.** `measurement_type` / `measurement_value` matches the
  integrated database, which is long and clustered by measurement type. A wide
  variant for CTD alone would make the largest dataset the odd one out, and the
  wide pivot view is *slower* on broad cross-cruise scans anyway (§3, Finding 2).
- **No per-dataset special cases.** The five non-CTD datasets already serve this
  way in effect; folding CTD in removes the exception rather than adding one.

**Size the container, not the heap.** From §5, the JVM heap never exceeds
~1.7 GB, so `-Xmx` is the wrong dial; the requirement is DuckDB's off-heap
allocation. Give the ERDDAP container **≥ 5 GB** (`mem_limit`, with
`memswap_limit` equal so the cap is real). At 4 GB the thinned CTD full download
OOMs; at 5 GB it completes using ~4.2 GB.

### 6.2 · The one scale limit, stated plainly

`ctd_measurement` (232 M rows) is **queryable but not bulk-downloadable** through
ERDDAP. A single variable across the whole record works (309 MB in ~18 s); three
variables or all variables OOM **even at a 6 GB cap**. This is not a format
problem to engineer around — it is a 10-GB-class result set, and no memory limit
we would sensibly grant ERDDAP changes that.

That is fine, because bulk access already has a better answer: the **versioned
Parquet release**, browsable at
[`…/ducklake/releases/index.html`](https://storage.googleapis.com/calcofi-db/ducklake/releases/index.html),
readable in place without downloading:

```sql
SELECT * FROM read_parquet(
  'https://storage.googleapis.com/calcofi-db/ducklake/releases/v2026.07.17/parquet/obs.parquet')
WHERE measurement_type = 'temperature_ave' LIMIT 10;
```

The right guidance for users is therefore: **ERDDAP for subsetting, Parquet for
bulk** — one sentence, no format menu.

### 6.3 · NetCDF as dataset-level output, not a second live backend

Generate **CF NetCDF per dataset, outside ERDDAP**, published alongside the
Parquet release rather than mounted as a parallel `EDDTableFromNcCFFiles`
dataset. Rationale:

- It serves the audience that actually wants it (ocean/climate modellers, CF
  tooling) without making every other user choose a format.
- It is the only representation that can carry a dataset's **one-to-many
  structure** — `sample` → `obs` → `obs_attribute` plus event-level effort — in a
  single self-describing file. ERDDAP's tabledap can only ever return one flat
  table, so a relational dataset must be either denormalized (repeating effort on
  every row, inviting double-counting) or split across several datasets the user
  re-joins by hand. NetCDF-4 groups and ragged arrays avoid both.
- The generator already exists (`libs/erddap_netcdf.R`); only the destination
  changes. Cost is ~740 MB per full regeneration (687 MB `ctd_measurement` +
  56 MB `ctd_thin`).
- Caveat worth stating to any recipient: CF covers the profile/trajectory case
  cleanly, but there is **no CF standard** for a tow → net → taxon → size-bin
  hierarchy. Such files are CF-compliant where CF applies and netCDF-4 elsewhere.

Granularity is a free choice here: **per-cruise (96 files)** or **per-cast
(14,290 files)** — both are ordinary collection sizes (§1 grain correction).

### 6.4 · What we are deliberately *not* doing

| rejected | why |
|---|---|
| Wide schema for CTD | makes the largest dataset the only wide one; slower on broad scans (§3) |
| NetCDF *and* DuckDB as live ERDDAP backends | same table served two ways — the confusion cost exceeds the benefit |
| `EDDTableFromParquetFiles` on one large file | whole-file on-heap reads; this is what OOM'd the original `ctd_wide` (§3, Finding 1) |
| Raising `ERDDAP_MEMORY` to fix OOMs | the heap is not the constraint (§5); it cannot work |

## 7 · Reproduce

Everything runs in the CalCOFI server containers (`../server/`), reading from and
writing to the shared **`/share/data/erddap-duckdb`** tree, mounted identically into
`rstudio` (build), the live `erddap`, and the bench `erddap_bench` — no
host-specific paths.

**Code** (all in [CalCOFI/workflows](https://github.com/CalCOFI/workflows)):

| file | role |
|---|---|
| [`libs/erddap_netcdf.R`](https://github.com/CalCOFI/workflows/blob/main/libs/erddap_netcdf.R) | per-cruise + lumped CF NetCDF builders + `EDDTableFromNcCFFiles` XML |
| [`libs/erddap_duckdb.R`](https://github.com/CalCOFI/workflows/blob/main/libs/erddap_duckdb.R) | `.db` view builder + `EDDTableFromDatabase` XML |
| [`libs/erddap.R`](https://github.com/CalCOFI/workflows/blob/main/libs/erddap.R) | shared dataVariable / type / units helpers |
| [`scripts/gen_ctd_netcdf.R`](https://github.com/CalCOFI/workflows/blob/main/scripts/gen_ctd_netcdf.R) · [`gen_ctd_wide.R`](https://github.com/CalCOFI/workflows/blob/main/scripts/gen_ctd_wide.R) · [`gen_ctd_lumped.R`](https://github.com/CalCOFI/workflows/blob/main/scripts/gen_ctd_lumped.R) | build the split/lumped, wide/long serving artifacts |
| [`scripts/gen_bench_datasets.R`](https://github.com/CalCOFI/workflows/blob/main/scripts/gen_bench_datasets.R) | emit the per-cell `<dataset>` blocks (datasetID = cell) |
| [`scripts/bench_erddap.sh`](https://github.com/CalCOFI/workflows/blob/main/scripts/bench_erddap.sh) | the format×schema×granularity matrix → `results_all.csv` |
| [`scripts/bench_parquet_layout.sh`](https://github.com/CalCOFI/workflows/blob/main/scripts/bench_parquet_layout.sh) | the partitioning/ordering experiment → `layout_results.csv` |
| [`server/erddap/Dockerfile`](https://github.com/CalCOFI/server/blob/main/erddap/Dockerfile) · [`docker-compose.bench.yml`](https://github.com/CalCOFI/server/blob/main/docker-compose.bench.yml) | ERDDAP + `duckdb_jdbc`; isolated bench instance |

**Data & artifacts** are browsable / downloadable via the Caddy file server at
[**file.calcofi.io/erddap-ctd**](https://file.calcofi.io/erddap-ctd/). Live datasets:
<https://erddap.calcofi.io/erddap/tabledap/calcofi_ctd_thin.html> ·
<https://erddap.calcofi.io/erddap/tabledap/calcofi_ctd_measurement.html>.

```{r}
#| label: data-files
read_csv("data/bench_erddap/results_all.csv", show_col_types = FALSE) |> kable(caption = "results_all.csv (the full grid)")
if (!is.null(lay)) kable(lay, caption = "layout_results.csv")
```

The per-cruise NetCDF builder and the benchmark harness, in full:

```{r}
#| label: embed-netcdf
#| eval: false
#| file: libs/erddap_netcdf.R
```

```{r}
#| label: embed-bench
#| eval: false
#| file: scripts/bench_erddap.sh
```