Why partitioning is needed in PostgreSQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a partitioned table by specifying the partition method.

PostgreSQL

CREATE TABLE sales_data (id INT, sale_date DATE, amount NUMERIC) PARTITION BY [1] (sale_date);

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

ALIST

BINDEX

CRANGE

DHASH

Attempts:

3 left

💡 Hint

Common Mistakes

Using INDEX partitioning which is not supported in PostgreSQL.

Confusing HASH with RANGE for date columns.

The RANGE partition method divides data into ranges, which is common for date columns like sale_date.

2fill in blank

medium

Complete the code to create a partition for sales_data for the year 2023.

PostgreSQL

CREATE TABLE sales_2023 PARTITION OF sales_data FOR VALUES FROM ('2023-01-01') TO ([1]);

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

A'2024-01-01'

B'2022-12-31'

C'2023-12-31'

D'2023-06-30'

Attempts:

3 left

💡 Hint

Common Mistakes

Using the last day of the year as upper bound which is inclusive.

Using a date before the partition start date.

The upper bound for the partition should be the start of the next range, so '2024-01-01' is correct.

3fill in blank

hard

Complete the code to create a partition for sales_data for the year 2024.

PostgreSQL

CREATE TABLE sales_2024 PARTITION OF sales_data FOR VALUES FROM ('2024-01-01') TO ([1]);

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

A'2024-12-31'

B'2023-12-31'

C'2024-06-30'

D'2025-01-01'

Attempts:

3 left

💡 Hint

Common Mistakes

Using the last day of the year as upper bound which is inclusive.

Using a date before the partition start date.

The upper bound for the partition should be the start of the next range, so '2025-01-01' is correct.

4fill in blank

hard

Fill both blanks to complete the query that selects sales data only from the 2023 partition.

PostgreSQL

SELECT * FROM sales_data WHERE sale_date [1] '2023-01-01' AND sale_date [2] '2024-01-01';

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

A>=

B<

C<=

D>

Attempts:

3 left

💡 Hint

Common Mistakes

Using <= for the upper bound which includes the next year.

Using < for the lower bound which excludes the first day.

The query selects sales from January 1, 2023 (inclusive) up to but not including January 1, 2024, so use >= and <.

5fill in blank

hard

Fill all three blanks to create a partitioned table by list and add a partition for region 'North'.

PostgreSQL

CREATE TABLE customer_data (id INT, region TEXT) PARTITION BY [1] (region);
CREATE TABLE customer_north PARTITION OF customer_data FOR VALUES IN ([2]);
INSERT INTO customer_north (id, region) VALUES (1, [3]);

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

ALIST

B'North'

DRANGE

Attempts:

3 left

💡 Hint

Common Mistakes

Using RANGE partitioning for discrete values.

Not quoting string values in the partition and insert.

List partitioning is used for discrete values like regions. The partition is created for the value 'North', and the insert uses the same string.

Practice

1. Why is partitioning used in PostgreSQL databases?

easy

A. To combine multiple small tables into one big table

B. To split large tables into smaller, manageable parts for faster queries

C. To encrypt data automatically for security

D. To create backups of the database

Solution

1. Step 1: Understand the purpose of partitioning

   Partitioning divides a large table into smaller pieces called partitions.

2. Step 2: Recognize the benefit of partitioning

   This division helps speed up queries and makes data easier to manage.

3. Final Answer:

   To split large tables into smaller, manageable parts for faster queries -> Option B

4. Quick Check:

   Partitioning = splitting big tables for speed [OK]

Hint: Partitioning breaks big tables into smaller parts [OK]

Common Mistakes:

• Thinking partitioning combines tables instead of splitting
• Confusing partitioning with encryption
• Assuming partitioning is for backups

2. Which of the following is the correct syntax to create a range partitioned table in PostgreSQL?

easy

A. CREATE TABLE sales (id INT, sale_date DATE) PARTITION BY RANGE (sale_date);

