Hash partitioning for distribution in PostgreSQL - Step-by-Step Execution

Concept Flow - Hash partitioning for distribution

Start: Insert row

↓

Compute hash on partition key

↓

Modulo hash by number of partitions

↓

Determine target partition

↓

Insert row into target partition

↓

End

When inserting data, the system computes a hash of the partition key, uses modulo to pick a partition, then inserts the row there.

Execution Sample

PostgreSQL

CREATE TABLE sales (
  id INT,
  region TEXT
) PARTITION BY HASH (region);

CREATE TABLE sales_p0 PARTITION OF sales FOR VALUES WITH (MODULUS 4, REMAINDER 0);

Defines a table partitioned by hashing the 'region' column into 4 partitions, creating one partition for remainder 0.

Execution Table

Step	Input Row (region)	Hash(region)	Modulo (hash % 4)	Target Partition	Action
1	'north'	hash('north') = 123456	123456 % 4 = 0	sales_p0	Insert into sales_p0
2	'east'	hash('east') = 234567	234567 % 4 = 3	sales_p3	Insert into sales_p3
3	'south'	hash('south') = 345678	345678 % 4 = 2	sales_p2	Insert into sales_p2
4	'west'	hash('west') = 456789	456789 % 4 = 1	sales_p1	Insert into sales_p1
5	No more rows	-	-	-	Stop insertion

💡 All rows assigned to partitions based on hash modulo; insertion ends when no more rows.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	Final
region	null	'north'	'east'	'south'	'west'	null
hash(region)	null	123456	234567	345678	456789	null
modulo	null	0	3	2	1	null
target_partition	null	sales_p0	sales_p3	sales_p2	sales_p1	null

Key Moments - 3 Insights

Why does the same region always go to the same partition?

Because the hash function always produces the same hash for the same input, so modulo result and partition stay consistent (see execution_table rows 1-4).

What happens if the number of partitions changes?

The modulo divisor changes, so the partition assignment can change for existing data, requiring data redistribution.

Why do we use modulo after hashing?

Modulo limits the hash value to the number of partitions, ensuring the row maps to a valid partition (see execution_table column 'Modulo').

Visual Quiz - 3 Questions

Look at the execution table, which partition does the region 'south' get assigned to?

A sales_p2

B sales_p1

C sales_p0

D sales_p3

💡 Hint

Check row 3 in the execution_table under 'Target Partition'.

At which step does the modulo operation result in 1?

A Step 3

B Step 2

C Step 4

D Step 1

💡 Hint

Look at the 'Modulo' column in the execution_table for value 1.

If the number of partitions changed from 4 to 5, what would change in the execution_table?

A Hash values would change

B Modulo divisor would change to 5

C Target partitions would remain the same

D Rows would not be inserted

💡 Hint

Modulo is hash % number_of_partitions, so changing partitions changes modulo divisor.

Concept Snapshot

Hash partitioning distributes rows by:
- Computing hash of partition key
- Using modulo with number of partitions
- Assigning row to partition by modulo result
This ensures even data spread and consistent partitioning.

Full Transcript

Hash partitioning in PostgreSQL works by computing a hash value of the partition key for each row. Then, the system calculates the modulo of this hash by the number of partitions to find which partition the row belongs to. For example, if there are 4 partitions, the modulo is hash % 4, resulting in a number from 0 to 3. Each number corresponds to a partition. Rows with the same key always go to the same partition because the hash is consistent. This method helps distribute data evenly across partitions. If the number of partitions changes, the modulo divisor changes, which can cause rows to move to different partitions. The execution table shows step-by-step how rows with different region values are assigned to partitions based on their hash and modulo results.