CrateDB v5.6, the latest version of CrateDB, has been released with new features and improvements!
What is it about?
Roles and role/privilege inheritance
Until now, CrateDB supported the creation of users and assigning different privileges to them. However, in larger environments, managing privileges for each user may become cumbersome and hard to maintain. Assigning privileges to groups of users and even supporting the inheritance of groups (and such privileges) can greatly ease user and privilege management. CrateDB now fully supports RBAC, in detail:
- Support to create roles (e.g. to be used as a group).
CREATE ROLE admin_group; - Support to assign privileges to roles not only users.
GRANT ALL PRIVILEGES TO admin_group; - Support to assign a role to a role/user (inheritance.
GRANT admin_group TO my_user;
For more details, please read the related user and roles management and privileges documentation sections.
Rename columns
Following up on the support to drop columns introduced by CrateDB 5.5, this release adds support to rename columns.
This can be handy when a column is created with a wrong name (e.g., a typo) either by human declaration or by imported dataset due to the dynamic schema feature of CrateDB.
Renaming a column will not touch any values stored.
ALTER TABLE fleet_data RENAME COLUMN car['buttary'] TO car['battery'];
Rename tables during restore
Sometimes, it might be necessary to restore a table while a version of the table still exists on the same cluster.
This can now be achieved by using various options on the RESTORE statement to rename tables, schema of tables, or even both by defining regular expression patterns.
Nested JOIN performance improvements
We added more optimizer rules pushing down filters and limit definition also below nested LEFT/RIGHT OUTER JOIN operation. This can improve performance a lot, as other rules will pick them up and push such filters or limits to the most inner plan possible, best to the operation collecting the data.
Let's look at the following example query to demonstrate the filter push-down:
SELECT * FROM (SELECT * FROM a LEFT JOIN b ON a.id = b.id LEFT JOIN c ON b.other_id = c.id) t WHERE a.id > 1;
Before the change, the resulting plan looks like:
+--------------------------------------------------------------------+ | QUERY PLAN | +--------------------------------------------------------------------+ | Rename[id] AS t (rows=0) | | └ Eval[id] (rows=0) | | └ Filter[(id > 1)] (rows=0) | Collect phase for a | └ NestedLoopJoin[LEFT | (other_id = id)] (rows=unknown) | | ├ NestedLoopJoin[LEFT | (id = id)] (rows=unknown) | | │ ├ Collect[doc.a | [id] | true] (rows=unknown) | | │ └ Collect[doc.b | [id, other_id] | true] (rows=unknown) | | └ Collect[doc.c | [id] | true] (rows=unknown) | +--------------------------------------------------------------------+
The filter a.id > 1 is applied on the top of all nested joins while it could be applied on the
With the change, the filter is now pushed down to the Collect operation for the table a which not only filter out unwanted rows directly before joining any other, but can also utilize an index on the column.
+------------------------------------------------------------------+ | QUERY PLAN | +------------------------------------------------------------------+ | Rename[id] AS t (rows=unknown) | | └ Eval[id] (rows=unknown) | | └ NestedLoopJoin[LEFT | (other_id = id)] (rows=unknown) | | ├ NestedLoopJoin[LEFT | (id = id)] (rows=unknown) | | │ ├ Collect[doc.a | [id] | (id > 1)] (rows=unknown) | | │ └ Collect[doc.b | [id, other_id] | true] (rows=unknown) | | └ Collect[doc.c | [id] | true] (rows=unknown) | +------------------------------------------------------------------+
The filter is now on the Collect phase
Other than that, CrateDB 5.6 will bring a lot of other smaller but interesting SQL features and improvements.
Check out our full release notes for more details, especially the list of breaking changes.