Partitioning best practices in PostgreSQL - Interactive Code 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 (id SERIAL PRIMARY KEY, created_date DATE, amount NUMERIC) PARTITION BY [1] (created_date);

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

Arange

Blist

Chash

Dcolumn

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'list' instead of 'range' for date ranges.

Using 'hash' which is for distributing data evenly but not by ranges.

The range partitioning method is used to divide data based on ranges of values, such as dates.

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 [1] ('2023-01-01');

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

AFROM

BBEFORE

CTO

DIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'FROM' instead of 'TO' for the upper bound.

Using 'IN' which is for list partitions.

The TO keyword defines the upper bound for a range partition.

3fill in blank

hard

Fix the error in the partition creation statement by choosing the correct partition method.

PostgreSQL

CREATE TABLE sales PARTITION BY [1] (region);

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

Alist

Brange

Chash

Dpartition

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'range' for categorical data like regions.

Using invalid partition method names.

The list partitioning method is used to divide data by discrete values like regions.

4fill in blank

hard

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

PostgreSQL

CREATE TABLE logs (id SERIAL, event_time TIMESTAMP) PARTITION BY [1] (id);

CREATE TABLE logs_part_1 PARTITION OF logs FOR VALUES WITH ([2] 4, REMAINDER 0);

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

Ahash

Blist

CMODULUS

DRANGE

Attempts:

3 left

💡 Hint

Common Mistakes

Using RANGE or LIST keywords with hash partitioning.

Mixing partition methods and partition value clauses.

Hash partitioning uses the MODULUS keyword to specify partitions based on hash values.

5fill in blank

hard

Fill all three blanks to create a range partition for the 'events' table for dates from 2023-01-01 to 2023-06-30.

PostgreSQL

CREATE TABLE events (id SERIAL, event_date DATE) PARTITION BY [1] (event_date);

CREATE TABLE events_h1_2023 PARTITION OF events FOR VALUES FROM ([2]) TO ([3]);

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

Arange

B'2023-01-01'

C'2023-06-30'

Dlist

Attempts:

3 left

💡 Hint

Common Mistakes

Using list partitioning instead of range.

Forgetting quotes around date literals.

Range partitioning uses FROM and TO with date values to define partitions.