Tiger Cloud: Performance, Scale, Enterprise

Self-hosted products

MST

Create a hypertable partitioned on a single dimension with columnstore enabled, or create a standard Postgres relational table.

A hypertable is a specialized Postgres table that automatically partitions your data by time. All actions that work on a Postgres table, work on hypertables. For example, ALTER TABLE and SELECT. By default, a hypertable is partitioned on the time dimension. To add secondary dimensions to a hypertable, call add_dimension. To convert an existing relational table into a hypertable, call create_hypertable.

As the data cools and becomes more suited for analytics, add a columnstore policy so your data is automatically converted to the columnstore after a specific time interval. This columnar format enables fast scanning and aggregation, optimizing performance for analytical workloads while also saving significant storage space. In the columnstore conversion, hypertable chunks are compressed by more than 90%, and organized for efficient, large-scale queries. This columnar format enables fast scanning and aggregation, optimizing performance for analytical workloads. You can also manually convert chunks in a hypertable to the columnstore.

hypertable to hypertable foreign keys are not allowed, all other combinations are permitted.

CREATE TABLE extends the standard Postgres CREATE TABLE. This page explains the features and arguments specific to TimescaleDB.

Since TimescaleDB v2.20.0
  • Create a hypertable partitioned on the time dimension and enable columnstore:

    1. Create the hypertable:

      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'
      );
    2. Enable hypercore by adding a columnstore policy:

      CALL add_columnstore_policy('crypto_ticks', after => INTERVAL '1d');
  • Create a hypertable partitioned on the time with fewer chunks based on time interval:

    CREATE TABLE IF NOT EXISTS hypertable_control_chunk_interval(
    time int4 NOT NULL,
    device text,
    value float
    ) WITH (
    tsdb.hypertable,
    tsdb.partition_column='time',
    tsdb.chunk_interval=3453
    );
  • Enable data compression during ingestion:

    When you set timescaledb.enable_direct_compress_copy your data is compressed when it is ingested into memory during COPY and INSERT calls. This means that WAL records are written for the compressed batches rather than the individual tuples. Also, the columnstore policy you set is less important, INSERT already produces compressed chunks.

    1. Create a hypertable:
      CREATE TABLE t(time timestamptz, device text, value float) WITH (tsdb.hypertable,tsdb.partition_column='time');
    2. Enable direct compression copy:
      SET timescaledb.enable_direct_compress_copy;
    3. Copy data into the hypertable: You achieve the highest insert rate using binary format. CSV and text format are also supported.
      COPY t FROM '/tmp/t.binary' WITH (format binary);
  • Create a Postgres relational table:
    CREATE TABLE IF NOT EXISTS relational_table(
    device text,
    value float
    );

The syntax is:

CREATE TABLE <table_name> (
-- Standard Postgres syntax for CREATE TABLE
)
WITH (
tsdb.hypertable = true | false
tsdb.partition_column = '<column_name> ',
tsdb.chunk_interval = '<interval>'
tsdb.create_default_indexes = true | false
tsdb.associated_schema = '<schema_name>',
tsdb.associated_table_prefix = '<prefix>'
tsdb.orderby = '<column_name> [ASC | DESC] [ NULLS { FIRST | LAST } ] [, ...]',
tsdb.segmentby = '<column_name> [, ...]',
)
NameTypeDefaultRequiredDescription
tsdb.hypertableBOOLEANtrueCreate a new hypertable for time-series data rather than a standard Postgres relational table.
tsdb.partition_columnTEXTtrueSet the time column to automatically partition your time-series data by.
tsdb.chunk_intervalTEXT7 daysChange this to better suit your needs. For example, if you set chunk_interval to 1 day, each chunk stores data from the same day. Data from different days is stored in different chunks.
tsdb.create_default_indexesBOOLEANtrueSet to false to not automatically create indexes.
The default indexes are:
  • On all hypertables, a descending index on partition_column
  • On hypertables with space partitions, an index on the space parameter and partition_column
tsdb.associated_schemaREGCLASS_timescaledb_internalSet the schema name for internal hypertable tables.
tsdb.associated_table_prefixTEXT_hyperSet the prefix for the names of internal hypertable chunks.
tsdb.orderbyTEXTDescending order on the time column in table_name.The order in which items are used in the columnstore. Specified in the same way as an ORDER BY clause in a SELECT query.
tsdb.segmentbyTEXTNo segmentation by column.Set the list of columns used to segment data in the columnstore for table. An identifier representing the source of the data such as device_id or tags_id is usually a good candidate.

TimescaleDB returns a simple message indicating success or failure.

Keywords

Found an issue on this page?Report an issue or Edit this page in GitHub.