Power IoT and time-series workloads with TimescaleDB for Azure Database for PostgreSQL
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. Then, connect to 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.
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.
Note: General Purpose Compute Gen 5 with 4 vCores, 20GB 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
- Learn more Azure Database for PostgreSQL and get started using the Azure portal or command line.
- Learn more about TimescaleDB by visiting their website, docs, or join their Slack community and post any questions you may have there.
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.
Source: IoT