Partitioning policy

• 2/2/2021
• 11 minutes to read
• • o
  • y
  • y
  • Y
  • a
  • +2

The partitioning policy defines if and how extents (data shards) should be partitioned for a specific table or a materialized view.

By default, extents are partitioned by their ingestion time. In most cases, there's no need to apply another partitioning policy.

The main purpose of the partitioning policy is to improve performance of queries in specific scenarios.

Common scenarios

The following are common scenarios that can be addressed by setting a data partitioning policy:

• Low cardinality partition key: For example, multi-tenant solutions, or a metrics table where most or all queries filter on the partition key column of type string such as the TenantId or the MetricId.
  • Low cardinality is defined as less than 10M distinct values. In the examples above, the cardinality is likely to be much lower than that.
  • Set the hash partition key to be the ID column, and set the PartitionAssigmentMode property to uniform.
• High cardinality partition key: For example, IoT information from many different sensors, or academic records of many different students.
  • High cardinality is defined as more than 10M distinct values where the distribution of values in the column is approximately even.
  • In this case, set the hash partition key to be the column grouped-by or joined-on, and set the PartitionAssigmentMode property to default.
• Unordered Data ingestion: Data ingested into a table might not be ordered and partitioned into extents (shards) according to a specific datetime column that represents the data creation time and is commonly used to filter data. This could be due to a backfill from heterogeneous source files that include datetime values over a large time span.
  • In this case, set the Uniform range datetime partition key to be the datetime column.
  • If you need retention and caching policies to align with the datetime values in the column, instead of aligning with the time of ingestion, set the OverrideCreationTime property to true.

Partition keys

The following kinds of partition keys are supported.

Hash partition key

• A hash-modulo function is used to partition the data.
• Data in homogeneous (partitioned) extents is ordered by the hash partition key.
  • You don't need to include the hash partition key in the row order policy, if one is defined on the table.
• Queries that use the shuffle strategy, and in which the shuffle key used in join, summarize or make-series is the table's hash partition key, are expected to perform better because the amount of data required to move across cluster nodes is reduced.

Partition properties

Hash partition key example

A hash partition key over a string-typed column named tenant_id. It uses the XxHash64 hash function, with a MaxPartitionCount of 128, and the default Seed of 1.

{
  "ColumnName": "tenant_id",
  "Kind": "Hash",
  "Properties": {
    "Function": "XxHash64",
    "MaxPartitionCount": 128,
    "Seed": 1,
    "PartitionAssignmentMode": "Default"
  }
}

Uniform range datetime partition key

In these cases, you can reshuffle the data between extents so that each extent includes records from a limited time range. This process results in filters on the datetime column being more effective at query time.

The partition function used is bin_at() and isn't customizable.

Partition properties

Uniform range datetime partition example

The snippet shows a uniform datetime range partition key over a datetime typed column named timestamp. It uses datetime(1970-01-01) as its reference point, with a size of 1d for each partition, and does not override the extents' creation times.

{
  "ColumnName": "timestamp",
  "Kind": "UniformRange",
  "Properties": {
    "Reference": "1970-01-01T00:00:00",
    "RangeSize": "1.00:00:00",
    "OverrideCreationTime": false
  }
}

The policy object

By default, a table's data partitioning policy is null, in which case data in the table won't be partitioned.

The data partitioning policy has the following main properties:

• PartitionKeys:

  • A collection of partition keys that define how to partition the data in the table.
  • A table can have up to 2 partition keys, with one of the following options:
    • One hash partition key.
    • One uniform range datetime partition key.
    • One hash partition key and one uniform range datetime partition key.
  • Each partition key has the following properties:
    • ColumnName: string - The name of the column according to which the data will be partitioned.
    • Kind: string - The data partitioning kind to apply (Hash or UniformRange).
    • Properties: property bag - Defines parameters according to which partitioning is done.

• EffectiveDateTime:

  • The UTC datetime from which the policy is effective.
  • This property is optional. If it isn't specified, the policy will take effect for data ingested after the policy was applied.
  • Any non-homogeneous (non-partitioned) extents that may be dropped because of retention are ignored by the partitioning process. The extents are ignored because their creation time precedes 90% of the table's effective soft-delete period.

