Timescaledb

Installation

Currently working on arch AUR package: https://aur.archlinux.org/packages/timescaledb/

Creating timescaledb hypertables

Large tables are maintained with timescaledb extension:

• Create model and migrate as usual with django manage.py. You might want to define the id field like this to avoid problem with big tables: id = models.BigIntegerField(unique=True, primary_key=True)
• Then copy the structure of the table but not the indexes:

CREATE TABLE ihr_delay_alarms_new (LIKE ihr_delay_alarms INCLUDING DEFAULTS INCLUDING CONSTRAINTS EXCLUDING INDEXES);

• Set the proper tablespace:

ALTER TABLE ihr_delay_alarms_new SET TABLESPACE nas0_slow_tablespace ;

• For big tables,change the id type to bigint:

ALTER TABLE project ALTER COLUMN id TYPE BIGINT;

• Create the hypertable and insert data in it:

SELECT create_hypertable('ihr_delay_alarms_new', 'timebin', chunk_time_interval => interval '7 day');
INSERT INTO ihr_delay_alarms_new  SELECT * FROM ihr_delay_alarms;

• Create necessary indexes:

CREATE INDEX ON ihr_delay_alarms_new (asn_id, timebin DESC);

• Rename tables:

ALTER TABLE ihr_delay_alarms RENAME TO ihr_delay_alarms_old;
ALTER TABLE ihr_delay_alarms_new RENAME TO ihr_delay_alarms;