--- title: "Normalizing a Wide Well and Pipeline Telemetry Table Without Re-Ingesting History" published: 2026-09-30T09:58:45.000-04:00 updated: 2026-09-30T09:58:45.000-04:00 excerpt: "Reshape a wide oilfield SCADA table into a narrow hypertable plus tag metadata, in place, with dashboards running and history left where it is." tags: IoT authors: Damaso Sanoja --- > **TimescaleDB is now Tiger Data.** _An oilfield SCADA migration guide: reshape a wide sensor table into a narrow hypertable plus a metadata table, in place, with your dashboards still running._ Every new instrument the field commissions turns into a schema change for you. A casing-pressure transmitter goes on a pad, a flow computer lands on a gathering line, and your wide telemetry table gets another indexed column on top of years of readings. You already know the shape is wrong. What has kept you from fixing it is the cost of the fix: reshaping a table that size looks like re-ingesting all of that history, and the dashboards on top of it cannot go dark while you do. This guide walks through that migration: moving a wide time-series table to a narrow schema in place, with history intact and dashboards still running. It leaves out the harder case of tags whose descriptors change over time, and it picks up where [Time-Series Cardinality](https://www.tigerdata.com/blog/time-series-cardinality) leaves off, so it does not re-argue the shape. If you control both the ingest and the queries, there is a gentler route: run a second ingest into the new table alongside the old one, then move queries over one at a time. That breaks the migration into small, separately reversible steps, and it is worth taking when you can. This guide covers the common case where you can't, because the ingest can't be duplicated or the queries aren't yours to rewrite. If you are on Tiger Cloud and would rather not run it yourself, the Enterprise plan includes a [migration team](https://www.tigerdata.com/enterprise) that will help design a migration plan around your own tables. ## Before you start What you get at the end is a table that stops fighting you. Readings land in a narrow hypertable of `(recorded_at, tag_id, value)`, so the next instrument the field adds becomes one row in a metadata table instead of a column across years of history. Every descriptor the wide table repeated on each reading now lives once, on the tag. And the dashboards you cannot take down keep working, because the old table name and the old columns survive as a view over the new shape. On a hundred million generated readings, the whole sequence took under seven minutes of machine time on our hardware, against a clean dataset with no live ingest competing for the disk; most of that was the reshape in Step 2. Three things need to be true before you schedule it: - The origin table is a TimescaleDB hypertable. The guide assumes it throughout. - You control new tag registration for the migration window. In practice that is a freeze on adding pads or instruments to your ingest for the duration, for a reason Step 2 makes concrete. - You can find a quiet minute for the swap in Step 4. This is a typical wide origin table. Tables shaped like this rarely come from a commercial historian such as PI, which keeps tag metadata apart from the readings and does not store them in SQL at all. They come from in-house ingest, where a project starts with a few wells and a hand-written `INSERT`, then keeps growing as pads and instruments are added. Map your own columns onto it; the names below are the ones every later step uses. ```SQL CREATE TABLE sensor_readings ( recorded_at TIMESTAMPTZ NOT NULL, tag_id TEXT NOT NULL, -- tag path, e.g. 'Pad07/Well3/TubingPressure' device_id TEXT NOT NULL, site TEXT NOT NULL, line TEXT NOT NULL, firmware_ver TEXT NOT NULL, unit TEXT NOT NULL, value DOUBLE PRECISION NOT NULL ) WITH ( tsdb.hypertable, tsdb.partition_column = 'recorded_at' ); CREATE INDEX ON sensor_readings (tag_id, device_id, site, line, firmware_ver, recorded_at DESC); ``` The DDL is shown in the current table-option form. Yours was probably created with `create_hypertable()`, and that changes nothing below. If your origin is a plain Postgres table rather than a hypertable, the migration gets easier in exactly two places: Step 3 gets `CREATE INDEX CONCURRENTLY` back, and the rename in Step 4 is an ordinary Postgres rename. Everything else is identical. ## Design the destination schema Create the destination before you touch the origin. Two tables: the metadata table owns the descriptors, and the narrow hypertable owns the facts. ```SQL CREATE TABLE tag_metadata ( tag_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, tag_name TEXT NOT NULL UNIQUE, -- 'Pad07/Well3/TubingPressure' device_id TEXT NOT NULL, site TEXT NOT NULL, line TEXT NOT NULL, firmware_ver TEXT NOT NULL, unit TEXT NOT NULL, ts_start TIMESTAMPTZ, ts_end TIMESTAMPTZ, ts_last_seen TIMESTAMPTZ ); CREATE TABLE sensor_readings_narrow ( recorded_at TIMESTAMPTZ NOT NULL, tag_id BIGINT NOT NULL, value DOUBLE PRECISION NOT NULL ) WITH ( tsdb.hypertable, tsdb.partition_column = 'recorded_at', tsdb.create_default_indexes = false, tsdb.segmentby = 'tag_id' ); ``` The shape itself is the standard one, and TigerData's [metadata table best practices](https://www.tigerdata.com/learn/best-practices-for-time-series-metadata-tables) cover the reasoning; the cardinality piece measured the benefits. Three choices inside that DDL decide whether the narrow shape pays off. **Keep `tag_id` a narrow BIGINT surrogate.** The text tag path goes in tag\_name once, on the metadata row. A fat TEXT key repeated on every fact row forfeits most of the win. **Enforce referential integrity at the ingest layer.** A foreign key from the facts to `tag_metadata` [adds a per-row check on a high-ingest path](https://www.tigerdata.com/blog/hidden-cost-postgres-constraints-scale#4-drop-the-fk-constraint), which is why the DDL above declares none. If you want the constraint anyway, the supported direction is a [hypertable referencing a regular table](https://www.tigerdata.com/docs/build/performance-optimization/ensure-data-integrity-with-constraints), which is the direction drawn here. **Segment by `tag_id`.** [Compression segmented by the tag](https://www.tigerdata.com/blog/ignition-and-timescaledb-perfect-pairing#third-convert-the-table-to-a-hypertable) is where the narrow schema's advantage widens most, and a source identifier is the [usual segmentby candidate](https://www.tigerdata.com/docs/reference/timescaledb/hypercore/alter_table#arguments). One consequence of creating the table this way: columnstore is on by default, so TimescaleDB [creates a columnstore policy automatically](https://www.tigerdata.com/docs/reference/timescaledb/hypercore/add_columnstore_policy), set to convert any chunk older than one chunk interval on a daily schedule. Every historical slice you are about to load is older than that, so the policy would start converting chunks while Step 2 is still writing next to them. The docs' own guidance for [backfilling while converting](https://www.tigerdata.com/docs/reference/timescaledb/hypercore#backfill-while-converting) is to pause the policy until the load is done, because conversion contends for locks with any concurrent write to the same chunk. Pause it now and re-enable it once the verification checks pass. ```SQL SELECT alter_job(job_id, scheduled => false) FROM timescaledb_information.jobs WHERE proc_name = 'policy_compression' AND hypertable_name = 'sensor_readings_narrow'; ``` Two things are deliberately absent from the DDL. There is no index on the narrow table yet; `tsdb.create_default_indexes = false` keeps TimescaleDB from adding one, and Step 3 builds the composite index after the load. And there is one `value DOUBLE PRECISION` column, which assumes every tag is a float. Industrial tags are not uniformly float. If yours carry booleans, state codes, or strings, the appendix at the end shows the typed-column variant of this schema and the three places it changes the steps. Make that call before you reshape. ## Step 0: Check that your tag descriptors are stable The whole migration rests on one assumption about your data: that a tag's descriptors are functionally dependent on the tag, so a given tag is always on the same device, at the same site, on the same line, reporting the same unit. The original post closes by asking you to check one property of your data before you plan anything. This is that check. Its answer decides whether the migration is an afternoon or a project. One scan, and it took about a minute against a hundred million rows. ```SQL SELECT tag_id, COUNT(*) FROM ( SELECT DISTINCT tag_id, device_id, site, line, firmware_ver, unit FROM sensor_readings ) d GROUP BY tag_id HAVING COUNT(*) > 1; ``` An empty result is the clean case: every tag has carried exactly one descriptor set across its whole history, the backfill in Step 1 falls straight out of the data, and the rest of this guide is for you. Rows mean some tags changed descriptors over their lifetime. On a pad this is routine. A workover moves a transmitter from one well to another, an instrument swap changes the device behind a tag path, or an RTU firmware upgrade bumps `firmware_ver` on every tag it serves. Those tags need availability windows, which is what the `ts_start` and `ts_end` columns in the metadata table exist for: one metadata row per descriptor set, bounded in time, with `ts_last_seen` tracking which row is current. That path makes the reshape join in Step 2 time-dependent and materially slower, and it is a longer migration, not just a different `INSERT`. This guide does not walk it; a follow-up will. If Step 0 returns rows, stop here and scope that migration instead of forcing this one. ## Step 1: Backfill the metadata table One scan of the wide table, one row per tag. The text tag path lands in tag\_name, and the surrogate is generated. ```SQL INSERT INTO tag_metadata (tag_name, device_id, site, line, firmware_ver, unit) SELECT DISTINCT s.tag_id, s.device_id, s.site, s.line, s.firmware_ver, s.unit FROM sensor_readings s WHERE NOT EXISTS ( SELECT 1 FROM tag_metadata m WHERE m.tag_name = s.tag_id ); ``` The guard is `NOT EXISTS` rather than `ON CONFLICT ... DO NOTHING`, and the difference matters. Both make the backfill safe to re-run, which Step 2 depends on. Only `NOT EXISTS` leaves Step 0's safety net armed: a tag that somehow carries two descriptor sets still trips the `UNIQUE` constraint on `tag_name` loudly, where `ON CONFLICT` would swallow it in silence and keep whichever set it saw first. ## Step 2: Reshape the facts in resumable time slices The pattern is a control table plus one transaction per time slice, where each transaction writes both the facts and its own completion marker. One unbounded `INSERT ... SELECT` over a multi-terabyte hypertable is the wrong instinct at this size, because a backfill this large cannot afford one long transaction: it holds a lock for hours, [generates write-ahead log faster than replicas can consume it](https://www.tigerdata.com/blog/postgres-performance-why-peak-throughput-benchmarks-miss-real-problem#wal-never-stops-arriving), and cannot resume if it dies at 90 percent. ```SQL CREATE TABLE migration_slices ( slice_start TIMESTAMPTZ PRIMARY KEY, slice_end TIMESTAMPTZ NOT NULL, completed_at TIMESTAMPTZ NOT NULL DEFAULT now(), row_count BIGINT NOT NULL ); ``` A control table, rather than a unique constraint on (tag\_id, recorded\_at), records completions because that constraint would be expensive at this scale and [may not even hold](https://www.tigerdata.com/blog/unified-namespace-historian-schema#the-schema-that-falls-out-of-it) in field telemetry. A week is a reasonable slice width for a table this size; pick whatever keeps one transaction to minutes on your hardware. ```SQL BEGIN; WITH moved AS ( INSERT INTO sensor_readings_narrow (recorded_at, tag_id, value) SELECT r.recorded_at, m.tag_id, r.value FROM sensor_readings r JOIN tag_metadata m ON m.tag_name = r.tag_id WHERE r.recorded_at >= TIMESTAMPTZ '2024-01-01' AND r.recorded_at < TIMESTAMPTZ '2024-01-08' RETURNING 1 ) INSERT INTO migration_slices (slice_start, slice_end, row_count) SELECT TIMESTAMPTZ '2024-01-01', TIMESTAMPTZ '2024-01-08', count(*) FROM moved; COMMIT; ``` The primary key on `slice_start` covers both ways a slice can be run twice. Retry a slice that already completed and the control-row insert fails on the key, taking the facts written in the same transaction down with it: nothing duplicated, one wasted slice of work. Kill a slice mid-run, as we did in the drill, and the transaction rolls back whole, no control row is written, and the retry starts from a clean slice. The inner join silently drops any tag missing from `tag_metadata`, with no error and no warning, and a tag that first appears after Step 1's backfill is exactly such a tag. That is what the registration freeze in the prerequisites is for. If you cannot hold the freeze, re-run Step 1 before each slice; the `NOT EXISTS` guard makes that free. How you drive the loop is the other trap. Ask the control table which windows are missing, and never track a `MAX(slice_end)` high-water mark, which steps straight over holes left by parallel slices or a crashed run. ```SQL SELECT g.slice_start, g.slice_start + INTERVAL '7 days' AS slice_end FROM generate_series(TIMESTAMPTZ '2024-01-01', now(), INTERVAL '7 days') AS g(slice_start) WHERE NOT EXISTS ( SELECT 1 FROM migration_slices s WHERE s.slice_start = g.slice_start ) ORDER BY g.slice_start; ``` Feed that list to whatever runs your slices, in parallel if you like, and the loop is done when it returns nothing. Stop one slice short of the present, though. Ingest is still landing in the wide table until the swap in Step 4, so the slice that contains now is incomplete by construction. Run it last, after the cutover, with `sensor_readings_old` as the source, because by then that table receives no writes and the slice is final. Never re-run a completed slice without first deleting its rows and its control row, or every fact in the window lands twice. ## Step 3: Build the indexes after the load [Build the indexes after the bulk load](https://www.tigerdata.com/docs/learn/hypertables/creating-and-configuring-hypertables#disable-default-indexes), not before. The load runs materially faster without an index to maintain, and the resulting B-tree is denser. The live-table reflex, [CREATE INDEX CONCURRENTLY](https://www.postgresql.org/docs/current/sql-createindex.html), is [not supported on hypertables](https://www.tigerdata.com/docs/reference/timescaledb/hypertables/create_index). The substitute is a one-line table option that builds the index one chunk per transaction. ```SQL CREATE INDEX sensor_readings_narrow_tag_time_idx ON sensor_readings_narrow (tag_id, recorded_at DESC) WITH (timescaledb.transaction_per_chunk); ``` One transaction per chunk means inserts into other chunks keep running while the index builds. If your read path has time-only scans with no tag predicate, add a plain `(recorded_at DESC)` index in the same step, since the DDL disabled the default one. This only goes wrong if the build is interrupted, by a cancel, a crash, or an error partway through. Then the root index is marked invalid, with some chunks indexed and some not. It keeps working and still applies to new chunks; only the chunks the build missed go without it. To guarantee full coverage, find it, drop it, and rebuild: ```SQL SELECT indexrelid::regclass FROM pg_index WHERE indisvalid IS FALSE; ``` Then `ANALYZE` both `sensor_readings_narrow` and `tag_metadata`. The join the compatibility view is about to put on the read path is exactly the plan that needs fresh statistics, and the planner has not seen a row of either table yet. ## Step 4: Cut over behind a compatibility view A rename is cheap in Postgres because tables are identified by OID rather than name, so `ALTER TABLE ... RENAME TO` is [a metadata operation whatever the table's size](https://www.postgresql.org/docs/current/sql-altertable.html), and hypertables rename the same way. Renaming alone is still not a non-breaking cutover. The rename preserves the table's name, not its shape. The narrow table has three columns where the old one had eight, so every query selecting `site` or `line` fails the moment the swap commits. A view restores the old shape under the old name, and the dashboards never learn the table moved. ```SQL BEGIN; SET LOCAL lock_timeout = '5s'; ALTER TABLE sensor_readings RENAME TO sensor_readings_old; CREATE VIEW sensor_readings AS SELECT r.recorded_at, m.tag_name AS tag_id, m.device_id, m.site, m.line, m.firmware_ver, m.unit, r.value FROM sensor_readings_narrow r JOIN tag_metadata m USING (tag_id); COMMIT; ``` The `lock_timeout` is not optional. The rename takes [`ACCESS EXCLUSIVE`](https://www.tigerdata.com/blog/how-timescaledb-solves-common-postgresql-problems-in-database-operations-with-data-retention-management#types-of-postgresql-locks). If one long-running read is in flight, the rename queues behind it, every later reader queues behind the rename, and a metadata operation turns into an outage. In a live two-session test, a three-second `lock_timeout` fired on schedule against a single held-open read instead of queueing behind it. Set it short and expect to retry during a quiet minute. The `CREATE VIEW` sits inside the same transaction on purpose: between the rename committing and the view existing there is no `sensor_readings` at all, and one short transaction closes that gap. The view is a join on the read path, and the cardinality piece measured what that join costs against querying the narrow table directly: about two percent on the rollups and below measurement noise on the sub-millisecond queries. The view is read-path only, so repoint your ingest at `sensor_readings_narrow` explicitly rather than through an `INSTEAD OF` trigger. In practice that is the ingest service writing `(recorded_at, tag_id, value)` with the surrogate resolved from `tag_metadata`, as soon as the swap commits. Without the view, normalizing would mean [rewriting every dashboard query](https://www.tigerdata.com/blog/how-relational-complexity-crushes-real-time-dashboards#the-join-explosion-problem) on cutover day; with it, the rewrite becomes incremental. Repoint your hottest queries at the narrow table when it suits you. The rewrite happens on your schedule, not on cutover day's. ![Normalizing a Wide Well and Pipeline Telemetry Table Without Re-Ingesting History](https://assets.tigerdata.com/blog/2026/09/normalizing-well-pipeline.svg) ## Re-point what the rename left behind Dependent objects do not follow a rename. [Continuous aggregates, retention and compression policies](https://www.tigerdata.com/docs/reference/timescaledb/continuous-aggregates/add_policies), triggers, and grants all stay bound to the renamed-away table. In our drill a real continuous aggregate kept aggregating `sensor_readings_old`, and nothing reported it: the rollup simply stopped seeing new rows the moment ingest moved to the narrow table. This is the class of failure most likely to cost you real data. Inventory every dependent object before the swap, while the table still has its original name. ```SQL -- Continuous aggregates reading the wide table SELECT view_name, materialization_hypertable_name FROM timescaledb_information.continuous_aggregates WHERE hypertable_name = 'sensor_readings'; -- Retention and columnstore policies bound to it. Refresh policies list -- under the aggregate's materialization hypertable, found above. SELECT job_id, application_name, proc_name, config FROM timescaledb_information.jobs WHERE hypertable_name = 'sensor_readings'; -- Triggers and grants SELECT tgname FROM pg_trigger WHERE tgrelid = 'sensor_readings'::regclass AND NOT tgisinternal; SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name = 'sensor_readings'; ``` Then re-point each one after the swap. A continuous aggregate cannot be re-targeted, so recreate it against `sensor_readings_narrow` (joined to `tag_metadata` if it groups by a descriptor, which continuous aggregates support from TimescaleDB 2.10; note that only changes to the hypertable are tracked, so an edit to a metadata row does not reach the aggregate until you refresh it by hand), add its refresh policy back, and drop the old one once the new one has caught up. Triggers get recreated on the narrow table. Grants on the old table become grants on the view, since that is what the old readers now hit. Treat retention policies as the dangerous ones, and re-create them last. A policy added to the narrow table judges every chunk you just moved on its first run, measured from `now()`, and any chunk older than `drop_after` goes immediately. In our drill a re-created 365-day policy over history whose newest rows were already older than a year deleted every chunk within seconds. Before you add the policy back, compare its window against the oldest chunk the narrow table now holds, and decide on purpose whether that history is meant to survive. If it is, the policy waits until you have moved or archived it. The old table's own retention policy is the mirror problem: it followed `sensor_readings_old` into retirement and keeps dropping chunks from the fallback you are counting on. Pause it with the same `alter_job` call right after the swap. ## Verify the migration, and know your move if a step fails Keep the old table until old and new agree on the overlap. Ingest is writing to the narrow table, the view is serving reads from it, and `sensor_readings_old` is still there untouched. Hold that [reconciliation window](https://www.tigerdata.com/docs/migrate/dual-write-and-backfill/dual-write-from-postgres#5-determine-the-completion-point-t) open until the checks below pass. The cheap check is per-slice row counts, using the counts the control table already recorded. ```SQL SELECT s.slice_start, s.row_count AS moved, (SELECT count(*) FROM sensor_readings_old o WHERE o.recorded_at >= s.slice_start AND o.recorded_at < s.slice_end) AS original FROM migration_slices s WHERE s.row_count <> (SELECT count(*) FROM sensor_readings_old o WHERE o.recorded_at >= s.slice_start AND o.recorded_at < s.slice_end) ORDER BY s.slice_start; ``` An empty result means every slice moved as many rows as the wide table held for that window. A row means either the inner join dropped a tag in that slice or ingest was still landing in that window when the slice ran; the failure table below has the move. The stronger check is a per-day checksum over a sample window on both sides: row count and first and last timestamp compared exactly, and the sum of every value compared with a relative tolerance. The tolerance is doing real work. Two full scans sum a hundred million doubles in a different physical order, and an exact comparison of the sums fails on floating-point rounding with no row out of place. In our validation run a seven-day checksum came back with zero mismatches. ```SQL WITH old_side AS ( SELECT recorded_at::date AS day, count(*) AS n, min(recorded_at) AS min_ts, max(recorded_at) AS max_ts, sum(value) AS sum_value FROM sensor_readings_old WHERE recorded_at >= TIMESTAMPTZ '2024-01-01' AND recorded_at < TIMESTAMPTZ '2024-01-08' GROUP BY 1 ), new_side AS ( SELECT recorded_at::date AS day, count(*) AS n, min(recorded_at) AS min_ts, max(recorded_at) AS max_ts, sum(value) AS sum_value FROM sensor_readings_narrow WHERE recorded_at >= TIMESTAMPTZ '2024-01-01' AND recorded_at < TIMESTAMPTZ '2024-01-08' GROUP BY 1 ) SELECT coalesce(o.day, n.day) AS day, o.n AS old_n, n.n AS new_n FROM old_side o FULL OUTER JOIN new_side n USING (day) WHERE o.n IS DISTINCT FROM n.n OR o.min_ts IS DISTINCT FROM n.min_ts OR o.max_ts IS DISTINCT FROM n.max_ts OR abs(o.sum_value - n.sum_value) > 1e-9 * greatest(abs(o.sum_value), abs(n.sum_value), 1); ``` Only when both checks pass, and your dashboards have run a normal cycle against the view, drop `sensor_readings_old`. That is also the moment to re-enable the columnstore job you paused in the schema step, with the same `alter_job` call and `scheduled => true`. Until you drop the old table, the cutover is a reversible rename. If a step fails before then, find your row. .td-table { border-collapse: collapse; width: 100%; font-size: 0.95em; } .td-table th, .td-table td { border: 1px solid #d0d0d0; padding: 10px 12px; vertical-align: top; text-align: left; } .td-table th { background: #f6f6f6; font-weight: 700; } .td-table code { font-family: ui-monospace, SFMono-Regular, "SF Mono", Menlo, Consolas, "Roboto Mono", monospace; font-size: 0.9em; color: #188038; background: rgba(24, 128, 56, 0.08); padding: 0.1em 0.35em; border-radius: 3px; white-space: nowrap; } | What happened | Why | Your move | | --- | --- | --- | | Step 1 trips the UNIQUE constraint on tag_name | A tag carries two descriptor sets | You are in the lifecycle case from Step 0. Stop and re-scope; do not weaken the constraint. | | A slice dies mid-run | Crash, kill, or timeout inside the transaction | Re-run the slice. The transaction rolled back whole and the primary key on slice_start makes a blind retry safe. | | Row counts disagree for a slice | A tag was registered after the backfill and the inner join dropped it, or ingest was still landing in the window when the slice ran | Re-run Step 1, delete that slice's rows and its control row, and run the slice again from sensor_readings_old. | | Some windows never completed | The loop was driven from a high-water mark | Ask migration_slices which windows are missing and run those. | | An index build fails partway | The root index is invalid | pg_index WHERE indisvalid IS FALSE finds it. Drop it and rebuild. | | The rename times out | A long-running read holds the lock | Retry during a quiet minute. Do not remove the lock_timeout. | | A dashboard breaks after cutover | The view does not expose a column that query selects | Fix the view, not the query. | | An aggregate or policy still reads the old table | It stayed bound through the rename | Re-point it per the previous section. | ## What you now own A narrow hypertable, a metadata table with one row per tag, dashboards that never noticed the swap, and an old table you can still fall back to until you choose not to. Before you book the window, rehearse it. Run Step 0 tonight. Then copy one week of your wide table into a scratch schema, run Steps 1 through 4 against it with the same freeze in place, and kill a slice mid-run on purpose so you see the rollback happen on your own hardware. And if you would rather not own the columnstore and retention policies you just re-created by hand, [Tiger Cloud manages the hypertable, compression, and policies](https://www.tigerdata.com/blog/self-hosted-timescaledb-vs-tiger-cloud-decision#what-does-tiger-cloud-manage-and-what-do-i-still-own). If the rehearsal leaves you wanting a second pair of eyes before the real window, TigerData's [support plans](https://www.tigerdata.com/support) put PostgreSQL and TimescaleDB experts on the other end of the ticket. ## Appendix: When your tags are not all floats The float-only schema keeps every step above readable, and plenty of telemetry tables really are all measurements. When yours also carry pump run states, valve positions, alarm codes, or a text mode label, the standard fix is what TigerData calls the [medium table layout](https://www.tigerdata.com/docs/learn/data-model/wide-narrow-medium-tables): one nullable value column per data type instead of one value column for everything. A new tag still costs one metadata row; only a new data type costs a column. ```SQL CREATE TABLE sensor_readings_narrow ( recorded_at TIMESTAMPTZ NOT NULL, tag_id BIGINT NOT NULL, value DOUBLE PRECISION, -- measurements value_int BIGINT, -- booleans as 0/1, state and alarm codes value_text TEXT, -- mode labels, free-text states CHECK (num_nonnulls(value, value_int, value_text) = 1) ) WITH ( tsdb.hypertable, tsdb.partition_column = 'recorded_at', tsdb.create_default_indexes = false, tsdb.segmentby = 'tag_id' ); ``` The float column keeps the name `value`, so the Step 4 view still serves the old `value` column unchanged for every measurement tag. The CHECK enforces exactly one value per row and needs no lookup, so unlike a foreign key it stays cheap on the ingest path. The rest of the migration changes in three places: - **Step 2:** the reshape INSERT carries each typed column from the wide table into its match instead of a single value. - **Step 4:** the compatibility view selects `value_int` and `value_text` alongside value, so queries reading state or text tags keep working. - **Verification:** the per-day checksum sums `value` only, so a state or text value that lands in the wrong typed column passes it. Add `count(value_int)` and `count(value_text)` to both sides.