A Unified Namespace Determines Your Historian Schema, Not the Other Way Around

digitado ⋅ 17 de June de 2026

Most teams build the historian schema first, then connect it to a Unified Namespace. Adopt a UNS and the dependency runs the other way. The namespace owns tag identity, externally and continuously, and that single fact decides what the schema has to be. You are not designing a data model and fitting the UNS to it; the UNS sets the constraints, and one schema falls out of them.

This piece is for the data or platform engineer who owns that data model. You write the DDL, choose the keys, and decide what happens when a tag is renamed. We start from the UNS constraints and walk each design decision back to the pressure that forces it, so you can defend the resulting schema in your own review, adapt it where your plant needs more, and recognize the cases where a different shape is the better call.

What the Unified Namespace changes

A UNS flattens how data flows. Any device at any level publishes to the broker, and the topic path (enterprise/site/area/line/cell/tag_name) carries the hierarchy. The path is the identity, governed by the broker and the naming standard your commissioning team agreed to, by something that lives outside your historian. The historian is now a subscriber to an identity it does not control.

Here is the structural consequence the bundled-historian world rarely has to face: under a UNS, tag churn is a permanent operating condition, not an edge case, and a UNS puts your historian directly downstream of it. Tags get added at commissioning, renamed when a line is reconfigured, and retired when a sensor is decommissioned. PLC firmware upgrades can alter tag address maps, forcing re-validation of paths across the namespace. Sparkplug birth and death certificates announce this churn continuously as a normal part of operation. You are not modeling a fixed set of tags. You are modeling a tag set that moves.

Wide-table-per-asset breaks against that motion. It encodes identity in column names. One table per machine, one column per tag:

-- Wide-table-per-asset anti-pattern
CREATE TABLE press_01_detroit (
    ts              TIMESTAMPTZ NOT NULL,
    press_cycles    DOUBLE PRECISION,
    vibration_rms   DOUBLE PRECISION,
    temperature_c   DOUBLE PRECISION,
    oil_pressure    DOUBLE PRECISION
    -- ... every tag is a nullable column
);

Every new tag requires an ALTER TABLE ... ADD COLUMN. The structural problem is what accumulates: a rename is a new column plus a data migration, a retired tag leaves a dropped column whose disk Postgres does not promptly reclaim, and the column count only climbs. Postgres caps a table at roughly 1,600 columns, so a wide table runs out of room for tags entirely; the per-asset variant escapes that only by multiplying tables, each carrying its own migrations. See Designing Your Database Schema: Wide vs. Narrow for the general tradeoff. The sharper claim here is that continuous tag churn specifically rules out wide-per-asset, even where a wide table would otherwise be fine

That qualification matters, so state it plainly. Wide tables are correct for dense, simultaneous, stable sensor sets, where every column is populated on every reading, and a missing value would waste storage. The narrow model wins only when the tag set is dynamic, and a plant’s tag set is dynamic by nature. A UNS does not create that churn; it governs the namespace the churn lives in. Wide-table-per-asset is only one shape that struggles under that pressure.

The platform-embedded historian operates in a different scope. It absorbs churn fine for its own internal use; that is what it is built for. Its schema is system-managed and not exposed to the standard Postgres query surface, by design: it is gateway data, optimized for the gateway’s own queries and tools. The truly opaque industrial historians (PI, AVEVA, and similar) take this further: a proprietary query layer sits between you and the data, and getting cross-system answers means building a connector. What the Core Historian cannot do, because integrating into an enterprise-wide schema is not the gateway-local job it was built for, is hand you a schema you can govern, extend, and join across the systems that sit above the gateway.

Both failure modes trace back to the same root: identity is encoded in the wrong place, in a column name or in an opaque internal store. The next question is where it should go instead.

Identity belongs to the namespace, not the table

If the namespace owns identity, then the durable place to store identity is a governed, relational namespace table with a stable surrogate key. Not a path string. Not a column name. The rest of the design follows from one rule: identity should be a row you point at, not a string you parse or a column you migrate.

The decoupling that buys you is the whole game. When identity is a surrogate id and the path is just an attribute of the row, a rename is one UPDATE to one namespace row. Every reading in the historian keeps pointing at the same id, so no history moves, splits, or breaks. Compare that to identity-as-path. Ignition’s SQL Historian module stores the tag path as a string in sqlth_te.tagpath; when a tag is renamed, it inserts a new row and retires the old one, so the old path and the new path become two separate series. That is okay as long as the path never changes, but it makes the path the identity. The moment the path is the identity, a rename is a new identity. A surrogate key is the thing that breaks that coupling.

