Activate Timescale for "OBSERVATIONS" table

[claudiadragoste]

Hello,

I have a doubt about activating the timescale for the table "OBSERVATIONS". Is there somebody that can share their experience?

In a Sensorthings Training about Frost Deployment, the recomandation was to do:

ALTER TABLE public."OBSERVATIONS" drop constraint "OBSERVATIONS_PKEY";
SELECT create_hypertable('"OBSERVATIONS"','PHENOMENON_TIME_START’);
CREATE INDEX "OBSERVATIONS_ID" ON public."OBSERVATIONS"("ID");

comenting that (and I quote) (https://ediaqi.eu/sites/default/files/resources/03%20-%20FROST%20Deployment.pdf):

"Timescale creates hypertables «splitting» tables along their temporal dimention,
but cannot guarantee cross-tables uniqueness constraints;
The solution is to remove the uniqueness constraint from the observation ID but keeping the
index (the sequential ID generator will guarantee uniqueness nonetheless);

My question is: should we do it like it is described?

What do you think about this other solution:

ALTER TABLE "OBSERVATIONS" DROP CONSTRAINT "OBSERVATIONS_pkey";
ALTER TABLE "OBSERVATIONS" ADD PRIMARY KEY ("ID", "PHENOMENON_TIME_START");
SELECT * FROM create_hypertable('OBSERVATIONS', 'PHENOMENON_TIME_START');

Thanks again,

[hylkevds]

Try it out and see if it works...
Though I think the time column must be the first column in the primary key.

[aiguesmanresa]

Hi @hylkevds, when we execute:

DROP CONSTRAINT "OBSERVATIONS_pkey";

We're getting several FK errors because we have all plugins enabled (STAPlus and Tasking Core):

SQL Error [2BP01]: ERROR: cannot drop constraint OBSERVATIONS_pkey on table "OBSERVATIONS" because other objects depend on it Detail: constraint RELATIONS_SUBJECT_ID_FKEY on table "RELATIONS" depends on index "OBSERVATIONS_pkey" constraint RELATIONS_OBJECT_ID_FKEY on table "RELATIONS" depends on index "OBSERVATIONS_pkey" constraint GROUPS_OBSERVATIONS_OBSERVATION_ID_FKEY on table "GROUPS_OBSERVATIONS" depends on index "OBSERVATIONS_pkey" Hint: Use DROP ... CASCADE to drop the dependent objects too.

We can go to "RELATIONS" table and do:

ALTER TABLE "RELATIONS" DROP CONSTRAINT "RELATIONS_SUBJECT_ID_FKEY";

We can DROP all troublesome FKs and then execute:

SELECT * FROM create_hypertable('OBSERVATIONS', 'PHENOMENON_TIME_START');

So far so good. We have OBSERVATIONS converted to HYPERTABLE, but from now on, we won't be able to restore FKs because we won't be able to create OBSERVATIONS("ID") as PRIMARY KEY anymore. From this moment on OBSERVATIONS("PHENOMENON_TIME_START", "ID") must be the PRIMARY KEY (or whatever involving "PHENOMENON_TIME_START", but never again a single "ID" field).

At this point, how could we restore previous FKs to ensure data integrity?

Thanks

[hylkevds]

The target of a foreign key can be a unique index, it doesn't have to be a primary key.

[aiguesmanresa]

We've already tried that, but when should we create the unique index?

I mean, if we first convert table OBSERVATIONS to hypertable and then try to create the unique index, the error thrown is the one that follows:

SQL Error [TS103]: ERROR: cannot create a unique index without the column "PHENOMENON_TIME_START" (used in partitioning)

On the contrary, if we first create the unique index (with or without creating a primary key) and then we try to convert OBSERVATIONS to hypertable, the result is the same:

SQL Error [TS103]: ERROR: cannot create a unique index without the column "PHENOMENON_TIME_START" (used in partitioning)

After creating the unique index referencing "ID" field, cannot convert table to hypertable.

What are we missing?

Thanks

[hylkevds]

In that case it's not possible to create a foreign key to the Observations table when using Timescale.
The only issue with not having a foreign key is that relations between Observations won't be automatically cleaned up when an Observation is removed. The relation itself will still work just fine.

[georghas]

Hello,

just chiming in real quick to mention this thread.
@limond presented this setup recommendation. They create the OBSERVATIONS table completely anew.

I used their response and tweaked it slightly to hypertable_setup.sql.txt (GitHub does not support .sql extension for uploads, that is why I upload it as .txt). Note that this does not include compression. Storage and/or performance might improve if you do enable it.

As a side note, based on some of my benchmarks and @ndevilleBE's observations, enabling Timescale in FROST is not objectively good. It can be good in some specific circumstances. Otherwise, it may even perform worse. FROST was not built with TimescaleDB in mind. Hence, there are some internal database requests that are not ideal for TimescaleDB. Anyway, @ndevilleBE claim that they experience performance improvements at 1 billion rows. My benchmarks show slight performance improvements starting at 500+ million rows. With fewer than 500 million rows, I did not notice substantial differences. Furthermore, TimescaleDB performs specifically well if you request data within a short interval. (E.g. all recent data from within a week.) Otherwise it is not as useful, if at all.

Tl;dr: TimescaleDB is good with a large data set and with ordered requests. Otherwise default Postgres suffices.