Modify your data in Hypercore

You set up Hypercore to automatically convert data between the rowstore and columnstore when it reaches a certain age. After you have optimized data in the columnstore, you may need to modify it. For example, to make small changes, or backfill large amounts of data. You may even have to update the schema to accommodate these changes to the data.

This page shows you how to update small and large amounts of new data, and update the schema in columnstore.

Prerequisites

To follow the procedure on this page you need to:

Create a target Timescale Cloud service

Optimize your data for real-time analytics

Modify small amounts of data

You can INSERT UPDATE and DELETE data in the columnstore, even if the data you are inserting has unique constraints. When you insert data into a chunk in the columnstore, a small amount of data is decompressed to allow a speculative insertion, and block any inserts which could violate constraints.

When you DELETE whole segments of data, filter your deletes using the column you segment_by instead of separate deletes. This considerably increases performance.

Modify large amounts of data

If you need to modify or add a lot of data to a chunk in the columnstore, best practice is to stop any jobs moving chunks to the columnstore, convert the chunk back to the rowstore, then modify the data. After the update, convert the chunk to the columnstore and restart the jobs. This workflow is especially useful if you need to backfill old data.

Stop the jobs that are automatically adding chunks to the columnstore
Retrieve the list of jobs from the timescaledb_information.jobs view to find the job you need to alter_job.
```
SELECT alter_job(JOB_ID, scheduled => false);
```

Convert a chunk to update back to the rowstore


CALL convert_to_rowstore('_timescaledb_internal._hyper_2_2_chunk');

Update the data in the chunk you added to the rowstore
Best practice is to structure your INSERT statement to include appropriate partition key values, such as the timestamp. TimescaleDB adds the data to the correct chunk:
```
INSERT INTO metrics (time, value)
VALUES ('2025-01-01T00:00:00', 42);
```

Convert the updated chunks back to the columnstore


CALL convert_to_columnstore('_timescaledb_internal._hyper_1_2_chunk');

Restart the jobs that are automatically converting chunks to the columnstore
```
SELECT alter_job(JOB_ID, scheduled => true);
```

Modify a table schema for data in the columnstore

You can modify the schema of a table in the columnstore. To do this, you need to:

Stop the jobs that are automatically adding chunks to the columnstore
Retrieve the list of jobs from the timescaledb_information.jobs view to find the job you need to alter_job.
```
SELECT alter_job(JOB_ID, scheduled => false);
```