Skip to content
Documentation

TimescaleDB

This page shows how to install and configure TimescaleDB in a PowerTSM PostgreSQL database, how to convert PowerTSM data tables into TimescaleDB hypertables, and how to update such a database.

  • A list of supported versions can be found here: System Requirements.
  • An already installed PowerTSM database under PostgreSQL.
  • TimescaleDB matching your installed PostgreSQL server.
  • psql.exe

Follow the according steps provided by TimescaleDB for the target environment: Install TimescaleDB.

The following describes the installation process on a Windows machine:

  1. Stop PostgreSQL Server Service and all services accessing PostgreSQL Server.

  2. Check if the bin directory of the PostgreSQL server is entered in the Windows PATH environment variable.

  3. Execute the Setup.exe in timescaledb-postgresql-v.v_x.y.z-windows...\timescaledb\ as administrator.

    1. The setup will prompt to optimize the postgresql.conf file. It is recommended to apply this step. The required .conf file is located at C:\Program Files\PostgreSQL\XY\data\ (XY corresponds to the respective major and minor version, for example 12). If the optimization option was selected, some scaling and memory optimization steps have to be confirmed within Command Prompt.

    2. If TimescaleDB was successfully installed, you will see the following message:

      TimescaleDB installation completed successfully.
      Press ENTER/Return key to close...
  4. The PostgreSQL Server Service can now be started up again.

To activate TimescaleDB, refer to the following tutorials: Getting started with TimescaleDB.

The following shows the steps for activation of a TimescaleDB version 1.7.4.

After installing TimescaleDB, the TimescaleDB Extension must be activated for each database where it will be used.

  1. Open a command prompt.

  2. Connect to PostgreSQL Server using psql -U postgres -h localhost.

    C:\Windows\system32>psql -U postgres -h localhost
    Password for user:
    psql (12.5)
  3. Connect to PostgreSQL database (for example to hakom_tsm) using \c hakom_tsm.

    postgres=# \c hakom_tsm
  4. Activate TimescaleDB for the selected database using CREATE EXTENSION IF NOT EXISTS timescaledb;.

    hakom_tsm=# CREATE EXTENSION IF NOT EXISTS timescaledb;
    WARNING:
    WELCOME TO
    _____ _ _ ____________
    |_ _(_) | | | _ \ ___ \
    | | _ _ __ ___ ___ ___ ___ __ _| | ___| | | | |_/ /
    | | | | _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
    | | | | | | | | | __/\__ \ (_| (_| | | __/ |/ /| |_/ /
    |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
    Running version 1.7.4
    For more information on TimescaleDB, please visit the following links:
    1. Getting started: https://docs.timescale.com/getting-started
    2. API reference documentation: https://docs.timescale.com/api
    3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture
    Note: TimescaleDB collects anonymous reports to better understand and assist our users.
    For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.
    CREATE EXTENSION
    hakom_tsm=#

TimescaleDB is now enabled for the database.

Converting a PowerTSM Data Table to a Hypertable

Section titled “Converting a PowerTSM Data Table to a Hypertable”

The following steps are based on these TimescaleDB tutorials:

The following steps create a TimescaleDB Hypertable on the basis of a standard PowerTSM data table (FWT_TSDATA).

First an empty copy of the desired table must be created: CREATE TABLE ZAMS.FWT_TSDATA_TSCDB (LIKE ZAMS.FWT_TSDATA INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);

hakom_tsm=# CREATE TABLE ZAMS.FWT_TSDATA_TSCDB (LIKE ZAMS.FWT_TSDATA INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES);
CREATE TABLE
hakom_tsm=#

For the conversion into a TimescaleDB table a date column or optionally an integer column must be defined. For date columns TimescaleDB can derive the chunk interval lengths from the date. For integer columns, like the Time column in FWT_TSDATA, TimescaleDB must be told which date logic the Time column follows. This is done by using the chunk_time_interval parameter to specify an interval at which the data should be partitioned.

Assuming that time series data are mostly retrieved over a complete day or a multiple of it, 1 day (i.e. 86,400 seconds, or 86,400,000 milliseconds) is chosen as the partition size.

The following assumes a PowerTSM database with internal time unit second.

The FWT_TSDATA_TSCDB table is converted into a TimescaleDB hypertable with chunk_time_interval 1 day with the following command: SELECT create_hypertable('ZAMS.FWT_TSDATA_TSCDB', 'time', chunk_time_interval => 86400);

hakom_tsm=# SELECT create_hypertable('ZAMS.FWT_TSDATA_TSCDB', 'time', chunk_time_interval => 86400);
create_hypertable
---------------------------
(2,zams,fwt_tsdata_tsc,t)
(1 row)

Under certain circumstances, additional time dimensions can be useful. After creating the hypertable, before filling it with data, additional time dimensions can be added for partitioning purposes. However, this is not necessary for a FWT_TSDATA table.

More information about TimescaleDB dimensions

Now all or selected data from the FWT_TSDATA table can be migrated to the newly created hypertable. For all data use: INSERT INTO ZAMS.FWT_TSDATA_TSCDB SELECT * FROM ZAMS.FWT_TSDATA;

hakom_tsm=# INSERT INTO ZAMS.FWT_TSDATA_TSCDB SELECT * FROM ZAMS.FWT_TSDATA;
INSERT 0 82772

The table is migrated and ready to use!

To use the table in PowerTSM components, the according tables (actualdatatable for live data or archivedatatable for historical data) must be updated in FWT_TIMESERIES accordingly.

The following query shows the update of all time series that use standard FWT_TSDATA table to the new Hypertable:

hakom_tsm=# UPDATE FWT_TIMESERIES SET actualdatatable='FWT_TSDATA_TSCDB' WHERE actualdatatable='FWT_TSDATA';
UPDATE 30

Double check with PowerTSM support whether the table structure of PowerTSM data tables used within TimescaleDB have been changed. If not, a standard PostgreSQL update can be conducted. If yes, follow the below instructions.

TimescaleDB tables behave within PostgreSQL as standard PostgreSQL tables, as such PowerTSM update scripts for PostgreSQL should be also executable against TimescaleDB tables. To be on the safe side the following steps are recommended for updates.

  1. Run the standard PowerTSM PostgreSQL update → that will update all PowerTSM tables under standard PowerTSM naming conventions and skipping newly created tables (that are used within TimescaleDB scope).
  2. Create new TimescaleDB Hypertables as described in Converting a PowerTSM Data Table to a Hypertable.
  3. Migrate Time Series Data from old Hypertable into new Hypertable using a simple insert into query (similar to Migrate Time Series Data).
  4. Delete old Hypertable.
  5. Rename new Hypertable to match with time series definitions in FWT_TIMESERIES (using ALTER TABLE) or update FWT_TIMESERIES with the new table name.

As an alternative to the Safe Update procedure you may also try to run PowerTSM update scripts against TimescaleDB Hypertables directly, since these are supposed to fully support PostgreSQL schema management commands (ALTER TABLE schema management).

© 2026 Volue.Version v0.6.38017 November 2026