Timescaledb - Hypertables

Pages: 103

---

chunks_detailed_size()

URL: llms-txt#chunks_detailed_size()

Contents:

• Samples
• Required arguments
• Returns

Get information about the disk space used by the chunks belonging to a hypertable, returning size information for each chunk table, any indexes on the chunk, any toast tables, and the total size associated with the chunk. All sizes are reported in bytes.

If the function is executed on a distributed hypertable, it returns disk space usage information as a separate row per node. The access node is not included since it doesn't have any local chunk data.

Additional metadata associated with a chunk can be accessed via the timescaledb_information.chunks view.

Required arguments

Name	Type	Description
hypertable	REGCLASS	Name of the hypertable

Column	Type	Description
chunk_schema	TEXT	Schema name of the chunk
chunk_name	TEXT	Name of the chunk
table_bytes	BIGINT	Disk space used by the chunk table
index_bytes	BIGINT	Disk space used by indexes
toast_bytes	BIGINT	Disk space of toast tables
total_bytes	BIGINT	Total disk space used by the chunk, including all indexes and TOAST data
node_name	TEXT	Node for which size is reported, applicable only to distributed hypertables

If executed on a relation that is not a hypertable, the function returns NULL.

===== PAGE: https://docs.tigerdata.com/api/hypertable/create_hypertable_old/ =====

Examples:

Example 1 (sql):

SELECT * FROM chunks_detailed_size('dist_table')
  ORDER BY chunk_name, node_name;

     chunk_schema      |      chunk_name       | table_bytes | index_bytes | toast_bytes | total_bytes |       node_name
-----------------------+-----------------------+-------------+-------------+-------------+-------------+-----------------------
 _timescaledb_internal | _dist_hyper_1_1_chunk |        8192 |       32768 |           0 |       40960 | data_node_1
 _timescaledb_internal | _dist_hyper_1_2_chunk |        8192 |       32768 |           0 |       40960 | data_node_2
 _timescaledb_internal | _dist_hyper_1_3_chunk |        8192 |       32768 |           0 |       40960 | data_node_3

---

add_columnstore_policy()

URL: llms-txt#add_columnstore_policy()

Contents:

• Samples
• Arguments

Create a [job][job] that automatically moves chunks in a hypertable to the columnstore after a specific time interval.

You enable the columnstore a hypertable or continuous aggregate before you create a columnstore policy. You do this by calling CREATE TABLE for hypertables and ALTER MATERIALIZED VIEW for continuous aggregates. When columnstore is enabled, [bloom filters][bloom-filters] are enabled by default, and every new chunk has a bloom index. If you converted chunks to columnstore using TimescaleDB v2.19.3 or below, to enable bloom filters on that data you have to convert those chunks to the rowstore, then convert them back to the columnstore.

Bloom indexes are not retrofitted, meaning that the existing chunks need to be fully recompressed to have the bloom indexes present. Please check out the PR description for more in-depth explanations of how bloom filters in TimescaleDB work.

To view the policies that you set or the policies that already exist, see [informational views][informational-views], to remove a policy, see [remove_columnstore_policy][remove_columnstore_policy].

A columnstore policy is applied on a per-chunk basis. If you remove an existing policy and then add a new one, the new policy applies only to the chunks that have not yet been converted to columnstore. The existing chunks in the columnstore remain unchanged. This means that chunks with different columnstore settings can co-exist in the same hypertable.

Since TimescaleDB v2.18.0

To create a columnstore job:

1. Enable columnstore

Create a [hypertable][hypertables-section] for your time-series data using [CREATE TABLE][hypertable-create-table]. For [efficient queries][secondary-indexes] on data in the columnstore, remember to segmentby the column you will use most often to filter your data. For example:

• [Use CREATE TABLE for a hypertable][hypertable-create-table]

If you are self-hosting TimescaleDB v2.19.3 and below, create a [Postgres relational table][pg-create-table], then convert it using [create_hypertable][create_hypertable]. You then enable hypercore with a call to [ALTER TABLE][alter_table_hypercore].

• [Use ALTER MATERIALIZED VIEW for a continuous aggregate][compression_continuous-aggregate]

1. Add a policy to move chunks to the columnstore at a specific time interval

• 60 days after the data was added to the table:

  • 3 months prior to the moment you run the query:

• With an integer-based time column:

• Older than eight weeks:

• Control the time your policy runs:

When you use a policy with a fixed schedule, TimescaleDB uses the initial_start time to compute the next start time. When TimescaleDB finishes executing a policy, it picks the next available time on the schedule, skipping any candidate start times that have already passed.

