Creating partitioned tables in PostgreSQL - Try It Yourself

Creating Partitioned Tables in PostgreSQL

📖 Scenario: You are managing a sales database for a retail company. The company wants to organize its sales data by year to improve query performance and maintenance. You will create a partitioned table in PostgreSQL to store sales records partitioned by year.

🎯 Goal: Create a partitioned table called sales partitioned by the sale_year column. Then create two partitions for the years 2023 and 2024.

📋 What You'll Learn

Create a parent table sales partitioned by RANGE on sale_year

Create a partition table sales_2023 for sales in the year 2023

Create a partition table sales_2024 for sales in the year 2024

Each partition must inherit from the parent sales table

Use correct RANGE boundaries for each partition

💡 Why This Matters

🌍 Real World

Partitioned tables help manage large datasets by splitting data into smaller, manageable parts. This improves query speed and maintenance.

💼 Career

Database administrators and backend developers often use partitioning to optimize performance and scalability of databases.

1

Create the parent partitioned table

Write a SQL statement to create a table called sales with columns id (integer), product (text), amount (numeric), and sale_year (integer). Make this table partitioned by RANGE on the sale_year column.

PostgreSQL

-- Your code here to create the partitioned table sales

Hint

Use CREATE TABLE with PARTITION BY RANGE (sale_year) to define the parent table.

2

Create the 2023 partition

Write a SQL statement to create a partition table called sales_2023 for the year 2023. It should be a partition of sales with RANGE values from 2023 (inclusive) to 2024 (exclusive).

PostgreSQL

CREATE TABLE sales (
    id INTEGER,
    product TEXT,
    amount NUMERIC,
    sale_year INTEGER
) PARTITION BY RANGE (sale_year);

-- Your code here to create the sales_2023 partition

Hint

Use CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES FROM (2023) TO (2024).

3

Create the 2024 partition

Write a SQL statement to create a partition table called sales_2024 for the year 2024. It should be a partition of sales with RANGE values from 2024 (inclusive) to 2025 (exclusive).

PostgreSQL

CREATE TABLE sales (
    id INTEGER,
    product TEXT,
    amount NUMERIC,
    sale_year INTEGER
) PARTITION BY RANGE (sale_year);

CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM (2023) TO (2024);

-- Your code here to create the sales_2024 partition

Hint

Use CREATE TABLE sales_2024 PARTITION OF sales FOR VALUES FROM (2024) TO (2025).

4

Add a CHECK constraint to the 2023 partition

Write a SQL statement to add a CHECK constraint named chk_sale_year_2023 on the sales_2023 partition to ensure sale_year is exactly 2023.

PostgreSQL

CREATE TABLE sales (
    id INTEGER,
    product TEXT,
    amount NUMERIC,
    sale_year INTEGER
) PARTITION BY RANGE (sale_year);

CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM (2023) TO (2024);

CREATE TABLE sales_2024 PARTITION OF sales
    FOR VALUES FROM (2024) TO (2025);

-- Your code here to add the CHECK constraint on sales_2023

Hint

Use ALTER TABLE sales_2023 ADD CONSTRAINT chk_sale_year_2023 CHECK (sale_year = 2023).

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