Setting Non-default Partition Count for Databases

If you increase the number of vCPUs without sufficient partitions, you can end up with too many cores per partition (e.g., more than 16 cores on one partition), which under-utilizes parallelism if your workload has low concurrency.
Conversely, if you create too many partitions, each partition becomes small, which can hurt Queries Per Second (QPS) for columnstore/vector/full-text search (FTS) workloads and increase per-query overhead.
If you have configured partition counts too low then they cannot be scaled further. For example, if you began with the S0 default partition count of 4 this may later become a constrained even if you move to larger configurations, such as S8 with 4 leaves. This illustrates a real limitation: insufficient partitioning can prevent effective horizontal scaling.

SingleStore Helios automatically handles rebalancing in the background by redistributing partitions across the cluster when scaling, but the original size when creating the database dictates the total number of partitions.

Partition changes are a deliberate operation. There is no automatic re-partitioning when you resize or scale a cluster. If your workload size changes dramatically, you may also need to plan a migration which should involve your account team and should be done only with a proper plan in place.

Trade-offs in Scaling and Performance

Using fewer partitions than the default can give performance benefits in terms of:

Lower per-query overhead for distributed reads:
- Fewer partitions means fewer threads and messages per query, which cuts coordination cost and can improve latency and throughput for analytics, full-text, and vector queries.
Larger columnstore and vectors:
- Data is concentrated into fewer partitions, leading to larger segments and fewer index structures; since vector search cost grows sub-linearly with segment size, this improves QPS.

However fewer partitions impact ingest and future scaling costs:

Slower ingest and write parallelism:
- Fewer partitions mean fewer flush and index threads; bulk loads and very write-heavy workloads will generally ingest more slowly than with more partitions, once other ingest tuning methods are exhausted.
Limits how far you can scale-out compute before repartitioning:
- A partition cannot span leaf nodes (“leaves”), and to exploit N leaves you need at least N partitions and a reasonable cores-per-partition ratio (typically a small number of cores per partition).
- Limiting the number of partitions provides more cores per partition; if you later add a large number of cores or leaves, those extra cores will be under-utilized until you perform a deliberate repartition (either via a new database + INSERT…SELECT or a backup-with-split-partitions and a restore).
Harder to change later:
- Increasing partition count is an explicit, operationally heavy step (create new database and copy data or use backup with split partitions and then merge/OPTIMIZE). A it is not a cheap inline “toggle,” picking “too few” partitions up front can force a migration project later.

Using more partitions than the default is not recommended because of higher per-query overhead as well as more background and metadata overhead.

In summary:

More partitions within reasonable limits can increase ingest throughput and parallelism when a workload is under-partitioned, but they also increase per-query overhead and can reduce QPS for vector, full-text, and analytic workloads.
Fewer partitions can improve per-query efficiency for many analytic, vector, and full-text workloads, but they reduce ingest parallelism and limit the ability to take advantage of very large numbers of cores.

SingleStore Helios-Specific Recommendations

Use the default unless you have a clear, measured reason to override it. SingleStore Helios sets default_partitions_per_leaf based on current guidance and may adjust it over time, so specifying a different PARTITIONS value opts you out of the default tunings.
Consider a non-default partition count in the following scenarios:
1. High-QPS analytic/vector/full-text workloads where the goal is to maximize QPS, not ingest.
  
  For large analytic, vector, and full-text workloads, using fewer physical partitions with larger segments is generally preferable; flexible parallelism (sub-partitions) can then provide parallel execution.
  
  It can be useful to set PARTITIONS lower than the default for these workloads. (Vector/FTS explicitly recommend “a smaller-than-default number of partitions in your CREATE DATABASE statement”. )
2. Under-utilized CPU on a scaled-up cluster where metrics show low CPU use, but QPS/ingest are still constrained and the database has very few partitions (for example, effectively ~1 partition per leaf).
  
  In such cases you can create a new database with more partitions, enough to obtain a healthy “cores-per-partition” ratio, then migrate data.
3. Ingest-bound workloads, where other settings and subsequent tuning are not improving performance.
  
  If a workload remains ingest-bound after tuning batching, thread pools, and OPTIMIZE ... FLUSH cadence, and CPU remains underutilized, modestly increase the partition count and verify that:
  - Segment sizes remain reasonably large.
  - QPS for analytic queries does not regress.

Changing the Partition Count

The following methods are available for changing partition count:

Create a new database with the desired count and copy data. For example:
```
CREATE DATABASE db2 PARTITIONS <n>;
```
Then copy using INSERT ... SELECT in batches (recommended for large tables).

This is the most controllable option and avoids columnstore fragmentation if you size correctly.
Use BACKUP WITH SPLIT PARTITIONS (doubling only). For example:
```
BACKUP DATABASE db WITH SPLIT PARTITIONS BY 2;
```
This creates a backup while doubling the database's partition count.

Then after RESTORE, run OPTIMIZE TABLE ... FULL on all large columnstore tables to re-compact segments.
Use SELECT…INTO… S3/Kafka… and then pipeline the data into a new database.

Once a database exists, its physical partition count remains what it was set to until you take one of these above explicit actions.