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

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 integerbased 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.