Creating partitioned tables in PostgreSQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a partitioned table by range on the column 'created_date'.

PostgreSQL

CREATE TABLE orders (
  order_id SERIAL,
  created_date DATE NOT NULL,
  amount NUMERIC(10, 2) NOT NULL,
  PRIMARY KEY (created_date, order_id)
) PARTITION BY [1] (created_date);

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

Acolumn

Blist

Chash

Drange

Attempts:

3 left

💡 Hint

Common Mistakes

Using PARTITION BY LIST instead of RANGE.

Using PARTITION BY COLUMN which is not valid syntax.

The PARTITION BY RANGE clause creates partitions based on ranges of values in the specified column.

2fill in blank

medium

Complete the code to create a partition for the 'orders' table for dates before 2023-01-01.

PostgreSQL

CREATE TABLE orders_2022 PARTITION OF orders
FOR VALUES FROM (MINVALUE) [1] ('2023-01-01');

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

ABEFORE

BTO

CIN

DFROM

Attempts:

3 left

💡 Hint

Common Mistakes

Using FROM instead of TO for upper bound.

Using IN which is for list partitions.

The FOR VALUES FROM (MINVALUE) TO clause defines the upper bound for the range partition.

3fill in blank

hard

Fix the error in the partition creation by choosing the correct keyword for the range start.

PostgreSQL

CREATE TABLE orders_2023 PARTITION OF orders
FOR VALUES [1] ('2023-01-01') TO ('2024-01-01');

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

AFROM

BIN

CTO

DBETWEEN

Attempts:

3 left

💡 Hint

Common Mistakes

Using IN which is for list partitions.

Using TO instead of FROM for the lower bound.

The FROM keyword specifies the lower bound of the range partition.

4fill in blank

hard

Fill both blanks to create a list partition for the 'orders' table for specific regions.

PostgreSQL

CREATE TABLE orders (
  order_id SERIAL,
  region TEXT NOT NULL,
  amount NUMERIC(10, 2) NOT NULL,
  PRIMARY KEY (region, order_id)
) PARTITION BY [1] (region);

CREATE TABLE orders_us PARTITION OF orders
FOR VALUES [2] ('US', 'Canada');

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

Alist

Brange

CIN

DTO

Attempts:

3 left

💡 Hint

Common Mistakes

Using RANGE instead of LIST for specific values.

Using TO instead of IN for list values.

PARTITION BY LIST is used to partition by specific values, and FOR VALUES IN specifies those values.

5fill in blank

hard

Fill all three blanks to create a hash partitioned table with 4 partitions.

PostgreSQL

CREATE TABLE users (
  user_id SERIAL PRIMARY KEY,
  username TEXT NOT NULL
) PARTITION BY [1] (user_id);

CREATE TABLE users_part_1 PARTITION OF users FOR VALUES WITH ([2] 4, [3] 0);

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

AHASH

BREMAINDER

DMODULUS

Attempts:

3 left

💡 Hint

Common Mistakes

Using RANGE or LIST instead of HASH.

Confusing MODULUS and REMAINDER keywords.

PARTITION BY HASH partitions data by hashing the column. The MODULUS defines the number of partitions, and REMAINDER specifies the partition number.

Practice

1. What is the main purpose of creating partitioned tables in PostgreSQL?

easy

A. To split a large table into smaller, manageable parts based on a column

B. To create multiple copies of the same table for backup

C. To combine several tables into one large table

D. To encrypt the data in a table for security

Solution

1. Step 1: Understand partitioning concept

   Partitioned tables divide a big table into smaller parts based on a column value, improving management and query performance.

2. Step 2: Compare options

   Backup, merging several tables, and encryption are not related to partitioning.

3. Final Answer:

   To split a large table into smaller, manageable parts based on a column -> Option A

4. Quick Check:

   Partitioned tables split big tables = A [OK]

Hint: Partitioning means splitting big tables by column values [OK]

Common Mistakes:

• Confusing partitioning with backup or encryption
• Thinking partitioning merges tables instead of splitting
• Assuming partitioning duplicates data

