--- title: "A Unified Namespace Determines Your Historian Schema, Not the Other Way Around" published: 2026-06-10T09:16:00.000-04:00 updated: 2026-06-10T09:17:22.000-04:00 excerpt: "A UNS puts your historian downstream of continuous tag churn. Learn the schema that survives renames, reorgs, and retirements without rewriting history." tags: IoT, Database authors: Damaso Sanoja --- > **TimescaleDB is now Tiger Data.** 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](https://sparkplug.eclipse.org/specification/version/2.2/documents/sparkplug-specification-2.2.pdf) 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: ```SQL -- 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](https://www.tigerdata.com/learn/designing-your-database-schema-wide-vs-narrow-postgres-tables) 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`](https://www.docs.inductiveautomation.com/docs/8.1/appendix/reference-pages/ignition-database-table-reference); 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](https://www.tigerdata.com/learn/best-practices-for-time-series-metadata-tables) 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: ```SQL 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: ```SQL 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: ```SQL 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](https://www.tigerdata.com/docs/api/latest/hypertable/create_table); 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. ```SQL 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. ```SQL 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. ```SQL 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](https://www.tigerdata.com/blog/cross-database-queries-with-postgresql-foreign-data-wrappers), 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: ```SQL 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: ```SQL 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. 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. (For the cross-system reach, joining `work_orders`, a quality system, or an ML feature store via [Postgres Foreign Data Wrappers](https://www.tigerdata.com/blog/cross-database-queries-with-postgresql-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](https://www.tigerdata.com/blog/ask-factory-floor-anything-structuring-industrial-data-ai-agents). For the ingestion path that feeds it (batching, back-pressure, the `ON CONFLICT` dedup the unique index enables), see [From MQTT to SQL](https://www.tigerdata.com/blog/mqtt-sql-practical-guide-sensor-data-ingestion). For compression and continuous-aggregate tuning once the data is flowing, see [Optimizing for High-Volume Production Data](https://www.tigerdata.com/blog/timescaledb-for-manufacturing-iot-optimizing-for-high-volume-production-data).