I would like to know what is manual Query for autoscale down for a 3 node cluster of cratedb

I would like to know Query command to do autoscale down for a 3 node cluster of cratedb.
Also, please suggest the recommended steps to scale down the crate pods

Are you looking to scale to a smaller number of pods or to pods with smaller specs? or maybe your question is about finding out when the cluster may be overprovisioned?
In general, in a k8s deployment you would adjust the number of pods with kubectl scale.
Scaling down you want to make sure that the cluster maintains a quorum and if you have tables without replicas you also want to make sure all shards will be available as you scale down.
Maybe you want to share a bit more about the context and what you are trying to achieve?

Basically, Hernan,
We would like to apply scale up and scale down policy, we were earlier experienced shards unavailability issue during the time of autoscale down from 6 to 3 , the 4th and 5th no. of shards went unavailable, when scale down has happened through kubernetes scaling policy.

So, we want to know is there any way from cratedb perspective where we can do the autoscale down of pods so that shards unavailability issue will not come again.
Please help to reply at the earliest, as suggestions needed on urgent basis.

This is handled by CrateDB Cloud or e.g. using the Kubernetes Operator, which checks for shard availability before removing nodes. Outside of Kubernetes you could use the DECOMISSION statement which allows the safe removal of nodes.

can you suggest the Query please


Suppose, in case cratedb pods count 6 and in order to scale down to 3 manually what command we can run so that shard can able to manage iinternally and there will be no shard issue.

ok, @proddata , let me try this command to scale down effectivily with no shard issue

I will test it and will get back to you in this.

In a typical k8s setup, this will not work, as it at the end stops the process, which would restart the pod and the node would join again.

Also as noted in the documentation one needs to set the graceful stop settings to appropriate values:

Generally speaking such production relevant scenarios are handled via our support channels and not in the Community. I.e. if you need a comprehensive guide how to set this up the correct way for a production setup, please contact the CrateDB Support or sales team.

best regards

I was going through an article
Scaling CrateDB clusters up and down to cope with peaks in demand - Tutorials - CrateDB Community

in this below notes,

When we are ready to decommission the temporary nodes, we need to move the data collected during the days of the event.

In CrateDB 5.1.2 or higher we can achieve this with:

ALTER TABLE test SET ("routing.allocation.exclude.storage" = 'temporarynodes');
ALTER TABLE test RESET ("routing.allocation.total_shards_per_node");

The data movement takes place one replica at a time and there is no impact on querying of the event’s data while it is being moved, new data also continues to flow to the baseline nodes and its ingestion and querying are also not impacted.

We can monitor the progress of the data relocation querying sys.shards and sys.allocations.

Once all shards have moved away from the temporary nodes, we can decommission them gracefully:


Once this is done, the machines can safely be shutdown.

Now, do i have to run set and reset command before node decomm,
ALTER TABLE test SET (“routing.allocation.exclude.storage” = ‘temporarynodes’);
ALTER TABLE test RESET (“routing.allocation.total_shards_per_node”);

I did not understand its complete meaning.

Regarding those SET and RESET commands, earlier in the article the partition level total_shards_per_node is configured so that the new data from the time of the special event is balanced across all nodes and does not just go to the new nodes, in that example this is done so that the CPU power of all nodes can be used for ingestion and queries.
So if you have been following the rest of the article you will need both commands, the SET command is telling CrateDB to not allocate shards for new partitions to the nodes that will be decommissioned, and the RESET one is removing the limit on shards per node so that the nodes that are staying can allocate enough shards for the new partitions.
Then, as mentioned by @proddata , in a k8s deployment you will not be able to use ALTER CLUSTER DECOMMISSION but what you could do instead is use routing allocation rules to get all shards relocated, then you can scale down with kubectl scale.
Would you like to book a call with us?

Sure, if needed I will book call.
But can you suggest in mutiple schemas we have multiple tables with shards alloacted.
Do we have to run ALTER command by providing each table of schema?

I am able to use ALTER commands above and then kubectl scale down command to scale down pod which is successful,
My query is we have multiple tables in a schema, do we have any iterative Query to handle all tables in 1 ALTER SET command
@Hernan @prodata
Kindly suggest.


do we have any iterative Query to handle all tables in 1 ALTER SET command

The idea in that tutorial is to do this for specific tables/partitions, but if you need to do this in bulk for many tables you could have a query against INFORMATION_SCHEMA.TABLES (or other system views as needed) , using the CONCAT function to build the commands that would need to be executed and then you could pass the commands to a tool that can run batches of statements like the CrateDB Shell CLI.