Hypertable structure and doubts

1

Here is our current hypertable structure, generated through Object-Relational Mapping (ORM) using Spring Boot:

– db_vehicle_tracking.vehicle_tracking definition

CREATE TABLE db_vehicle_tracking.vehicle_tracking (

angle float8 NULL,

speed float8 NULL,

date_time timestamp(6) NOT NULL,

trip_id int8 NULL,

ip_address varchar(15) NULL,

vehicle_number varchar(15) NOT NULL,

device_type varchar(30) NULL,

current_status varchar(50) NULL,

params varchar(255) NULL,

“location” public.geometry(point, 4326) NOT NULL,

CONSTRAINT vehicle_tracking_device_type_check CHECK (((device_type)::text = ANY ((ARRAY[‘ELIXIA’, ‘FLEETX’, ‘DRIVER_APP’])::text))),

CONSTRAINT vehicle_tracking_pkey PRIMARY KEY (date_time, vehicle_number)

);

CREATE INDEX vehicle_tracking_date_time_idx ON db_vehicle_tracking.vehicle_tracking (date_time DESC);

CREATE INDEX vehicle_tracking_vehicle_number_date_time_idx ON db_vehicle_tracking.vehicle_tracking (vehicle_number, date_time DESC);

We have also created the hypertable and applied the respective policies using the following query:

SELECT create_hypertable(

‘oorjaa.db_vehicle_tracking.vehicle_tracking’,

‘date_time’,

‘vehicle_number’,

chunk_time_interval => INTERVAL ‘1 week’,

number_partitions => 10

);

SELECT add_compression_policy(‘oorjaa.db_vehicle_tracking.vehicle_tracking’, INTERVAL ‘1 week’);

SELECT add_retention_policy(‘oorjaa.db_vehicle_tracking.vehicle_tracking’, INTERVAL ‘6 months’);

We track 1,000 vehicles per day, with about 1,000 records inserted per second, totaling around 1 million rows daily. These numbers may increase in the future, so I would appreciate your input on whether the configuration related to chunks, compression, and retention is optimal for our use case.

Additionally, I have the following questions:

  1. When setting a compression or retention policy on a table with existing data, is there a way to specify a start time to avoid the immediate deletion of older data?
  2. Can chunk intervals, compression, and retention settings be modified later as our requirements evolve?

When trying to alter the device_type enum, I encountered the following error due to compression:

SQL Error [0A000]: ERROR: operation not supported on hypertables that have compression enabled

  1. Is there an alternative approach to updating the enum check without decompressing the data?
  2. Do policies like compression and retention depend on the role of the user who creates them? What is the recommended way to manage these policies?
  3. Is it possible to back up compressed data to S3 cold storage before retention policies are enforced?