Search code examples
postgresqltimescaledbazure-postgresql

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);