Hash partitioning for distribution in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is hash partitioning in a database?

Hash partitioning is a method of dividing a table into smaller parts (partitions) based on a hash function applied to a column's values. This helps distribute data evenly across partitions.

beginner

How does hash partitioning help with data distribution?

Hash partitioning uses a hash function to assign rows to partitions, ensuring data is spread evenly. This improves query performance and balances storage and workload.

intermediate

In PostgreSQL, how do you create a hash partitioned table?

You create a partitioned table with PARTITION BY HASH(column_name), then create partitions with FOR VALUES WITH (MODULUS n, REMAINDER r) to specify which rows go where.

intermediate

What is the role of MODULUS and REMAINDER in PostgreSQL hash partitions?

MODULUS defines the total number of partitions, and REMAINDER specifies which partition a row belongs to based on the hash value modulo MODULUS.

intermediate

Why might hash partitioning be preferred over range partitioning?

Hash partitioning evenly distributes data regardless of value ranges, which is useful when data is uniformly accessed or when range boundaries are hard to define.

What does hash partitioning use to assign rows to partitions?

A A hash function on a column's value

B A range of values

C The row insertion time

D The primary key order

Hash partitioning uses a hash function applied to a column's value to decide which partition a row belongs to.

In PostgreSQL, which clause specifies hash partitioning when creating a table?

A PARTITION BY COLUMN(column_name)

B PARTITION BY RANGE(column_name)

C PARTITION BY HASH(column_name)

D PARTITION BY LIST(column_name)

The clause PARTITION BY HASH(column_name) tells PostgreSQL to use hash partitioning on that column.

What does the MODULUS value represent in PostgreSQL hash partitions?

A The hash function used

B The partition number for a row

C The maximum size of a partition

D The total number of partitions

MODULUS defines how many partitions the table is divided into.

Which of these is a benefit of hash partitioning?

A Sorting data alphabetically

B Even data distribution across partitions

C Grouping data by date ranges

D Storing data in a single partition

Hash partitioning helps spread data evenly across partitions.

If you want to partition a table into 4 parts using hash partitioning, what should MODULUS be set to?

A 4

B 2

C 1

D 8

MODULUS should be set to the number of partitions, so 4 for four partitions.

Explain how hash partitioning works and why it is useful for distributing data.

Think about how a hash function assigns rows to different partitions.

Describe the steps to create a hash partitioned table in PostgreSQL and how partitions are defined.

Focus on the syntax and meaning of MODULUS and REMAINDER.

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