September 2022 Summaries

Time-series data is often collected at a higher rate than it's displayed, requiring downsampling or rolling up the data into larger granularities. However, when downsampled data is used with high-frequency data, time zone support is crucial to ensure accurate results. The new TimescaleDB 2.8 release introduces full time zone support for the `time_bucket` function, eliminating the need for workarounds and simplifying analytical requests that require time zone information. Users can now provide a time zone parameter to adjust day boundaries, months, and years accordingly. This feature was developed with community input and is available as part of the latest release, which also includes experimental support for policy management and speed improvements.

TimescaleDB is an open-source time-series database that extends PostgreSQL to improve query performance by 1,000x or more and reduce storage utilization by up to 90%. It achieves this through features such as hypertables for efficient data partitioning, compression algorithms for reduced disk consumption, continuous aggregates for faster analytical queries, and a library of hyperfunctions for complex time-series analysis. TimescaleDB also provides built-in job scheduling and retains full compatibility with PostgreSQL and SQL.

To get the most out of Timescale Cloud, it's essential to tune key parameters such as background workers, memory allocation, and maintenance operations. Background workers perform critical processing for operations like live queries and background jobs, and their settings need to be carefully configured to avoid issues like User-Defined Actions not working properly or database size increasing rapidly. Memory allocation is crucial, with work_mem setting determining the amount of memory used by query operations, while maintenance_work_mem setting affects performance for VACUUM operations and database dump/restore. Shared_buffer settings can also impact performance, particularly for read-heavy workloads. By understanding these parameters and applying the recommended guidelines, users can diagnose and fix performance issues in their Timescale Cloud environment.

This installment of the "Community Member Spotlight" series highlights Andrew Engel, chief data scientist at Rasgo, who has been using TimescaleDB to build a time-series forecasting side project. Andrew initially worked with PostgreSQL and Airflow for his project but found the performance limitations of Python-based solutions. He then implemented custom extensions in C to improve performance by 10-100 times, releasing them as open-source packages called etu and pgetu. These packages provide faster and more efficient time-series feature engineering directly within TimescaleDB, making it easier for data scientists to work with the database. Andrew hopes to help others benefit from this project and plans to continue improving the libraries to support more feature calculators in tsfresh.

PostgreSQL's caching system was designed in the early days of the database, when network speeds were slower than hard drives, hard drives were slower than memory, and memory was slower than CPU. The basic idea was to keep frequently used data in memory to speed up retrieval. This improvement has proven far more effective and valuable over time. However, as PostgreSQL matured, the shared memory system also matured, and caching became less accurate due to other factors becoming prominent. The system follows a simple paradigm of locking or latching, with two types of pins for reading or writing to memory. It uses a page and leaf descriptor system to manage table data on disk, allowing for efficient lookup in memory. The system is tightly integrated into the PostgreSQL query planner and acts as both a cache for frequently accessed data and a change buffer for data modifying queries. Caching has its limitations, including cache eviction, which can lead to performance issues if not managed properly. Despite these challenges, caching remains an essential component of PostgreSQL's design principles, and future articles will explore its caveats and improvements.

Pie charts are widely used data visualization tools that represent whole data divided into multiple parts or slices, each representing a subcategory of the complete data. They can be used to answer various questions such as identifying the least traded stock volume, highest traded stock, and percentage of accrued votes in an election. The tutorial aims to teach users how to create beautiful and insightful pie charts using Grafana, a web application that utilizes PostgreSQL and TimescaleDB for analytics and interactive visualization. It will cover creating pie and donut charts with pre-aggregated data using time_bucket and show transaction volume in the same panel, providing real-world examples of pie chart creation in Grafana.

Measuring change in data is vital for understanding the world, and tools like PostgreSQL and TimescaleDB help us analyze historical data to predict future trends and values. Time-series forecasting is a technique that uses historical and current data to make informed decisions about business strategy and understand future trends. This method can be used in various industries such as business planning, control engineering, cryptocurrency trends, financial markets, modeling disease spreading, pattern recognition, resources allocation, signal processing, sports analytics, and weather forecasting. While time-series forecasting may seem universally applicable, there are limitations that developers need to consider, including understanding the data model and identifying stable trends within historical data. Key factors in time-series forecasting include the amount of data, data quality, seasonality, trends, and unexpected events. Several popular forecasting methods exist, such as time-series decomposition, time-series regression models, exponential smoothing, ARIMA models, and neural networks. Each method has its benefits and drawbacks, and choosing the right one depends on the specific situation and type of data. Neural networks offer robustness to noise, nonlinear support, multivariate inputs, and multi-step forecasts, making them a valuable option for time-series forecasting. TBATS models can handle complex seasonalities with no seasonality constraints, but may be slow when calculating predictions. Overall, time-series forecasting is a powerful method for predicting future trends and values in time-series data, and understanding its applications and limitations is crucial for informed decision-making.