Partition PolarDB Tables Evenly Using LINEAR HASH - PolarDB

Hash partitioning distributes rows across partitions by applying a modulo operation to an expression derived from the partition key. Use hash partitioning when you need to spread data evenly across a fixed number of partitions without range or list conditions.

Syntax

CREATE TABLE ... PARTITION BY [LINEAR] HASH(expr) [PARTITIONS number]
( PARTITION partition_name1,
  PARTITION partition_name2, ...);

Parameters

Parameter	Description
`expr`	The partition expression. Must return an INT value. String types are not supported.
`number`	The number of hash partitions.
`partition_name`	The partition name. Must be unique within the table.

How it works

To determine which partition a row belongs to, PolarDB evaluates the partition expression and applies the following formula:

MOD(partition_expression, number_of_partitions)

For example, with 7 partitions and a partition expression value of 15, the row is assigned to partition MOD(15, 7) = 1.

HASH vs LINEAR HASH

PolarDB supports two hash partitioning variants:

	HASH	LINEAR HASH
Algorithm	Modulo of the hash function value	Linear, quadratic, or exponential algorithm

The only syntactic difference is the LINEAR keyword in the PARTITION BY clause.

Examples

Create a hash partitioned table

CREATE TABLE sales_hash
(
  s_id        INT,
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT,
  PRIMARY KEY(s_id)
) PARTITION BY HASH (s_id)
PARTITIONS 7;

This creates a sales_hash table with 7 partitions. Each row is assigned to a partition based on MOD(s_id, 7).

Create a LINEAR HASH partitioned table

CREATE TABLE sales_linear_hash
(
  s_id        INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT,
  PRIMARY KEY(s_id)
) PARTITION BY LINEAR HASH (s_id)
PARTITIONS 7;

This creates a sales_linear_hash table with 7 partitions using a linear, quadratic, or exponential algorithm for faster partition maintenance.