3 Database Selection for IoT Workloads

3.2 Overview: Choose A Database From Evidence

Database selection is a release decision, not a popularity contest. An IoT system may store telemetry, device registry records, events, media artifacts, latest dashboard values, and analytics outputs. Those records do not all have the same query path, consistency need, retention window, or recovery requirement.

The useful first question is not "Which database should we use?" It is "What must this data prove, who reads it, how long must it live, and what failure behavior is acceptable?" Once those answers are visible, the team can choose the smallest set of storage roles that proves the release claims without creating unnecessary operational burden.

Worked example: a cold-chain service stores one temperature reading every minute from 4,000 containers, plus a device registry and a small set of shipping documents. The temperature readings are append-heavy and usually queried by container id plus time range, so the evidence points toward a time-series or time-partitioned telemetry role. The registry needs uniqueness, ownership, audit, and controlled updates, so it belongs in a transactional registry role even if the telemetry store can technically hold JSON documents.

The documents are a third role. A calibration certificate or delivery photo should be stored as an object with checksum, content type, retention rule, access policy, and metadata linking it to a device or trip. A latest dashboard value can be a cache only if it has a durable source-of-truth link and freshness label. One product may implement more than one role in a small deployment, but the role evidence must be reviewed before that consolidation is accepted.

The Four Selection Questions

Common Storage Roles

Read these roles by responsibility rather than product name. Start with the authoritative registry, follow high-volume measurements into the time-series path, separate variable events and binary artifacts, and treat caches as rebuildable views. This sequence keeps ownership, query, lifecycle, and recovery evidence attached to each class.

Transactional registry role: authority-sensitive device, owner, authorization, configuration, and audit state.

Time-series role: timestamped measurements, trends, windows, rollups, late arrivals, and retention rules.

Document or event role: variable operational records, event envelopes, logs, replay, and search indexing.

Cache or materialized view: latest-value and dashboard acceleration backed by a durable source of truth.

Object storage with metadata: images, audio, firmware, reports, bulk logs, checksums, lifecycle, and access policy.

Graph or relationship role: topology, dependency, ownership, and traversal queries when relationship paths are central.

Data Shape: Structured, Semi-Structured, and Unstructured

The "Data Shape" question above has a classic three-way answer worth naming explicitly. Structured data is organized into pre-designed fields with relational keys — most commonly rows and columns in a SQL-standard relational database, with common implementations including MySQL, PostgreSQL, and Oracle. Semi-structured data, such as XML or JSON, carries some organizational markup but does not live inside a fixed relational schema. Unstructured data — text, images, audio, video, and log files — has no predefined data model at all. An IoT deployment produces all three in the same day: a device registry row is structured, a sensor's JSON payload is semi-structured, and a camera clip is unstructured.

That shape is what drives the relational-versus-NoSQL tradeoff behind several of the roles above. Relational databases impose a set schema, and decades of production use have made them stable and efficient to query, but the fixed structure is difficult to scale across multiple machines ("horizontal scalability") and cannot cleanly hold data that has no consistent shape. NoSQL stores relax that structure — common categories include key-value stores, column-oriented stores, and document stores such as MongoDB — which is what makes them easier to scale horizontally and better suited to IoT's mixed data variety. That flexibility comes at a cost: transactional reliability is typically weaker than a mature relational engine provides. Neither category is a default choice; use the storage-role evidence above, not the category label, to decide which shape — and therefore which product category — fits each record class.

The three shapes are easier to keep apart when each carries its own sample. Figure 3.1 shows a registry row, a telemetry payload, and a camera clip side by side, with the traits that follow from each shape and the store where it belongs.

The samples in Figure 3.1 do the arguing. The registry table’s fixed device_id / zone / fw columns are what make it queryable by a stable schema — and what makes that schema hard to scale horizontally — so it lands in: SQL row store. The JSON payload keeps its own field names, so a new firmware field never breaks a table definition, while the camera clip has no fields at all and lands in: object storage under a metadata index. The SAME DAY, SAME SYSTEM strip is the selection lesson of this whole section: one greenhouse minute legitimately produces all three shapes, so the storage-role ledger should expect at least three different stores rather than force one product to hold every shape.

