LabDataStorageEvaluation/out/report/REPORT.md

Laboratory Data Storage Format Evaluation - Final Report

Generated by task 11 (report.py) from the measured benchmark matrix (task 09) and the extrapolation model (task 10). Every number at scales beyond the 150 MB corpus is a PROJECTION and is labeled as such; scaling assumptions are listed in section 6.

1. Executive summary

Recommendation: PostgreSQL as the system of record. The measured matrix and the projections confirm the expected outcome:

• PostgreSQL is the only format whose seven benchmark queries stay below the 1-hour practicality bound at every projected scale (600 GB, 1.2 TB, 6 TB); at 6 TB it needs one 16-core node with 551 GB RAM at an estimated $33,586 (bill of materials, prices as of 2026-07-11). Partitioned parallel scans and the track_summary materialized view are the scaling levers.
• CSV remains the canonical raw archive: byte-reproducible, instrument-native, and the best compressor (tar.gz ~2.8x); its full scans exceed 1 h beyond ~600 GB, so it is an archive, not a query layer.
• SQLite is an excellent single-user store up to ~600 GB (all queries OK), then its single-threaded scans degrade (Q5/Q7 IMPRACTICAL at 1.2 TB).
• Hybrid JSON-LD (metadata + sourceFile links, 1.8 MiB at 150 MB corpus) is the exchange format; the FULL variant re-reads the whole corpus per scan and becomes impractical past 600 GB.
• RDF (materialized triplestore) fails at scale: 11.7x storage blow-up, 5 of 7 queries FAIL at 6 TB, and the hot set needs 13.7 TB RAM across 14 nodes (~$527,732 projected). Semantic access should be a virtual layer (e.g. Ontop OBDA) over PostgreSQL instead.

2. Methodology snapshot

35 cells (Q1-Q7 x 5 formats); per cell 1 warm-up + 3 measured runs in isolated subprocesses sampled with psutil at 50 ms; randomized cell order; 30-min timeout; every run validated against canonical results (PostgreSQL cross-validated with SQLite, tolerance 1e-9). Medians are reported; raw runs and min/max live in out/bench/results_raw.csv. The cold-cache pass is skipped on this Windows host and marked as such. PostgreSQL runs on a remote host; its wall times include the LAN round-trip, and server-side metrics come from pg_stat_statements and Prometheus (docs/rules/bench-methodology.md section 6).

3. Environment

