Hash partitioning for distribution in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a hash partitioned table on the column 'user_id'.

PostgreSQL

CREATE TABLE users (
  user_id INT,
  name TEXT
) PARTITION BY [1] (user_id);

Drag options to blanks, or click blank then click option'

Alist

Bhash

Crange

Dcolumn

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'range' or 'list' instead of 'hash' for hash partitioning.

Using 'column' which is not a valid partition method.

The hash partitioning method distributes rows based on a hash of the partition key, here 'user_id'.

2fill in blank

medium

Complete the code to create a hash partition with 4 partitions.

PostgreSQL

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

Drag options to blanks, or click blank then click option'

A0

B1

C2

D3

Attempts:

3 left

💡 Hint

Common Mistakes

Starting remainder at 1 instead of 0.

Using a remainder outside the range 0 to modulus-1.

Partitions are created with a remainder value from 0 to modulus-1. Here, remainder 0 is the first partition.

3fill in blank

hard

Fix the error in the partition creation statement to correctly specify the remainder.

PostgreSQL

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

Drag options to blanks, or click blank then click option'

A3

B1

C2

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Using remainder equal to modulus causes an error.

Not understanding remainder range limits.

The remainder must be less than the modulus. Since modulus is 4, remainder 3 is the highest valid value.

4fill in blank

hard

Fill both blanks to create two hash partitions with modulus 3 and remainders 0 and 1.

PostgreSQL

CREATE TABLE users_part_[1] PARTITION OF users
FOR VALUES WITH (MODULUS [2], REMAINDER 0);

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

Drag options to blanks, or click blank then click option'

A0

B1

C3

D4

Attempts:

3 left

💡 Hint

Common Mistakes

Using modulus 4 instead of 3.

Using invalid remainder values.

The partition names use remainders 0 and 1, and modulus is 3 for both partitions.

5fill in blank

hard

Fill all three blanks to create a hash partitioned table and one partition with modulus 5 and remainder 2.

PostgreSQL

CREATE TABLE orders (
  order_id INT,
  customer_id INT
) PARTITION BY [1] (customer_id);

CREATE TABLE orders_part_[2] PARTITION OF orders
FOR VALUES WITH (MODULUS [3], REMAINDER 2);

Drag options to blanks, or click blank then click option'

Arange

Bhash

C5

D2

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'range' instead of 'hash' for partitioning.

Mixing up modulus and remainder values.

The table is hash partitioned on 'customer_id'. The partition name uses remainder 2, and modulus is 5.