Overview Knowledge Check

3.3 Practitioner: Build The Storage Role Ledger

A practical selection review separates storage role from product choice. The role describes the job the data store must do. The product and deployment shape are selected only after representative workloads prove the role can be operated safely.

This distinction matters because one product may safely serve multiple roles in a small system, while a larger system may need separate stores for telemetry history, registry authority, artifacts, search, and latest values. Splitting too early creates operational risk. Splitting too late can hide lifecycle, query, and consistency problems.

Selection Workflow

1. Define the contract. Record fields, IDs, timestamps, units, schema version, quality flags, owner, and authority boundary.
2. List access paths. Include ingest, retry, duplicate, backfill, dashboard, report, export, incident, and maintenance queries.
3. Name the role. Choose a storage role such as transactional registry, time-series, document/event, object, cache, search, or graph.
4. Choose configuration. Decide indexes, partitions, keys, consistency settings, access control, retention policy, and migration path.
5. Attach evidence. Capture representative queries, query plans, replay tests, retention dry runs, backup/restore proof, and owner approval.
6. Retest after change. Repeat the review when workload, schema, retention, scale, product configuration, or operational ownership changes.

Role Ledger

One Store Or Several?

Use one configured product when the data classes share access patterns, lifecycle policy, consistency needs, and operational ownership. Split roles when evidence shows real differences: telemetry retention differs from registry authority, search needs a derived index, latest values need a rebuildable cache, or media artifacts need object lifecycle rules.

Every additional store must have an owner, backup and restore proof, security policy, migration path, monitoring, and incident response. Polyglot persistence is a design tool only when the team can operate the additional moving parts.

Practitioner Knowledge Check

3.4 Under The Hood: Selection Is A Failure-Mode Decision

Database categories are shorthand. The release risk lives in failure modes: partitions, duplicate writes, late telemetry, stale cache values, schema drift, unbounded queries, missing backups, bad restores, access mistakes, and deletion errors. A database can be suitable for one role and unsafe for another depending on configuration and evidence.

For IoT systems, the same physical device can produce different records with different authority. A raw temperature measurement may tolerate eventual arrival. A firmware-assignment record may need tighter authority and audit behavior. A latest dashboard value may be disposable if it can be rebuilt, but misleading if it hides staleness. The selection review should make those differences explicit.

Worked example: the telemetry role accepts a duplicate sample after a gateway retry. If the store uses `(device_id, event_time, sequence)` as an idempotency key, the duplicate can update the same record or be ignored with an audit reason. If the key is only an auto-increment row id, the duplicate becomes a second reading and a one-hour average can be biased. A database category cannot answer that; the selected write path and key policy do.

The read path needs the same discipline. A dashboard query for "last 24 hours by container" should show bounded predicates, expected row count, index or partition pruning evidence, freshness status, and behavior when the durable store lags behind a cache. A restore drill should reload a representative backup and rerun that query against restored data. Include one negative test where the query is rejected or marked stale when evidence is missing. Record the retest trigger for schema, scale, and ownership changes. Selection is complete only when those failure-mode tests are attached to the chosen role.

Failure Modes To Surface

Review Tests

Run the tests in release order: prove representative writes and reads first, exercise failure and replay next, and finish with retention, backup, and restore evidence. A candidate store is acceptable only when the operational team can reproduce those results under the expected workload and ownership model.

Representative query test: prove the most important read paths with realistic predicates and expected data volume.

Replay test: resend duplicate, late, and out-of-order records and confirm the chosen store preserves the intended truth.

Partition test: document what happens when edge, gateway, cloud, or region components cannot communicate.

Retention dry run: show what would be deleted, rolled up, archived, or held before any data is removed.

Restore drill: recover the store and prove representative queries still answer correctly.

Migration rehearsal: apply schema or index changes to representative data and record rollback limits.

Under-the-Hood Knowledge Check