Today I would like to write about another improvement of the archiving process within the Ipesoft D2000 real-time application server – about the support of the database (or rather extension) TimescaleDB.
What is it good for
If you operate SCADA or MES systems, you know that archiving process data has its own specifics. Ipesoft D2000 collects values from thousands of measurement points, often in second or subsecond intervals, and this data needs to be stored for a long time, quickly queried and efficiently aggregated. TimescaleDB was created for such scenarios.
What is TimescaleDB
TimescaleDB is an extension for PostgreSQL, not a separate database. This is the key difference from other time-series databases like InfluxDB or Prometheus – TimescaleDB runs directly in PostgreSQL, uses standard SQL, fully supports all the tools you already know with PostgreSQL (pg_dump, replication, monitoring). Simply put: you get the performance of a specialized time-series database, but without losing the relational model and without the need to maintain two separate database systems side by side.
For the D2000 environment this feature is important – firstly, we do not have to make complex connections between two different database technologies, and secondly, we use the PostgreSQL database support, which is already included in the D2000 Archive process.
How it works – hypertables and chunks
The heart of TimescaleDB is the concept of hypertable. On the surface, a hypertable behaves like a regular PostgreSQL table – same SQL statements, same queries. Under the hood, however, TimescaleDB automatically divides it into smaller physical tables called chunks, typically by time interval (e.g. one chunk = one month or week of data).
Why is this beneficial
Faster writes – the index of a new chunk is much smaller than the index of one huge table with billions of rows, so inserts remain fast even after years of archiving.
Faster reads – when querying data from a specific time window, TimescaleDB can skip chunks that do not fall into the given window and search only the relevant part of the data.
Easier management – old chunks can be efficiently compressed, moved to cheaper storage, or deleted without burdening the rest of the table (for example, DELETE over an old chunk is practically an immediate DROP, not a row deletion).
This division is transparent – from the perspective of the D2000 archive, there is still one logical table with historical values.
Data compression
One of the strongest features of TimescaleDB is native columnar compression. Older chunks (e.g. older than 1 month) can be automatically converted from the standard row format to a compressed column format. For time-series data, where values often change little between adjacent measurements, this typically results in a 10- to 20-fold reduction in data volume compared to an uncompressed table in pure PostgreSQL.
For a SCADA data archive, this has a direct cost impact – the same disk can hold a much longer history, backups are faster and cheaper, and since less data is read from disk, queries over compressed data can paradoxically be faster than over uncompressed data.
What about time slices
Time slices were implemented in the D2000 Archiv process already in 2010. This is a "user partitioning" of data tables, which, unlike TimescaleDB, is functional on all supported platforms (PostgreSQL, Oracle, Sybase SQL Anywhere, Microsoft SQL Server, SQLite). On the other hand, time slices only "chop" the data into multiple tables with a fixed time size (30 days). No conversion to column format or compression is performed. By the way, data aggregation and compression (native PostgreSQL TOAST) is created during the compression of depository data, but D2000 Archive can no longer write to the resulting compressed data table in the depository. TimescaleDB can also write to compressed chunks.
The first version of this blog contained the following text: Because time slices and TimescaleDB solve a similar set of problems, they cannot be used simultaneously in D2000. If the archive database already uses time slices, TimescaleDB cannot be activated. Conversely, if the archive database has been converted to TimescaleDB hypertables and chunks, it is not possible to activate time slices.
But ... time slices are a good thing. They solve index fragmentation during deletion and optimize deletion. This means that data is not deleted continuously, but the entire time slice is cleaned when it is older than the configured archiving depth. This uses the TRUNCATE TABLE command, which does not generate REDO logs, is fast, and places much less strain on the database than a series of DELETE commands. On the other hand, time slices do not solve data compression - TimescaleDB would help here. And that is why we also implemented the combined mode. Time slices, where each time slice can be converted to a hypertable with one chunk and this chunk can then be compressed. This way, all the advantages of time slices are preserved and the advantage of compression is added.
Installing TimescaleDB
First, the TimescaleDB extension must be installed in PostgreSQL. This can be downloaded from the TimescaleDB website, and it must be downloaded for a specific platform (Linux, Windows) as well as for a specific version of PostgreSQL (currently supported versions 16, 17 and 18).
Attention - we recommend patching PostgresSQL to the latest available patch before installing the extension. It happened to us that the latest TimescaleDB (2.29.0) was installed on the older PostgreSQL 17.9, but it was not functional (installing the older 2.28.3 helped). After patching to 17.10, the latest 2.29.0 also worked.
Activating TimescaleDB in D2000 archive
Before activation, it is still necessary to ensure that the database user that is used to connect to the database (default dba) has SUPERUSER rights. This can be set in the D2000 Application Manager, or directly in Postgres (alter role dba superuser).
The extension is activated in D2000 by setting the TimescaleDBType, TimescaleDBInterval and optionally TimescaleDBIntervalCps parameters.
The TimescaleDBType parameter sets for which types of archive objects their data tables are converted to hypertables (similar to time slices):
- 0 - None (TimescaleDB support is disabled).
- 1 - All (simple and structured historical objects).
- 2 - Structured historical objects (archiving a structure column and the entire structure).
Similar to time slices, in configurations with a large number of simple archives, enabling hypertables on all objects can be problematic due to the large number of chunks, so we recommend a value of 2 for such configurations.
The TimescaleDBInterval parameter specifies the size of the time interval for one chunk (the recommended value is "1 month").
The optional TimescaleDBIntervalCps parameter specifies the compression time for individual hypertable chunks. Chunks that contain older data will be compressed. If this parameter is not specified, the TimescaleDBInterval value will be used. This parameter allows, for example, to have chunks of size 1 month, but wait 2 months for old data to be compressed.
Conversion in D2000 Archive
After setting the parameters and restarting D2000 Archive, the archive tables are converted. Their data is moved to chunks and older chunks are compressed. The conversion can take several hours or days depending on the size of the archive database and the server performance.
On my computer, the conversion of an older copy of the production database (only structured historical objects) took about 55 minutes. The original size of the archive database was 184 GB:
After conversion, the archive database was reduced to 133 GB, saving 50 GB (more than 25% of disk space).
Next, I wanted to try converting all historical objects (even unstructured ones). Since the structured ones were already converted in the previous step, only the tables of simple historical objects were converted now. This took 2 hours and 10 minutes and the resulting database was 24 GB in size, or only 13% of the original size.
Attention – since I used an older backup of the archive database, practically all data was compressed. If I had a current production database, the chunks with the newest data would remain uncompressed and would be compressed only after reaching the configured age (e.g. 1 month).
Information about objects
Information about individual chunks of the hypertable and about the data size of the compressed chunks before and after compression has been added to command SHOW_INFO in D2000 System Console. The following screenshot is an example of a listing showing monthly chunks (all compressed) that occupy 220 MB, while the original size was 1578 MB.
Licensing
What are the additional costs of a TimescaleDB license? Now I hope to please all administrators who want to reduce their archive databases. The TimescaleDB license allows free use of this extension on your own servers and in the cloud, modifying TimescaleDB for your own use, embedding it in your own applications or services, and using it in commercial software (i.e. also for purposes such as archiving in D2000). It only prohibits the use of database-as-a-service in the cloud, thus preventing cloud providers from parasitizing TimescaleDB (more information here).
Documentation
TimescaleDB support in the D2000 Archive process is documented in detail in our online documentation.
Conclusion
Support for the TimescaleDB extension will allow to reduce the disk space requirements of the archive database, similar to what the previously implemented data compression did for depository databases.
I assume that this feature will be used first in new applications - and in existing applications with time slices it will be deployed during migration to new hardware and a new version of D2000, as it will require the creation of a new archive database and filling it via the arcsynchro utility - and subsequent activation of TimescaleDB.
August 3, 2026, Ing. Peter Humaj, www.ipesoft.com
