> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# add_dimension()
> Add a space-partitioning dimension to a hypertable
export const CLOUD_LONG = 'Tiger Cloud';
export const table_name_0 = undefined
export const column_name_0 = undefined
export const CHUNK = 'chunk';
export const HYPERTABLE = 'hypertable';
export const COLUMNSTORE = 'columnstore';
export const TIMESCALE_DB = 'TimescaleDB';
export const PG = 'Postgres';
export const HYPERTABLE_CAP = 'Hypertable';
Since [0.1.0][tsdb-0.1.0]
Add an additional partitioning dimension to a {TIMESCALE_DB} {HYPERTABLE}. You can only execute this `add_dimension`
command
on an empty {HYPERTABLE}. To convert a normal table to a {HYPERTABLE}, call [create hypertable][create_hypertable].
The column you select as the dimension can use either:
* [Interval partitions][by-range]: for example, for a second range partition.
* [hash partitions][by-hash]: to enable parallelization across multiple disks.
**These instructions are for self-hosted {TIMESCALE_DB} deployments**
Best practice is to not use additional dimensions. However, {CLOUD_LONG} transparently provides seamless storage
scaling, both in terms of storage capacity and available storage IOPS/bandwidth.
This page describes the generalized {HYPERTABLE} API introduced in {TIMESCALE_DB} v2.13.0.
For information about the deprecated interface, see [add\_dimension(), deprecated interface][add-dimension-old].
## 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:
```sql theme={"dark"}
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 {TIMESCALE_DB} 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`.
```sql theme={"dark"}
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:
```sql theme={"dark"}
SELECT add_dimension(
hypertable = '',
dimension = by_range('', '') | by_hash('', ),
if_not_exists = true | false
);
```
| Name | Type | Default | Required | Description |
| --------------- | --------------------------------- | ------- | -------- | --------------------------------------------------------------------------------------------------------------------------------------------------- |
| `hypertable` | REGCLASS | - | ✔ | The {HYPERTABLE} to add the dimension to. |
| `dimension` | [DIMENSION\_INFO][dimension-info] | - | ✔ | To create a `_timescaledb_internal.dimension_info` instance to partition a {HYPERTABLE}, you call [`by_range`][by-range] and [`by_hash`][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][add_dimension]
to an existing hypertable.
#### Samples
{HYPERTABLE_CAP}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:
```sql theme={"dark"}
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, {TIMESCALE_DB} calls {PG}'s internal hash function for the given type.
You use a custom partitioning function for value types that do not have a native {PG} 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:
```sql theme={"dark"}
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 {HYPERTABLE} using [CREATE TABLE ... WITH ...][hypertable-create-table], the default partitioning
column is automatically the first column with a timestamp data type. Also, {TIMESCALE_DB} creates a
[columnstore policy][add_columnstore_policy] that automatically converts your data to the {COLUMNSTORE}, after an
interval equal to the value of the [chunk\_interval][create_table_arguments], 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
{COLUMNSTORE} conversion, {HYPERTABLE} {CHUNK}s are compressed by up to 98%, and organized for efficient, large-scale queries.
You can customize this policy later using [alter\_job][alter_job_samples]. However, to change `after` or
`created_before`, the compression settings, or the {HYPERTABLE} the policy is acting on, you must
[remove the columnstore policy][remove_columnstore_policy] and [add a new one][add_columnstore_policy].
You can also manually [convert {CHUNK}s][convert_to_columnstore] in a {HYPERTABLE} to the {COLUMNSTORE}.
[add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy
[alter_job_samples]: /api-reference/timescaledb/jobs-automation/alter_job#samples
[convert_to_columnstore]: /api-reference/timescaledb/hypercore/convert_to_columnstore
[create_table_arguments]: /api-reference/timescaledb/hypertables/create_table#arguments
[hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table
[remove_columnstore_policy]: /api-reference/timescaledb/hypercore/remove_columnstore_policy
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:
```sql theme={"dark"}
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, {TIMESCALE_DB} uses flexible range
intervals to manage {CHUNK} 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 {HYPERTABLE}.
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. {TIMESCALE_DB} allows you to
add multiple tablespaces to a *single* {HYPERTABLE}. However, although under the hood, a {HYPERTABLE}'s
{CHUNK}s are spread across the tablespaces associated with that {HYPERTABLE}.
When using multiple tablespaces, a best practice is to also add a second hash-partitioned dimension to your {HYPERTABLE}
and to have at least one hash partition per disk. While a single time dimension would also work, it would mean that
the first {CHUNK} 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 {CHUNK}.
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.
{TIMESCALE_DB} 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
```sql theme={"dark"}
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.
[create_hypertable]: /api-reference/timescaledb/hypertables/create_hypertable
[add_dimension]: /api-reference/timescaledb/hypertables/add_dimension
[by-range]: /api-reference/timescaledb/hypertables/create_hypertable#by_range
[by-hash]: /api-reference/timescaledb/hypertables/create_hypertable#by_hash
## Returns
| Column | Type | Description |
| -------------- | ------- | ------------------------------------------------------------------------------------------------------------- |
| `dimension_id` | INTEGER | ID of the dimension in the {TIMESCALE_DB} 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 "{table_name_0}" is not a {HYPERTABLE} | The specified table has not been converted to a {HYPERTABLE} |
| column "{column_name_0}" does not exist | The specified column does not exist in the {HYPERTABLE} |
| column "{column_name_0}" 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 |
[add-dimension-old]: /api-reference/timescaledb/hypertables/add_dimension_old
[by-hash]: /api-reference/timescaledb/hypertables/add_dimension#by_hash
[by-range]: /api-reference/timescaledb/hypertables/add_dimension#by_range
[create_hypertable]: /api-reference/timescaledb/hypertables/create_hypertable
[dimension-info]: /api-reference/timescaledb/hypertables/add_dimension#dimension-info
[tsdb-0.1.0]: https://github.com/timescale/timescaledb/releases/tag/0.1.0