When you set the next_start time, it only changes the start time of the next immediate execution. It does not change the computation of the next scheduled execution after that next execution. To change the schedule so a policy starts at a specific time, you need to set initial_start. To change the next immediate execution, you need to set next_start. For example, to modify a policy to execute on a fixed schedule 15 minutes past the hour, and every hour, you need to set both initial_start and next_start using alter_job:

1. View the policies that you set or the policies that already exist

See [timescaledb_information.jobs][informational-views].

Calls to add_columnstore_policy require either after or created_before, but cannot have both.

Name	Type	Default	Required	Description
hypertable	REGCLASS	-	✔	Name of the hypertable or continuous aggregate to run this [job][job] on.
after	INTERVAL or INTEGER	-	✖	Add chunks containing data older than now - \{after\}::interval to the columnstore. Use an object type that matchs the time column type in hypertable: <ul><li><b><code>TIMESTAMP</code>, <code>TIMESTAMPTZ</code>, or <code>DATE</code></b>: use an <code>INTERVAL</code> type.</li><li><b> Integer-based timestamps </b>: set an integer type using the [integer_now_func][set_integer_now_func].</li></ul> after is mutually exclusive with created_before.
created_before	INTERVAL	NULL	✖	Add chunks with a creation time of now() - created_before to the columnstore. created_before is <ul><li>Not supported for continuous aggregates.</li><li>Mutually exclusive with after.</li></ul>
schedule_interval	INTERVAL	12 hours when [chunk_time_interval][chunk_time_interval] >= 1 day for hypertable. Otherwise chunk_time_interval / 2.	✖	Set the interval between the finish time of the last execution of this policy and the next start.
initial_start	TIMESTAMPTZ	The interval from the finish time of the last execution to the [next_start][next-start].	✖	Set the time this job is first run. This is also the time that next_start is calculated from.
next_start	TIMESTAMPTZ	-	✖	Set the start time of the next immediate execution. It does not change the computation of the next scheduled time after the next execution.
timezone	TEXT	UTC. However, daylight savings time(DST) changes may shift this alignment.	✖	Set to a valid time zone to mitigate DST shifting. If initial_start is set, subsequent executions of this policy are aligned on initial_start.
if_not_exists	BOOLEAN	false	✖	Set to true so this job fails with a warning rather than an error if a columnstore policy already exists on hypertable

===== PAGE: https://docs.tigerdata.com/api/hypercore/hypertable_columnstore_settings/ =====

Examples:

Example 1 (sql):

CREATE TABLE crypto_ticks (
        "time" TIMESTAMPTZ,
        symbol TEXT,
        price DOUBLE PRECISION,
        day_volume NUMERIC
     ) WITH (
       tsdb.hypertable,
       tsdb.partition_column='time',
       tsdb.segmentby='symbol',
       tsdb.orderby='time DESC'
     );

Example 2 (sql):

ALTER MATERIALIZED VIEW assets_candlestick_daily set (
        timescaledb.enable_columnstore = true,
        timescaledb.segmentby = 'symbol' );

Example 3 (unknown):

* 3 months prior to the moment you run the query:

Example 4 (unknown):

* With an integer-based time column:

---

Create distributed hypertables

URL: llms-txt#create-distributed-hypertables

Contents:

• Creating a distributed hypertable

[Multi-node support is sunsetted][multi-node-deprecation].

TimescaleDB v2.13 is the last release that includes multi-node support for Postgres versions 13, 14, and 15.

If you have a [multi-node environment][multi-node], you can create a distributed hypertable across your data nodes. First create a standard Postgres table, and then convert it into a distributed hypertable.

You need to set up your multi-node cluster before creating a distributed hypertable. To set up multi-node, see the multi-node section.

本篇目录

• Creating a distributed hypertable
• Add a column to a hypertable
• Rename a hypertable
• Creating a trigger
• Adding a data retention policy
• Creating a continuous aggregate
• Creating a continuous aggregate with the WITH NO DATA option
• Querying a continuous aggregate
• Create a window function
• Window function workaround for older versions of TimescaleDB
• Ordering and segmenting.
• Required arguments
• How chunk skipping works
• When to enable chunk skipping
• Enable chunk skipping
• Parallelizing queries across multiple data nodes
• Parallelizing disk I/O on a single node
• Attaching a new data node to a distributed hypertable
• Time partitioning
• Closed and open dimensions for space partitioning
• Repartitioning distributed hypertables
• Partial push down
• Limitations of query push down
• Selecting chunks to compress
• Compressing chunks manually
• Discovering the name of a materialized hypertable
• Creating a trigger on a distributed hypertable