CrateDB v5.2: Scrollable Cursors, Bitwise Operators & New Aggregations

CrateDB v5.2 is now ready to use!

Our team just released CrateDB v5.2! We’ve added support for (backward) scrollable cursors, bitwise operators and min_by/max_by aggregations and continued to improve our SQL and PostgreSQL compatibility as well as the Administration UI.

Scrollable cursors

As mentioned in our CrateDB 5.1 release blog post, we’ve introduced support for cursors with CrateDB 5.1. But as mentioned, the option to SCROLL cursors, especially to move backward, wasn't possible. With CrateDB 5.2 this missing functionality is added and CrateDB completes the full cursor support.

Let us recall: cursors are used to process rows of a result set individually, meaning only N rows at a time will be returned to the client. It allows simple iterations (loop) over each rows without the need to hold a complete result set in memory on the client side. Compared to OFFSET + LIMIT, which would also return N rows only, the query won't be re-executed if one moves on to the next N rows.
With SCROLL and backward movement implemented, cursors are now fully supported and such the integration with various tools, e.g., recent Tableau versions using this feature or Azure Data Studio, is improved.

If you’re interested more in that, don’t miss our dedicated blog post about Feature Focus: Working with cursors with SQL commands and also a follow-up blog post dedicated to the SCROLL and backward moving functionality, which will be online soon.

Bitwise operators

With CrateDB 5.2, AND, OR and XOR bitwise operations on integral numeric and BITSTRING data types are now possible.

They are very handy in various scenarios, for example:

• If you want to store multiple pieces of information in a single column. For example, you can use a bitwise OR operator to combine multiple flags into a single value.
• Simplifying conditional statements. Bitwise operators can be used to check the state of individual bits within a value. For example, you can use a bitwise AND operator to check if a particular bit is set or not.
• Easier data manipulation. Bitwise operators can be used to set or manipulate specific bits within a value. For example, you can use a bitwise OR operator to set a particular bit to 1, or a bitwise XOR to swap bit value (1 to 0 or 0 to 1).

We will follow up here with a more detailed feature blog post coming up soon.

MIN_BY/MAX_BYaggregations

CrateDB 5.2 adds two new aggregation functions: max_by and min_by are aggregation functions that allow users to quickly and easily search the value of one column based on the minimum or maximum value of another column, making them useful for analyzing trends, identifying outliers, or simply understanding the range of values within a dataset. An example use case is getting the latest measurement by using the time column and max_by(measurement, time) or the other way around: getting the time when some other column reached the maximum value by max_by(time, measurement). 

Compared to possible workarounds using sub-selects or JOINs, the native implementation improves the query performance by 2x-7x.

These nice new aggregations will also be covered in a dedicated feature blog post coming up soon.

Besides these more prominent features, CrateDB v5.2 includes many smaller improvements related to SQL and PostgreSQL compatibility and our administration UI, as well as some breaking changes.

Need more information? Check all the details in the release notes.