> ## 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() (deprecated)

> Add a space-partitioning dimension to a hypertable

export const HYPERTABLE_CAP = 'Hypertable';

export const CHUNK = 'chunk';

export const TIMESCALE_DB = 'TimescaleDB';

export const HYPERTABLE = 'hypertable';

export const table_name_0 = undefined

export const column_name_0 = undefined

<Icon icon="archive" iconType="duotone" /> Deprecated [v2.13.0][tsdb-2.13.0]. Use [add\_dimension()][add-dimension]. <Icon icon="tag" iconType="duotone" /> Since [1.0.0][tsdb-1.0.0]

Add an additional partitioning dimension to a {TIMESCALE_DB} {HYPERTABLE}.
The column selected as the dimension can either use interval
partitioning (for example, for a second time partition) or hash partitioning.

<Warning>
  The `add_dimension` command can only be executed after a table has been
  converted to a {HYPERTABLE} (via `create_hypertable`), but must similarly
  be run only on an empty {HYPERTABLE}.
</Warning>

**Space partitions**: Using space partitions is highly recommended
for distributed {HYPERTABLE}s to achieve
efficient scale-out performance. For regular {HYPERTABLE}s
that exist only on a single node, additional partitioning can be used
for specialized use cases and not recommended for most users.

Space partitions use hashing: Every distinct item is hashed to one of
*N* buckets. Remember that we are already using (flexible) time
intervals to manage {CHUNK} sizes; the main purpose of space
partitioning is to enable parallelization across multiple
data nodes (in the case of distributed {HYPERTABLE}s) or
across multiple disks within the same time interval
(in the case of single-node deployments).

## Samples

First convert table `conditions` to {HYPERTABLE} with just time
partitioning on column `time`, then add an additional partition key on `location` with four partitions:

```sql theme={"dark"}
SELECT create_hypertable('conditions', 'time');
SELECT add_dimension('conditions', 'location', number_partitions => 4);
```

Convert table `conditions` to {HYPERTABLE} with time partitioning on `time` and
space partitioning (2 partitions) on `location`, then add two additional dimensions.

```sql theme={"dark"}
SELECT create_hypertable('conditions', 'time', 'location', 2);
SELECT add_dimension('conditions', 'time_received', chunk_time_interval => INTERVAL '1 day');
SELECT add_dimension('conditions', 'device_id', number_partitions => 2);
SELECT add_dimension('conditions', 'device_id', number_partitions => 2, if_not_exists => true);
```

Now in a multi-node example for distributed {HYPERTABLE}s with a cluster
of one access node and two data nodes, configure the access node for
access to the two data nodes. Then, convert table `conditions` to
a distributed {HYPERTABLE} with just time partitioning on column `time`,
and finally add a space partitioning dimension on `location`
with two partitions (as the number of the attached data nodes).

```sql theme={"dark"}
SELECT add_data_node('dn1', host => 'dn1.example.com');
SELECT add_data_node('dn2', host => 'dn2.example.com');
SELECT create_distributed_hypertable('conditions', 'time');
SELECT add_dimension('conditions', 'location', number_partitions => 2);
```

### Parallelizing queries across multiple data nodes

In a distributed {HYPERTABLE}, space partitioning enables inserts to be
parallelized across data nodes, even while the inserted rows share
timestamps from the same time interval, and thus increases the ingest rate.
Query performance also benefits by being able to parallelize queries
across nodes, particularly when full or partial aggregations can be
"pushed down" to data nodes (for example, as in the query
`avg(temperature) FROM conditions GROUP BY hour, location`
when using `location` as a space partition).

### Parallelizing disk I/O on a single node

Parallel I/O can benefit in two scenarios: (a) two or more concurrent
queries should be able to read from different disks in parallel, or
(b) a single query should be able to use query parallelization to read
from multiple disks in parallel.

Thus, users looking for parallel I/O have two options:

