Hash partitioning for distribution in PostgreSQL

Introduction

Hash partitioning helps split a big table into smaller parts based on a hash function. This makes data easier to manage and faster to find.

When you have a large table and want to spread data evenly across partitions.

When you want to improve query speed by searching only relevant partitions.

When you want to balance data storage and workload across multiple disks or servers.

When your data does not have natural ranges but you want even distribution.

When you want to simplify maintenance by working on smaller chunks of data.

Syntax

PostgreSQL

CREATE TABLE table_name (
  column1 data_type,
  column2 data_type,
  ...
) PARTITION BY HASH (column_name);

CREATE TABLE partition_name PARTITION OF table_name
  FOR VALUES WITH (MODULUS n, REMAINDER r);

The main table is created with PARTITION BY HASH on a chosen column.

Each partition is created with FOR VALUES WITH (MODULUS n, REMAINDER r) where n is total partitions and r is the partition number starting from 0.

Examples

This creates a main table users partitioned by hashing the id column.

PostgreSQL

CREATE TABLE users (
  id INT,
  name TEXT
) PARTITION BY HASH (id);

This creates the first partition for users where hash(id) % 4 = 0.

PostgreSQL

CREATE TABLE users_part_0 PARTITION OF users
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);

This creates the second partition for users where hash(id) % 4 = 1.

PostgreSQL

CREATE TABLE users_part_1 PARTITION OF users
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);

Sample Program

This example creates an employees table partitioned by hashing emp_id into 3 parts. It inserts 5 employees and selects all rows ordered by emp_id.

PostgreSQL

CREATE TABLE employees (
  emp_id INT,
  emp_name TEXT
) PARTITION BY HASH (emp_id);

CREATE TABLE employees_part_0 PARTITION OF employees
  FOR VALUES WITH (MODULUS 3, REMAINDER 0);

CREATE TABLE employees_part_1 PARTITION OF employees
  FOR VALUES WITH (MODULUS 3, REMAINDER 1);

CREATE TABLE employees_part_2 PARTITION OF employees
  FOR VALUES WITH (MODULUS 3, REMAINDER 2);

INSERT INTO employees (emp_id, emp_name) VALUES
  (1, 'Alice'),
  (2, 'Bob'),
  (3, 'Charlie'),
  (4, 'Diana'),
  (5, 'Evan');

SELECT * FROM employees ORDER BY emp_id;

Important Notes

Hash partitioning evenly distributes rows but does not guarantee order.

Choose the number of partitions (modulus) based on your data size and hardware.

Queries filtering on the partition key can be faster because only relevant partitions are scanned.

Summary

Hash partitioning splits data into parts using a hash function on a column.

It helps balance data and improve query speed by limiting search to certain partitions.

Each partition is defined by modulus (total parts) and remainder (partition number).

Practice

1. What is the main purpose of hash partitioning in PostgreSQL?

easy

A. To split data into parts using a hash function on a column

B. To sort data alphabetically in each partition

C. To store data only on one server

D. To encrypt data for security

Solution

1. Step 1: Understand hash partitioning concept

   Hash partitioning divides data by applying a hash function to a column value, distributing rows into partitions.

2. Step 2: Compare options with concept

   Only To split data into parts using a hash function on a column correctly describes this purpose; others describe unrelated features.

3. Final Answer:

   To split data into parts using a hash function on a column -> Option A

4. Quick Check:

   Hash partitioning = split data by hash [OK]

Hint: Hash partitioning splits data by hashing a column [OK]

Common Mistakes:

• Confusing hash partitioning with sorting
• Thinking it stores data on one server only
• Mixing partitioning with encryption

2. Which of the following is the correct syntax to create a hash partitioned table in PostgreSQL?

easy

A. CREATE TABLE sales PARTITION BY COLUMN (region);

B. CREATE TABLE sales PARTITION BY RANGE (region);

C. CREATE TABLE sales PARTITION BY LIST (region);

D. CREATE TABLE sales PARTITION BY HASH (region);

