Answer a 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 ?

Answers

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/