Practical Implementation of TimescaleDB and PostgreSQL at Hytera: Concepts, Deployment, and Performance

The article introduces time‑series (chronological) data, its characteristics—time‑centric, append‑only, rarely updated—and typical scenarios such as system monitoring, IoT sensor streams, event logging, and business intelligence.

Hytera’s specialized network services require a time‑series database with compression, automatic expiration, sharding, high insert performance, partition management, SQL compatibility, and rich data types.

TimescaleDB is a PostgreSQL extension that adds native time‑series capabilities while retaining full SQL support. It provides automatic time‑ and space‑based sharding (chunks), vertical and horizontal scaling, parallel query across multiple servers, and automatic chunk size adjustment.

Key features (nine points): time‑series optimization, automatic sharding, full SQL support, distributed scaling, automatic time/space partitioning, parallel chunk queries, automatic chunk resizing, write‑optimizations (batch commits, in‑memory indexes, back‑fill support), and advanced query planning with chunk‑pruning and push‑down limits.

The core data model uses hypertables (logical tables) that are internally split into chunks (physical storage units). Chunks can be distributed across nodes, making the architecture transparent to applications.

Deployment modes: (1) Single‑instance PostgreSQL with TimescaleDB, (2) Primary‑replica streaming replication for read‑only scaling and high availability, (3) Distributed multi‑node deployment where chunks are spread across nodes; current versions still rely on PostgreSQL streaming replication for HA.

TimescaleDB supports native compression of old chunks, reducing storage and improving query speed. Compression is handled by an internal job scheduler without external tools.

Data expiration can be performed via PostgreSQL’s automatic vacuuming or by dropping whole chunks, e.g., SELECT drop_chunks('conditions', INTERVAL '24 hours'); . Retention policies can be set automatically, for example: SELECT add_retention_policy('conditions', INTERVAL '6 months'); SELECT add_retention_policy('conditions', BIGINT '600000');

Backup and restore leverage PostgreSQL’s existing tools (pg_basebackup, pg_dump/pg_restore) and can include distributed restore points for multi‑node setups.

Performance tests comparing plain PostgreSQL and TimescaleDB show significant gains for large‑scale time‑series writes, with benchmark graphs illustrating higher throughput and lower latency for TimescaleDB.

In Hytera’s production environment, TimescaleDB was chosen over InfluxDB, Kdb+, and Prometheus due to its SQL compatibility, low migration cost, strong community support, and ability to integrate with existing PostgreSQL‑based services and GIS extensions.

The practical deployment includes using TimescaleDB for monitoring, analytics, and storage of police communication system metrics, with future plans to integrate big‑data offline analysis and micro‑service architecture upgrades.

The article concludes with a thank‑you note and a call for readers to share, like, and follow the DataFunTalk platform.