E6 ยท Publication Volume 28

Database and Object-Storage Boundaries

SQL, Parquet, raster, mesh, images and immutable artefacts

*SQL, Parquet, raster, mesh, images and immutable artefacts*

Storage decision boundaries for transactional state, analytical columns and immutable spatial artefacts
Storage decision boundaries for transactional state, analytical columns and immutable spatial artefacts

Learning objectives

This lesson is general and institution-neutral. It uses no real company, individual, property, project or identifiable place. Generic roles describe responsibilities only, and SYN-ARCH identifiers denote explicitly synthetic teaching evidence.

  • Frame the decision governed by SQL, Parquet, raster, mesh, images and immutable artefacts.
  • Model the relevant boundaries, states and contracts before selecting an implementation.
  • Define measurable invariants, failure evidence and a safe release consequence.
  • Produce a workload-based storage allocation and immutable-artefact specification from synthetic evidence and defend its trade-offs.

Decision boundary

Allocate data according to access pattern, consistency, mutability, scale, reference semantics and delivery needs. Transactional entities, relationships, permissions and workflow state benefit from constrained row-level updates and queries. Large rasters, meshes, images, point blocks and model packages are usually safer as immutable artefacts referenced from governed metadata. Columnar files support analytical scans and predicate pruning but are poor substitutes for rapidly changing transaction rows. One logical data product may therefore combine database records, object manifests and derived delivery representations without pretending they share one storage behaviour.

Core concepts

Separate logical model, physical encoding and storage service. The logical model defines identities, relationships, units, references and validity. An encoding chooses rows, columns, chunks, tiles, pyramids or scene hierarchy. Storage supplies durability, retrieval, concurrency and lifecycle operations. Object storage does not remove the need for transactions around manifests, and a database does not make an opaque binary object queryable. Immutable artefacts simplify provenance and caching: new content receives a new identity or digest, while mutable aliases point to an approved version under controlled transaction. Derived previews remain distinguishable from authoritative source artefacts.

System model and contracts

Use a metadata record for artefact identity, media type, logical schema, spatial and temporal extent, coordinate reference, units, byte length, digest, creation activity, source dependencies, validity, access class and lifecycle state. The object key is an implementation locator, not domain identity. Tables hold normalized entities, relationships, search attributes and current workflow pointers. Analytical snapshots use partitioned columnar files with declared geometry metadata. Rasters and multidimensional arrays use chunking and reduced-resolution structures matched to read windows. Three-dimensional packages use hierarchical levels of detail and explicit transforms. Images retain colour, scale and processing metadata.

Invariants and acceptance criteria

| Invariant | Test evidence | Release consequence | |---|---|---| | Domain identity remains independent of object key and physical encoding. | contract test and recorded counterexample | block publication | | Published artefacts are immutable and verified by manifest and digest. | replay comparison and digest check | quarantine the artefact | | Every physical representation declares reference, units and schema version. | role-based acceptance trace | return the decision unresolved | | Derived previews never replace their authoritative source artefact. | failure injection and recovery record | retain the last verified version | | A tested restore can reconstruct every retained published version. | domain review against declared evidence | record an explicit review finding |

Quantitative engineering

Estimate read amplification as R_a=B_{read}/B_{needed} for a representative query window, and write amplification as W_a=B_{written}/B_{changed}. Report both with cache state and compression. Partition pruning rate, row-group selectivity, chunk count, range-request count, object count and metadata-query latency expose different bottlenecks. Small objects can create request overhead; huge objects can force unnecessary transfer. Storage cost models include retained versions, replication, egress, indexing, compaction and restore tests. A benchmark must preserve realistic geometry complexity, attribute width and spatial distribution rather than scale only row count.

Data quality, evidence and uncertainty

Storage choice is supported by a workload catalogue: transactions, point lookup, spatial subset, analytical scan, viewport streaming, bulk export, revision and recovery. Record frequency, concurrency, object distribution, consistency need and decision consequence for each. Format support claims require conformance tests and round trips of reference, null, empty, precision, orientation and metadata states. A fast preview is not evidence that the authoritative artefact can be reconstructed. Preserve unsupported extensions and unknown fields when safe, or reject explicitly; silent loss at a format boundary is a data-quality event.

Interoperability and versioning

An artefact contract declares stable identity, content digest, media type, format and schema version, byte order where relevant, compression and codec, chunk or tile layout, coordinate reference, axis order, unit, nodata policy, bounding extent, feature count or shape, dependencies and conformance profile. A manifest lists every member and its digest. Database foreign keys reference artefact identity, not an unversioned path. Readers state supported editions and fail visibly when a required extension is unknown. Migration creates a derived artefact and records source, tool, parameters and validation; it does not mutate the prior representation.

Security and professional responsibility

Authorise metadata search and artefact retrieval separately because an index can reveal sensitive extent even when bytes are protected. Use short-lived, scope-limited retrieval capability when indirect access is required. Validate media type and content rather than trusting file extension. Prevent path traversal, decompression exhaustion, malicious geometry complexity and active image payloads. Encryption does not replace authorisation or audit, and a digest provides integrity detection rather than confidentiality. Lifecycle deletion needs policy, hold and approval evidence; removing an object while retaining a published manifest creates a broken scientific record.

Operational workflow and observability

Continuously verify referential integrity between metadata and objects, sample content digests and rehearse restoration into an isolated location. Inventory orphan objects, missing objects, unexpected mutable writes, unsupported versions and lifecycle-policy drift. Compaction and retiling are versioned jobs with before-and-after manifests. Replication status is not a restore test. Monitor latency by workload and object size, not one global average. Capacity alarms use growth rate and retained-version policy. When a format reader changes, replay a golden corpus including malformed, boundary and legacy artefacts before allowing production migration.

Integration checkpoint

Connect the database and object-storage boundaries artefact to the preceding volume architecture. Trace one synthetic object from source identity through the new boundary to a reviewed output, then trace one rejection or failure back to the earliest violated invariant. Update the architecture decision record with the chosen option, alternatives, assumptions, evidence, consequences, owner role, review state and triggers for reconsideration. A checkpoint passes only when another reviewer can reconstruct both the successful path and the blocked path without oral explanation.

Synthetic worked example

SYN-ARCH-04 stores every raster cell and mesh triangle as transactional rows. Point edits are consistent, but viewport reads amplify data and one approved model cannot be reconstructed after rows are overwritten. The revised design keeps entity and approval state in constrained tables, publishes immutable tiled rasters and hierarchical three-dimensional packages, and references both through manifests. A columnar snapshot supports analytical scans without becoming the edit store. Benchmarking shows fewer transferred bytes for representative windows; a restore drill proves the approved package can be rebuilt from metadata and objects.

Practice and assessment

  1. Which workload and consistency need justify each storage boundary?
  2. What metadata is required to interpret an artefact independently?
  3. How are mutable aliases separated from immutable versions?
  4. Which test proves a format conversion preserved scientific meaning?

Assessed artefact: a workload-based storage allocation and immutable-artefact specification. Submit the artefact with its source manifest, acceptance evidence, unresolved risks and a short explanation of why one plausible alternative was not selected.

Common failure modes

  • Using one storage technology for every workload without measurement.
  • Treating an object path as stable scientific identity.
  • Overwriting a published artefact while retaining its version label.
  • Claiming format support without round-trip boundary tests.
  • Counting replicas as proof that restoration works.

Sources and further reading