How to create multiple column partitions for postgresql db, one for time range and one for specific sensor ID?

Question

I have one application to store and query the time series data from multiple sensors. The sensor readings of multiple months needed to be stored. And we also need add more and more sensors in the future. So I need consider the scalability in two dimensions, time and sensor ID. We are using postgresql db for data storage. In addition, to simplify the data query layer design, we want to use one table name to query all the data.

In order to improve the query efficiency and scalability, I am considering to use Partitions for this use case. And I want to create the partitions based on two columns. RANGE for the event time for the readings. And VALUE for the sensor ID. So under the paritioned table, I want to get some sub table as sensor_readings_1week_Oct_2020_ID1, sensor_readings_2week_Oct_2020_ID1, sensor_readings_1week_Oct_2020_ID2. I knew PostgreSQL supports multiple columns Partition, but from most examples I can only see RANGE are used for all the columns. One example is as below. How can I create the multiple column partitions, one is for the time RANGE, another is based on the specific sensor ID? Thanks!

CREATE TABLE p1 PARTITION OF tbl_range
FOR VALUES FROM (1, 110, 50) TO (20, 200, 200);

Or are there some better solutions besides Paritions for this use case?

Answer 1

The two level partitions is a good solution for my use case. It improves the efficiency a lot.

CREATE TABLE sensor_readings (
    id bigserial NOT NULL,
    create_date_time timestamp NULL DEFAULT now(),
    value int8 NULL
) PARTITION BY LIST (id);
        
CREATE TABLE sensor_readings_id1 
  PARTITION OF sensor_readings 
  FOR VALUES IN (111) PARTITION BY RANGE(create_date_time);
        
CREATE TABLE sensor_readings_id1_wk1 
  PARTITION OF sensor_readings_id1 
  FOR VALUES FROM (''2020-10-01 00:00:00'') TO (''2020-10-20 00:00:00'');