What’s New in TimescaleDB 2.8?

TimescaleDB 2.8 is now available on Timescale and for download. This major release includes the following new features:

• The time_bucket hyperfunction now supports bucketing by month, year, and time zone, enabling easier time-based queries and reporting.
• You can now manage refresh, compression, and retention policies for continuous aggregates all in one easy step.
• Improved performance of bulk SELECT statements that fetch high volumes of data from distributed hypertables using the COPY protocol.
• Support for ON CONFLICT ON CONSTRAINT UPSERT statements on hypertables, which enables better compatibility with some GraphQL/ PostgreSQL middlewares.

Let’s explore these improvements in more detail.

The time_bucket hyperfunction now supports bucketing by month, year, and time zone

“While the new time_bucket functionality is rather simple, its impact is immense and simplifies analytical requests with the need of time zone information.”

Chris Engelbert, “Nightmares of Time Zone Downsampling: Why I’m Excited About the New time_bucket Capabilities in TimescaleDB”

Support for time zones and bucketing your data by monthly or yearly intervals using the time_bucket hyperfunction were some of the most requested features, and they’re finally available in TimescaleDB 2.8.

Developed with the community's help over the last couple of months, the new implementation initially lived in TimescaleDB’s experimental namespace as time_bucket_ng. The functionality has since been improved through testing and feedback and is now generally available as part of the time_bucket hyperfunction. These capabilities simplify analytical requests with the need for time zone information. By providing a time zone parameter in the time_bucket hyperfunction, developers can now adjust the origin according to the given time zone. That means that those daily, monthly, or yearly boundaries are also modified automatically. When migrating existing code, all your current queries will work just as they did before the change.

Using monthly or yearly buckets and specifying a time zone is simple, as illustrated in the query below:

SELECT
   time_bucket('1 month', created, 'Europe/Berlin') AS bucket,
   avg(value)
FROM metrics
GROUP BY 1

📚 Read more about improvements to the time_bucket hyperfunction.

Create and manage multiple policies for continuous aggregates in one step

Continuous aggregates help developers query large amounts of time-series data more quickly, and are one of the hallmark features of TimescaleDB. Prior to TimescaleDB 2.8, you could only add one policy at a time, which could be tedious and complicated. Now with TimescaleDB 2.8’s one-step policy for continuous aggregates, you can add, remove, or modify multiple policies of continuous aggregates with a single command, including refresh, compression, and retention policies. Note that this feature is experimental, so we welcome your feedback on how to improve it for production use.

Watch the demo video or read our documentation to learn more about one-step policy management for continuous aggregates.

Performance improvements for distributed hypertables using the COPY protocol for high data volume queries

In TimescaleDB 2.8, we improved the performance of queries that select a high volume of data from distributed hypertables, such as SELECT *.... For this, we started to use the so-called COPY protocol—the subset of the PostgreSQL native protocol used to efficiently transfer data in bulk. We also made our code more efficient and reduced overhead by streamlining how we work with the internal data structures used to hold and transfer the row data.

Hypertables now support ON CONFLICT ON CONSTRAINT UPSERT statements

With the TimescaleDB 2.8 release, hypertables now support the ON CONFLICT ON CONSTRAINT clause, which fixes a long-standing compatibility issue with GraphQL/PostgreSQL middlewares like Hasura, Prisma, and Postgraphile.

Try TimescaleDB 2.8

If you are using Timescale, upgrades are automatic, and you’ll be upgraded to TimescaleDB 2.8 in your next maintenance window.

New to Timescale? Start a free 30-day trial, no credit card required, and get your new database journey started in five minutes.

If you’re self-hosting TimescaleDB, follow the upgrade instructions in our documentation.