6d156f98c4
- include link to one-page executive summary in README - update dashboard section in evaluation HTML to reference summary - revise report metadata to reflect additional CSV tables generated
343 lines
16 KiB
Markdown
343 lines
16 KiB
Markdown
# LabDataStorageEvaluation
|
|
|
|
A reproducible pipeline that answers one practical question: **which storage
|
|
format should a materials-science laboratory use for its measurement data?**
|
|
|
|
The pipeline simulates a tribology laboratory (Pt-Au LDRD context, Sandia
|
|
National Laboratories),
|
|
generates ~150 MB of physically plausible raw measurement data in CSV,
|
|
converts the corpus into four additional storage formats, benchmarks seven
|
|
laboratory-standard retrieval scenarios against all five formats, extrapolates
|
|
the measurements to 600 GB - 6 TB, and produces a ranked decision report for
|
|
five laboratory use cases: analysis, reporting, search/filtering, archiving,
|
|
and inter-lab exchange.
|
|
|
|
**Results - interactive, single-file HTML (open in a browser or download and share):**
|
|
|
|
- **[Storage Decision](https://git.boskadoff.com/Public/LabDataStorageEvaluation/raw/branch/master/docs/research/Tribology_Storage_Decision.html)** -
|
|
one-page executive summary: the problem, the verdict (relational vs graph
|
|
database), and what it costs - written for management, every claim linked
|
|
to the full study
|
|
(source: [docs/research/Tribology_Storage_Decision.html](docs/research/Tribology_Storage_Decision.html)).
|
|
- **[Storage Format Evaluation](https://git.boskadoff.com/Public/LabDataStorageEvaluation/raw/branch/master/docs/research/Tribology_Storage_Evaluation.html)** -
|
|
the full study results: dashboard, measured matrix, projections to 6 TB,
|
|
hardware bill of materials, weighted scoring and verdict
|
|
(source: [docs/research/Tribology_Storage_Evaluation.html](docs/research/Tribology_Storage_Evaluation.html)).
|
|
- **[Foreign-Key Architecture Study](https://git.boskadoff.com/Public/LabDataStorageEvaluation/raw/branch/master/docs/research/Tribology_FK_Architecture.html)** -
|
|
the measured key-schema study behind the relational design decision
|
|
(source: [docs/research/Tribology_FK_Architecture.html](docs/research/Tribology_FK_Architecture.html)).
|
|
|
|
Reference documents:
|
|
|
|
- Task specifications: [docs/specs/](docs/specs/) (plan + tasks 01-11).
|
|
- Binding conventions: [docs/rules/](docs/rules/) and [CLAUDE.md](CLAUDE.md).
|
|
- Full decision report (markdown): [out/report/REPORT.md](out/report/REPORT.md).
|
|
|
|
## Goals
|
|
|
|
1. Define a simulated tribology laboratory with real instruments and a
|
|
realistic test workflow.
|
|
2. Generate ~150 MB of physically plausible measurement data in CSV (the
|
|
canonical raw format), deterministically from seed `20260711`.
|
|
3. Convert the CSV corpus into 4 target storage formats: JSON-LD, SQLite
|
|
(optimized), PostgreSQL 16 (indexed + partitioned), RDF triplestore.
|
|
4. Benchmark 7 retrieval scenarios (Q1-Q7) against all 5 formats, measuring
|
|
wall time, peak RAM, CPU utilization, disk I/O, and disk footprint.
|
|
5. Extrapolate measured results to 600 GB / 1.2 TB / 6 TB with
|
|
complexity-aware scaling models.
|
|
6. Produce summary tables, charts, and a weighted scoreboard that rank the
|
|
formats per use case.
|
|
|
|
## Pipeline
|
|
|
|
```mermaid
|
|
flowchart TD
|
|
T01[01_lab_configuration] --> T02[02_process_flow_diagrams]
|
|
T01 --> T03[03_csv_data_generation]
|
|
T03 --> T04[04_convert_json]
|
|
T03 --> T05[05_convert_sqlite]
|
|
T03 --> T06[06_convert_postgresql]
|
|
T03 --> T07[07_convert_rdf]
|
|
T04 --> T08[08_benchmark_queries]
|
|
T05 --> T08
|
|
T06 --> T08
|
|
T07 --> T08
|
|
T03 --> T08
|
|
T08 --> T09[09_benchmark_execution]
|
|
T09 --> T10[10_extrapolation_model]
|
|
T10 --> T11[11_reporting]
|
|
T09 --> T11
|
|
```
|
|
|
|
Execution order: `01 -> 02 -> 03 -> (04 | 05 | 06 | 07) -> 08 -> 09 -> 10 -> 11`.
|
|
Tasks 04-07 are independent and may run in parallel; task 09 requires a quiet
|
|
machine (no parallel work). Every task writes a completion marker
|
|
`./out/.done/<NN>.ok` and validates its dependencies' markers before starting.
|
|
|
|
| Task | Script | Main output | Status |
|
|
|---|---|---|---|
|
|
| 01 Lab configuration | `make_lab_config.py` | `out/config/lab_config.yaml` | implemented |
|
|
| 02 Process flow diagrams | `make_process_flow.py` | `out/report/process_flow.md`, `out/report/diagrams/` | implemented |
|
|
| 03 CSV data generation | `generate_data.py` | `out/csv/` tree + `MANIFEST.csv` | implemented |
|
|
| 04 Convert to JSON-LD | `convert_json.py` | `out/json/full/`, `out/json/hybrid/dataset.jsonld` | implemented |
|
|
| 05 Convert to SQLite | `convert_sqlite.py` | `out/sqlite/tribo.db` | implemented |
|
|
| 06 Convert to PostgreSQL | `convert_postgresql.py` | live PostgreSQL DB (+ dump when pg_dump is available) | implemented |
|
|
| 07 Convert to RDF | `convert_rdf.py` | `out/rdf/dataset.nt.gz`, `dataset.ttl`, oxigraph store | implemented |
|
|
| 08 Benchmark queries | `make_queries.py` | `out/bench/queries/`, `out/bench/expected/` | implemented |
|
|
| 09 Benchmark execution | `bench_runner.py` | `out/bench/results_raw.csv`, `results_median.csv` | implemented |
|
|
| 10 Extrapolation | `extrapolate.py` | `out/bench/extrapolation.csv`, `hardware_sizing.csv` | implemented |
|
|
| 11 Reporting | `report.py` | `out/report/REPORT.md`, charts, tables | implemented |
|
|
|
|
## The simulated laboratory
|
|
|
|
Ti-6Al-4V coupons (10 x 10 x 3 mm) with a sputtered Cr adhesion layer and a
|
|
Pt-Au coating deposited as a composition gradient (film thickness
|
|
0.3-1.1 um). Four deposition batches (B721-B724) vary gun tilt, power, and
|
|
discharge voltage. Instruments: RAPID 6-probe tribometer (friction), Bruker
|
|
TI980 (nanoindentation), Bruker M4 Tornado micro-XRF (composition), Kurt J.
|
|
Lesker PVD 200 (deposition), AFM (roughness), optical profilometry
|
|
(thickness), SIMTRA (sputter transport simulation).
|
|
|
|
```mermaid
|
|
flowchart LR
|
|
B[Batch x4] --> W[Wafer x3 per batch]
|
|
W --> C[Coupon x49 per wafer, 7x7 grid]
|
|
C --> X[xrf_map: 400 points]
|
|
C --> N[nanoindentation: 25 indents]
|
|
C --> A[afm: Ra / Rq]
|
|
C --> P[profilometry: 100 points]
|
|
C --> T[Track x3, friction coupons only]
|
|
T --> CY[cof_vs_cycle: 1000 cycles]
|
|
T --> L[friction_loops: 10 x 200 points]
|
|
T --> WR[wear: 1 row]
|
|
```
|
|
|
|
| Entity | Count |
|
|
|---|---|
|
|
| Batches | 4 |
|
|
| Wafers | 12 |
|
|
| Coupons | 588 (480 friction-tested + 108 reserve/QA) |
|
|
| Tracks | 1,440 |
|
|
| Friction cycle rows | 1,440,000 |
|
|
| Friction loop points | 2,880,000 |
|
|
| XRF map points | 235,200 |
|
|
|
|
Friction runs R1/R2 execute in Lab Air, R3/R4 in Dry N2. Generated values
|
|
follow explicit physical models (COF run-in exponential, steady-state COF and
|
|
hardness vs Au content, Archard wear), so benchmark queries return physically
|
|
meaningful results.
|
|
|
|
## Storage formats under evaluation
|
|
|
|
| # | Format | Role in the comparison |
|
|
|---|---|---|
|
|
| 1 | CSV (raw tree) | Canonical instrument output; baseline |
|
|
| 2 | JSON-LD (full + hybrid) | Ontology-annotated exchange format (FAIR) |
|
|
| 3 | SQLite | Single-file relational, optimized for size and speed |
|
|
| 4 | PostgreSQL 16 | Production relational: indexed, partitioned |
|
|
| 5 | RDF triplestore (oxigraph) | Semantic-web store queried via SPARQL |
|
|
|
|
## Benchmark scenarios (Q1-Q7)
|
|
|
|
| ID | Scenario | Access pattern |
|
|
|---|---|---|
|
|
| Q1 | COF vs cycle curve for one track | Point read |
|
|
| Q2 | Steady-state COF for coupons with mean Au = 10 +/- 0.5 wt% | Indexed filter |
|
|
| Q3 | Hardness vs steady-state COF across all batches | Indexed join |
|
|
| Q4 | Tracks in Dry N2, 100 mN, cof_ss > 0.20 (anomaly filter) | Indexed filter |
|
|
| Q5 | Mean run-in cycles per batch over ALL raw cycle rows | Forced full scan |
|
|
| Q6 | Wear volume vs load per coupon | Indexed join |
|
|
| Q7 | Stribeck-style mean COF by (environment, speed-load bucket) | Full aggregation |
|
|
|
|
Protocol: every cell (format x query) runs in its own subprocess; 1 warm-up +
|
|
3 measured runs; randomized cell order; 30-minute hard timeout recorded as
|
|
`TIMEOUT`; every result validated against a canonical checksum before the
|
|
measurement counts. See
|
|
[docs/rules/bench-methodology.md](docs/rules/bench-methodology.md).
|
|
|
|
## Repository layout
|
|
|
|
```
|
|
docs/
|
|
Initial_Prompt.md the generative prompt behind specs 00-11 (provenance, non-binding)
|
|
specs/ task specifications 00-11 (WHAT to build)
|
|
rules/ binding conventions (HOW work is done)
|
|
research/ measured design studies and interactive result pages
|
|
out/ ALL generated artifacts (reproducible; bulk git-ignored,
|
|
report/ config/ .done/ committed - see .gitignore):
|
|
config/ csv/ json/ sqlite/ pg/ rdf/ bench/ report/ .done/
|
|
common/ shared helpers (storage_sizes.csv contract, ...)
|
|
queries/ Q1-Q7 implementations for csv / json / rdf formats
|
|
make_lab_config.py task 01 entry script (one script per task, repo root)
|
|
make_process_flow.py task 02 entry script
|
|
generate_data.py task 03 entry script
|
|
convert_json.py task 04 entry script
|
|
convert_sqlite.py task 05 entry script
|
|
convert_postgresql.py task 06 entry script (TRIBO_PG_DSN, TRIBO_PROM_URL)
|
|
convert_rdf.py task 07 entry script
|
|
make_queries.py task 08 entry script (canonical results need TRIBO_PG_DSN)
|
|
bench_runner.py task 09 entry script (TRIBO_PG_DSN, TRIBO_PROM_URL)
|
|
extrapolate.py task 10 entry script (seeds editable out/config/hw_prices.yaml)
|
|
report.py task 11 entry script (REPORT.md, charts, tables, scoring)
|
|
requirements.txt closed dependency list (docs/rules/code-python-style.md)
|
|
```
|
|
|
|
The bulk of the `out/` tree - the ~150 MB CSV corpus, the JSON-LD variants,
|
|
databases, stores, archives, and raw benchmark data - is **excluded from
|
|
git**; every artifact regenerates deterministically from the specs, the
|
|
rules, and the code. A curated results subset IS committed (decision of
|
|
2026-07-11): `out/report/` (REPORT.md, charts, tables, diagrams),
|
|
`out/config/` (lab_config.yaml, hw_prices.yaml), and the `out/.done/`
|
|
markers - see `.gitignore`. [docs/Initial_Prompt.md](docs/Initial_Prompt.md)
|
|
is the original prompt the 12 specification files were generated from - kept
|
|
for provenance only, never authoritative: where it disagrees with the specs,
|
|
rules, or code (e.g. the SQLite key schema, the JSON-LD vocabulary), those win.
|
|
|
|
## Getting started
|
|
|
|
Prerequisites: Python 3.11+ (3.12 tested). PostgreSQL 16 is required only for
|
|
tasks 06/08/09; its DSN comes from the `TRIBO_PG_DSN` environment variable
|
|
(default `postgresql://postgres@localhost:5432/tribo`).
|
|
|
|
Windows (PowerShell):
|
|
|
|
```powershell
|
|
py -3 -m venv .venv
|
|
.venv\Scripts\python -m pip install -r requirements.txt
|
|
.venv\Scripts\python make_lab_config.py
|
|
```
|
|
|
|
Linux:
|
|
|
|
```bash
|
|
python3 -m venv .venv
|
|
.venv/bin/python -m pip install -r requirements.txt
|
|
.venv/bin/python make_lab_config.py
|
|
```
|
|
|
|
Each task script supports `--log-level {DEBUG,INFO,WARNING,ERROR}` and prints
|
|
its protocol output (paths, counts, sizes) to stdout. A task refuses to run if
|
|
a dependency's `out/.done/<NN>.ok` marker is missing.
|
|
|
|
Note on cold-cache runs: the page-cache drop used by the cold-cache benchmark
|
|
pass is Linux-only; on Windows hosts that pass is skipped and marked as such.
|
|
Benchmark execution is planned for a dedicated Linux host.
|
|
|
|
## Results
|
|
|
|
Results of the full pipeline run of 2026-07-11 (seed 20260711, 141.8 MiB
|
|
corpus, all 35 benchmark cells checksum-valid). The generated sources of
|
|
truth are committed under [out/report/](out/report/) - the full decision
|
|
document is [out/report/REPORT.md](out/report/REPORT.md); the tables below
|
|
are copied from it. An interactive single-file companion (sidebar
|
|
navigation, per-query and per-scale views, cost explorer) is
|
|
[docs/research/Tribology_Storage_Evaluation.html](docs/research/Tribology_Storage_Evaluation.html).
|
|
Bulk measurement files (`out/bench/`) are git-ignored and regenerable.
|
|
|
|
### Process flow diagrams (task 02)
|
|
|
|
`out/report/process_flow.md` with `out/report/diagrams/D1..D6.mermaid`:
|
|
D1 coupon assembly, D2 characterization and batch assembly, D3 testing tree,
|
|
D4 tribometer session sequence, D5 execution loop, D6 data hierarchy.
|
|
|
|
### Measured results (task 09)
|
|
|
|
Storage footprint per format (working + compressed archival):
|
|
|
|
| Format | Measured | vs CSV | Archival (compressed) |
|
|
|---|---|---|---|
|
|
| CSV | 149 MB | 1.00x | 52.8 MB (tar.gz) |
|
|
| JSON-LD (full) | 311 MB | 2.09x | 57.2 MB (tar.gz) |
|
|
| SQLite | 123 MB | 0.83x | 55.1 MB (gzip) |
|
|
| PostgreSQL | 397 MB | 2.67x | 53.1 MB (pg_dump -Fc) |
|
|
| RDF triplestore | 1.74 GB | 11.70x | 126 MB (nt.gz) |
|
|
|
|
Median wall time on the 150 MB corpus, warm cache (peak RAM / CPU / read
|
|
bytes: [out/report/tables/t2_measured_medians.csv](out/report/tables/t2_measured_medians.csv)):
|
|
|
|
| Format | Q1 | Q2 | Q3 | Q4 | Q5 | Q6 | Q7 |
|
|
|---|---|---|---|---|---|---|---|
|
|
| CSV | 0.06 s | 0.17 s | 1.15 s | 0.72 s | 1.09 s | 0.28 s | 1.27 s |
|
|
| JSON-LD | 0.11 s | 1.48 s | 9.9 s | 9.9 s | 10 s | 9.8 s | 10 s |
|
|
| SQLite | 0.06 s | 0.06 s | 0.11 s | 0.06 s | 0.77 s | 0.06 s | 0.71 s |
|
|
| PostgreSQL | 0.22 s | 0.22 s | 0.22 s | 0.22 s | 0.44 s | 0.22 s | 0.33 s |
|
|
| RDF triplestore | 0.11 s | 2.81 s | 46 s | 20 s | 60 s | 0.17 s | 41 s |
|
|
|
|
### Projections (task 10)
|
|
|
|
Projected wall times at 600 GB - `(!)` = IMPRACTICAL (over 1 hour),
|
|
`FAIL` = over 24 hours; 1.2 TB and 6 TB scales:
|
|
[out/report/tables/t3_projected_600gb.csv](out/report/tables/t3_projected_600gb.csv)
|
|
and `out/bench/extrapolation.csv`:
|
|
|
|
| Format | Q1 | Q2 | Q3 | Q4 | Q5 | Q6 | Q7 |
|
|
|---|---|---|---|---|---|---|---|
|
|
| CSV | 0.06 s | 7.1 min | 1.2 h (!) | 44 min | 1.2 h (!) | 15 min | 1.4 h (!) |
|
|
| JSON-LD | 0.11 s | 1.5 h (!) | 11 h (!) | 11 h (!) | 11 h (!) | 10.9 h (!) | 11.2 h (!) |
|
|
| SQLite | 0.06 s | 1.3 s | 5.7 min | 0.06 s | 47 min | 5.2 s | 44 min |
|
|
| PostgreSQL | 0.22 s | 0.22 s | 3.4 s | 11 s | 3.7 min | 4.1 s | 1.9 min |
|
|
| RDF triplestore | 0.11 s | 3.0 h (!) | 2.1 d FAIL | 22 h (!) | 2.8 d FAIL | 5.9 min | 1.9 d FAIL |
|
|
|
|
Hardware sizing at 600 GB - bill of materials with street prices as of
|
|
2026-07-11 (component sources: REPORT.md section 7 and
|
|
[out/config/hw_prices.yaml](out/config/hw_prices.yaml)):
|
|
|
|
| Format | Configuration | Total |
|
|
|---|---|---|
|
|
| CSV, JSON-LD, SQLite, PostgreSQL (each) | 1 x 1U Supermicro AS-1015CS-TNR (16 cores); 1 x 64 GB DDR5 ECC RDIMM ($1,887); 1 x 7.68 TB NVMe U.2 ($3,995) | $10,500 |
|
|
| RDF triplestore | 2 nodes; 22 x 64 GB DDR5 ECC RDIMM; 2 x 7.68 TB NVMe U.2 | $58,740 |
|
|
|
|
At 6 TB PostgreSQL still fits one 16-core node - 9 RDIMM modules, 3 NVMe
|
|
drives, $33,586; the RDF triplestore needs 14 nodes and $527,732
|
|
(projected; full table: `out/bench/hardware_sizing.csv`).
|
|
|
|
### Charts (task 11)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
(Task 11 writes the charts to `out/report/charts/` under exactly these
|
|
names; c3 covers all seven queries - `c3_q1..q7_wall_time.png` - and c4 has
|
|
three query classes: `point_read`, `indexed`, `full_scan`.)
|
|
|
|
### Final scoreboard (task 11)
|
|
|
|
Weighted scoring, max 80 points: search speed x5 (0-50), RAM economy x2
|
|
(0-20), disk economy x1 (0-10); a FAIL projection at 600 GB zeroes the
|
|
search subscore. Values below are the projected-600 GB scoreboard from the
|
|
2026-07-11 pipeline run (seed 20260711); the generated source of truth is
|
|
`out/report/tables/t5_weighted_scores.csv`, and REPORT.md also carries the
|
|
measured-scale scoreboard.
|
|
|
|
| Format | Search (0-50) | RAM (0-20) | Disk (0-10) | Total (0-80) |
|
|
|---|---|---|---|---|
|
|
| CSV | 0.6 | 20.0 | 8.3 | 28.9 |
|
|
| JSON-LD | 0.1 | 20.0 | 4.0 | 24.0 |
|
|
| SQLite | 21.8 | 15.2 | 10.0 | 47.0 |
|
|
| PostgreSQL | 50.0 | 5.5 | 3.1 | 58.6 |
|
|
| RDF | 0.0 | 0.2 | 0.7 | 0.9 |
|
|
|
|
| Use case | Recommended format |
|
|
|---|---|
|
|
| Analysis | PostgreSQL (SQLite acceptable single-user up to ~600 GB) |
|
|
| Report generation | PostgreSQL (track_summary materialized view) |
|
|
| Search / filtering | PostgreSQL or SQLite (indexed) |
|
|
| Archiving | CSV tree + tar.gz, plus pg_dump of the system of record |
|
|
| Inter-lab exchange | Hybrid JSON-LD (metadata + sourceFile links to CSV) |
|
|
|
|
## Conventions
|
|
|
|
- IDs: `batch:B721`, `wafer:B721-W1`, `coupon:B721-W1-C07`,
|
|
`track:B721-W1-C07-T2`, runs `R1..R4`.
|
|
- Timestamps: ISO-8601 UTC; all corpus timestamps are simulated.
|
|
- Units are explicit ASCII suffixes in column names (`_mN`, `_um`, `_GPa`,
|
|
`_wtpct`, `_nm`).
|
|
- Random seed `20260711`, read from `lab_config.yaml`; regeneration is
|
|
byte-identical (proof: `MANIFEST.csv` sha256).
|
|
- Python: tabs for indentation; closed dependency list; streaming discipline
|
|
(the corpus never fits in memory by design).
|