Data partitioning example

Data partitioning policy object with two partition keys.

1. A hash partition key over a string-typed column named tenant_id.
   • It uses the XxHash64 hash function, with a MaxPartitionCount of 128, and the default Seed of 1.
2. A uniform datetime range partition key over a datetime type column named timestamp.
   • It uses datetime(1970-01-01) as its reference point, with a size of 1d for each partition.

{
  "PartitionKeys": [
    {
      "ColumnName": "tenant_id",
      "Kind": "Hash",
      "Properties": {
        "Function": "XxHash64",
        "MaxPartitionCount": 128,
        "Seed": 1,
        "PartitionAssignmentMode": "Default"
      }
    },
    {
      "ColumnName": "timestamp",
      "Kind": "UniformRange",
      "Properties": {
        "Reference": "1970-01-01T00:00:00",
        "RangeSize": "1.00:00:00",
        "OverrideCreationTime": false
      }
    }
  ]
}

Additional properties

The following properties can be defined as part of the policy. These properties are optional and we recommend not changing them.

The data partitioning process

• Data partitioning runs as a post-ingestion background process in the cluster.
  • A table that is continuously ingested into is expected to always have a "tail" of data that is yet to be partitioned (non-homogeneous extents).
• Data partitioning runs only on hot extents, regardless of the value of the EffectiveDateTime property in the policy.
  • If partitioning cold extents is required, you need to temporarily adjust the caching policy.

Monitor partitioning

Use the .show diagnostics command to monitor the progress or state of partitioning in a cluster.

.show diagnostics
| project MinPartitioningPercentageInSingleTable, TableWithMinPartitioningPercentage

The output includes:

• MinPartitioningPercentageInSingleTable: The minimal percentage of partitioned data across all tables that have a data partitioning policy in the cluster.
  • If this percentage remains constantly under 90%, then evaluate the cluster's partitioning capacity.
• TableWithMinPartitioningPercentage: The fully qualified name of the table whose partitioning percentage is shown above.

Use .show commands to monitor the partitioning commands and their resource use. For example:

.show commands 
| where StartedOn > ago(1d)
| where CommandType == "ExtentsPartition"
| parse Text with ".partition async table " TableName " extents" *
| summarize count(), sum(TotalCpu), avg(tolong(ResourcesUtilization.MemoryPeak)) by TableName, bin(StartedOn, 15m)
| render timechart with(ysplit = panels)

Partitioning capacity

• The data partitioning process results in the creation of more extents. The cluster may gradually increase its extents merge capacity, so that the process of merging extents can keep up.
• If there's a high ingestion throughput, or a large enough number of tables that have a partitioning policy defined, then the cluster may gradually increase its Extents partition capacity, so that the process of partitioning extents can keep up.
• To avoid consuming too many resources, these dynamic increases are capped. You may be required to gradually and linearly increase them beyond the cap, if they're used up entirely.
  • If increasing the capacities causes a significant increase in the use of the cluster's resources, you can scale the cluster up/out, either manually, or by enabling autoscale.

Limitations

• Attempts to partition data in a database that already has more than 5,000,000 extents will be throttled.
  • In such cases, we recommend that you temporarily disable partitioning and re-evaluate your configuration and policies. For example, you can set the EffectiveDateTime to a future date until the extent count stabilizes on a lower value.

Outliers in partitioned columns

• The following situations can contribute to imbalanced distribution of data across the cluster's nodes, and degrade query performance:
  • If a hash partition key includes values that are much more prevalent than others, for example, an empty string, or a generic value (such as null or N/A).
  • The values represent an entity (such as tenant_id) that is more prevalent in the data set.
• If a uniform range datetime partition key has a large enough percentage of values that are "far" from the majority of the values in the column, the overhead of the data partitioning process is increased and may lead to many small extents that the cluster will need to keep track of. An example of such a situation is datetime values from the distant past or future.

In both of these cases, either "fix" the data, or filter out any irrelevant records in the data before or at ingestion time, to reduce the overhead of the data partitioning on the cluster. For example, use an update policy.

Next steps

Use the partitioning policy control commands to manage data partitioning policies for tables.