July 2021 Summaries

The National Football League (NFL) has made its historical player position and play data available for public use. The dataset contains 18+ million rows of detailed information on every regular season NFL game, including the location of players on the field, speed, distance, and more. Using PostgreSQL and TimescaleDB, a relational database designed for time-series data, users can analyze this data to gain insights into player performance, team strategies, and game outcomes. The dataset is available for free and can be used by anyone, from casual fans to professional analysts, to explore the world of football in new and interesting ways.

This article showcases Paolo Bergantino's journey in deploying a time-series database solution using TimescaleDB for AROYA, a cannabis production platform. Initially, AROYA used a combination of AWS services and a custom solution that was adequate but had performance issues with growing data volumes. Paolo recognized the need for a more scalable solution and researched various time-series databases, ultimately choosing TimescaleDB due to its continuous aggregates feature, which allowed for efficient compression and reduced storage needs. The deployment process involved optimizing bucket sizes, compressing data, and creating views to improve performance. With the new solution, AROYA has been able to ingest billions of readings monthly without compromising performance, and Paolo credits TimescaleDB with delivering a solid foundation for their infrastructure. The article highlights the importance of monitoring, load testing, and vigilance in ensuring the success of such deployments.

Time-weighted averages are a way to calculate an unbiased average when working with irregularly sampled data, which is common in time-series data analysis. The problem arises because more frequent sampling points can skew the average if not accounted for, especially when dealing with applications like industrial IoT, remote sensing, and trigger-based systems. To solve this, TimescaleDB introduces hyperfunctions, new SQL functions that simplify working with time-series data. One of these hyperfunctions enables computing time-weighted averages quickly and efficiently, making it possible to analyze time-series data more effectively and gain productivity boosts for projects. By using the `time_weight` hyperfunction, developers can calculate time-weighted averages in a concise and efficient way, enabling them to build complex analyses and identify patterns in their data.

Today, TimescaleDB is launching hyperfunctions, a series of SQL functions that make it easier to manipulate and analyze time-series data with fewer lines of code. These functions, built on top of PostgreSQL, allow developers to calculate percentile approximations, compute time-weighted averages, downsample and smooth data, and perform faster COUNT DISTINCT queries using approximations. The hyperfunctions are designed to be easy to use, with the same SQL syntax as existing TimescaleDB features. They are written in Rust and can be used on hypertables or regular PostgreSQL tables. By building new SQL functions instead of introducing new syntax, TimescaleDB aims to preserve the flexibility and compatibility of its existing features while providing a more efficient and productive way to work with time-series data. The hyperfunctions are designed to simplify complex analysis and manipulation operations, bringing them closer to the data, reducing network transmission costs, and enabling faster query performance. They can be used in various use cases, such as IoT devices, IT systems, marketing analytics, user behavior, financial metrics, and more. The TimescaleDB team invites developers to try hyperfunctions with a free 30-day trial of their fully managed service or by downloading the timescaledb_toolkit extension for free.

In this edition of the Timescale newsletter, we shared advice on how to start contributing to Postgres, including tips from Aleksander Alekseev on becoming a PostgreSQL contributor. The newsletter also highlighted new technical content, such as videos and tutorials, on managing time-series data with TimescaleDB's built-in features. Additionally, it featured recordings of recent virtual events attended by the team, including talks from industry leaders at Postgres Vision 2021. The newsletter also provided tips for using TimescaleDB, including how to get 94-97% compression rates and analyze time-series data with full SQL in your favorite visualization tool. Furthermore, it shared a roundup of community resources, including a reading list on improving PostgreSQL database insert performance and using Postgres and TimescaleDB with Node.js. The team also discussed their efforts to build a remote-first team culture and hiring for open positions.