Problem Creating INDEX

TimescaleDB and PostgreSQL
1

Hi everyone,
I’m migrating a database from vanilla PostgreSQL to TimescaleDB.
Specifically, I have a table containing measurements from various devices.
TimescaleDB was installed using Docker with the following image:
timescale/timescaledb-ha:pg17-all
Background: until now, to improve performance, we had used partitioning — one partition per device, and each device had yearly partitions. Now, I’ve migrated everything into a hypertable created like this:

CREATE TABLE public.sensors_data 
(   
    time timestamp without time zone NOT NULL, 
    device integer NOT NULL, 
    sensor integer NOT NULL, 
    value numeric,  
    time_upd timestamp without time zone DEFAULT now(), 
    CONSTRAINT sensors_data_pkey PRIMARY KEY (device, sensor , time) 
) 
WITH (
   tsdb.hypertable = true,
   tsdb.partition_column = 'time', 
   tsdb.chunk_interval = '30 days',
   tsdb.create_default_indexes = true,
   tsdb.associated_table_prefix = 'sensors_hyp', 
   tsdb.orderby = 'time ASC', 
   tsdb.segmentby = 'device, sensor ' 
);

CALL add_columnstore_policy('public.sensors_data', after => INTERVAL '30d');

My problem is this: I need to create a regular index on the time_upd column, but although no error is returned, the index doesn’t seem to work.

In the partitioned table, the index was created like this:

CREATE INDEX time_upd_idx ON public.sensors_data (time_upd);

I also tried, as suggested in the documentation:

CREATE INDEX time_upd_idx ON public.sensors_data (time, time_upd);

I also attempted a REINDEX on the table.

However, none of these approaches seem to work. Is there a way to have a working index on this column?
Note: the original table was around 520 GB in size, and now the hypertable is only 42 GB. The index creation process on the partitioned table used to take several hours; with the hypertable, it only took about 2 minutes, which seems odd to me.

Thanks for your support.

2

Hi, @richimela!

This is because hypercore TAM is not set for the hypertable. For your hypertable you should set hypercore TAM for every existing chunk.
PostgreSQL Indexes for Columnstore: 1,185x Faster Lookup Queries, 224x Faster Inserts in TimescaleDB

The table access method configured for the hypertable will only be applied to new chunks. To change the table access method for an existing chunk, you use the ALTER TABLE command on that specific chunk:

ALTER TABLE _timescaledb_internal._hyper_1_1_chunk SET ACCESS METHOD hypercore;