CockroachDB brings user-defined functions to distributed SQL
User-defined functions land in Cockroach Labs' new database update aiming to improve application development. The release also adds Intelligent Insights for performance monitoring.
Cockroach Labs updated its distributed SQL database platform with the general availability of CockroachDB 22.2.
The new release, introduced on Dec. 6, is the second update from the well-funded database vendor in 2022, following an update in May that added automation and scalability features, including a time-to-live data management capability.
With the update, the vendor added support for user-defined functions (UDFs), which enable developers to add functions for application development. CockroachDB 22.2 also integrated a new Intelligent Insights service that provides performance monitoring capabilities for the database.
Users want the ability to execute business logic closer to data, said Sanjeev Mohan, analyst at advisory firm SanjMo. That's a capability the CockroachDB update brings with UDFs and is a welcome addition for CockroachDB users, he said.
How Intelligent Insights helps users
Among the users of CockroachDB is online household management service Yohana. The company's services are built on a microservices architecture that interacts with CockroachDB.
"Our application collects PII [personally identifiable information] and large amounts of user data," said Doris Hung, tech lead manager at Yohana. "So we needed a database that was resilient and could scale quickly and easily store data close to customers for performance and compliance to ensure an ideal customer experience no matter what."
We have already started to reap some of the benefits of Intelligent Insights.
Doris HungTech lead manager, Yohana
Hung said she's particularly interested in the new Intelligent Insights capability, noting that it provides database monitoring with actionable fixes directly from the user interface.
"We have already started to reap some of the benefits of Intelligent Insights as we were able to test it in beta," she said. "The data quickly exposed several unused indexes within our workload that had high maintenance overhead."
Hung said that with the adjustments suggested by Intelligent Insights, Yohana was able to improve write performance by 40% for some of the company's most critical database tables.
What UDFs bring and what's coming
UDFs have been among the most highly requested features by users, said Peter Mattis, co-founder and CTO of Cockroach Labs.
UDFs are commonly available in relational database platforms, including PostgreSQL, but were not integrated into CockroachDB previously. Another feature users have been asking for that isn't yet in CockroachDB is support for stored procedures, which are often used alongside UDFs.
Meanwhile, CockroachDB 22.2 supports the same UDFs that run in the open source PostgreSQL database. UDFs take an input and then produce an output, such as enabling computation on values in a database and then outputting a result. Stored procedures enable more complex procedures, such as manipulating sets of tables.
UDFs enable calculations on data that is already in a database and for data coming in from data pipelines. The update's new change data capture (CDC) capability also introduces support for more complex data transformations. CDC transformations enable users to use SQL queries to filter data pipeline information.
Mattis said future updates of CockroachDB will add capabilities to help users of other database platforms migrate to the vendor's distributed SQL database.
"That includes some work within the database itself and then the stuff around the higher-order functions like stored procedures," he said.
