Sharding and partitioning guidelines

[kneth]

Sharding and partitioning are two important topic to understand when working with CrateDB. It is documented but information is scattered which makes it harder for our customers to create optimal strategies.

Currently, we have information about it in

• https://www.elastic.co/docs/deploy-manage/production-guidance/optimize-performance/size-shards
• https://cratedb.com/blog/guide-to-sharding-and-partitioning-best-practices-in-cratedb
• https://community.cratedb.com/t/sharding-and-partitioning-guide-for-time-series-data/737

ChatGPT can summarize it to:

Optimal / Recommended Settings for CrateDB

1. Shard Size
   Target 3–70 GB per shard (sweet spot: 20–40 GB).
   Avoid:

   • Tiny shards (<1 GB): too much metadata overhead.
   • Huge shards (>100 GB): slow recovery and queries.

2. Number of Shards

• Per partition: Choose #shards so each shard lands in the 3–70 GB range.
• Example: If a monthly partition is ~210 GB → 6 shards ≈ 35 GB each.
• Per node: Keep <1000 shards total (including replicas).
• Cluster-wide: At least as many shards as CPU cores (to allow parallelism), but don’t multiply shards just to “match” cores.

3. Partitioning Strategy

• Use time-based partitions for time-series data:
• Month is often a good balance (date_trunc('month', ts)).
• Week or day only if data volume is very high.
• Keep partitions coarse enough that you don’t exceed 1000 partitions per table.
• Drop old partitions instead of deleting rows (much faster & cheaper).

4. Replication

• Replicas = 1 in production for fault tolerance.
• Replicas = 0 for dev/test or non-critical data.
• More replicas improve read scaling but increase disk and write cost.

5. Routing / Sharding Key

• Default: CrateDB uses the primary key hash for shard routing.
• You can specify CLUSTERED BY if you want custom routing (e.g. by customer ID).
• For time-series, keep routing simple — partitioning already distributes well.

6. Schema & Indexing

• Use generated columns for partitioning keys (date_trunc).
• Avoid over-indexing; each indexed column adds cost to writes and storage.

7. Operational Guidance

-Estimate data volume growth and adjust #shards for new partitions as needed (via ALTER TABLE … SET (number_of_shards = …)).

• Monitor shard sizes over time to detect growth hotspots.
• Balance shards across nodes to avoid hotspots; CrateDB does this automatically, but adding/removing nodes may require REBALANCE.

8. Monitoring & Maintenance

• Track shard counts per node (system tables or Admin UI).
• Keep an eye on:
  • Query latency for very large partitions.
  • Recovery/rebalance times if nodes restart.
• Run regular benchmarks with realistic data + queries before finalizing partition/shard settings.

9. Table creation guidelines

• Depending on the character of the table, different sharding and partitioning conditions and recommendations are applicable. For example, a small-sized relatively static table without much traffic may not want to be replicated that often, and also sharding and partitioning it is likely not applicable.

  This statement has been added by @amotl, picked up from recent discussions, but needs to be verified before converging it into the docs.

In short

• Shard size: 3–70 GB.
• Partitions: monthly (adjust if needed).
• Shards per node: <1000 (incl. replicas).
• Replication: 1 for prod.
• Routing: default unless strong reason otherwise.
• Plan ahead — you can only adjust shard count for future partitions.

[surister]

I think we should take all the information (mainly from the community post) and improve https://cratedb.com/docs/guide/performance/sharding.html

[amotl]

Yes, I also think we should possibly bundle those, or fragments thereof, and spice them up with missing information.

• https://cratedb.com/docs/guide/admin/sharding-partitioning.html
• https://cratedb.com/docs/guide/performance/sharding.html

Other than background and sizing information, we should also educate readers about caveats and DON'T DO ... items, to reduce chances for people to overload and kill their clusters easily.

[surister]

In a bit of a pickle here, we have two sections:

• Guides and tutorials / Administration / Sharding and partition
• Guides and tutorials / Performance / Sharding

I think we should have all the information that a user would need to effectively calculate, debug,
shard/parttition in ONE place, wdt @amotl @bmunkholm?

This also occurs with other pieces of documentation:

• Guides and tutorials / Administration / Clustering / scale
  Where we explain how to do different types of clustering/scaling

• Guides and tutorials / Performance / Scaling
  Where we explain some caveats or things to take into consideration when we do scaling

[bmunkholm]

Fine w me to rethink this.

[amotl]

I think we should have all the information that a user would need to effectively calculate, debug,
shard/parttition in ONE place.

Yes, I also think we should possibly bundle content within/across those where applicable. 💯

• https://github.com/crate/tech-content/issues/35
• https://github.com/crate/tech-content/issues/47
• https://github.com/crate/tech-content/issues/49
• https://github.com/crate/tech-content/issues/79
• In Depth Tutorial crate-admin#691
• Content Curation Backlog #48

The "Administration vs. Performance" sections in question did not receive much layout love throughout the years. We started the other day to improve the situation, but that work only yielded a single blueprint and preparations. Well, better than nothing.

• Scale: Refurbish section, and absorb relevant tutorials from community forum #150
• Layout: Curated index page for "Administration" and "Performance Guides" #265

[kneth]

Great comments from you all. I think we should have a condensed version as you mention. Do we also want to add some guidelines on how to use the xmover tool to help users with running clusters?

[amotl]

That's a good idea to weave in details about the xmover program where applicable. We planned to do it, and it's certainly an excellent fit.

[amotl]

This patch makes a start to improve this particular page coming from recent learnings. More refactorings and further improvements will be welcome.

• Sharding strategy and recommendations #355