March 2023 Summaries

The company, flowkey, is a leading app for learning to play the piano with over 10 million registered users in more than 100 countries. The company was launched in 2015 and has become one of the global leaders in its category. Flowkey's data analysts are well-versed in SQL and PostgreSQL, and they needed a scalable solution for categorical, time-series data analysis. They chose TimescaleDB, an open-source relational database that suits their needs perfectly. With TimescaleDB, they can analyze user behavioral data using Metabase or Jupyter/Rmarkedown Notebooks, drive rapid growth and experimentation, and improve their online piano lessons by understanding millions of user events daily. The company's deployment is self-hosted on machines, with automated data tiering that reduces disk space usage by 28%. They use compression to store JSONB fields, and have established a workflow for writing custom continuous aggregates for experiments. Flowkey recommends the Timescale documentation and Slack Community for advice and resources, and they are always keen to feature new community projects and stories on their blog.

PostgreSQL is a popular choice for storing time-series data due to its familiarity among developers, although it may not be the first target for this use case. The database can handle various types of time-series data, including metrics, logs, and payment records. To scale PostgreSQL for efficient storage of large amounts of time-series data, approaches such as partitioning and indexing are often employed. Additionally, the Timescale extension provides a dedicated solution for storing time-series data in PostgreSQL, offering features like efficient data compression and aggregation. By leveraging these scalable approaches, developers can efficiently store and analyze their time-series data within the PostgreSQL framework.

Timescale Cloud, a high-performance PostgreSQL service, offers a compelling alternative to Amazon RDS for PostgreSQL, facilitated by the new migration tool Hypershift. Hypershift streamlines the migration process by enabling on-the-fly compression, reducing storage costs significantly, and minimizing downtime. Built as a Rust wrapper around pg_dump and pg_restore, it enhances migration speed through parallelization and eliminates the need for interim storage, allowing direct data transfer. Hypershift also supports Timescale-specific features like hypertables and compression, providing immediate benefits by transforming PostgreSQL tables without schema changes. While the tool focuses on migrating from Amazon RDS to Timescale Cloud, its platform-agnostic nature allows adaptation to other PostgreSQL sources and destinations. The migration process involves planning platform differences, such as the inability of Timescale Cloud to host multiple databases, and ensuring compatibility of versions, roles, and connection strings. Timescale Cloud's support team is available to assist throughout the migration, ensuring a seamless transition for users seeking improved performance and cost-efficiency.

This article discusses Linked Data Event Streams (LDES) and their integration with the Timescale database. LDES is a data event stream of a group of immutable objects described as machine-readable RDF, enabling different systems and applications to easily consume and act on data streams in a consistent and standardized way. The article shows how to effortlessly insert sensor data from an LDES into a Timescale database using Apache NiFi. It covers the configuration of a data flow to ingest an LDES into PostgreSQL, the use of version materialization to remove version information from LDES members, and the transformation of the data into a structured JSON file. The article also highlights the analysis that can be done with data in Timescale, including time-series visualization, anomaly detection, forecasting, and aggregation of time-series data.

PostgreSQL views serve as aliases for database queries, simplifying complex queries for users but potentially obscuring performance issues if overused, as they do not execute queries themselves. In contrast, materialized views execute and store query results, acting as a cache to improve performance and allow indexing, though they require periodic refreshing to maintain accuracy. This refresh process can be inefficient, especially for time-series data, due to the need for complete data re-materialization. To address this, continuous aggregates offer a solution by incrementally updating with only new data, allowing for real-time and pre-computed aggregations to deliver accurate and timely results, which Timescale has developed further to enhance performance in time-series and analytical applications.