2. Which of the following is the correct syntax to create a partitioned table by range on column created_date?

easy

A. CREATE TABLE orders PARTITION BY LIST (created_date);

B. CREATE TABLE orders PARTITION ON RANGE (created_date);

C. CREATE TABLE orders PARTITION BY HASH (created_date);

D. CREATE TABLE orders PARTITION BY RANGE (created_date);

Solution

1. Step 1: Recall partition syntax

   PostgreSQL uses PARTITION BY RANGE (column) to create range partitions.

2. Step 2: Check options

   CREATE TABLE orders PARTITION BY RANGE (created_date); uses correct syntax. CREATE TABLE orders PARTITION ON RANGE (created_date); uses wrong keyword 'PARTITION ON'. PARTITION BY LIST and PARTITION BY HASH use different partition types.

3. Final Answer:

   CREATE TABLE orders PARTITION BY RANGE (created_date); -> Option D

4. Quick Check:

   Correct syntax uses PARTITION BY RANGE [OK]

Hint: Use PARTITION BY RANGE (column) for range partitions [OK]

Common Mistakes:

• Using PARTITION ON instead of PARTITION BY
• Mixing partition types (LIST or HASH) when RANGE is needed
• Omitting parentheses around column name

3. Given the following commands, what will be the result of querying SELECT * FROM sales WHERE sale_year = 2023;?

CREATE TABLE sales (
  id INT,
  sale_year INT,
  amount NUMERIC
) PARTITION BY LIST (sale_year);

CREATE TABLE sales_2022 PARTITION OF sales FOR VALUES IN (2022);
CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES IN (2023);

INSERT INTO sales VALUES (1, 2022, 100), (2, 2023, 200), (3, 2023, 300);

medium

A. Rows with id 2 and 3 will be returned

B. Rows with id 1 and 2 will be returned

C. Only row with id 1 will be returned

D. No rows will be returned

Solution

1. Step 1: Understand partitioning by LIST on sale_year

   Table sales is partitioned by sale_year with partitions for 2022 and 2023.

2. Step 2: Analyze inserted data and query

   Rows with sale_year 2023 have ids 2 and 3. Query filters sale_year = 2023, so these rows are returned.

3. Final Answer:

   Rows with id 2 and 3 will be returned -> Option A

4. Quick Check:

   Query filters sale_year=2023, returns matching rows [OK]

Hint: Query returns rows matching partition values [OK]

Common Mistakes:

• Assuming all rows return regardless of partition
• Confusing partition column with other columns
• Forgetting to insert data into partitions

4. Identify the error in the following partition creation commands:

CREATE TABLE logs (
  id SERIAL,
  log_date DATE
) PARTITION BY RANGE (log_date);

CREATE TABLE logs_2023 PARTITION OF logs FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

medium

A. Partitioning by RANGE is not allowed on DATE columns

B. Missing PRIMARY KEY on the parent table

C. The TO value should be '2024-01-01' to include all 2023 dates

D. FOR VALUES clause should use LIST instead of RANGE

Solution

1. Step 1: Check RANGE partition boundaries

   RANGE partitions include values from FROM (inclusive) up to TO (exclusive). To cover all 2023 dates, TO must be '2024-01-01'.

2. Step 2: Analyze given TO value

   TO is '2023-12-31', which excludes that date and any after. This causes missing data for 2023-12-31.

3. Final Answer:

   The TO value should be '2024-01-01' to include all 2023 dates -> Option C

4. Quick Check:

   RANGE TO is exclusive, so use next day [OK]

Hint: RANGE TO value is exclusive; use next day after range end [OK]

Common Mistakes:

• Using inclusive TO value in RANGE partitions
• Thinking RANGE partitioning disallows DATE columns
• Confusing LIST and RANGE partition syntax

5. You want to create a partitioned table events partitioned by HASH on user_id with 4 partitions. Which set of commands correctly creates the table and its partitions?

hard

