October 2022 Summaries

Continuous aggregates were developed by Timescale to solve the challenge of aggregating massive amounts of data efficiently without querying billions of raw data rows. They use PostgreSQL materialized views to continuously and incrementally refresh a query in the background, reducing the amount of data that needs to be computed. The new version of continuous aggregates, available in TimescaleDB 2.8.1, offers significant performance improvements, including faster queries for common operations like counting rows, querying within time ranges, and applying filters. Additionally, it reduces storage costs by using a FILTER clause to store aggregated data without additional rows, and increases query flexibility with features like the HAVING clause at materialization time.

The United Manufacturing Hub is an open-source Helm chart for Kubernetes that combines state-of-the-art IT/OT tools and technologies to bring them into the hands of engineers, standardizing the IT/OT infrastructure across customers and making it easy to integrate and maintain. The team uses TimescaleDB to store both relational and time-series data coming from MQTT and Kafka, and visualizes it using Grafana and their own REST API. This allows them to prevent and predict maintenance issues, analyze and optimize production losses, reduce resource consumption, and more. The use of TimescaleDB provides reliability, fault tolerance, and ease of horizontal scaling, making it an ideal choice for manufacturing data management.

Timescale Cloud provides impressive time-series optimizations on top of PostgreSQL, offering performance at scale, cost-efficiency, and all the time-series features needed without a separate learning curve. Database indexes in Timescale Cloud work similarly to those in regular PostgreSQL, with some additional considerations for hypertable-specific indexing. When converting a PostgreSQL table to a hypertable, existing indexes are preserved and can be used as usual, while new indexes can also be created directly on the hypertable using the standard CREATE INDEX method. The use of indexes with hypertables is fully supported, but there is a limitation on concurrent index creation. Timescale Cloud manages chunks as separate PostgreSQL tables, each with its own set of indexes, and query planning takes into account internal partitioning into chunks automatically.

Virtual Private Cloud (VPC) peering enables secure access to cloud data without exposing services to the public internet, allowing for maximum security and privacy. This feature creates a private network "peering" connection between Amazon VPCs and Timescale Cloud VPCs, isolating services and gaining greater control over databases. To configure VPC peering, users must familiarize themselves with networking fundamentals, including IP addresses, subnetting, CIDR, routing, security groups, and VPC settings. For example, users must ensure that there is no overlap between the AWS VPC CIDR block and the Timescale Cloud VPC CIDR block to avoid conflicts. Additionally, users need to configure routes for traffic flow, open port 5432 for incoming connections, and create a security group to allow communication between the two networks. By following these steps, users can establish secure access to their cloud data and maintain maximum security and privacy.

Testing performance improvements in a production database can be challenging, but leveraging a managed database in the cloud can simplify the process. Timescale Cloud offers a convenient way to test performance improvements by creating "forks" - copies of the production database that can be used to test different configurations without affecting the original database. This allows for rapid and safe testing of performance improvements, including identifying slow queries, optimizing ingest rates, and configuring database parameters. The process involves designing and writing tests, applying configuration changes to one or both forks, running tests using tools like pgbench, and comparing results to determine if changes improve performance. By following these steps and utilizing Timescale Cloud's features, developers can efficiently test performance improvements without risking production data or downtime.

Planning photovoltaic systems isn't easy, even with a specialist at hand. The author spent time collecting consumption data to find cost efficiency in their home's electricity usage. They used TimescaleDB, Node-RED, Grafana, a Raspberry Pi, and an open-source software combination to collect data from the power meter and plan their photovoltaic system. The author aimed to create a baseline of energy consumption per day, hour, weekday, and month. With this information, they can make educated decisions about battery capacity and optimize their solar panel system. The setup was designed as a temporary workaround but made it easy to set up and run the time-series database 24/7. The author encourages readers to try TimescaleDB for free with no credit card required.