Table of contentsClick link to navigate to the desired location
This content has been automatically translated from Ukrainian.
Time-series a database is a type of storage optimized for data, time-bound. Every record in such a system has a timestamp, and it is time that is the main feature by which this data is stored, read and analyzed. Similar databases are created for large streams of events that constantly arrive and mostly do not change after recording.
Such databases are used in server monitoring, financial systems, web traffic analytics, IoT devices or any processes where it is important to see the dynamics of indicators. For example, changes in processor load in recent hours, sensor temperature every second, or fluctuations in the stock index during the day.
Time-series databases provide fast reading of large time ranges, effective aggregations, compression and the ability to quickly delete old data without compromising performance.
Popular implementations include InfluxDB, TimescaleDB, Prometheus, VictoriaMetrics, and even Elasticsearch, which is often used for time-series analytics due to its ability to quickly index events and execute aggregated queries. This makes such bases indispensable for monitoring, analytics and high-frequency data systems, where time is a key factor.
MySQL and PostgreSQL can use for time-series, but they poorly scaled under such loads. The reason is not that they are "bad", but that their architecture is not designed for very frequent inserts and huge amounts of data organized precisely by time.
Why MySQL and PostgreSQL are not suitable for time-series
In classical relational databases, records are stored in tables, and indices are stored in B-trees. When data is constantly added over time (every second or even every millisecond), the index begins to "blow up" quickly, memory segments fragment, and frequent INSERTs put a strain on page locks and transaction logs. As a result, the base begins to slow down, especially when the data is no longer measured in millions, but in hundreds of millions or billions of rows.
The second disadvantage is aggregation over large time ranges. For example, getting a "three-month average temperature" in MySQL or PostgreSQL is a slow scan on a huge table. In time-series databases, these operations are performed instantly, because they are immediately optimized for range requests and store data in an easily aggregated format.
Another problem is the deletion of old data. In PostgreSQL, this causes a massive bloat and requires VACUUM, which constantly overloads the system. In MySQL, the situation is no better: when a large number of old records are deleted, tables and indexes become fragmented and performance drops. In time-series databases, this is solved architecturally: old data is stored in separate "pieces" and is simply discarded in whole blocks without load.
Also, conventional SQL databases do not have effective data compression optimized specifically for sequences of values over time. In time-series repositories (InfluxDB, VictoriaMetrics, Prometheus), compression allows you to store the same data 5–20 times more compactly.
To conclude: MySQL/PostgreSQL work great with classic transactional data, but with large streams of telemetry, logs or touch indicators, their performance drops rapidly. Time-series databases are created specifically for such a load: they optimize the writing, storage, reading and deletion of time-bound data.
This post doesn't have any additions from the author yet.