The surrogate key earns its place a second way: at the insert rate a UNS feeds your historian. Every reading lands in tag_history carrying an integer tag_id, resolved against uns_namespace(id). Referencing the namespace by its six-column path tuple instead would put a six-column text B-tree probe on every insert, at whatever rate your sensors publish. At tens of thousands of tags and sub-second publish rates, the integer key is a measurable difference in insert throughput, not a style preference.

Putting identity in a relational table also gives you write-time enforcement: the standard FK + NOT NULL + UNIQUE pattern means malformed data is rejected before application code runs. The UNS-specific reason it matters here is durability. The namespace contract has to survive commissioning-team turnover, firmware updates, and the next integrator who has never read the wiki. The database engine is the only place that contract holds, not a convention document, not application code. This is the series metadata table pattern, here applied to an ISA-95 hierarchy. Given those rules, the DDL falls into one shape.

The schema that falls out of it

The schema is not really a design choice. It is what those constraints leave you with. Here it is, and here is the pressure behind each line.

Start with the namespace table:

CREATE TABLE uns_namespace (
    id              INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    enterprise      TEXT NOT NULL,
    site            TEXT NOT NULL,
    area            TEXT NOT NULL,
    line            TEXT NOT NULL,
    cell            TEXT NOT NULL,
    tag_name        TEXT NOT NULL,

    uns_path        TEXT GENERATED ALWAYS AS (
                        enterprise || '/' || site || '/' || area || '/' || line || '/' || cell || '/' || tag_name
                    ) STORED,

    schema_version  TEXT NOT NULL DEFAULT 'v1',
    description     TEXT,

    UNIQUE (enterprise, site, area, line, cell, tag_name)
);

CREATE INDEX idx_uns_namespace_path ON uns_namespace (uns_path);

Walk it back to the pressure. The ISA-95 levels are columns, not a parsed path string, because you query and constrain on them individually (every press at every site, every cell on one line). The id surrogate key is the decoupling from the previous section: it is the identity the historian points at, so a rename touches only this row. The generated uns_path puts the path formatter in the database itself, not in every adapter, ingester, dashboard, and AI agent that reads the namespace. With one canonical formatter at the column, all consumers see the same string for the same row, regardless of who wrote the code.

The UNIQUE constraint across all six levels is not decoration; it is the conflict key the ingester uses when the ingester decomposes a Sparkplug topic into a namespace tuple and registers the tag:

INSERT INTO uns_namespace (enterprise, site, area, line, cell, tag_name)
VALUES (...)
ON CONFLICT (enterprise, site, area, line, cell, tag_name) DO UPDATE
    SET tag_name = EXCLUDED.tag_name
RETURNING id;

That ON CONFLICT clause is what makes tag registration idempotent: a tag that already exists resolves to its existing id instead of erroring or duplicating. The idx_uns_namespace_path index serves a different job, fast lookup by full path string, not conflict resolution. The schema_version column lets you migrate the namespace layout later without breaking the history joined to it.

Now the history table. It is narrow on purpose, and it is created as a hypertable in a single statement:

CREATE TABLE tag_history (
    ts          TIMESTAMPTZ      NOT NULL,
    tag_id      INT              NOT NULL REFERENCES uns_namespace (id),
    value       DOUBLE PRECISION NOT NULL
) WITH (
    tsdb.hypertable,
    tsdb.partition_column = 'ts',
    tsdb.chunk_interval    = '1 day'
);

CREATE UNIQUE INDEX idx_tag_history_dedup ON tag_history (ts, tag_id);

Three columns carry everything. ts is TIMESTAMPTZ NOT NULL and stores event time at the device, not ingestion time at the database. Timezone-aware storage keeps the device’s original time intact. In Ignition shops that clock is set by the gateway pushing time down to the PLC, not by NTP-down-the-stack. tag_id is the foreign key into uns_namespace(id), which means every insert is validated against the namespace before it is accepted; a reading for a tag that does not exist is rejected, not silently stored. The unique index on (ts, tag_id) is what lets the ingester run ON CONFLICT DO NOTHING for idempotent writes, which you need because MQTT QoS-1 delivers at least once and you will see duplicates. Adding a tag is one INSERT into uns_namespace; the hypertable schema never changes. That is the churn problem solved by construction.

The WITH (tsdb.hypertable) clause above requires TimescaleDB 2.20 or later; on older versions, create the table first and then convert it with create_hypertable(). Tiger Cloud runs the current version by default, so you don’t have to track this.

The contribution here is the why: each constraint is an answer to a pressure a UNS creates. You do not get to prefer this schema. You get handed it.

What survives the next reorganization

The immediate payoff is one query planner: a contextual question is one JOIN, not an integration project. The longer-running payoff is what happens to that schema when the plant changes, and it always changes.

