Preserving device _id values during table recreation/upgrade #18624

Hi CrateDB team / community,

I’m working at Hygenco Green Energies Pvt. Ltd. as a Staff Engineer, building a large-scale multi-tenant IoT platform using CrateDB for both device metadata and high-volume time-series measurement data. I’m reaching out because we’ve encountered a schema & upgrade challenge and would greatly appreciate your guidance.

Our Architecture & Design Rationale

We maintain a “device” table whose rows are identified by the implicit system column _id (we rely on CrateDB’s internal _id). [Ref: System columns page ⇒ _id is an internal document identifier.]
cratedb.com

We store measurement data in a separate table (“timeseries_measurement”) and use source_id = device._id to reference the device.

Typical queries use:

SELECT … FROM timeseries_measurement
WHERE tenant_id = X
AND source_id = Y
AND time >= T1 AND time < T2;

We chose this design because:

Using the system _id gives us a fixed, unique ID that aligns with our routing/partitioning strategy and efficient lookups.

It allowed us to avoid the extra complexity of defining composite primary keys, aligning routing/partition keys, and managing those constraints explicitly.

Because the device _id is referenced across multiple downstream tables (measurements, logs, events), this relational style simplified our application logic.

The Upgrade / Migration Concern

We’re planning a major version upgrade of CrateDB. We’ve reviewed the docs and community posts where it states that tables created in older versions may need to be recreated or reindexed for compatibility. For example, the system-information docs note compatibility caveats.
cratedb.com
+1

Our key concern: if we recreate the “device” table and the _id values are regenerated or changed, our measurement table’s source_id references will break. As we have tens or hundreds of millions of measurement rows referencing device._id, this is a major operational risk.

Additional Note: Custom ID Column Alternative

We understand that one alternative would be to define our own custom id column (for example device_id of type UUID) and treat that as the primary key, cluster/routing column etc. We could then use that custom column instead of the system _id.
However:

It seems wasteful to re-generate a UUID for each device when CrateDB already implicitly generates _id for each row.

We would prefer to continue leveraging the existing _id values because they already serve our lookup/relational design.

We also want to avoid the extra complexity of managing a custom ID + routing/partition constraints on top of our current setup.

Our Ask / Questions

Is there a supported mechanism or recommended workflow in CrateDB to recreate or migrate a table during a major version upgrade while preserving the original _id values, thereby keeping downstream references valid?

If yes — can you share best-practices or example workflows (e.g., INSERT … SELECT, COPY TO / FROM, snapshot/restore) for preserving _id values through migration?

If no native mechanism exists, what is your recommended strategy when an application depends on stable _id values across tables (i.e., foreign-key style lookups) during schema/table migration/upgrades?

Are there any version-specific caveats or constraints we should plan for? For example: “tables created prior to version X cannot be retained when upgrading to version Y” or metadata changes that impact _id preservation.

Any documentation links, checklists or migration scripts you can share to assist us in planning our upgrade path without breaking referential-lookups would be immensely helpful.

Thank you in advance for your help and time. We’re planning our upgrade path very carefully and would appreciate any guidance or pointers you can share.

Best regards,
Aman Sharma
Staff Engineer | Hygenco Green Energies Pvt. Ltd.
Email: aman.sharma@hygenco.in