Volume 17, Number 3/4/5

Architectural Patterns and Performance Analysis of Integer Surrogate Keys for Time-Series Data Warehousing

  Authors

Sviatoslav Stumpf and Vladislav Povyshev, ITMO University, Russia

  Abstract

This paper provides a comprehensive analysis of architectural patterns and optimization techniques for time-series data processing, centered on the replacement of native DATE/TIMESTAMP types with integer-based surrogate keys. We demonstrate that employing 32-bit (YYYYMMDD) and 64-bit integer formats for datetime representation, coupled with specialized algorithms for indexing, range search, and aggregation, yields substantial performance gains. Empirical evaluations confirm a 30–60% reduction in storage footprint, a 25–40% acceleration in query execution, and up to an eightfold increase in system throughput through batched operations. Beyond these metrics, the study delves into advanced practical implementations across high-frequency trading, telecommunications, and industrial IoT, detailing extended use cases such as real-time fraud detection and predictive maintenance. The paper further introduces a set of actionable implementation guidelines, including hybrid data models and optimized partitioning strategies, to facilitate adoption. Finally, we explore the application of International Atomic Time (TAI) to eliminate temporal ambiguities and outline future research directions integrating this approach with machine learning pipelines and edge computing architectures. The collective findings position integer-based timestamp storage as a foundational element in the design of high-performance, scalable, and reliable time-series data warehouses.

  Keywords

Integer labels, time series, optimization, performance, data warehouse, indexing, aggregation