Why cratedb creating more than 1000 shards on partitioned table?

CrateDB
1

We are using 4.0.7 cratedb version

In my scenario, we are using days (current day midnight epoch) as a partition for table and we have configured 8 shards and table retention is 15-45 days for records.

So due to partition and retention, It will create around 1400-2500 shards for single table

Can I choose days column for partition?

Does this multiple shards create bad impact on read/write performance?

I have read https://crate.io/docs/crate/howtos/en/latest/performance/sharding.html
We have 2 core cpu, 8 gb ram around 3-4 nodes.
As per guide, shard count should be equal to cpu count.

Is it over allocation for table?
What should I need to do to avoid this over allocation?
What is the difference between Configured shards vs Started shards?

Regarding the more partition, How can I delete partitions from the table?

We have just find the workaround using close partition by below query.

Is there any performance impact if we close partition using below query?
ALTER TABLE my_table PARTITION (days=epoch) CLOSE;

2

It will create around 1400-2500 shards for single table

That is indeed a very high count of shards and unless it is a very big cluster, probably should be drastically reduced.

Can I choose days column for partition?

Yes, depending on the use case days can be an appropriate partition key

Does this multiple shards create bad impact on read/write performance?

In general not. Sharding is in place to improve read/write performance. With a bad sharding strategy, it might suffer however.

As per guide, shard count should be equal to cpu count.

This is a general rule of thumb, though this mostly depends on how you want to write/read data from the db. For optimal performance the shard count, that you want to typically access within a query should be around 1-2x the cpu core count.

Regarding the more partition, How can I delete partitions from the table?
If you delete data from a partitioned table, partitions and corresponding shards normally should get deleted as well.