/
TimescaleDB support

TimescaleDB support

TimescaleDB support in the archive

Since D2000 V26.0, the D2000 Archiv process has implemented support for the TimescaleDB extension (https://www.tigerdata.com/timescaledb) on the PostgreSQL platform.

The TimescaleDB extension optimizes work with time series using so-called hypertables. A regular table is converted to a hypertable, which consists of smaller tables - chunks (the parameter is the size of the time interval for one chunk). Additionally, chunks containing older data are converted to columnar tables and compressed, reducing the amount of disk space the archive database takes up. This is similar to depository data compression, except that you can continue to write to the compressed chunks and the compression is more efficient.

The extension is activated by setting the TimescaleDBType, TimescaleDBInterval, and optionally TimescaleDBIntervalCps parameters.

  • The TimescaleDBType parameter configures for which types of historical objects their data tables will be converted to hypertables:

    • 0 - None (TimescaleDB support is disabled).

    • 1 - All (simple and structured historical objects).

    • 2 - Structured historical objects (archiving either a structure column or the entire structure).
      Note: Similar to time slices (see parameter DataTableSlices), in configurations with a large number of simple historical objects, enabling hypertables on all objects can be problematic due to the large number of chunks, so for this case, we recommend a value of 2 or 4.

  • The TimescaleDBInterval parameter specifies the size of the time interval for one chunk (the recommended value is “30 day” or “1 month”).

  • The TimescaleDBIntervalCps parameter specifies the compression time for each hypertable chunk in PostgreSQL interval syntax. Chunks that contain older data will be compressed. If this parameter is not specified, the TimescaleDBInterval value is used. So the parameter allows you to have chunks of 1 month in size, for example, but wait 2 months for old data to be compressed.

Caution: Hypertables can be used together with time slices, which combines the advantages of both approaches:

  • D2000 Archiv creates time slices and keeps track of which time slice is for which time.

  • D2000 Archiv does not delete data in time slices, but removes entire time slices beyond the archiving depth.

  • Older time slices are compressed.

The following conditions must be met before setting the TimescaleDBType and TimescaleDBInterval parameters:

After setting the TimescaleDBType and TimescaleDBInterval parameters and restarting the D2000 Archiv process, the following irreversible conversion of the archive database is performed:

  • D2000 Archiv installs the TimescaleDB extension into the archive database (CREATE EXTENSION IF NOT EXISTS timescaledb).

  • D2000 Archiv converts all data tables to hypertables, for example:
    SELECT create_hypertable('"DT0000251"', by_range('CAS', INTERVAL '1 month'), migrate_data => true, create_default_indexes => false, if_not_exists => true)).

  • D2000 Archiv activates chunk compression, for example:
    ALTER TABLE "DT0000251" SET (timescaledb.compress = true, timescaledb.compress_orderby = ‘"CAS” DESC’)
    ALTER TABLE "DT0000252RC" SET (timescaledb.compress = true, timescaledb.compress_orderby = ‘"CAS” DESC’, timescaledb.compress_segmentby = '"ROWX","COL"')
    The conversion may take longer, depending on the size of the archive database.

  • D2000 Archiv activates a policy to compress chunks older than a specified time interval (e.g. CALL add_columnstore_policy('"DT0000251"', after => INTERVAL '1 month')).

  • D2000 Archiv finally inserts the TSDB_CONVERTED entry (information about the completed conversion to TimescaleDB) into the LOG_DATA table.

Subsequently, when creating historical objects during the D2000 Archive process, newly created tables are immediately converted to hypertables.

Older chunks will be automatically compressed to columnar store format, which can significantly reduce disk space requirements.

Note: By enabling the DBG.ARCHIV.SLICES debug category (with the /E+DBG.ARCHIV.SLICES startup parameter or from the D2000 System Console process), it is possible to activate debug dumps during archive database conversion.

Note: The value of the TimescaleDBType parameter can be changed from 1 to 2 and back (change requires restart). This will affect whether the data tables of all or only the structured historical objects that will be subsequently created are converted to hypertables. If it is necessary to convert data tables of simple historical objects to hypertables, simply manually delete the TSDB_CONVERTED item from the LOG_DATA table and restart D2000 Archiv (with TimescaleDBType = 1) - all data tables that have not yet been converted will be converted again.
If the value of the TimescaleDBType parameter is set to 0, the data tables of historical objects that will be created subsequently will not be converted to hypertables.

 

More information about working with hypertables

Backup

Enabling TimescaleDB also affects the archive database recovery process (see https://www.tigerdata.com/docs/deploy/self-hosted/backup-and-restore/logical-backup).

Reorganization

When vacuuming a hypertable, individual chunks are automatically vacuumed, so the standard archive maintenance mechanism (reorganization after deleting ReorganizeTableRowLimit rows, but not more often than every ReorganizeTableTimeLimit hours) is functional.

Diagnostics

The command SHOW_DYN_INFO displays a list of chunks belonging to an archive object, along with information about whether the chunk is compressed. The information displayed comes from the timescaledb_information.chunks view.

After the list, statistics about the data size of compressed chunks are displayed (from hypertable_columnstore_stats):

------------ Hypertable chunks -------------
ChunkName RangeFrom RangeTo Compressed
_hyper_27_23_chunk <06-07-2026 04:00, 05-08-2026 04:00> FALSE
_hyper_27_34_chunk <06-06-2026 04:00, 06-07-2026 04:00> TRUE
Compressed chunk statistics: original size 2686 kB, compressed size 122 kB

 

The command STATISTICS_TSDB collects statistics about the size of compressed chunks (original size/size after compression) of all historical objects in a file.

Manual Compression

If time slices are enabled, manual compression of time slice chunks for a specific time for one or more historical objects can be performed using the COMPRESS_TSDB command.

Testing

TimescaleDB support has been tested on PostgreSQL 17 and 18, with TimescaleDB versions 2.28.3 and 2.29.0.

Upgrading TimescaleDB

Upgrading TimescaleDB requires shutting down the D2000 Archiv process as well as shutting down the PostgreSQL server.

After installing a new version of TimescaleDB, you need to connect to the archive database (via psql) and run the ALTER EXTENSION command (the version in the following example corresponds to the newly installed version 2.29.0; previously, the version was 2.28.3):

ALTER EXTENSION timescaledb UPDATE TO '2.29.0';