A. CREATE TABLE events (id INT, user_id INT) PARTITION BY HASH (user_id);
CREATE TABLE events_p0 PARTITION OF events FOR VALUES IN (0);
CREATE TABLE events_p1 PARTITION OF events FOR VALUES IN (1);
CREATE TABLE events_p2 PARTITION OF events FOR VALUES IN (2);
CREATE TABLE events_p3 PARTITION OF events FOR VALUES IN (3);

B. CREATE TABLE events (id INT, user_id INT) PARTITION BY HASH (user_id);
CREATE TABLE events_p0 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE events_p1 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE events_p2 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE events_p3 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 3);

C. CREATE TABLE events (id INT, user_id INT) PARTITION BY LIST (user_id);
CREATE TABLE events_p0 PARTITION OF events FOR VALUES IN (0);
CREATE TABLE events_p1 PARTITION OF events FOR VALUES IN (1);
CREATE TABLE events_p2 PARTITION OF events FOR VALUES IN (2);
CREATE TABLE events_p3 PARTITION OF events FOR VALUES IN (3);

D. CREATE TABLE events (id INT, user_id INT) PARTITION BY RANGE (user_id);
CREATE TABLE events_p0 PARTITION OF events FOR VALUES FROM (0) TO (1);
CREATE TABLE events_p1 PARTITION OF events FOR VALUES FROM (1) TO (2);
CREATE TABLE events_p2 PARTITION OF events FOR VALUES FROM (2) TO (3);
CREATE TABLE events_p3 PARTITION OF events FOR VALUES FROM (3) TO (4);

Solution

1. Step 1: Understand HASH partition syntax

   HASH partitions require FOR VALUES WITH (MODULUS n, REMAINDER r) to define partitions.

2. Step 2: Check each option

   CREATE TABLE events (id INT, user_id INT) PARTITION BY HASH (user_id); CREATE TABLE events_p0 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 0); CREATE TABLE events_p1 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 1); CREATE TABLE events_p2 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 2); CREATE TABLE events_p3 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 3); correctly uses HASH partitioning with modulus 4 and remainders 0 to 3. CREATE TABLE events (id INT, user_id INT) PARTITION BY HASH (user_id); CREATE TABLE events_p0 PARTITION OF events FOR VALUES IN (0); CREATE TABLE events_p1 PARTITION OF events FOR VALUES IN (1); CREATE TABLE events_p2 PARTITION OF events FOR VALUES IN (2); CREATE TABLE events_p3 PARTITION OF events FOR VALUES IN (3); uses LIST syntax incorrectly. CREATE TABLE events (id INT, user_id INT) PARTITION BY LIST (user_id); CREATE TABLE events_p0 PARTITION OF events FOR VALUES IN (0); CREATE TABLE events_p1 PARTITION OF events FOR VALUES IN (1); CREATE TABLE events_p2 PARTITION OF events FOR VALUES IN (2); CREATE TABLE events_p3 PARTITION OF events FOR VALUES IN (3); uses LIST partitioning, not HASH. CREATE TABLE events (id INT, user_id INT) PARTITION BY RANGE (user_id); CREATE TABLE events_p0 PARTITION OF events FOR VALUES FROM (0) TO (1); CREATE TABLE events_p1 PARTITION OF events FOR VALUES FROM (1) TO (2); CREATE TABLE events_p2 PARTITION OF events FOR VALUES FROM (2) TO (3); CREATE TABLE events_p3 PARTITION OF events FOR VALUES FROM (3) TO (4); uses RANGE partitioning, not HASH.

3. Final Answer:

   The commands using PARTITION BY HASH (user_id) with FOR VALUES WITH (MODULUS 4, REMAINDER 0-3) -> Option B

4. Quick Check:

   HASH partitions use MODULUS and REMAINDER [OK]

Hint: HASH partitions use MODULUS and REMAINDER in FOR VALUES WITH clause [OK]

Common Mistakes:

• Using FOR VALUES IN instead of FOR VALUES WITH for HASH
• Mixing partition types (LIST or RANGE) with HASH
• Omitting modulus or remainder values