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.

To follow the procedure on this page you need to:

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.

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.

  1. 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);
  2. Convert a chunk to update back to the rowstore

    CALL convert_to_rowstore('_timescaledb_internal._hyper_2_2_chunk');
  3. 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);
  4. Convert the updated chunks back to the columnstore

    CALL convert_to_columnstore('_timescaledb_internal._hyper_1_2_chunk');
  5. Restart the jobs that are automatically converting chunks to the columnstore

    SELECT alter_job(JOB_ID, scheduled => true);

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

  1. 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);
  2. Convert a chunk to update back to the rowstore

    CALL convert_to_rowstore('_timescaledb_internal._hyper_2_2_chunk');
  3. Modify the schema:

    Possible modifications are:

    • Add a nullable column:

      ALTER TABLE <hypertable> ADD COLUMN <column_name> <datatype>;

    • Add a column with a default value and a NOT NULL constraint:

      ALTER TABLE <hypertable> ADD COLUMN <column_name> <datatype> NOT NULL DEFAULT <default_value>;

    • Rename a column:

      ALTER TABLE <hypertable> RENAME <column_name> TO <new_name>;

    • Drop a column:

      ALTER TABLE <hypertable> DROP COLUMN <column_name>;

    You cannot change the data type of an existing column.

  4. Convert the updated chunks back to the columnstore

    CALL convert_to_columnstore('_timescaledb_internal._hyper_1_2_chunk');
  5. Restart the jobs that are automatically converting chunks to the columnstore

    SELECT alter_job(JOB_ID, scheduled => true);

Keywords

Found an issue on this page?Report an issue or Edit this page in GitHub.