Timescale Hyperfunctions Ease PostgreSQL Query Complexities – InApps Technology 2022

PostgreSQL database startup Timescale has introduced some new SQL functions to make PostgreSQL developers more productive.

Timescale introduced TimescaleDB hyperfunctions, which are pre-built functions for the most common — and often most difficult — queries that developers write today, said Joshua Lockerman, a software engineer at Timescale who co-developed the new hyperfunctions.

“Hyperfunctions are particularly great for helping developers measure what matters in their time-series data, which is relentless and generates massive, ever-growing streams of information,” Lockerman said in a blog post. “Hyperfunctions are available for TimescaleDB, as well as PostgreSQL.”

TimescaleDB is a time-series SQL database providing fast analytics and scalability, with automated data management on a proven storage engine, the company contends.

“Hyperfunctions enable developers to more easily manipulate and analyze time-series data right in their TimescaleDB and PostgreSQL database. Just as we made the decision to build TimescaleDB on top of PostgreSQL, we built hyperfunctions using existing SQL syntax with new functions so that developers can continue to use all the tools and frameworks they already know and be productive immediately,” said Ajay Kulkarni, CEO and co-founder of Timescale. “Hyperfunctions lower the barrier for building on time-series data so that more developers can join the existing developer community running nearly three million active TimescaleDB databases, powering mission-critical applications across industries worldwide.”

Meanwhile, Lockerman noted that SQL is a powerful language and Timescale’s strategy is that adding a specialized set of functions for time-series analysis can make it even better, as developers can call a hyperfunction using the same SQL syntax that they are familiar with.

Lockerman also said the popularity of SQL is one of the key reasons the company built TimescaleDB on top of PostgreSQL. SQL is the third most commonly used programming language in the world, according to a Stack Overflow survey.

Following a canvas of its customer base to learn how users analyzed and manipulated time-series data, Timescale noticed several common queries begin to take shape. But these queries were difficult to compose in standard SQL, said Prashant Sridharan, vice president of marketing at Timescale.

So, in addressing the more common, but difficult queries, “We made the decision to take the hard path ourselves so that we could give developers an easier path,” he said.

Andrew Brust, CEO and founder of Blue Badge Insights, a New York-based IT consulting firm and a Microsoft regional director and most valuable professional, said he believes the TimescaleDB hyperfunctions to be “useful” but not overly groundbreaking.

“This announcement seems to be around new library functions, callable from SQL, that make it easier to do time-series calculations in SQL queries and scripts,” he said. “Essentially, they’ve created some high-level functions that have the effect of extending Postgres’ SQL dialect to handle time series calculations, so that said calculations don’t have to be done with low-level SQL aggregate functions. Makes for less code and less reinventing of wheels… plus makes it possible to do these calculations for people who wouldn’t have known how to implement them from scratch.”

Moreover, Timescale made its APIs appealing to developers without breaking SQL compatibility, Lockerman said.

“This means we can create custom functions, aggregates, and procedures but no new syntax — and all the drivers and interfaces can still work,” he said in the post. “You get the peace of mind that your query will run in every visualization tool, database admin tool, or data analysis tool that speaks SQL.”

That is why Timescale chose to create new SQL functions for time-series analysis and manipulation rather than to try to improve the developer experience by introducing new SQL syntax.

“While introducing new syntax with new keywords and new constructs may have been easier from an implementation perspective, we made the deliberate decision not to do so since we believe that it actually leads to a worse experience for the end user,” Lockerman said. “New SQL syntax means that existing drivers, libraries, and tools may no longer work. This can leave developers with more problems than solutions as their favorite tools, libraries, or drivers may not support the new syntax, or may require time-consuming modifications to do so.”

Timescale developed the new hyperfunctions using the Rust programming language, because of “its superior productivity, community, and the pgx software development kit. We felt Rust was a more friendly language for a project like ours and would encourage more community contributions,” Lockerman wrote. “The inherent safety of Rust means we could focus more time on feature development rather than worrying about how the code is written. The extensive Rust community (crates.io), along with excellent package-management tools, means we can use off-the-shelf solutions for common problems, leaving us more time to focus on the uncommon ones.”