add_dimension()

Add a space-partitioning dimension to a hypertable

Since 0.1.0

Add an additional partitioning dimension to a . You can only execute this add_dimension command on an empty . To convert a normal table to a , call create hypertable.

The column you select as the dimension can use either:

• Interval partitions: for example, for a second range partition.
• hash partitions: to enable parallelization across multiple disks.

These instructions are for self-hosted deployments

Best practice is to not use additional dimensions. However, transparently provides seamless storage scaling, both in terms of storage capacity and available storage IOPS/bandwidth.

This page describes the generalized API introduced in v2.13.0. For information about the deprecated interface, see add_dimension(), deprecated interface.

Samples

First convert table conditions to a hypertable with just range partitioning on column time, then add an additional partition key on location with four partitions:

SELECT create_hypertable('conditions', by_range('time'));
SELECT add_dimension('conditions', by_hash('location', 4));

The by_range and by_hash dimension builders are an addition to 2.13.

Convert table conditions to a hypertable with range partitioning on time then add three additional dimensions: one hash partitioning on location, one range partition on time_received, and one hash partitioning on device_id.

SELECT create_hypertable('conditions', by_range('time'));
SELECT add_dimension('conditions', by_hash('location', 2));
SELECT add_dimension('conditions', by_range('time_received', INTERVAL '1 day'));
SELECT add_dimension('conditions', by_hash('device_id', 2));
SELECT add_dimension('conditions', by_hash('device_id', 2), if_not_exists => true);

Arguments

The syntax is:

SELECT add_dimension(
    hypertable = '<hypertable_name>',
    dimension = by_range('<column_name>', '<interval>') | by_hash('<column_name>', <number_partitions>),
    if_not_exists = true | false
);

Name	Type	Default	Required	Description
hypertable	REGCLASS	-	✔	The to add the dimension to.
dimension	DIMENSION_INFO	-	✔	To create a _timescaledb_internal.dimension_info instance to partition a , you call by_range and by_hash.
if_not_exists	BOOLEAN	false	✖	Set to true to print an error if a dimension for the column already exists. By default an exception is raised.

Dimension info

To create a _timescaledb_internal.dimension_info instance, you call add_dimension to an existing hypertable.

Samples

s must always have a primary range dimension, followed by an arbitrary number of additional dimensions that can be either range or hash, Typically this is just one hash. For example:

SELECT add_dimension('conditions', by_range('time'));
SELECT add_dimension('conditions', by_hash('location', 2));

For incompatible data types such as jsonb, you can specify a function to the partition_func argument of the dimension build to extract a compatible data type. Look in the example section below.

Custom partitioning

By default, calls ‘s internal hash function for the given type. You use a custom partitioning function for value types that do not have a native hash function.

You can specify a custom partitioning function for both range and hash partitioning. A partitioning function should take a anyelement argument as the only parameter and return a positive integer hash value. This hash value is not a partition identifier, but rather the inserted value’s position in the dimension’s key space, which is then divided across the partitions.

by_range()

Create a by-range dimension builder. You can partition by_range on it’s own.

Samples

• Partition on time using CREATE TABLE

  The simplest usage is to partition on a time column:

  CREATE TABLE conditions (
     time        TIMESTAMPTZ       NOT NULL,
     location    TEXT              NOT NULL,
     device      TEXT              NOT NULL,
     temperature DOUBLE PRECISION  NULL,
     humidity    DOUBLE PRECISION  NULL
  ) WITH (
     tsdb.hypertable
  );
  

  When you create a using CREATE TABLE … WITH …, the default partitioning column is automatically the first column with a timestamp data type. Also, creates a columnstore policy that automatically converts your data to the , after an interval equal to the value of the chunk_interval, defined through compress_after in the policy. This columnar format enables fast scanning and aggregation, optimizing performance for analytical workloads while also saving significant storage space. In the conversion, s are compressed by up to 98%, and organized for efficient, large-scale queries.

  You can customize this policy later using alter_job. However, to change after or created_before, the compression settings, or the the policy is acting on, you must remove the columnstore policy and add a new one.

  You can also manually convert s in a to the .

  This is the default partition, you do not need to add it explicitly.

