March 2024 Summaries

In a recent Timescale Community Slack discussion, Dustin Sorensen shared an innovative solution for efficiently managing and aggregating data from multiple sensors or IoT devices without the need to create a dedicated column for each sensor. The solution involves creating custom aggregate functions that allow for the aggregation of any JSONB values, providing flexibility to handle both numeric and non-numeric data within the same column. This approach eliminates the need for multiple columns for each data type, streamlining the data aggregation process. Dustin's solution demonstrates the power of TimescaleDB's extensibility and the innovative spirit of the community.

Data visualization tools like Apache Superset and Metabase help analysts and stakeholders understand complex data quickly by transforming it into graphical representations such as charts and graphs. These tools allow users to identify trends, outliers, and patterns, leading to informed decisions and insights. While paid options like QlikView, Power BI, and Tableau offer wider feature sets, better scalability, and more support, open-source tools like Apache Superset are free to use and modify but may require technical expertise and have limitations for large deployments. In this article, we demonstrate how to visualize data using Apache Superset with PostgreSQL by creating a dataset, visualization, and dashboard. We also discuss best practices for data visualization and provide an FAQ section for common issues.

In this blog post, the authors discuss how they made real-time data aggregation in Postgres faster by 50,000% using continuous aggregates (CAggs), a core feature of TimescaleDB. CAggs are self-updating materialized views optimized for aggregation queries over time-series data. The authors introduced enhancements to significantly accelerate real-time data aggregation in TimescaleDB 2.13.0, resulting in a planning time reduction of over 50,000% and a substantial decrease in query execution times. They achieved this by constifying the watermark function call, which allowed plan time chunk exclusion and scans only for the chunks that are actually accessed by the query.

Timescale first announced its plan to build a self-sustaining open-source business in the cloud era back in 2018, introducing the Timescale License (TSL). The TSL is a source-available license that maintains an open-source spirit but restricts companies from offering software licensed under it via a hosted database-as-a-service. This approach has been successful and enabled TimescaleDB to grow its community significantly. TimescaleDB, the leading relational database for time-series data, is engineered on top of PostgreSQL and offers massive scale, high compression rates, faster queries, and overall reliability. The growth of the Timescale Community indicates a strong demand for a new database to handle time-series data. Cloud Protection Licenses like the TSL attempt to maintain an open-source spirit but protect the right of offering the software in the cloud for the main creator and maintainer of the project. This "cloud protection" enables open-source businesses like TimescaleDB to become self-sustaining in the cloud era. The response from the community has been overwhelmingly positive, with many developers supporting the general direction of the TSL. The updated license in 2020 added some rights and removed others based on user feedback while preserving the main restriction preventing other companies from offering TimescaleDB-as-a-service. In summary, the Timescale License allows users to run TimescaleDB for internal use or utilize it as part of a value-added service, but restricts them from utilizing it for external use unless as part of a value-added service and distributing modified source code.

Datacake, a low-code IoT platform founded by Lukas Klein and Simon Kemper in 2016, has been storing around 20 million measurements per day for about 20,000 users. The company migrated from Influx to TimescaleDB due to the latter's Postgres-nativity and compatibility with their existing knowledge base. They chose the fully managed Timescale cloud solution over self-managed TimescaleDB for its cost-effectiveness and robustness, as well as the support provided by Timescale. The migration process was smooth, with no downtime or negative impact on users. Datacake continues to utilize various features of Timescale, such as compression, insights, continuous aggregates, and high-availability replicas, to optimize their platform's performance and meet the growing demands of their enterprise customers.

Time-series analysis is a crucial aspect of software development, allowing engineers to extract valuable insights from temporal data. By leveraging Python as the primary programming language, developers can efficiently analyze and model time-dependent data using libraries like pandas and NumPy. Python's simplicity, versatility, and extensive library ecosystem make it an ideal choice for working with time-series data. Time-series analysis encompasses various techniques, including trend analysis, seasonality detection, forecasting, and anomaly detection. The autoregressive integrated moving average (ARIMA) model is a popular approach for predicting future values based on historical data. Python offers numerous libraries and tools specifically designed for time-series analysis, such as pandas, NumPy, statsmodels, and scikit-learn. These libraries simplify complex operations, allowing developers to focus on extracting meaningful insights from time-dependent data. Moreover, Python facilitates code reusability, enabling developers to build upon existing solutions and accelerate the analysis process. The choice of time-series library depends on specific requirements, such as data format, size, and complexity. Common libraries include pandas for basic analysis and visualization, sktime for advanced modeling, pdmarima for ARIMA modeling, and tsfresh for feature extraction. To work with time-series data in Python, developers must consider loading and storing data efficiently, handling large datasets, and performing data cleaning operations. By leveraging Timescale, a powerful time-series-specific database, developers can execute complex queries and analyze data in real-time, making it an ideal choice for organizations requiring high-performance time-series analysis. Overall, Python and its extensive library ecosystem provide a robust platform for exploring, visualizing, and modeling time-dependent data, empowering software engineers to extract valuable insights from temporal data.

TimescaleDB 2.14.0 introduces iterative compression for dynamic applications, allowing users to modify their compression settings on the fly and enabling new chunks to adopt these updated settings. This feature greatly simplifies adjusting compression settings and offers more control and flexibility. The new version also includes per-chunk compression settings, which allows each chunk to have distinct column definitions, enabling customization of compression configurations based on specific needs. Additionally, the compression API has been updated with new optional arguments and deprecated functions. Compression is a powerful cost-saving feature in TimescaleDB that helps users manage their data more efficiently, and this latest update makes it even easier to leverage its benefits.

Timescale has launched four new cloud regions in London (eu-west-2), Tokyo (ap-northeast-1), São Paulo (sa-east-1), and Canada Central (ca-central-1). Users can now enjoy the full suite of Timescale Cloud features in these regions. The company carefully selects its cloud regions based on factors such as customer requests, sales discussions, and regional analysis of their customer base. They use a hub-and-spoke model for their multi-region cloud offering, with a central cluster managing control plane elements necessary for all other regional clusters. This approach provides benefits like speed, compliance, blast radius reduction, and operational flexibility while also requiring significant overhead in terms of compute resources and observability stack footprint per cluster.

PostgreSQL, a popular database management system, supports scaling through streaming replication. This feature enables higher read performance and high availability (HA) by streaming records of database modifications from the primary server to one or more replicas. The Write Ahead Log (WAL) is used for this purpose, capturing every atomic database change and ensuring durability. PostgreSQL supports three basic modes of replication: asynchronous, synchronous write, and synchronous apply. Each mode offers different levels of consistency guarantees and impacts on write performance. Asynchronous replication is the most performant for inserts but can result in temporary data inconsistency between the primary and replicas. Synchronous write ensures that all specified replicas will write their data to the WAL, while synchronous apply guarantees not only that the WAL will be written to all specified replicas but also that the WAL segments will be fully applied to the database. In terms of read performance, using streaming replication with multiple nodes can lead to a significant increase in query speed. The benefits of scaling reads linearly as more replica nodes are added to the cluster. Users should carefully weigh their requirements and costs when choosing which replication mode to use for their specific workloads.