CODEWITHSUNDEEP
postgresqltime-seriestimescaledb
David Montgomery
David Montgomery

Reputation: 473

Segment by in TimescaleDB requires foreign key id to be used for segmenting

I am working with the following use case: A large number of users that each have their own separate time series. Each measurement in the time series has been made with a device, that has some accompanying metadata.

To create a TimescaleDB hypertable for this, I did the following:

CREATE TABLE devices (
  id              VARCHAR     NOT NULL,
  brand           VARCHAR,
  model           VARCHAR,
  serial_number   VARCHAR,
  mac             VARCHAR,
  firmware        VARCHAR,
  UNIQUE (id),
  PRIMARY KEY (id)
);
CREATE TABLE measurements (
  time                                 TIMESTAMPTZ         NOT NULL,
  measurement_location   VARCHAR,
  measurement_value       DOUBLE PRECISION    NOT NULL,
  device_id,                        VARCHAR             NOT NULL,
  customer_id                    VARCHAR             NOT NULL,
  FOREIGN KEY (device_id) REFERENCES devices (id)
);
SELECT create_hypertable('measurements', 'time');
ALTER TABLE measurements SET (
  timescaledb.compress,
  timescaledb.compress_segmentby='customer_id'
);

I wanted to segment by the customer id - since all measurements for a user is what generally will be queried.

However, when I do this I get the following error:

ERROR:  column "device_id" must be used for segmenting
DETAIL:  The foreign key constraint "measurements_device_id_fkey" cannot be enforced with the given compression configuration.

Why is it that I must use the foreign key for my segmentation? Is there another better way to accomplish what I want to do here?

Upvotes: 0

Views: 1296

Answers (1)

dbeng
dbeng

Reputation: 61

Timescale engineer here. One of the limitations with compression is that we cannot cascade deletes from foreign tables to compressed hypertables, unless it is a non compressed column. Segment by columns are stored in non compressed form. That's the reason behind the restriction on foreign key constraints.

Upvotes: 2

Related Questions