How to revert back to normal table from hypertable

TimescaleDB and PostgreSQL
, , , ,
1

Hello,

I created hypertable and saw a performance decline because many chunks were created (1 sec interval). This was done automatically because I did not specify INTERVAL. The documents however says that default interval of 7 days are created when nothing is specified.

Now I wan to revert back to normal table and re-specify the interval. DROP command will delete data and I do not want this.

I have combed the documentations pages but could not find the right syntax. I am new to Timescale and beginning to think this is not possible . I would be glad if my assumption is wrong here because I do not want to lose my data.

Could anyone help out here?

2

Welcome @firmphem :nerd_face:

Let’s say you want to convert your actual_hypertable into your new_hypertable. First, duplicate your data with a command to Create a table from select:

CREATE TABLE new_hypertable AS SELECT * FROM actual_hypertable;

Then you can convert it back with create_hypertable call.

select create_hypertable('new_hypertable', by_range(INTERVAL '<your new interval>'));

Now confirm that your data is present in both in the same amount:

select count(1) from new_hypertable;
select count(1) from actual_hypertable;

if they’re the same, you’re good to go and drop the old or rename it:

alter table actual_hypertable rename to old_hypertable;

With the original name free, then you can rename the new_hypertable back to actual_hypertable in the same way.

alter table new_hypertable rename to actual_hypertable;

Later, drop the old_hypertable and that’s it. :slight_smile:

3

Hi,
We have a requirement where we have to convert the chunk intervals of an existing hyper table from 7 days to 3.5 days.

Now the challenge here is that the existing hyper table has approx 5 billion of data, and we would like to change the chunk interval for both new and old chunks to 3.5 days from 7 days.

Also, note that this table is getting updated regularly on production means live data is keep coming so how we can perform such kind of activity in a production environment without affecting to any data safely?

We are keen to perform this activity to improve the performance of the queries as with the existing chunk size of 7 days. We are having performance issues, and we made a POC at our end with similar kind of data and noticed via trial and error that with 3.5 days of interval length, we are getting better result compare to 7 days of chunk interval.

4

Hi Bhavin,
For your scenario with a 5 billion row production hypertable, here’s a more accurate approach:

TimescaleDB doesn’t have a direct built-in method to change chunk intervals for existing chunks. Your best option is a migration strategy:

  1. Create a new hypertable with 3.5-day chunks:
CREATE TABLE new_hypertable (/* same schema as original */);
SELECT create_hypertable('new_hypertable',  
                         by_range('time_column', INTERVAL '3.5 days');
  1. Create all indexes and constraints on the new table before data loading:
CREATE INDEX ... ON new_hypertable ...;
-- Add all indexes/constraints from original table
  1. Migrate data in batches during low-traffic periods:
INSERT INTO new_hypertable 
SELECT * FROM original_hypertable 
WHERE time_column >= '2023-01-01' AND time_column < '2023-01-08';
-- Repeat for each time window

Find a window size that works for you, like a week, a day or an hour.

  1. Set up a CDC solution or application-level dual writes to keep both tables in sync during migration.

  2. When ready for cutover, lock the original table briefly, perform final sync, and rename tables:

BEGIN;
LOCK TABLE original_hypertable IN ACCESS EXCLUSIVE MODE;
-- Final sync of any remaining data
ALTER TABLE original_hypertable RENAME TO original_hypertable_old;
ALTER TABLE new_hypertable RENAME TO original_hypertable;
COMMIT;

This non-trivial operation requires thorough planning, sufficient disk space, and careful execution in a production environment.