Solution

1. Step 1: Identify partitioning syntax

   PostgreSQL uses PARTITION BY HASH(column) to create hash partitions.

2. Step 2: Match syntax with options

   Only CREATE TABLE sales PARTITION BY HASH (region); uses PARTITION BY HASH correctly; others use different partition types or invalid syntax.

3. Final Answer:

   CREATE TABLE sales PARTITION BY HASH (region); -> Option D

4. Quick Check:

   Hash partition syntax = PARTITION BY HASH [OK]

Hint: Use PARTITION BY HASH(column) for hash partitioning [OK]

Common Mistakes:

• Using RANGE or LIST instead of HASH
• Writing PARTITION BY COLUMN which is invalid
• Omitting parentheses around column

3. Given the table definition:

CREATE TABLE users (id INT, name TEXT) PARTITION BY HASH (id);
CREATE TABLE users_p0 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE users_p1 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE users_p2 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE users_p3 PARTITION OF users FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Which partition will the row with id = 7 be stored in?

medium

A. users_p3

B. users_p1

C. users_p2

D. users_p0

Solution

1. Step 1: Calculate hash partition remainder

   Partition is chosen by (hash(id) % modulus). Assuming hash(id) = id for simplicity, 7 % 4 = 3.

2. Step 2: Match remainder to partition

   Remainder 3 corresponds to partition users_p3.

3. Final Answer:

   users_p3 -> Option A

4. Quick Check:

   7 % 4 = 3 -> users_p3 [OK]

Hint: Compute id % modulus to find partition remainder [OK]

Common Mistakes:

• Confusing remainder with modulus
• Using id directly without modulo
• Mixing partition numbers

4. You try to create a hash partition with this command:

CREATE TABLE orders_p0 PARTITION OF orders FOR VALUES WITH (MODULUS 3, REMAINDER 3);

What is the error in this statement?

medium

A. MODULUS must be a prime number

B. REMAINDER cannot be equal to or greater than MODULUS

C. Partition name must start with 'orders_'

D. FOR VALUES WITH is not valid syntax for hash partitions

Solution

1. Step 1: Understand modulus and remainder rules

   Remainder must be less than modulus because remainder is result of modulo operation.

2. Step 2: Check given values

   MODULUS is 3, REMAINDER is 3, which is invalid since remainder must be 0, 1, or 2.

3. Final Answer:

   REMAINDER cannot be equal to or greater than MODULUS -> Option B

4. Quick Check:

   Remainder < Modulus rule violated [OK]

Hint: Remainder must be less than modulus in partitions [OK]

Common Mistakes:

• Setting remainder equal to modulus
• Thinking modulus must be prime
• Misunderstanding syntax for hash partitions

5. You want to distribute a large logs table by hashing the user_id column into 5 partitions. Which of the following is the correct way to define the partitions?

hard

A. Create 5 partitions with MODULUS 6 and REMAINDER values from 0 to 5

B. Create 5 partitions with MODULUS 4 and REMAINDER values from 0 to 4

C. Create 5 partitions with MODULUS 5 and REMAINDER values from 0 to 4

D. Create 5 partitions with MODULUS 5 and REMAINDER values from 1 to 5

Solution

1. Step 1: Understand modulus and remainder for partitions

   MODULUS is total partitions count; REMAINDER ranges from 0 to MODULUS-1.

2. Step 2: Apply to 5 partitions

   MODULUS must be 5; REMAINDER values must be 0,1,2,3,4 for 5 partitions.

3. Final Answer:

   Create 5 partitions with MODULUS 5 and REMAINDER values from 0 to 4 -> Option C

4. Quick Check:

   Partitions = MODULUS 5, REMAINDER 0-4 [OK]

Hint: Use MODULUS = partitions count; REMAINDER from 0 to MODULUS-1 [OK]

Common Mistakes:

• Setting remainder range incorrectly
• Using wrong modulus number
• Starting remainder at 1 instead of 0