Back in 2018 I deployed a project by one of my customers where I did not partition the tags history. Now after 8 years, the database has grown very huge and my customer is asking me to cut the first 4 years of samples.
I’ve 2 questions:
can I simply delete records from the sqlth_1_data table where t_stamp<‘20220101’? Will Ignition get confused if I leave the other historian table as they are (sqlth_partitions, sqlth_sce, sqlth_scinfo)?
If, after records deletion, I enable partitioning and pruning, will Ignition get confused? Will the customer keep getting trends correctly if they go back to 2022, i.e. to a period in time when partitioning was not enabled yet?
Yes you can just delete the records in the table, that will not have an negative effect, just be very careful to insure your where clause is filtering the correct data that you want to prune.
It would be extreamly prudent to grab a backup of the table prior to running any deltion operations.
You should be able to turn on partitioning an pruning, though I would probably do that first. IIRC, partitioning will not take effect until the next partitioning period. Once you verify that partitioning is working and Ignition is performing as you would expect, then you can manually prune the initial table.
After you make the deletions, I would update the minimum timestamp in the sqlth_partitions table to make sure the historian doesn't think there's any old data present and try to find it, wasting cycles. I don't know offhand how much that actually matters, though.