Convert hypertable to regular postgres table

Question

quite new to timescaledb and I'm struggling with a migration script. I'm trying to create migrations for a flask application with SQLAlchemy.

Let's say I created a table (as in timescaledb docs) like the following:

CREATE TABLE conditions (
  time        TIMESTAMPTZ       NOT NULL,
  location    TEXT              NOT NULL,
  temperature DOUBLE PRECISION  NULL,
  humidity    DOUBLE PRECISION  NULL
);

To add the hypertable, my upgrade migration script should do:

SELECT create_hypertable('conditions', 'time');

What should the downgrade part look like ? From timescaledb docs, they suggest:

DROP table conditions;

But I don't want the whole table to be dropped, only the "hypertable" part if that makes sense. Maybe this is silly and pointless, I want to provide a way out of timescaledb via our migrations. I've already read this SO question: Creating Hypertables through SQL Alchemy where no specific support seems provided for SQLAlchemy and they suggest triggers to create hypertables instead of a specific migration.

What would you suggest ?

Pedaias

Answer 1

You need to migrate and drop. A hypertable is not just some additional information we stick on top of an underlying table, it's a different partitioning/organization of the data.

So in the above command, when you immediately call create_hypertable after creating the table -- you don't yet have any data in the table, so we're just changing around schema definitions and such. But if you call create_hypertable on a table that already has data (with the explicit migrate_data argument [1] to the create_hypertable command), we need to migrate data (which involves copying it from your existing table to the new internal chunks/tables we create.

Hence, "migrating it back" to a standard table would again involve moving the data around inside the hypertable, so it's really akin to just creating a new standard table, copying the data from the hypertable to the standard table, then deleting the hypertable.

[1] https://docs.timescale.com/api/latest/hypertable/create_hypertable/

Plagiary

Answer 2

As Mike says hypertables are an entirely different storage mechanism which means you cannot simply turn them off. Instead like when you convert a table with data to a hypertable you need to migrate a table out of a hyper table.

-- if you already have data in a table, you need to migrate that data
SELECT create_hypertable('conditions', 'time',  migrate_data => true);

You could use any of the answers from here to copy data https://mcmap.net/q/157479/-how-do-i-copy-data-from-one-table-to-another-in-postgres-using-copy-command but here's what I'd do as part of a migration downgrade.

CREATE TABLE pg_conditions (LIKE conditions INCLUDING ALL); -- duplicate table structure
INSERT INTO pg_conditions (SELECT * FROM conditions); -- copy all data
DROP TABLE conditions; -- drops hypertable
ALTER TABLE pg_conditions RENAME TO conditions; -- conditions is now a regular postgres table again

Communicable

Thanks for the late but precise answer ! – Pedaias