Range partitioning by date in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is range partitioning by date in PostgreSQL?

Range partitioning by date means splitting a large table into smaller tables based on date ranges. Each partition holds rows for a specific date period, making data easier to manage and query.

beginner

How do you define a range partition on a date column in PostgreSQL?

You create a parent table with a date column and then create child tables (partitions) using the PARTITION BY RANGE clause on that date column, specifying the date ranges for each partition.

beginner

Why use range partitioning by date?

It improves query speed by scanning only relevant partitions, helps with data maintenance like archiving old data, and can improve performance for large datasets with time-based data.

intermediate

Write a simple SQL example to create a range partitioned table by date in PostgreSQL.

CREATE TABLE sales ( id SERIAL PRIMARY KEY, sale_date DATE NOT NULL, amount NUMERIC ) PARTITION BY RANGE (sale_date); CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2024-01-01'); CREATE TABLE sales_2024 PARTITION OF sales FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

intermediate

What happens if you insert a row with a date outside all defined partitions?

PostgreSQL will raise an error because the row does not fit into any existing partition. You must create a partition covering that date range or handle the data differently.

What clause is used to create a range partitioned table by date in PostgreSQL?

A PARTITION BY RANGE (date_column)

B PARTITION BY LIST (date_column)

C PARTITION BY HASH (date_column)

D PARTITION BY DATE (date_column)

Range partitioning uses PARTITION BY RANGE with the date column to split data into ranges.

If you want to partition sales data by year, which of these is a correct partition range?

A FOR VALUES IN ('2023')

B FOR VALUES FROM ('2023-01-01') TO ('2023-12-31')

C FOR VALUES HASH ('2023')

D FOR VALUES FROM ('2023-01-01') TO ('2024-01-01')

The range partition uses an inclusive start and exclusive end date, so '2024-01-01' is correct as the upper bound.

What is a benefit of range partitioning by date?

A Allows storing different data types in partitions

B Faster queries on specific date ranges

C Eliminates need for indexes

D Automatically compresses data

Range partitioning helps queries by scanning only relevant partitions for the date range.

What happens if you insert a row with a date not covered by any partition?

A The row is discarded silently

B The row is inserted into a default partition

C PostgreSQL raises an error

D The row is inserted into the first partition

PostgreSQL requires the row to fit into a partition; otherwise, it raises an error.

Which of these commands creates a partition for dates from 2024-01-01 to 2025-01-01?

A CREATE TABLE sales_2024 PARTITION OF sales FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

B CREATE TABLE sales_2024 PARTITION OF sales FOR VALUES IN ('2024');

C CREATE TABLE sales_2024 PARTITION OF sales FOR VALUES HASH ('2024');

D CREATE TABLE sales_2024 PARTITION OF sales FOR VALUES LIST ('2024');

Range partitions use FROM and TO to specify the date range.

Explain how range partitioning by date works in PostgreSQL and why it is useful.

Think about how you might organize a large calendar of events by year or month.

Describe the steps to create a range partitioned table by date in PostgreSQL.

Start with the main table, then add partitions for each date range.

Practice

1. What is the main purpose of range partitioning by date in PostgreSQL?

easy

A. To create random partitions without any order

B. To split data into parts based on date ranges for better management

C. To encrypt date columns for security

D. To combine all data into a single large table

Solution

1. Step 1: Understand range partitioning concept

   Range partitioning divides data into segments based on continuous ranges, such as dates.

2. Step 2: Identify the purpose of date-based partitioning

   Using date ranges helps organize data by time periods, improving query speed and management.

3. Final Answer:

   To split data into parts based on date ranges for better management -> Option B

4. Quick Check:

   Range partitioning by date = split data by date ranges [OK]

Hint: Range partitioning splits data by continuous date intervals [OK]

Common Mistakes:

• Thinking partitioning combines data instead of splitting
• Confusing partitioning with encryption
• Assuming partitions are random, not range-based

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

easy

A. CREATE TABLE orders (id INT, order_date DATE) PARTITION BY RANGE (order_date);

B. CREATE TABLE orders PARTITION BY RANGE (order_date) (id INT, order_date DATE);

C. CREATE TABLE orders (id INT, order_date DATE) PARTITION BY LIST (order_date);

D. CREATE TABLE orders (id INT, order_date DATE) PARTITION BY HASH (order_date);

Solution

1. Step 1: Check correct partitioning clause placement

   In PostgreSQL, PARTITION BY RANGE (column) comes after table columns definition.

