September 2019 Summaries

TimescaleDB continuous aggregates are a feature that allows users to speed up repetitive queries that aggregate over time, making it easier to analyze and visualize important real-time and historical metrics. This feature is particularly useful for building dashboards that require frequent updates with ad-hoc queries. TimescaleDB's automated partitioning and continuous aggregates reduce disk throughput and compute requirements when running historical aggregate queries, making it an ideal solution for organizations looking to expose their time-series data across teams. The feature supports out-of-order inserts, allows users to configure refresh intervals, lags, and maximum intervals per job, and provides statistics through `timescaledb_information.continuous_aggregate_stats`. By leveraging continuous aggregates, users can improve the performance of their dashboards and gain valuable insights from their time-series data.

The crypto market is experiencing a resurgence after the Crypto Winter in 2018. The data-driven analysis of Bitcoin and Ethereum prices, new coin growth, trading volume, daily returns, and more reveals some interesting insights. For example, if you had invested $100 in Bitcoin 9 years ago, it would be worth over $16 million today. The best day to "day-trade" Bitcoin was February 26, 2014, with a daily return factor of 4.368. The top countries for Bitcoin trading volume are the US, Japan, South Korea, and Poland. Ethereum is popularly regarded as the cryptocurrency with the second-largest interest base after Bitcoin, but its price has been trending downward since 2018. New cryptocurrencies are being launched at an incredible rate, with over 4000 in existence. Some of the most profitable new cryptocurrencies include Mixin (MIXI), BOMB, Double Eagle Coin (XDE2), Gold Reserve (XGR), SoulCoin (SOUL), and CCCoin (CCC). The analysis also reveals that the coins with the highest frequency of having the top daily return are MIXI, Bitether (BTR), and IceChain (ICHX).

The Internet of Things (IoT) is a trend where computing is becoming ubiquitous and embedded in physical things to collect sensor data about the environment. TimescaleDB is a time-series database that can handle this type of data, which is generally time-series in nature with relational metadata. The tutorial explores the features and capabilities of TimescaleDB using an IoT sensor dataset meant to simulate a real-world IoT deployment. It starts by creating a new TimescaleDB instance via Timescale Cloud and setting up two tables: `sensors` and `sensor_data`. The `sensor_data` table is then populated with simulated data for four sensors, recording data every 5 minutes for the past 24 hours. Basic queries are run to calculate the average temperature and CPU by 30-minute window, and later to get the last temperature value in each period. Finally, a continuous aggregate view is set up to recompute the query automatically at regular time intervals and materialize the results into a table, speeding up the query significantly.