timescale.sql

-- Extensions
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Data source
CREATE TABLE IF NOT EXISTS sources (
  source_id SERIAL PRIMARY KEY,
	source_key VARCHAR(64) NOT NULL,
	facility_id INT NOT NULL,
	description TEXT,
  UNIQUE(source_key)
);

-- Data
CREATE TABLE IF NOT EXISTS source_data(
    time TIMESTAMPTZ NOT NULL,
    source_id INT NOT NULL REFERENCES sources(source_id),
    data JSONB NOT NULL,
    PRIMARY KEY (time, source_id)
);

-- Indexes
CREATE INDEX IF NOT EXISTS source_data_source_time_idx ON source_data (source_id, time DESC);
CREATE INDEX IF NOT EXISTS source_data_data_gin ON source_data USING GIN (data);

SELECT create_hypertable('source_data', 'time', if_not_exists => TRUE, chunk_time_interval => INTERVAL '72 hours');

CREATE OR REPLACE PROCEDURE migrate_data()
LANGUAGE PLPGSQL
AS $$
DECLARE
    chunk_size interval;
    start_time timestamptz;
    stop_time timestamptz;
BEGIN
    SELECT '2 months' INTO STRICT chunk_size;
    
	FOR start_time IN SELECT generate_series('2020-01-01', now(), chunk_size)
	LOOP
		-- Migrating a chunk
		RAISE NOTICE 'Migrating data from % to %', start_time, start_time + chunk_size;
		SELECT start_time + chunk_size INTO STRICT stop_time;
		INSERT INTO source_data_new
		SELECT time, source_id, data FROM source_data
		WHERE time BETWEEN start_time AND stop_time
		ON CONFLICT (time, source_id) DO NOTHING;
	END LOOP;
END
$$;

CALL migrate_data();