Power IoT and time-series workloads with TimescaleDB for Azure Database for PostgreSQL

在 三月 18, 2019 上貼文

Principal Program Manager, Azure SQL Engineering

We’re excited to announce a partnership with Timescale that introduces support for TimescaleDB on Azure Database for PostgreSQL for customers building IoT and time-series workloads. TimescaleDB has a proven track record of being deployed in production in a variety of industries including oil & gas, financial services, and manufacturing. The partnership reinforces our commitment to supporting the open-source community to provide our users with the most innovative technologies PostgreSQL has to offer.

TimescaleDB allows you to scale for fast ingest and complex queries while natively supporting full SQL. It leverages PostgreSQL as an essential building block, which means that users get the familiarity and reliability of PostgreSQL, along with the scalability and performance of TimescaleDB. Enabling TimescaleDB on your new or existing Azure Database for PostgreSQL server will eliminate the need to run two databases to collect relational and time-series data.

How to get started

If you don’t already have an Azure Database for PostgreSQL server, you can create one with the Azure CLI command az postgres up. Next, run the following command to add TimescaleDB to your Postgres libraries:

az postgres server configuration set --resource-group mygroup ––server-name myserver --name shared_preload_libraries --value timescaledb

Restart the server to load the new library:

az postgres server restart --resource-group mygroup --name myserver​

Then, connect your Postgres database and run:

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

You can now create a TimescaleDB hypertable from scratch or migrate your existing time-series data. For more information about TimescaleDB and other extensions in Azure Database for PostgreSQL, take a look at the documentation, "PostgreSQL extensions in Azure Database for PostgreSQL." 

Postgres with TimescaleDB as a foundation for IoT applications

PostgreSQL is enabling many IoT scenarios. To learn more, refer to the blog post, “Creating IoT applications with Azure Database for PostgreSQL.” With TimescaleDB, this experience is even better. IoT organizations can now also leverage the insights hidden in machine generated data to build new features, automate processes, and drive efficiency.

Challenge Solution
IoT devices generate a lot of data which needs to be stored efficiently. TimescaleDB automatically partitions data into chunks to scale for these types of workloads.
IoT data is complex (i.e. marrying device metadata, geospatial data, and time-series data). TimescaleDB combines relational capabilities with time-series specific functions and is compatible with other PostgreSQL extensions including PostGIS.
IoT data needs to be accessed by multiple users (i.e. internal users for analytics or external users to expose data in real-time). TimescaleDB speaks full SQL, a query language that is familiar across entire organizations.
IoT data requires diverse, customizable ingest pipelines that require a database with a broad ecosystem. TimescaleDB inherits PostgreSQL’s entire ecosystem of tools and extensions.
IoT applications are made up of data at their core, and need to be stored in a reliable database. TimescaleDB inherits PostgreSQL’s 20+ years of reliability and stability.

TimescaleDB offers valuable performance characteristics on top of PostgreSQL. For IoT use cases that highly leverage time-series data, TimescaleDB implements automatic chunk partitioning to support high insert rates. Below is a comparison on Azure PostgreSQL with and without TimescaleDB and observed degradation in insert performance over time. You can imagine that with IoT use cases with large amounts of time-series data, using TimescaleDB can provide significant value for IoT applications that need both relational features and scalability.

A comparison on Azure PostgreSQL with and without TimescaleDB and observed degradation in insert performance over time.

Note: General Purpose Compute Gen 5 with 8 vCores, 45GB RAM with Premium Storage

Although IoT is an obvious use case for a time-series database, time-series data actually exists everywhere. Time-series data is essentially collected over time with an associated timestamp. With TimescaleDB, developers can continue to use PostgreSQL, while leveraging TimescaleDB to scale for time-series workloads.

Next steps

As always, we encourage you to leave feedback below. You can also engage with the Azure Database for PostgreSQL through our feedback page and our forums if you have questions or feature suggestions.