Walk through three real evolution pressures every plant faces, and notice what moves and what does not.

A tag is renamed. A corporate naming standard rebrands press_cycles to stroke_count. Update one row in uns_namespace; nothing in tag_history is touched because identity is the surrogate id, not the path.

UPDATE uns_namespace SET tag_name = 'stroke_count' WHERE id = 4217;

The uns_path generated column recomputes when the source columns change. Every consumer of the namespace sees the new path the next time it queries; every reading in history still resolves to the same equipment, because it always pointed at the id.

The plant reorganizes. A new VP carves site/area/line/cell into site/department/workcell. Add a department column, backfill it from existing rows, and tag the migrated rows with a new schema_version. The version column lets the old and the new coexist; the query layer filters on the version it expects.

ALTER TABLE uns_namespace ADD COLUMN department TEXT;
UPDATE uns_namespace SET department = ..., schema_version = 'v2' WHERE ...;
ALTER TABLE uns_namespace ALTER COLUMN department SET NOT NULL;

History is again untouched. The FK still points at the same id rows; only the columns describing those rows changed.

A tag retires but its history must stay. Soft-delete on uns_namespace: add retired_at, set it on the retired row, and let live consumers filter on it while history queries reach back through the same FK as before.

ALTER TABLE uns_namespace ADD COLUMN retired_at TIMESTAMPTZ;
UPDATE uns_namespace SET retired_at = NOW() WHERE id = 4217;

The reading-side query plan does not change because the join column did not change.

That same tag_id anchor reaches across systems: joining work_orders, a quality system, or an ML feature store through Postgres Foreign Data Wrappers hangs off the foreign key, a payoff with its own article.

What does not survive any of these moves: the wide-table-per-asset shape. In that world, a column rename is a data migration, a hierarchy reorg is a new set of tables, and a tag retirement leaves dead space that compounds. Easier queries are the surface benefit; the deeper one is that the narrow tag_history plus relational uns_namespace absorbs change without rewriting history.

Continuous aggregates inherit the same durability

The same durability extends to what you build on the schema. A continuous aggregate keyed on tag_id keeps materializing across every one of those moves, because the surrogate key it groups on never changes:

CREATE MATERIALIZED VIEW reading_hourly
WITH (timescaledb.continuous) AS
SELECT
    time_bucket('1 hour', ts) AS bucket,
    tag_id,
    avg(value) AS avg_value
FROM tag_history
GROUP BY bucket, tag_id;

Ask it for the line speeds at a given site, and the hierarchy is resolved from the namespace at read time, so a rename or a reorg shows up in the answer without rebuilding the aggregate:

SELECT r.bucket, ns.line, r.avg_value AS avg_line_speed
FROM reading_hourly r
JOIN uns_namespace ns ON ns.id = r.tag_id
WHERE ns.site = 'detroit' AND ns.tag_name = 'line_speed'
ORDER BY r.bucket, ns.line;

The rollup is keyed on identity that does not move; the labels are resolved from a namespace that can.

(For the cross-system reach, joining work_orders, a quality system, or an ML feature store via Postgres Foreign Data Wrappers, the same tag_id FK is your anchor. That payoff has its own article.)

Where this goes as UNS adoption grows

The choice compounds. As more consumers, dashboards, ML pipelines, and AI agents query the namespace directly, they all need an equipment model to read. With this schema, the uns_namespace table is that model. The enforcement already happened at write time, through the foreign key and the constraints, so there is no separate registry, config file, or lookup service to keep in sync with reality.

That is why the relational namespace stops being a schema detail and becomes the shared equipment model the rest of the stack depends on. Each consumer you add reads a model that is already correct, because the enforcement happened once, at write time, rather than once per integration.

Hold the boundary on the build, not on the idea. The full implementation here, a relational namespace table with a narrow hypertable foreign-keyed to it, earns its place when three conditions hold together: the namespace is governed externally by a UNS broker, the tag set churns continuously, and analytics must join across system boundaries in SQL. Short of that, on a single-gateway, platform-native stack with a stable tag set, the bundled historian is the right call and this schema is work you do not need yet. The principle underneath has no such exception: identity belongs to the namespace, not to the table or the historian you happen to store it in. Get that ownership right, and the storage choice stays reversible; get it wrong and no historian, bundled or built, will save you.

If you are building this, three reads carry it forward. For this schema in production, see Ask Your Factory Floor Anything. For the ingestion path that feeds it (batching, back-pressure, the ON CONFLICT dedup the unique index enables), see From MQTT to SQL. For compression and continuous-aggregate tuning once the data is flowing, see Optimizing for High-Volume Production Data.

Like 0

Liked Liked