2. Step 2: Identify correct partition type for date ranges

   Range partitioning is used for continuous ranges like dates, so PARTITION BY RANGE is correct.

3. Final Answer:

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

4. Quick Check:

   Syntax: columns then PARTITION BY RANGE [OK]

Hint: Define columns first, then PARTITION BY RANGE (date_column) [OK]

Common Mistakes:

• Placing PARTITION BY before columns
• Using LIST or HASH instead of RANGE for dates
• Incorrect syntax order causing errors

3. Given the following partitioned table and partitions:

CREATE TABLE sales (id INT, sale_date DATE) PARTITION BY RANGE (sale_date);
CREATE TABLE sales_2023 PARTITION OF sales FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE sales_2024 PARTITION OF sales FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

What will be the result of this query?

SELECT tableoid::regclass, * FROM sales WHERE sale_date = '2023-06-15';

medium

A. Returns rows from sales_2024 partition with sale_date '2023-06-15'

B. Returns no rows because '2023-06-15' is not in any partition

C. Returns rows from both partitions

D. Returns rows from sales_2023 partition with sale_date '2023-06-15'

Solution

1. Step 1: Identify which partition contains '2023-06-15'

   The date '2023-06-15' falls between '2023-01-01' and '2024-01-01', so it belongs to sales_2023 partition.

2. Step 2: Understand query behavior on partitioned tables

   Query on partitioned table routes to matching partition(s) based on WHERE clause; here, only sales_2023 matches.

3. Final Answer:

   Returns rows from sales_2023 partition with sale_date '2023-06-15' -> Option D

4. Quick Check:

   Date in sales_2023 range = rows from sales_2023 [OK]

Hint: Check date range to find correct partition for query [OK]

Common Mistakes:

• Choosing wrong partition based on date
• Assuming query scans all partitions
• Ignoring partition boundaries

4. You try to create a partition for a range partitioned table by date with this command:

CREATE TABLE sales_2025 PARTITION OF sales FOR VALUES FROM ('2025-01-01') TO ('2024-12-31');

What is the problem with this statement?

medium

A. The TO date is earlier than the FROM date, causing a range error

B. Partition names cannot contain numbers

C. You must specify LIST partitioning, not RANGE

D. The sales table must be dropped before adding partitions

Solution

1. Step 1: Check the FROM and TO values in partition definition

   The TO value '2024-12-31' is before the FROM value '2025-01-01', which is invalid for range partitions.

2. Step 2: Understand partition range rules

   Range partitions require FROM value to be less than TO value to define a valid range.

3. Final Answer:

   The TO date is earlier than the FROM date, causing a range error -> Option A

4. Quick Check:

   FROM must be less than TO in range partitions [OK]

Hint: FROM date must be before TO date in range partitions [OK]

Common Mistakes:

• Swapping FROM and TO dates
• Thinking partition names cannot have numbers
• Confusing range with list partitioning

5. You have a large sales table partitioned by month using range partitioning on sale_date. You want to add a new partition for March 2024. Which of the following commands correctly adds this partition?

hard

A. CREATE TABLE sales_2024_03 PARTITION OF sales FOR VALUES FROM ('2024-03-01') TO ('2024-03-31');

B. CREATE TABLE sales_2024_03 PARTITION OF sales FOR VALUES FROM ('2024-02-28') TO ('2024-03-31');

C. CREATE TABLE sales_2024_03 PARTITION OF sales FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

D. CREATE TABLE sales_2024_03 PARTITION OF sales FOR VALUES FROM ('2024-03-01') TO ('2024-03-30');

Solution

1. Step 1: Understand range partition boundaries for months

   Range partitions use inclusive FROM and exclusive TO, so March 2024 is from '2024-03-01' up to but not including '2024-04-01'.

2. Step 2: Check each option's date range correctness

   CREATE TABLE sales_2024_03 PARTITION OF sales FOR VALUES FROM ('2024-03-01') TO ('2024-04-01'); correctly uses FROM '2024-03-01' TO '2024-04-01'. Options B, C, and D have incorrect boundaries that either overlap or exclude days.

3. Final Answer:

   CREATE TABLE sales_2024_03 PARTITION OF sales FOR VALUES FROM ('2024-03-01') TO ('2024-04-01'); -> Option C

4. Quick Check:

   Range partitions: FROM inclusive, TO exclusive [OK]

Hint: Use TO date as first day of next month for monthly partitions [OK]

Common Mistakes:

• Using TO date as last day of month (should be exclusive)
• Overlapping partition ranges
• Using incorrect FROM dates