B. CREATE TABLE sales PARTITION BY RANGE (sale_date) (id INT, sale_date DATE);

C. CREATE PARTITIONED TABLE sales (id INT, sale_date DATE) BY RANGE (sale_date);

D. CREATE TABLE sales (id INT, sale_date DATE) PARTITION ON RANGE (sale_date);

Solution

1. Step 1: Recall PostgreSQL partition syntax

   The correct syntax places PARTITION BY RANGE after the column definitions.

2. Step 2: Match syntax with options

   CREATE TABLE sales (id INT, sale_date DATE) PARTITION BY RANGE (sale_date); correctly uses: CREATE TABLE ... (columns) PARTITION BY RANGE (column);

3. Final Answer:

   CREATE TABLE sales (id INT, sale_date DATE) PARTITION BY RANGE (sale_date); -> Option A

4. Quick Check:

   Partition syntax = columns then PARTITION BY [OK]

Hint: PARTITION BY RANGE comes after columns in CREATE TABLE [OK]

Common Mistakes:

• Placing PARTITION BY before columns
• Using PARTITION ON instead of PARTITION BY
• Using CREATE PARTITIONED TABLE which is invalid

3. Given a table orders partitioned by range on order_date, what will the query below return?

SELECT count(*) FROM orders WHERE order_date < '2023-01-01';

medium

A. Count of all orders before 2023-01-01 from all relevant partitions

B. Count of orders only from the first partition

C. Syntax error due to partitioning

D. Count of all orders ignoring the date filter

Solution

1. Step 1: Understand partition pruning in PostgreSQL

   PostgreSQL automatically checks only partitions that can contain rows matching the WHERE condition.

2. Step 2: Analyze the query effect

   The query counts rows with order_date before 2023-01-01 across all relevant partitions.

3. Final Answer:

   Count of all orders before 2023-01-01 from all relevant partitions -> Option A

4. Quick Check:

   Partition pruning returns matching rows only [OK]

Hint: Partition pruning counts only matching partitions [OK]

Common Mistakes:

• Thinking query counts only first partition
• Assuming syntax error due to partitioning
• Ignoring WHERE clause and counting all rows

4. You created a partitioned table but your queries are slow. Which of the following is a likely cause?

medium

A. You forgot to create partitions for the table

B. You used too many partitions

C. You used the wrong data type for the partition key

D. You did not create indexes on the partitions

Solution

1. Step 1: Identify common performance issues with partitioning

   Indexes on partitions speed up queries; missing them slows queries.

2. Step 2: Evaluate options

   You did not create indexes on the partitions correctly points out missing indexes as a cause of slow queries.

3. Final Answer:

   You did not create indexes on the partitions -> Option D

4. Quick Check:

   Missing indexes = slow queries [OK]

Hint: Create indexes on partitions for faster queries [OK]

Common Mistakes:

• Assuming missing partitions cause slow queries (usually error instead)
• Thinking wrong data type always slows queries
• Believing too many partitions always slow queries

5. You have a large logs table with millions of rows. You want to improve query speed for recent logs and easily drop old logs. Which partitioning strategy is best?

hard

A. No partitioning, just add indexes

B. Hash partitioning by log message content

C. Range partitioning by log date, creating monthly partitions

D. List partitioning by log severity levels

Solution

1. Step 1: Understand the data and goals

   Logs are time-based; queries focus on recent data and dropping old data is needed.

2. Step 2: Choose partitioning strategy

   Range partitioning by date with monthly partitions allows fast queries on recent logs and easy removal of old partitions.

3. Final Answer:

   Range partitioning by log date, creating monthly partitions -> Option C

4. Quick Check:

   Time-based data = range partitioning [OK]

Hint: Use range partitions by date for time-based data [OK]

Common Mistakes:

• Choosing hash partitioning for time-based queries
• Using list partitioning on severity which doesn't help date queries
• Skipping partitioning and relying only on indexes