1. Use a RAID setup across multiple physical disks, and expose a
   single logical disk to the {HYPERTABLE} (that is, via a single tablespace).

2. For each physical disk, add a separate tablespace to the
   database. {TIMESCALE_DB} allows you to actually add multiple tablespaces
   to a *single* {HYPERTABLE} (although under the covers, a {HYPERTABLE}'s
   {CHUNK}s are spread across the tablespaces associated with that {HYPERTABLE}).

We recommend a RAID setup when possible, as it supports both forms of
parallelization described above (that is, separate queries to separate
disks, single query to multiple disks in parallel).  The multiple
tablespace approach only supports the former. With a RAID setup,
*no spatial partitioning is required*.

That said, when using space partitions, we recommend using 1
space partition per disk.

{TIMESCALE_DB} does *not* benefit from a very large number of space
partitions (such as the number of unique items you expect in partition
field).  A very large number of such 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.

## Arguments

The syntax is:

```sql theme={"dark"}
SELECT add_dimension(
    hypertable = '<hypertable_name>',
    column_name = '<column_name>',
    number_partitions = <num>,
    chunk_time_interval = <interval>,
    partitioning_func = '<function_name>',
    if_not_exists = true | false
);
```

| Name                  | Type     | Default | Required | Description                                                                                                                        |
| --------------------- | -------- | ------- | -------- | ---------------------------------------------------------------------------------------------------------------------------------- |
| `hypertable`          | REGCLASS | -       | ✔        | {HYPERTABLE_CAP} to add the dimension to                                                                                           |
| `column_name`         | TEXT     | -       | ✔        | Column to partition by                                                                                                             |
| `number_partitions`   | INTEGER  | -       | ✖        | Number of hash partitions to use on `column_name`. Must be > 0                                                                     |
| `chunk_time_interval` | INTERVAL | -       | ✖        | Interval that each {CHUNK} covers. Must be > 0                                                                                     |
| `partitioning_func`   | REGCLASS | -       | ✖        | The function to use for calculating a value's partition (see `create_hypertable` [instructions][create_hypertable])                |
| `if_not_exists`       | BOOLEAN  | `false` | ✖        | Set to true to avoid throwing an error if a dimension for the column already exists. A notice is issued instead. Defaults to false |

## Returns

| Column         | Type    | Description                                                                                    |
| -------------- | ------- | ---------------------------------------------------------------------------------------------- |
| `dimension_id` | INTEGER | ID of the dimension in the {TIMESCALE_DB} internal catalog                                     |
| `schema_name`  | TEXT    | Schema name of the {HYPERTABLE}                                                                |
| `table_name`   | TEXT    | Table name of the {HYPERTABLE}                                                                 |
| `column_name`  | TEXT    | Column name of the column to partition by                                                      |
| `created`      | BOOLEAN | True if the dimension was added, false when `if_not_exists` is 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 |

When executing this function, either `number_partitions` or
`chunk_time_interval` must be supplied, which dictates if the
dimension uses hash or interval partitioning.

The `chunk_time_interval` should be specified as follows:

* If the column to be partitioned is a TIMESTAMP, TIMESTAMPTZ, or
  DATE, this length should be specified either as an INTERVAL type or
  an integer value in *microseconds*.

* If the column is some other integer type, this length
  should be an integer that reflects
  the column's underlying semantics (for example, the
  `chunk_time_interval` should be given in milliseconds if this column
  is the number of milliseconds since the UNIX epoch).

<Warning>
  Supporting more than **one** additional dimension is currently
  experimental. For any production environments, users are recommended
  to use at most one "space" dimension.
</Warning>

[add-dimension]: /api-reference/timescaledb/hypertables/add_dimension

[create_hypertable]: /api-reference/timescaledb/hypertables/create_hypertable_old

[tsdb-1.0.0]: https://github.com/timescale/timescaledb/releases/tag/1.0.0

[tsdb-2.13.0]: https://github.com/timescale/timescaledb/releases/tag/2.13.0