Invalid number of partitions error while migrating PostgreSQL table to TimescaleDB

I have a table:

CREATE TABLE mytable
(
    device_id bigint       NOT NULL,
    start    TIMESTAMP WITHOUT TIME ZONE,
    level    varchar(255) NOT NULL,
    amount   integer
);

and I want to migrate it to a hypertable like that:

SELECT create_hypertable('mytable', 'start','device_id', migrate_data => true);

but I get an error:

ERROR:  invalid number of partitions for dimension "device_id"
HINT:  A closed (space) dimension must specify between 1 and 32767 partitions.
SQL state: 22023

What am I doing wrong? I had similar tables, where it worked without an issue.

Using PostgreSQL 11 and TimescaleDB 1.7.4 running on Azure PostgreSQL.

Solution

The error is not related to migration of data. It is due to specified space dimension device_id without specifying the number of dimensions. From the documentation of create_hypertable:

| `partitioning_column` | Name of an additional column to partition by. |
| `number_partitions` | Number of hash partitions to use for `partitioning_column`. Must be > 0. Default is the number of `data_nodes`. |

For example, the create hypertable statement can be fixed to

SELECT create_hypertable('mytable', 'start','device_id', 4, migrate_data => true);

Where the magic number 4 for number of partitions requires good reasoning.

Actually space dimension is rarely needed and according to the best practice in the same documentation not recommended in usual case:

Space partitions: In most cases, it is advised for users not to use space partitions. However, if you create a distributed hypertable, it is important to create space partitioning, see create_distributed_hypertable. The rare cases in which space partitions may be useful for non-distributed hypertables are described in the add_dimension section.

So it might be better to use the following statement without specifying space partition:

SELECT create_hypertable('mytable', 'start', migrate_data => true);