How to Deploy Timescale on Hidora: Database to Store Time-series Data
Today I would like to discuss an opensource database Timescale, designed to store time-series data. To begin with, let’s understand what is time-series data and when you definitely need to use it.
A time series is the statistics of observations of the same parameter of any process, within a certain time period. Each observation result corresponds to the time when this parameter was measured, or to the sequence number on the time scale. Therefore, the analysis of time series takes into account not only the basic statistical patterns but also the interrelation of measurements with time.
Simply put, if we memorize all changes of objects in time then our data will turn into time series. Such data is required and accumulated for a variety of tasks and for various needs. This may be information from different sources:
- Website statistics (site visits, hardware and network resources, etc.)
- Financial and stock statistics;
- Macroeconomic statistics;
- Monitoring system;
- Data from IoT devices;
- Audit log;
The demand for tools that store and process time-series data is quite high, especially in financial markets.
The reason that traditional RDBMS (relational database management systems) did not allow to work effectively with an ordered set of elements of the time series, had become a catalyst for the development of a new software optimized for these tasks.
In a contrast to ordinary business data that we used to store in a relational database, time series data mandatory contains a time stamp. Existing records are not updated, but new ones are added. Usually, the data is stored on the time axis. These requirements impose certain restrictions on the effective organization of time series storage. This is where specialized time series databases such as TimescaleDB can help.
TimescaleDB – is an opensource database for storing and managing data presented in the form of time series. The DBMS is available as an extension to PostgreSQL, which is good because you can install it on top of any existing PostgreSQL instance.
This database management system allows using full-fledged SQL queries for analyzing the accumulated data, combining the convenience of work characteristic of relational DBMS with the scaling and capabilities of specialized NoSQL systems.
A key feature of Timescale is automatic partitioning support of a data array. The input data stream is automatically distributed to partitioned tables. Partitions are created depending on the time (data is stored in each partition for a certain period of time) or in relation to a random key (for example, device identifier, location, etc.).
Since the release of the first version, all the important features like the usability of the system, the automation of certain functions and stabilization of the tool were improved. In the last version, its developers made the following changes:
- Added the ability to use a wide range of date-time pointers. Changed the days of the week visualization in the calendar – each week starts on Monday in accordance with ISO standards.
- Simplified the management of multiple tablespaces to make hyper-table scaling more flexible — an ordered space from several tables with constantly accumulating data.
- Improved support for Windows, FreeBSD and NetBSD.
- Laid the foundation for creating a framework-scheduler that would allow automating background tasks. In upcoming releases, incoming data will be automatically collected, stored and archived.
- Made the DBMS more stable – the tool now responds better to extreme situations related to changes in schemas and tablespaces and provides a more transparent system of access permissions for backup / restore database templates.
- Implemented the integration with Grafana and Prometheus, monitoring and visualization systems. That helped to expand the ecosystem of compatible tools: Tableau, Kafka, Apache Spark, Zabbix, PostGIS, PowerBI and others can work with DBMS.
- Added support for the visual query editor developed by Grafana.
How to Deploy Timescale on Hidora
Go to https://app.hidora.com/.
Click the marketplace button on the Hidora dashboard. Type ‘Timescale’ in the search line. And click the Install button. The installation will be finished within a few minutes!