Today in Edworking News we want to talk about We are excited to launch pg_timeseries: a PostgreSQL extension focused on creating a cohesive user experience around the creation, maintenance, and use of time-series tables. You can now use pg_timeseries to create time-series tables, configure the compression and retention of older data, monitor time-series partitions, and run complex time-series analytics functions with a user-friendly syntax.
The PostgreSQL extension pg_timeseries is open-sourced under the PostgreSQL license and can be added to your existing PostgreSQL installation or tried as a part of the Timeseries Stack on Tembo Cloud.
What is Time-series Data?
Time-series data represents a collection of data points where each point is associated with a specific timestamp. This data can come from various sources, including stock prices recorded during trading hours, sensor data from IoT devices measuring temperature and availability, or web traffic on a website. Time-series workloads typically involve queries that filter data based on time (often for recent data) and aggregation queries to summarize the data for analytics purposes.
Using PostgreSQL for Time-series Workloads
PostgreSQL is a versatile database system well-suited to handle diverse data workloads, thanks to its extensions and ecosystem tools. At Tembo, our goal is to simplify the modern data stack by making it easy for users to leverage the entire PostgreSQL ecosystem. Over the past year, we have developed several stacks and extensions that facilitate running analytics, AI, and operational workloads on PostgreSQL. One of the most requested features from our customers has been the ability to store and analyze time-series data, so they can rely on a single PostgreSQL provider for all their needs.
Why Not TimescaleDB?
Although TimescaleDB is a popular choice for time-series databases, its license restricts the use of valuable features like compression, incremental materialized views, and bottomless storage. Due to these limitations, we decided to build our own PostgreSQL-licensed extension tailored to our customers' needs.
Building Blocks for pg_timeseries
Creating an efficient time-series data storage and query system requires specific building blocks. PostgreSQL provides several features that serve these purposes:
Native Partitioning
Various Indexes
Materialized Views
Window and Analytics Functions
Additional extensions can be integrated to enhance PostgreSQL's capabilities further:pg_partman for partition management
pg_cron for job scheduling
columnar for compression
pg_ivm for incremental materialized views
pg_tier for long-term offloading of older partitions
Combining all these components cohesively is challenging for most users, but pg_timeseries aims to solve this problem by integrating these functionalities into a single, intuitive interface.
pg_timeseries: Simplifying Time-series Data Management
pg_timeseries merges the functionalities of extensions like pg_partman, pg_cron, and Hydra's columnar to deliver a unified interface for managing and querying time-series tables. This extension ensures PostgreSQL users can fully leverage these tools to efficiently manage their time-series workloads.
To get started with pg_timeseries, all you need is a table with a time-like column, partitioned on that column. You can then enable time-series functionality by calling enable_ts_table. The extension provides views like ts_table_info and ts_part_info to display important information about your tables and partitions.
As partitions age, you can choose to compress them using columnar storage, delete them, or both. For example, you can compress partitions older than 90 days while deleting those older than one year.
pg_timeseries also includes additional functions like locf, last, and date_bin_table to simplify writing time-series queries. These functions enhance query maintainability by making SQL easier to manage for time-series data.
We're Just Getting Started
Building a comprehensive time-series extension for PostgreSQL involves many components, but we are committed to developing it in collaboration with the community. Our current roadmap includes integrating pg_tier with pg_timeseries to enable offloading older tables to cold storage options like S3. The roadmap will prioritize new features based on user demand.
The easiest way to try out pg_timeseries is by spinning up a free instance of the Timeseries Stack on Tembo Cloud. We welcome your feedback and look forward to improving the extension with your input.
Remember these 3 key ideas for your startup:
Utilize PostgreSQL Ecosystem: PostgreSQL, with its extensions like pg_timeseries, provides a powerful platform for managing complex data workloads, including time-series data.
Choose Open-source Solutions: Opting for open-source extensions like pg_timeseries avoids the limitations of proprietary licenses, giving you more flexibility and control.
Leverage Unified Tools: Integrating multiple functionalities into a single interface simplifies data management and enhances productivity, making tools like pg_timeseries essential for efficient operations.
Try pg_timeseries on Tembo Cloud
What's Next?
We invite you to explore pg_timeseries and share your feedback with us. Your insights will help us prioritize and enhance features based on actual user needs.
Edworking is a FREE superapp of productivity that connects Task Management, Docs, Chat, Videocall, and File Management in one app. Save money today by not paying for Slack, Trello, Dropbox, Zoom, and Notion.
For more details, see the original source.