• Extract time from a non-time column using create_hypertable

  If you have a table with a non-time column containing the time, such as a JSON column, add a partition function to extract the time:

  CREATE TABLE my_table (
     metric_id serial not null,
     data jsonb,
  );
  
  CREATE FUNCTION get_time(jsonb) RETURNS timestamptz AS $$
    SELECT ($1->>'time')::timestamptz
  $$ LANGUAGE sql IMMUTABLE;
  
  SELECT create_hypertable('my_table', by_range('data', '1 day', 'get_time'));
  

Arguments

Name	Type	Default	Required	Description
column_name	NAME	-	✔	Name of column to partition on.
partition_func	REGPROC	-	✖	The function to use for calculating the partition of a value.
partition_interval	ANYELEMENT	-	✖	Interval to partition column on.

If the column to be partitioned is a:

• TIMESTAMP, TIMESTAMPTZ, or DATE: specify partition_interval either as an INTERVAL type or an integer value in microseconds.

• Another integer type: specify partition_interval as an integer that reflects the column’s underlying semantics. For example, if this column is in UNIX time, specify partition_interval in milliseconds.

The partition type and default value depending on column type is:

Column Type	Partition Type	Default value
TIMESTAMP WITHOUT TIMEZONE	INTERVAL/INTEGER	1 week
TIMESTAMP WITH TIMEZONE	INTERVAL/INTEGER	1 week
DATE	INTERVAL/INTEGER	1 week
SMALLINT	SMALLINT	10000
INT	INT	100000
BIGINT	BIGINT	1000000

by_hash()

The main purpose of hash partitioning is to enable parallelization across multiple disks within the same time interval. Every distinct item in hash partitioning is hashed to one of N buckets. By default, uses flexible range intervals to manage sizes.

Parallelizing disk I/O

You use Parallel I/O in the following scenarios:

• Two or more concurrent queries should be able to read from different disks in parallel.
• A single query should be able to use query parallelization to read from multiple disks in parallel.

For the following options:

• RAID: use a RAID setup across multiple physical disks, and expose a single logical disk to the . That is, using a single tablespace.

  Best practice is to use RAID when possible, as you do not need to manually manage tablespaces in the database.

• Multiple tablespaces: for each physical disk, add a separate tablespace to the database. allows you to add multiple tablespaces to a single . However, although under the hood, a ‘s s are spread across the tablespaces associated with that .

  When using multiple tablespaces, a best practice is to also add a second hash-partitioned dimension to your and to have at least one hash partition per disk. While a single time dimension would also work, it would mean that the first is written to one tablespace, the second to another, and so on, and thus would parallelize only if a query’s time range exceeds a single .

When adding a hash partitioned dimension, set the number of partitions to a multiple of number of disks. For example, the number of partitions P=N*Pd where N is the number of disks and Pd is the number of partitions per disk. This enables you to add more disks later and move partitions to the new disk from other disks.

does not benefit from a very large number of hash partitions, such as the number of unique items you expect in partition field. A very large number of hash partitions leads both to poorer per-partition load balancing (the mapping of items to partitions using hashing), as well as much increased planning latency for some types of queries.

Samples

CREATE TABLE conditions (
   "time"      TIMESTAMPTZ       NOT NULL,
   location    TEXT              NOT NULL,
   device      TEXT              NOT NULL,
   temperature DOUBLE PRECISION  NULL,
   humidity    DOUBLE PRECISION  NULL
) WITH (
   tsdb.hypertable
   tsdb.chunk_interval='1 day'
);

SELECT add_dimension('conditions', by_hash('location', 2));

Arguments

Name	Type	Default	Required	Description
column_name	NAME	-	✔	Name of column to partition on.
partition_func	REGPROC	-	✖	The function to use to calculate the partition of a value.
number_partitions	INTEGER	-	✔	Number of hash partitions to use for partitioning_column. Must be greater than 0.

Returns

by_range and by-hash return an opaque _timescaledb_internal.dimension_info instance, holding the dimension information used by this function.

Returns

Column	Type	Description
dimension_id	INTEGER	ID of the dimension in the internal catalog
created	BOOLEAN	true if the dimension was added, false when you set if_not_exists to true and no dimension was added.

On failure, an error is returned:

Error	Description
table "" is not a	The specified table has not been converted to a
column "" does not exist	The specified column does not exist in the
column "" is already a dimension	A dimension already exists for this column
cannot specify both the number of partitions and an interval	Both number_partitions and chunk_time_interval were provided
invalid interval type for bigint dimension	An INTERVAL type was used for a BIGINT column instead of an integer value