Optimizing Time Series Data Storage and Querying: Migrating `candle_data` from PostgreSQL to QuestDB for Enhanced Performance

Introduction: The Challenge of Time Series Data in PostgreSQL Handling large-scale time series data in PostgreSQL, particularly in tables like candle\_data, exposes inherent limitations of general-purpose databases when pushed to their limits. The mechanical process of ingesting, storing, and querying time series data in PostgreSQL involves row-based storage and sequential disk I/O, which degrades under high write throughput and complex temporal queries. For instance, as the candle\_data table grows, index bloat and disk contention become observable effects, leading to query latency spikes. This is exacerbated by PostgreSQL’s lack of native optimizations for time series workloads, such as columnar compression or vectorized execution, which are critical for reducing storage overhead and accelerating analytical queries. Performance Bottlenecks in PostgreSQL The root cause of performance degradation lies in PostgreSQL’s row-oriented storage architecture. When querying time series data, th