benchmark_host_os: Windows-2025Server-10.0.26100-SP0
benchmark_host_cpu: Intel64 Family 6 Model 141 Stepping 1, GenuineIntel
benchmark_host_cores_logical: 4
benchmark_host_cores_physical: 4
benchmark_host_ram_gib: 8.0
python: 3.12.10
benchmark_host_disks: VMware Virtual NVMe Disk / SSD / 100 GB
sqlite: 3.49.1
psycopg: 3.3.4
pyoxigraph: 0.5.9
ijson: 3.5.1
psutil: 7.2.2
postgresql_server: PostgreSQL 16.14 (Ubuntu 16.14-0ubuntu0.24.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit
postgresql_host: 192.168.10.73 (remote; 4 cores / 3.8 GiB, see .done/06.ok)
cold_cache_pass: skipped (Windows host, no page-cache drop - bench-methodology section 6)
pg_backend_sampling: not possible for a remote server; Prometheus windows + pg_stat_statements deltas instead

4. Storage footprint

format	measured	vs CSV	archival (compressed)	projected @600 GB	projected @1.2 TB	projected @6 TB
CSV	149 MB	1.00x	53 MB	600 GB	1.20 TB	6.00 TB
JSON-LD	311 MB	2.09x	57 MB	1.26 TB	2.51 TB	13 TB
SQLite	123 MB	0.83x	55 MB	498 GB	996 GB	4.98 TB
PostgreSQL	397 MB	2.67x	53 MB	1.60 TB	3.21 TB	16 TB
RDF triplestore	1.74 GB	11.70x	126 MB	7.02 TB	14 TB	70 TB

5. Measured performance (150 MB corpus)

format	query	wall time	peak RSS	CPU util	read
CSV	q1	0.06 s	4 MB	0%	0.0 MB
CSV	q2	0.17 s	20 MB	16%	2.5 MB
CSV	q3	1.15 s	21 MB	24%	31.8 MB
CSV	q4	0.71 s	21 MB	22%	16.8 MB
CSV	q5	1.09 s	21 MB	23%	31.8 MB
CSV	q6	0.28 s	20 MB	17%	1.2 MB
CSV	q7	1.27 s	21 MB	23%	32.5 MB
JSON-LD	q1	0.11 s	22 MB	10%	1.4 MB
JSON-LD	q2	1.48 s	24 MB	24%	53.1 MB
JSON-LD	q3	9.94 s	24 MB	25%	297.6 MB
JSON-LD	q4	9.92 s	24 MB	25%	297.8 MB
JSON-LD	q5	9.96 s	24 MB	25%	297.6 MB
JSON-LD	q6	9.79 s	24 MB	24%	297.8 MB
JSON-LD	q7	10 s	24 MB	24%	297.9 MB
SQLite	q1	0.06 s	4 MB	0%	0.0 MB
SQLite	q2	0.06 s	4 MB	0%	0.0 MB
SQLite	q3	0.11 s	4 MB	0%	0.0 MB
SQLite	q4	0.06 s	4 MB	0%	0.0 MB
SQLite	q5	0.77 s	25 MB	23%	117.9 MB
SQLite	q6	0.06 s	4 MB	0%	0.0 MB
SQLite	q7	0.71 s	25 MB	23%	93.0 MB
PostgreSQL	q1	0.22 s	42 MB	18%	4.1 MB
PostgreSQL	q2	0.22 s	42 MB	20%	4.1 MB
PostgreSQL	q3	0.22 s	42 MB	18%	4.1 MB
PostgreSQL	q4	0.22 s	42 MB	18%	4.1 MB
PostgreSQL	q5	0.44 s	42 MB	10%	4.1 MB
PostgreSQL	q6	0.22 s	42 MB	19%	4.1 MB
PostgreSQL	q7	0.33 s	42 MB	12%	4.1 MB
RDF triplestore	q1	0.11 s	32 MB	17%	3.6 MB
RDF triplestore	q2	2.81 s	85 MB	24%	375.6 MB
RDF triplestore	q3	46 s	264 MB	25%	5445.1 MB
RDF triplestore	q4	20 s	168 MB	25%	2694.6 MB
RDF triplestore	q5	60 s	264 MB	25%	5390.5 MB
RDF triplestore	q6	0.17 s	46 MB	16%	15.9 MB
RDF triplestore	q7	41 s	264 MB	25%	5380.5 MB

Sub-interval note: cells faster than the 50 ms sampling cadence (fast SQLite/CSV point reads) under-report client RSS/CPU; wall times are exact.

Per-query charts: Q1, Q2, Q3, Q4, Q5, Q6, Q7

6. Projections (600 GB / 1.2 TB / 6 TB)

All values in this section are projected, never measured. Laws per access pattern (spec 10): flat / index-depth log / linear result set / linear x depth / full scan / parallel partitioned scan; constants calibrated on the measured point after subtracting the per-format harness floor. Flags: IMPRACTICAL > 1 h, FAIL > 24 h.

Projected wall times at 600 GB:

format	query	projected wall time	flag
CSV	q1	0.06 s	OK
CSV	q2	7.1 min	OK
CSV	q3	73.5 min	IMPRACTICAL
CSV	q4	44.1 min	OK
CSV	q5	69.6 min	IMPRACTICAL
CSV	q6	14.5 min	OK
CSV	q7	81.6 min	IMPRACTICAL
JSON-LD	q1	0.11 s	OK
JSON-LD	q2	92.0 min	IMPRACTICAL
JSON-LD	q3	11.0 h	IMPRACTICAL
JSON-LD	q4	11.0 h	IMPRACTICAL
JSON-LD	q5	11.0 h	IMPRACTICAL
JSON-LD	q6	10.9 h	IMPRACTICAL
JSON-LD	q7	11.2 h	IMPRACTICAL
SQLite	q1	0.06 s	OK
SQLite	q2	1.27 s	OK
SQLite	q3	5.7 min	OK
SQLite	q4	0.06 s	OK
SQLite	q5	47.5 min	OK
SQLite	q6	5.18 s	OK
SQLite	q7	43.7 min	OK
PostgreSQL	q1	0.22 s	OK
PostgreSQL	q2	0.22 s	OK
PostgreSQL	q3	3.42 s	OK
PostgreSQL	q4	11 s	OK
PostgreSQL	q5	3.7 min	OK
PostgreSQL	q6	4.06 s	OK
PostgreSQL	q7	115 s	OK
RDF triplestore	q1	0.11 s	OK
RDF triplestore	q2	3.0 h	IMPRACTICAL
RDF triplestore	q3	2.1 d	FAIL
RDF triplestore	q4	22.1 h	IMPRACTICAL
RDF triplestore	q5	2.8 d	FAIL
RDF triplestore	q6	5.9 min	OK
RDF triplestore	q7	45.3 h	FAIL

Degradation by data size: point read, indexed / join, full scan

7. Hardware sizing and cost at 600 GB (projected)

Bill-of-materials costing with street prices as of 2026-07-11 (sources below). Every configuration buys whole components: 64 GB DDR5 ECC RDIMM modules, 7.68 TB enterprise NVMe U.2 drives, and a priced 1U chassis; disk capacity includes 30% free-space headroom.

format	configuration	chassis	RAM	disk	extra nodes	total
CSV	1 x 1U Supermicro AS-1015CS-TNR (16 cores); 1 x 64 GB DDR5 ECC RDIMM; 1 x 7.68 TB NVMe U.2	$4,618	$1,887	$3,995	$0	$10,500
JSON-LD	1 x 1U Supermicro AS-1015CS-TNR (16 cores); 1 x 64 GB DDR5 ECC RDIMM; 1 x 7.68 TB NVMe U.2	$4,618	$1,887	$3,995	$0	$10,500
SQLite	1 x 1U Supermicro AS-1015CS-TNR (16 cores); 1 x 64 GB DDR5 ECC RDIMM; 1 x 7.68 TB NVMe U.2	$4,618	$1,887	$3,995	$0	$10,500
PostgreSQL	1 x 1U Supermicro AS-1015CS-TNR (16 cores); 1 x 64 GB DDR5 ECC RDIMM; 1 x 7.68 TB NVMe U.2	$4,618	$1,887	$3,995	$0	$10,500
RDF triplestore	2 x 1U Supermicro AS-1015CS-TNR (16 cores); 22 x 64 GB DDR5 ECC RDIMM; 2 x 7.68 TB NVMe U.2	$4,618	$41,514	$7,990	$4,618	$58,740

Component prices (retrieved 2026-07-11):

• 64 GB DDR5-5600 ECC RDIMM (A-Tech, 2x32GB kit): $1,887 - https://atechmemory.com/collections/ddr5-memory-ram
• 7.68 TB NVMe U.2 1DWPD enterprise SSD (Dell 14-16G compatible, Cloud Ninjas): $3,995 - https://cloudninjas.com/products/new-7-68tb-nvme-u-2-1dwpd-sie-2-5-enterprise-solid-state-drive-for-14th-15th-16th-gen-dell
• 1U AMD EPYC 9004/9005 chassis (Broadberry starting configurations): Supermicro AS-1015CS-TNR $4,618, Supermicro AS-1115CS-TNR $6,272 - https://www.broadberry.com/amd-epyc-9004-supermicro-servers
• Market context - server DRAM is in a documented 2025-2026 price surge: https://www.techpowerup.com/342331/server-dram-pricing-jumps-50-only-70-of-orders-getting-filled

Full sizing at 1.2 TB and 6 TB: out/bench/hardware_sizing.csv. RDF exceeds the 1 TB single-node RAM ceiling already at 600 GB and reaches 14 nodes at 6 TB.

8. Weighted scoring

Subscores are 10 x best/value per metric; weights: search x5, RAM economy x2, disk economy x1; max 80. Search uses the inverse geometric mean of Q1-Q7 (FAIL at 600 GB zeroes the search subscore). Left: measured scale; right: projected 600 GB.

Measured scale (150 MB):

format	search (x5)	RAM (x2)	disk (x1)	total (max 80)
SQLite	50.0	16.9	10.0	76.9
CSV	15.5	20.0	8.3	43.8
PostgreSQL	26.2	10.0	3.1	39.3
JSON-LD	1.7	17.2	4.0	22.9
RDF triplestore	1.3	1.6	0.7	3.6

Projected at 600 GB:

format	search (x5)	RAM (x2)	disk (x1)	total (max 80)
PostgreSQL	50.0	5.5	3.1	58.6
SQLite	21.8	15.2	10.0	47.0
CSV	0.6	20.0	8.3	28.9
JSON-LD	0.1	20.0	4.0	24.0
RDF triplestore	0.0	0.2	0.7	0.9

9. Use-case mapping

Use case	Recommended format(s)
Interactive analysis (joins, aggregations)	PostgreSQL; SQLite acceptable single-user up to ~600 GB
Report generation (repeated summaries)	PostgreSQL (track_summary materialized view)
Search / filtering	PostgreSQL or SQLite (indexed); flat formats need full scans
Archiving	CSV tree + tar.gz (canonical raw) plus pg_dump of the system of record
Inter-lab exchange	Hybrid JSON-LD (metadata + sourceFile links to CSV)
Semantic / ontology queries	Virtual RDF layer over PostgreSQL (e.g. Ontop OBDA), not a materialized triplestore

10. Limitations

• Single-node measurements on one Windows host + one remote PostgreSQL host; no cluster or cloud variance.
• The corpus is simulated (physically plausible models, fixed seed 20260711); real instrument data may have different value distributions, though volumes and shapes match the lab.
• Extrapolation uses analytic laws calibrated on a single 150 MB point; no intermediate-scale validation runs were performed.
• Client wall times include a per-format harness floor (interpreter start, imports, connection); the floor is assumed constant across scales. The cheapest cell of each format therefore projects flat.
• The cold-cache matrix pass is skipped (no page-cache drop on Windows); all measured runs are warm-cache.
• The pandas CSV variant was not measured; its RAM is O(n) and it is infeasible above roughly 10 GB (spec 10).
• RDF numbers reflect the compact modeling and a client that derives run-in / steady-state from raw cycles; SPARQL-side aggregation engines could shift (not remove) the scan penalty.
• Hardware prices are spot street prices retrieved 2026-07-11 during a documented server-DRAM price surge; re-cost by editing out/config/hw_prices.yaml and re-running tasks 10-11.

Appendix A - process flow diagrams (task 02)

D1 - Coupon assembly

flowchart LR
    A([Ti-6Al-4V Base 10x10x3 mm]) --> B[Cleaning]
    B --> B1([Rinsed in deionized water])
    B1 --> B2([Sonicated in cleaning solution])
    B --> C[Smearing Adhesive]
    C --> C1([Cr adhesive coating])
    C --> D[Vapor Deposition System<br/>Kurt J. Lesker PVD 200]
    D --> D1([PtAu sputter coating, gradient])
    D --> E([Test Coupon])

D2 - Characterization and batch assembly

flowchart LR
    TC([Test Coupon]) --> S[SIMTRA simulation<br/>composition / atom energies]
    S --> OP[Optical profilometry<br/>film thickness 0.3-1.1 um]
    OP --> TW[Assemble Test Wafer<br/>x49 coupons]
    TW --> TB[Assemble Test Batch<br/>x3 wafers]
    TB --> B721[B721<br/>Pt 150 W / Au 50 W]
    TB --> B722[B722<br/>Pt 100 W / Au 100 W]
    TB --> B723[B723<br/>Pt 150 W / Au 50 W]
    TB --> B724[B724<br/>Pt 50 W / Au 150 W]

D3 - Testing tree

flowchart TD
    TC([Test Coupon]) --> F[Friction<br/>RAPID 6-probe tribometer]
    F --> FA([Lab Air -> COF dataset mu_normal])
    F --> FN([Dry N2 -> COF dataset mu_dry_nit])
    TC --> NI[Nanoindentation<br/>Bruker TI980]
    NI --> NIR([Hardness, reduced modulus<br/>25 indents per coupon])
    TC --> AF[AFM]
    AF --> AFR([Topography -> Ra / Rq])
    TC --> XR[micro-XRF<br/>Bruker M4 Tornado]
    XR --> XRR([Pt/Au wt% map, 20x20 grid])

D4 - Tribometer session sequence

sequenceDiagram
    actor Op as Operator
    participant Prep as Sample Prep
    participant Plan as Excel Test Plan
    participant SW as Control Software
    participant TR as RAPID Tribometer

    Op->>Prep: Request test
    Op->>Prep: Get samples
    Op->>Prep: Load samples onto plates
    Op->>Prep: Load ball holders (3 counterfaces per holder)
    Op->>Plan: Create test plan<br/>(Sample Plate, Plate Location, Sample ID, Save Location,<br/>Folder Name, X/Y/Z Offset, Load, Iterations)
    Op->>TR: Transfer plates to tribometer
    Op->>TR: Set up platter
    Op->>SW: Activate software
    Op->>SW: Load plan
    SW->>TR: Activate tribometer
    TR-->>SW: Run reciprocating tests (execution loop, see D5)
    Op->>SW: Software stop
    SW-->>Op: Save avg files
    Op->>TR: Remove plates and platters
    Op->>SW: Close software
    Op->>TR: Pull equipment out

D5 - Execution loop

flowchart TD
    L[Load all counterfaces] --> P{5 plates}
    P -->|for each plate| PR{6 probes in parallel}
    PR -->|for each probe| C{4 coupons on coupon square}
    C -->|for each coupon| T[Draw track]
    T --> RC[Rotate counterface - fresh ball per track]
    RC -->|3 tracks per coupon| T
    T --> DONE([120 coupons x 3 tracks = 360 tracks per run])

D6 - Data hierarchy

erDiagram
    DEPOSITION_RUN ||--|| SIMTRA_PROFILE : produces
    DEPOSITION_RUN ||--|| BATCH : creates
    BATCH ||--|{ WAFER : contains
    WAFER ||--|{ COUPON : contains
    COUPON ||--|| XRF_MAP : has
    COUPON ||--|| NANOINDENTATION : has
    COUPON ||--|| AFM : has
    COUPON ||--|| PROFILOMETRY : has
    COUPON ||--o{ TRACK : "friction coupons only"
    TRACK ||--|{ CYCLE : has
    TRACK ||--|{ LOOP_POINT : has
    TRACK ||--|| WEAR : has